Generally speaking, what pecking order should be used when tuning SQL Server?

Question

When tuning SQL Server, what generally is the pecking order to use when trying to decide what to tune first? I'm asking in regards to an existing database that would I would have little ability to redesign.

For example, my first thought is to tune clustered indexes first, then nonclustered indexes, and maybe check constraints. Partitioning of tables and indexes would be in there somewhere. I expect that I am missing many items that could go on this list.

Answer 1

I like to follow the Performance Tuning by Waits and Queues methodology introduced by SQLCAT team member Tom Davidson.

You can download and read his seminal white paper from here.

SQLCAT is Microsoft's SQL Customer Advisory Team - the folks who deal with the most demanding, largest SQL Server customers and thus have unique insights into some of the biggest installations and how to make them fly.

Basically, SQL Server collects a lot of statistical data on why and what it waits for. Collecting and interpreting that information can lead you to perf tune in the right spot and fixing the right issues, instead of just bumbling around and not knowing where to start and what to do.

Read that whitepaper! It's really very enlightening!

And of course - you'll need to establish a baseline first (e.g. have a set of queries - if ever possible a reasonable and realistic "workload" from your system in production - and measure how they perform) and then you need to tweak something (based on the insights from collecting and interpreting the wait stats) and then you need to measure again to see if that tweak help anything at all - or not.

Answer 2

For most systems start with the nonclustered indexes. Making changes to clustered indexes can get very tricky very fast.

Partitioning shouldn't be considered unless you are hundreds of millions of rows or more.

Answer 3

Order-of-operations for performance tuning SQL Server depends on many factors, such as:

• Is the database used for reporting, or is it very transactional?
• What performance issues are apparent?

The easiest first thing to do is drop indexes that are not being used - there is a cost to maintain them, and if they are not being used, you are wasting resources. That info is available by looking at SELECT * FROM sys.dm_db_index_usage_stats.