SQL Server Performance Office Hours Episode 83 – Special Guest Host
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:01:45 – Temp DB Usage Explained
- 00:03:45 – No Lock Usage
- 00:05:30 – Order By Optimization
- 00:06:00 – Indexing vs Query Rewriting
Full Transcript
Erik Darling here, Darling Data. One of our final days in the Azores, and today we have a special guest, temporary co-host. My wife who can’t work a laptop. Click the… There you go. Alright. She’s gonna be asking me questions because I can’t do both today, but once again, this is what I have to deal with, and this is what I’m taking time away from. to answer your questions. So, you’re all very lucky people. Alright. Mrs. Darling, what is the first question? Why does temp DB usage explode even when we don’t use temp tables that much?
Why does temp DB usage explode even when we don’t use temp tables that much? Do you want to fancy a guess at that one, or do you want me to handle it? I’m good. Alright. Well, the lucky thing about SQL Server is there are lots of things that use temp DB aside from temp tables. Even the much mythicized table variables use temp DB quite a bit. You also have other things that might appear in your query plans like sorts and spills and…
If you’re gonna light a cigarette, you have to give me one. And spools that might also affect temp DB usage. And this is not even to broach the subject of triggers, optimistic isolation levels, many other things that might eventually hit temp DB and cause it to explode. And quite frankly, I’m a little shocked that you would even bother asking me that. You know that it’s not just temp tables. It’s so many things.
Alright. What’s the next one? Is there a safe way to use no lock or should it always be avoided? I reckon I’ll fetch this one as well. Unless you have an opinion on it. No? Alright. Cool.
Well, yes. The safe way to use no lock is in a query that you don’t care about. If you absolutely cannot be bothered to care about the results of your query being consistent, accurate, precise, or any of those things, then sure, go off, fella. Alright. And I suppose one other thing that’s worth saying here is that on the rare occasion when you want your query to use an index allocation map scan, rather than an index order scan, you may provide a no lock hint or a tab lock hint, but in general, for most of you out there, you should just avoid them.
Because they’re not good for you. Like cigarettes. Alright. What’s next? Why do some queries run faster when I add an order by that I don’t even need?
Why do some queries run faster when I add an order by that I don’t even need? Most likely, the optimizer has chosen a parallel execution plan for you because your order by is not supported by an index and SQL Server’s wonderful cost-based query optimizer is no longer choosing a serial execution plan to run your query with.
It has chosen a parallel plan in which we sacrifice on a terrible alter multiple CPUs in order to reduce wall clock time. Alright. What’s next? When is query rewriting better than adding indexes?
When is query rewriting better than adding indexes? That’s the question. Well, the simple answer to that is when the optimizer has a perfectly serviceable index that it’s not using. And you might be able to rewrite your query in a way that nudges it towards using that index.
I’ve recorded many videos about this phenomenon in SQL Server, which I would appreciate if you took the time to watch, like, maybe even pass on to your friends. No. If you have any friends.
No what? No, you’re fine. Alright. Okay. What’s next? I’m going to read this one in French. In French? Yeah. In French. Why SQL Server, Subban, Ignore, Regarde, Perfect Index for a Query?
Ah. Why does SQL Server sometimes ignore a perfect index for a query? Well, again, this all comes down to costing.
And the costing algorithms are not always correctamundo, as they say in France. On Paris. In French.
Indeed. And sometimes SQL Server might misestimate the cost of using your, your so-called perfect index in favor of a different index that is less perfect for your query, and it is up to you, again, this sounds a lot, this sounds quite related to the last question, doesn’t it, Ben? Way.
Way. Way indeed. where you just might have to, again, write your query in a way that makes that index more appealing, more attractive to the optimizer, or…
Quit hamming it up. I don’t know. Whatever you’re doing. Or you might… I forget now.
Anyway, I have one day left in the Azores. That’s tomorrow. Where I will record a fresh new video. Is it all five questions, or do we have another one? Way.
Way. How do you say five in French? Sank. Sank? Sank. Sank? Alright. Well, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that someday you, too, will visit the Azores. It is a lovely place.
I might be living here by then, so you can always not stop by my place and enjoy your time here. Alright. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.