Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Aug 10 '12 at 0:25

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
1  
+1 the pecking order is driven by observation and measurement of bottleneck, which is exactly what the paper teaches how to identify. –  Remus Rusanu Aug 10 '12 at 8:57

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.

share|improve this answer

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.

share|improve this answer
2  
I would be very careful about removing indexes prior to ensuring they are not referenced in code via the INDEX = someindexhere query hint. Also, randomly removing indexes can have a very negative effect on performance. –  Max Vernon Sep 5 '12 at 20:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.