There’s no Top in the title

And that’s because a TOP without an ORDER BY is non-deterministic, and you’ll get yelled at on the internet for doing that. This is just a short collection of things that I’ve done in the past, and still find people doing today when troubleshooting performance. Sure, this list could be a lot longer, but I only have the attention span to blog.

So in a totally non-determinstic order, here they are, just like when you write a TOP without and ORDER BY:

Only testing indexes with the query you’re tuning

Number three and climbing the B-tree! You get a query that needs to be tuned, there’s a missing index request, case closed. Right? Well, aside from the fact that you shouldn’t always believe missing index requests, there’s a bigger problem.

You guessed it!

Other queries might decide to use that index, too, and they may not perform as well when they use it. Weird, right?

When you add an index, make sure you test out other important queries that hit the table you added it to, to see if their plans change. On the flip side, you could hit performance wins for multiple queries. In which case, you should do the Ickey Shuffle into your boss’ office and demand a raise immediately.

Only testing with one query running

Coming in at number five and looking alive! Did you know that query plans can be different on busy servers? I bet not! And aside from that, your performance problem might not even be the query itself, it may be blocking, or a poison wait. This stuff may not show up in Dev, unless yous spend a lot of time and money engineering load tests.

This is what safety groups call “Situational Awareness”, and this is the kind of stuff that you really want a monitoring tool in place for. Sure, that query ran slowly, but if that’s all you know, and you can’t reproduce it, then you need to start digging deeper.

Not testing modification queries

Number two is just for you! And this is one that a lot of people miss by a country mile. Maybe even a continent mile (I’m not sure if that’s longer or shorter than an incontinent mile). SQL Server has to keep all your indexes in sync, well, synchronously. When you issue DUIs, guess what? You need to maintain nonclustered index data right alongside clustered index data.

SQL may give you a wide plan where it shows you all of them, or it may bury them all under one “nonclustered index [modification]” operator. Fun, right? All those modifications need locks, and they may need spools, and it’s really just a whole ordeal. Eventually, maintaining all those indexes may start to slow write queries down, even for a single row. This should prepare you for number three, which is…

Testing with too much RECOMPILE

Four up from the floor up! So there’s like this whole Parameter Sniffing thing. When you use RECOMPILE hints, SQL Server generates a new plan for you, every single time. This may make it seem like your code or index changes are bulletproof, because every single time you run your query, it’s fast, and the plan is awesome. As soon as you pull out that hint, you may cache one of those awesome plans, and it might not be so awesome for another set of paramiablements.

Not testing with enough RECOMPILE

The Numero Uno That You Know! So you run your query with a set of values, and the plan looks good. Then you run it with another set, and they get the same plan. It’s still fast; lucky you! But uh… what if that second set of values goes first? Do you get a different plan? Is it still fast? What happens if another set of values gets that plan?

Oh dear, you’ve shot the other foot.

Honorable Mention

I know, I know — that’s a lot to absorb all at once, and you’re feeling like you need to call old employers the way you called all your exes that time you had an ingrown hair that you weren’t sure was an ingrown hair to let them know they may have a problem that needs to get checked out. Don’t worry, like reading a WebMD article about headaches, it’s about to get worse.

• Testing With Local Variables
• Not testing in an environment that mimics production. What does that mean? Well, if you’re using Log Shipping, Mirroring, or AGs, all those unbatched DUIs might behave a whole heck of a lot differently once then squeak out into production.

Thanks for reading!

Brent says: another one I’ve been seeing lately is running DBCC DROPCLEANBUFFERS to “clean out memory” in between query runs. People think they’re testing more accurately because data isn’t in RAM, but that may not mimic production. In one example, the tests didn’t show any performance difference between query versions – but it was because the storage was so doggone slow that any query was horrible.