SQL Server Performance Case Studies

This page is devoted to actual SQL Server performance tuning and optimization case studies. If you would like to contribute your own experience, please e-mail webmaster@SQL-Server-Performance.Com.

*****

Our company runs an ERP program, which uses SQL Server as its back-end. We have been running the application for about 6 months, and recently a user called, telling me that a particular operation was taking over 10 minutes to complete.

I was able to duplicate the problem from the application, so my first step to finding out what caused the problem was to run a trace in Profiler. This easily identified the guilty query.

I cut the text of the slow-performing query from the trace window and pasted it into the Query Analyzer, then ran the "Perform Index Analysis" option. This resulted in the suggestion that I add a new index to the table the query was hitting.

I added the index and then re-ran the operation from the application. Now, instead of taking over 10 minutes to run, the operation now ran in about 5 seconds.

Any time you add an index, you always raise the possibility that the addition of the new index will slow down INSERT, UPDATE, and DELETE operations on OLTP databases. Because of this, I closely examined the table that needed the new index, but I couldn't see any obvious problems that would be caused by the adding the index. 

It is not often than resolving performance problems is this easy, but sometimes you luck out. [7.0, 2000, 2005] Added 9-8-2000

*****

Recently, I was noticing what appeared to be a network-related performance problem. I discovered it when I copied a database backup from our production server to our standby server. The database, which was about 400MB, took over an hour to copy from the production server to the backup server. Obviously there was a problem. I hadn't heard about any performance problems from my users (yet), but the server was new and wasn't used very heavily at that time.

I got in contact with our hardware technical staff. They did a trace of the network traffic, which indicated that there were a lot of network errors originating from the server's network card.

I suggested that we take a look at the network hardware and drivers, which was done by one of the technical staff. And what do you know, they found not one, but several problems. One, the production server had two NICS, one of which had an incorrect default gateway address, and both cards were running a generic version of the NIC driver, not the IBM-approved NIC driver. Once both problems were fixed, performance went back to normal.

This is a good example of how hardware you rarely think about can affect SQL Server's performance. [7.0, 2000, 2005] Added 9-8-2000

*****

When I first started as a DBA with my current company, I was asked to investigate why a particular internally written VB application was running slow. What the VB application did was to go to a SQL Server database, summarize some data, and then write the summarized data to a DB2 database running on an IBM mainframe. The program was run periodically, perhaps summarizing as many as 10,000 records at a time. The problem was that the process ran for hours.

To help figure out what was going on, I did two things. First, I actually examined the VB source code to see how it was written and to see how SQL Server was accessed from it. I also ran a Profiler trace on the application as it was running.

When I was done, I had found many performance-related problems, not just one or two. Some of the problems I found included:

·         All database access and modification was done through ADO methods, not stored procedures.

·         The VB code created a very slow server-side cursor, instead of accessing all the data at once as a recordset operation.

·         The CacheSize property was set to default, which is very slow. It needed to be increased.

·         The VB code used unbound columns, which incurs unnecessary overhead, especially in the loops found in the program.

·         One of the key SELECT statements had an unsargable WHERE clause, forcing a table scan of the data, slowing down access.

As a result of my analysis and findings, the application was currently rewritten using my recommendations, and after the rewrite, performance increased dramatically. [7.0, 2000, 2005] Added 9-8-2000

*****

I ran into a strange SQL Server performance-related problem. Normally, one of the SQL Server's I monitor runs at about 3-10% total CPU utilization. But I noticed that all of a sudden that the SQL Server was running at about 35% utilization as the minimum amount, and then it would spike higher than that on occasion. It seemed as if some process was chewing up 35% of the CPU on a constant basis. This lasted well over an hour as I was investigating it.

My first thought was to use Enterprise Manager to view the current activity, but this showed nothing unusual. I also used the Performance Monitor to check which process was causing the CPU usage, thinking it might be something else on the server causing the problem. When I checked, I found that it was the SQL Server service causing the problem.

The next thing I tried was to watch the individual threads running on the server using Performance Monitor. Here, I was able to identify that a particular thread that seemed to be causing the problem.

I then matched this thread to a SPID running in the activity window of Enterprise Manager. What was strange was that this SPID seemed to be inactive.

I contacted the user who was represented by the SPID and asked him to disconnect from SQL Server. He was connecting to SQL Server using Crystal Reports development software.

Once the user disconnected, the CPU activity did not die immediately. Instead, it stayed the same for about a minute, then the CPU on the server shot way up to 100% (on a 4 CPU server), then went down, all over a period of about 30 seconds. Then the CPU utilization went to normal on the server.

I still don't know what caused this problem, or if what I did helped, or was a coincidence, but I will be watching closely to see if it happens again. [7.0, 2000, 2005] Added 9-8-2000