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.
Problem solved.
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?
Yeah. 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?
No.
Table variables? After all, they’re only in memory.
No.
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 What?
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.
Surely the initial architecture of the DB is half of the battle for performance won.
How about the following as broad strokes:
1. Design tables so that the fewest numbers of pages are used;
2. Keep metadata columns to a minimum or store them in their own tables;
3. Long tables rather than wide tables;
4. Use tables with a 1:1 relationship for data seldomly asked for rather than one really wide table;
5. indexes on foreign-keys as a standard and remove the ones over time that are written to more often than they are read;
6. Time-series tables: updates only ever for the end-date of the validity of a column. All changes to data require a new entry in the table. It is akin to temporal tables.
7. Built-in archival system: have copies of all tables with, say, an archive schema or archive database. All rows, say, 2 years’ old and older, is moved to the archive schema. Referential integrity has to be borne in mind. The data in the main tables remains reasonably hot and the indexes on them should remain quite fast. The application has to be aware of the archive system though. Keep the tables with frequently used data as small as possible.
8. Not really a DB-problem: not using Entity Framework (or another ORM) to query data;
9. Hardware: SSDs for Temp-DBs, use of memory-optimised tables instead of temporary tables and so on;
Data structure absolutely matters. Without a doubt. I mean, it ALL accumulates and matters. However, the toughest nut, is the code. You can have a great structure, crap code will make it ineffectual. In this regard databases aren’t unique. Bad code anywhere causes problems.
Your suggestions are good. No arguments. However, go back through ’em and look at how many require code changes or demand that coding be done a certain way in order to work correctly.
Infrastructure matters. Servers matter. Data structure absolutely matters. Crap code can destroy them all.
Agreed. Crap code reduces everything to a crawl. Welcome to the world of the full-stack developer.
Ha! Also worth noting, as correct as all of your suggests are, they also require a ton of work and discipline and are not a single magic ‘run fast’ switch. This stuff just isn’t easy.
I’ve also found that throwing money at the problem in the form of hardware (and the incredible expense of licensing if the hardware involves more cores) is that it can and frequently does make the problem much worse. We had a major problem where 16 core (hyper-threaded to 32) would saturate for a half hour at a time. While I was trying to determine the cause in the code and the fix, they decided to bump the system up to 24 core hyper-threaded to 48. Instead of having “just” 30 or so core saturated during those half hours, we now had about 46 saturated and the problem jumped from 30 minutes to 50 or so. It jumped that high because the rollbacks the code was doing take a whole lot longer than if the code didn’t rollback and more core meant more threads that needed to be rolled back.
Also, “going parallel” is sometimes a problem, as well even if they need to go parallel for performance. That’s why people recommend setting the Max Degree of Parallelism. We had several long running jobs at night that were taking on the order of 4 hours to run. We had previous set MAXDOP system wide to 8. Because of an issue (too long to explain here), I dropped that to 4 and they thought I broke the system because the 4 hours jobs started running to successful completion in only 2-3 hours.
p.s. One of my mantras is “Fix the code because that’s where the performance is… or is not”.
No arguments from me. The issue seems clear. Money looks like an easy solution. Everyone knows, code is hard, AND you can’t change the code. That’s where I’ve always spent most of my time fighting, and working.