The Fox
Interpreting query plans and problems has gotten a lot easier over the years. Especially with more actuals getting added to actual plans. A lot of the ambiguity about where time and effort was spent in a query plan is gone.
Taking the guesswork out of performance tuning is awesome! The faster I can tune a query for someone, the better. And boy howdy, sometimes I get some awfully big query plans to look at.
But once you know what was slow, what do you do with it? How do you make it not slow?
The Hound
There are the “obvious” things, which we’ve talked about earlier in the series. They’re a good place to start.
There’s other stuff too, which we’ll talk about in detail next week. Normally I’d feel like a broken record, but having all of my fundamentals stuff in one set of posts seems like a reasonable idea. I think that’s the point of all this, anyway.
It’s stuff that isn’t surprising, if you’ve been around the block a little tuning queries. But T-SQL anti-patterns are like a bad haircut. Sometimes you don’t believe you have one until someone tells you.
A lot of the time I spend with clients is showing them how seemingly harmless choices are wrecking performance. Someone a long time ago read about this ~great new feature~ and used it everywhere and now the whole show is slumping towards catastrophe.
Thousand Dollars
This is another place where actual plans tell you way more than estimated or cached plans, but if you know enough about what to look for in a query plan you can find The Usual Suspects for performance issues.
You see, there are some things that nearly guarantee you’ll get one of those “bad estimates” that people blame things on before rebuilding every index in sight.
But all the rebuilt indexes in the world won’t fix these anti-patterns, because most performance problems aren’t because your data pages are a little out of order.
So what might your query plans tell you?
- If you got a huge memory grant
- If your query was forced to run serially
- If you have missing indexes
- If you have missing-missing indexes
- If you have very sneaky missing indexes
- If there was an icky function in your query
- If there’s a table variable hanging about
- If there’s implicit conversions
- If there’s inappropriate scans
And that’s just the estimated plan! With actual plans, you get even more detail. On top of stuff we’ve already talked about, like operator times and wait stats, you can also see if your memory grant got used, spills, and other important details.
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.