SQL Server Performance Office Hours Episode 86 – Drinking Champagne in an Algarve Bathtub
To ask your questions, head over here.
Chapters
- 00:00:00 – Introduction
- 00:01:30 – Managed Instance Discussion
- 00:03:00 – Misleading Performance Counters
- 00:04:30 – Diagnosing Specific-Time Problems
- 00:06:00 – Partitioning and Performance
- 00:07:30 – Query Store Wait Stats
- 00:08:45 – Merge Joins Explained
Full Transcript
Erik Darling here, with Darling Data, coming to you live from a bathtub, drinking champagne. That’s this office hours for you. Coming down to the end of the trip here, I didn’t end up recording anything in Kash Kash, because there was really no good place to record it where I felt okay doing this, because I don’t want to annoy people in public. Anyway, we’re going to clear out some more of this office hours question queue, because good lord, do we have a lot of questions. First one, from, well, I mean, I don’t know who any of this stuff is from. That’s great. I don’t have to know your name, and neither does anyone else. How are you feeling about managed instance? So, I get questions about Azure a lot, and I always try to answer them in the same way. Back in World War II, there was a saying that I would rather have a sister in a whorehouse than a brother in the Navy. And if you just replace Navy with Azure, you get the saying. So, that’s how I feel about managed instance. I would not curse anyone with it. I would not wish that on anyone. I don’t know how Microsoft managed a screw managed instance.
Azure SQL DB, Azure SQL DB, and all the other stuff so badly, but, yeah, no. All right. Next question. What are the most misleading perf counters people rely on? PoE? Any sort of queue depth? Logical reads? That’s another dumb one. How fast is a read? I don’t know.
How slow is a read? I don’t know. I tend to look at things that are… So, these are all sort of what I would call like second order metrics, right? The first order metrics would be things like wait stats or a query CPU and duration or other things that are a bit more directly indicate what you’re dealing with and the extent to which you’re dealing with it. All of the second order metrics are just a complete waste of everyone’s time.
You’re looking at something that is not directly measurable in a real way instead of looking at things that are directly measurable in a real way. And you’re using them for… I don’t know what. I don’t know. You people terrify me with the things you’ll cargo cult around.
Let’s see. How do you diagnose problems that only happen at very specific times of day? Well, that’s a great question.
I set up my free SQL Server performance monitoring tool, and I monitor the server. And then when things happen at a specific time of day, I have all of the stuff that happened before, during, and after the event. If you go to my GitHub repo, you can get my free SQL Server performance monitoring tool.
Bathtub and champagne not included. All right. Let’s see.
When is partitioning actually helpful for performance and not just management? You mean data management? Good question. You can also get partitioning. Usually when paired with columnstore. Clustered columnstore indexes.
When you set up partitioning correctly with them, correctly is an important word here, then not only do you get the usual segment row group elimination with your columnstore queries, but you also get the partition elimination of it as well.
So that is the one time when partitioning is actually useful. Let’s see here. Is query store wait stats reliable enough for real troubleshooting?
So, sort of, kind of. It’s certainly an interesting view of the data, and I don’t fault Microsoft for implementing it the way that they did. But there are all sorts of pre-execution wait stats that it does not track well.
So things like resource semaphore, query compile, resource semaphore, threadpool, things like that. They are invisible to query store because those are all things that happen before the query executes. So if you’ve got a lot of, like, pre-executions, and they’re not counting compile time because compilation metrics are in query store.
But if you’ve got a lot of other stuff going on in the server, then you might find that those wait stats are not being captured in query store. But for your usual stock and standard, what is this query doodling around with type wait stats? Yes.
However, and there’s a big however here, and it’s a sad however. However, the query store wait stats capture and cleanup mechanism in query store is very high overhead in my experience. And, you know, a lot of the times when I’m dealing with clients who have very high throughput systems, one of the things that I do, if they’re already using query store, then, you know, we’re seeing some observer overhead there, is to disable the wait stats collection.
Let the clean stuff up as it catches up with things, but stop capturing wait stats. If someone doesn’t have query store turned on, for whatever reason, maybe because they tried to turn it on in the overhead from wait stats was just too much, then I will usually say, well, we can just turn query store on and turn wait stats off.
Because a lot of the times the wait stats capture can be the most painful part of query store stuff. I forget if that was five questions or four questions, so I’m going to answer one more. This can either be the correct number of questions or it can be a bonus question, depending on how you look at it.
Let’s see here. What’s a good one? Why does SQL Server sometimes favor merge joins that look terrible on paper? I guarantee you they don’t just look terrible on paper.
There are very few circumstances where I prefer a merge join, especially in the batch mode world that we live in. Usually batch mode hash joins and even having to sort some data in batch mode is preferable for larger data sets than an order-preserving merge join would be.
There are some upsides to merge joins and stream aggregates and other order-preserving operators where across a query plan you don’t have to resort data at any point, which can be nice if you’re trying to avoid memory grants or trying to minimize memory grants.
But for the most part, especially parallel merge joins, they terrify me. Any sort of order-preserving operator around a parallel exchange, especially if it makes the parallel exchange acquire the order-preserving attribute, that can be really not a good time.
So for me, a merge join and like, or merge, merge, let’s expand that a little bit. Not just merge joins, but order-preserving operators in general in a serial execution plan if your goal and your aim is to preserve order and not have to resort data out of index order.
They can be useful for some things, but for the most part, boy, oh boy. But why does SQL Server choose them? It’s the same reason it chooses anything.
It costs things. It might look at the cost of resorting data or hashing data or all the other things and say, this merge join looks mighty cheap to me.
Those merge joins. Often more trouble than they’re worth, especially in a parallel execution plan. Anyway, that’s either five or six questions.
I forget. Again, it’s either a bonus or it’s not. I am going to continue to enjoy my champagne in a bathtub. Thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And, well, I probably won’t see you in your bathtub. Maybe you should watch this from your bathtub and then you can pretend that we’re just like Bert and Ernie or something hanging out together.
All right. 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.