SQL Server Troubleshooting

How to Narrow Down Your Performance Problem

First determine the source of the problem.

It is often just a handful of queries that make the whole system seem slow.

Run Performance Monitor, SQL Profiler, and the blocking script to capture what is occurring when you encounter the problem. It is important that they are run at the same time so that you can cross-reference the information.

These logs must be started BEFORE the problem occurs in order to capture the information about what leads up to the problem.

SQL Profiler

SQL 7.0

Open SQL Profiler (Start.Programs.SQL Server 7.0.Profiler)
Choose File.ImportTrace
Choose the file
When the "Trace Properties" window opens
Give the trace a name
Change the "SQL Server" to your ServerName
Make sure you have permissions to write to the location in "Capture to File"
Click on "OK" to start the trace
Run your query
Stop the trace

SQL 2000

Open SQL Profiler (Start.Programs.Microsoft SQL Server.Profiler)
Choose File.NewTrace
Logon to your server
Click on the folder icon to the right of "Template File Name" and choose the file
Choose to save to a file
Set the maximum file size to 20MB or a value you feel is appropriate
Check "enable file rollover"
Run the trace
Run your query
Stop the trace

Blocking

Create and run the stored procedure from the article that corresponds to your version of SQL Server:
Q251175 INF: How to Monitor SQL Server 6.5 Blocking
Q251004 INF: How to Monitor SQL Server 7.0 Blocking
Q271509 INF: How to Monitor SQL Server 2000 Blocking

Performance Monitor (PerfMon or SysMon)

Make sure "diskperf -y" has been run from a command prompt at some point so that disk counters will work.
Save the results to a .blg or .log file. This is a LOG file, not a CHART file.
Capture all counters.
Set the interval to no less than 15 seconds. For a long term log, use a 1 or 2 minute interval.

Analyze your logs

SQL Profiler

Look at your Profiler output. Generally you can sort by duration and find the longest running queries to concentrate on. If the file is extremely large or you want to do complex queries against it, open the saved log and save it as a table. Note that you don't want to save the file directly to a table while tracing as this can cause performance problems.

Take the long running queries and run them from Query Analyzer with these settings:

SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
-- Insert your query here --

Check for table or clustered index scans, warnings such as missing statistics, etc.

Blocking

Check your blocker script output for 'head of chain' spids. If you find any, check to see if they are long lasting. Some short term blocking is normal in your system. If the blocking lasts more than a couple of seconds then use the spids to match with the spids in Profiler to find out which query was executed to hold the blocking lock. Use this information to find the source of the problem.

Performance Monitor (PerfMon)

In most cases you can ignore spikes in the counters. If you see high values in a particular counter at a certain time, see if the counter is being influenced by SQL Server or by outside factors such as backup software, virus scanners, etc. If it is definitely tied to SQL Server, you can match the time span for the counter to queries executed in Profiler during that timeframe and work on tuning them.

Implement Changes

Test your changes on a representative test system. This means similar hardware, configuration, data, statistics, etc. Then schedule a rollout on your production system. Create new logs to track the improvement made by implementing the changes. Use these logs, along with documentation regarding data size, system load, etc., as a new baseline for future performance comparisons.

SQL Server Directory

How to Troubleshoot a Performance Problem
Troubleshooting Blocking
How to Move a Database
Get the Latest Service Pack
Misc. Tips and Tools
SQL Server Knowledge Base Articles
Common SQL Server Answers
SQL Server and Related Links