There are lots of things that can be fine in one query plan, and horrible in another.
Likewise, a query plan might be great for one set of parameters, and terrible for another. That’s called parameter sniffing, and that’s what we’re going to talk about next week.
What I want you to take away from the last couple weeks of posts is some of the improvements that have come into query plans over the years, and some of the shortcomings that still exist.
Despite the shortcomings, query plans offer an important view into things. When viewed alongside metrics in other DMVs, the text of the query, indexes that are available, and any parameters passed to the query, you can get a fairly complete view of things.
Of course, you can’t get rabbit-holed on this stuff. As much detail as gets captured in actual plans and in the other sources I mentioned, they don’t tell you about important things like blocking or resource contention.
You can infer this a bit by looking at waits on the server, or by comparing CPU to duration.
If you see lots of LCK, THREADPOOL, or RESOURCE_SEMAPHORE waits, it might not be an issue of tuning a single query. Likewise, if you see a lot of PAGEIOLATCH_xx waits, you may need to look at your RAM to data ratio (among other things).
We’re at the point now where we’re ready to dive into a tougher subject, and start answering questions like “why did my query suddenly get slower?”
Thanks for reading!
A Word From Our Sponsors
Following on the GREAT SUCCESS of previous online events, I’m kicking things back off with another offering of my Premium Performance Tuning class, before I retire and remix the material.
To buy tickets, head over here. It’s going to be taking place Friday, October 23rd from 9am-5pm EST.
The normal ticket price is $300, but all September I’m running a 50% off sale with the coupon code “stillonline”.
Every purchase comes with access to my recorded video training for life, too.