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 or you want to use RCSI
- Maybe there’s a lot of PAGEIOLATCH waits and there’s not many nonclustered indexes or you don’t have enough RAM
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, CPU time, and memory grants. In a way, that’s a lot easier.
For a whole server, you need to understand the problems, and how SQL Server surfaces them.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.