TLDR: There ain’t one.
I was privileged last week to be able to present a couple of sessions at the SQL Server and Azure SQL Conference (great event, I recommend it). One of my sessions was an intro to query tuning. Basically, I went through a bunch of common code smells and suggested different possible solutions. The one thing that came up, both from my own stories and the questions from the (engaged) audience, is just how much everyone wants a magic, run fast, switch.
Query Tuning The Easy Way
Here you go. The single easiest way to make your queries run faster:
Just throw money at the problem. Buy more hardware. Buy bigger hardware. Get more disks and disk controllers (not just more disks). Go to the next highest service tier on your cloud provider. That didn’t work? Go up another tier.
What’s that? Your organization won’t spend money on the problem? Then you have to spend time (which, yeah, is money, but sunk costs, your time doesn’t count, stuff like that).
Here we are. The boss has declared that you will make the queries run faster. Oh, and, he’d like it yesterday, or maybe the day before, thanks.
I assume you don’t have a time machine (if you do, please, get in touch). Now what?
Now, of course, you do a search, “Top Tips SQL Server Performance”. Honestly, the results don’t stink. However, the advice, well, it doesn’t seem terribly specific. Several of the sites suggest that picking the right index will help. True. How do you do that?
All of the ones I looked at in the results suggest identifying the problematic queries is a good first step. I agree. However, how the heck do you go about doing that?
Each of the results lists a few possibilities. Some of the advice is good. Some isn’t. Some of the advice, I’d argue is dangerous.
Hang on a sec. Did you just say dangerous?
Several of those sites, heck, lots and lots of “here’s the fast way to tune your servers” lists suggest you should use the Database Engine Tuning Advisor. Honestly, maybe, maybe, you’ll get some good results from this. Probably, in fact, most likely, you’ll get one or two good results and then a whole slew of useless results along with two or three outright bad suggestions for indexes. The Tuning Advisor stinks to the point of being almost criminal.
But What About…
Oh, I can hear it now.
What about NOLOCK?
Yeah, the magic turbo button fixes everything, as long as you don’t mind missing or duplicate rows and incorrect data.
What about views?
Table variables? After all, they’re only in memory.
The list goes on and on. What everyone wants, and honestly, I don’t blame them, is a miracle. A single step that fixes everything, preferably, without changing any of the code, at all, ever. There isn’t one.
Now, you have to spend that time you’ve been given and learn. And it’s not easy. There’s a lot to learn. This is why Microsoft has been doing so much to improve the optimizer through Intelligent Query Processing and the Automatic Tuning within Azure SQL Database. They recognize that this stuff just isn’t easy and are trying hard to make it so we don’t have to do as much query tuning (oh boy, you should see some of the improvements in SQL Server 2022, cool stuff).
However, even with all that, the only way I know to tune your queries is to spend the time learning how to do that. Sorry, but there it is. I’ll let you get started working.