Troubleshooting SQL Server Performance

If you ever need a refresher on Troubleshooting SQL Server Performance, or need to point your colleagues in the right direction, check out this great article by Gail Shaw.  In it, Gail runs through the entire process of capturing the server’s workload, processing the results, identifying issues, analyzing the underlying problems, implementing solutions, and reviewing the improvements.

Gail starts by creating a trace in SQL Profiler, which she uses in a server-side trace against the production machine in order to capture the workload (see Grant Fritchey’s post on the Profiler GUI to learn why the server-side trace is always preferred).  She captures the trace data in a table and analyzes it to find the worst performers.  Once she has that information, she walks through using SQL Management Studio to review the execution plans and statistics IO of the queries involved.  She goes into a good amount of detail when reviewing the execution plans, discussing clustered index scans, clustered index seeks, hash matches, nested loops, bookmark lookups, key lookups, logical reads, and CPU time, etc. – with a look at what’s good, what’s bad, and how to make improvements (from query changes to clustered indexes to non-clustered indexes, with and without included columns).  And, she concludes it all by revisiting her initial trace with the performance changes in place.  All in all, it is great intro (or review) on how to perform SQL performance tuning.

And… for a primer on indexing and column selectivity, review parts 1 and 2 from this blog on Index Columns And Selectivity.


Comments are closed.

Rss Feed