The Metrics That Matter For Performance Tuning

Generality vs. Specificity

If you’re stepping up to a SQL Server you’ve never seen before, you’re probably only armed with what people tell you the problem is.

Sometimes they’re right, more often they’re wrong. Even more often they’re just too vague, and it falls back to you to figure out exactly what’s “slow”.

 

As you start digging in, you’ll start noticing things you want to change, and building a mental case for why you want to change them:

  • Maybe the CX waits will be really high, and no one has changed MAXDOP and CTFP from defaults
  • Maybe there’s a lot of locking waits, and there’s a lot of overlapping indexes
  • Maybe there’s a lot of PAGEIOLATCH waits and there’s not many nonclustered indexes

Those are good general patterns to watch out for, and while there may be regressions in some places, you’re likely to make the server a better place overall.

Sometimes you’ll get handed a query to tune.

Or more realistically, you get handed a few-thousand line stored procedure to tune.

It probably calls other stored procedures, too.

Your job, one way or another, is to reduce the length of time between hitting F5 and having it complete.

For different sets of parameters.

Things That Change

In a perfect world, you’d change one variable (this could be a setting, and index, the way a query is written, or an actual variable being passed in), and see how the metrics you care about change.

But that hardly ever happens, does it?

You’ll probably:

  • Change a few settings you’re confident about
  • Deduplicate a bunch of indexes for a table at a time
  • Adjust a bunch of things in a stored procedure as you scroll through
  • Fix some Heaps all together

Look, it’s okay. You might need to be really effective and make a lot of progress quickly. Not everyone has weeks or months to make incremental changes towards better performance.

But you need to be aware of which metrics you’re hoping to improve when you make a change, and you need to be sure that the changes you make can actually make it wiggle.

For a query, it’s likely just a mix of elapsed time, and reads. In a way, that’s a lot easier.

For a whole server, you need to understand the problems, and how SQL Server surfaces them.

If you need help with that, drop me a line!

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *