SQL Server Performance Office Hours Episode 5
| Hi Erik! I recently read a post stating that not de-fragmenting your indexes lead to more expensive plans, therefore ignoring index fragmentation Is a bad idea.” What do you think about this? This Is the post I’m referring to: https://sqlperformance.com/2017/12/sql-indexes/impact-fragmentation-plans |
| How can I get more out of SolarWinds DPA? People like it so much that I really don’t know what I’m missing. What is it great for? |
| When tables get large and the default sample rate inflates estimates do you generally recommend increasing the sample rate to lower the estimates or doing a full scan and disabling stats updates, or something else? |
| When viewing a long-running query in sp_whoisactive, how can I retrieve the parameter values of the query? I thought I could get them with @get_plans = 1 and then examining the ParameterCompiledValue in the execution plan, but that is unfortunately the param value for the cached plan that it is using, not the current param value that is causing it to run slow. I’m on SQL Server 2019 and have query store enabled. This would be a huge help, thanks! |
| If you could only have one watch from your current collection, what would it be? Also, what’s a grail watch that you think about a lot but can’t afford/justify/get ahold of? PS, if you can only answer one, please answer my serious SQL related question. Cheers |
To ask your questions, head over here.
Video Summary
In this video, I dive into some interesting questions from the Darling Data community during our Office Hours session. We start by discussing a post from 2017 about index defragmentation and its impact on query plans. I share my thoughts on John Cahias’ perspective, emphasizing the importance of understanding different types of fragmentation and the specific context in which his advice was given. Moving on, we explore how to get more out of SolarWinds DPA, leading to a strong recommendation to switch to SQL Sentry instead due to its superior performance and reliability. The session then delves into statistical sampling rates for large tables, where I highlight the complexities involved and commend those who have successfully resolved such intricate issues. Finally, we tackle retrieving parameter values from long-running queries in SP:who is active, discussing limitations with SQL Server 2019 and suggesting alternative methods like extended events. Throughout the session, I share practical insights and tools that can help address these challenges effectively.
Full Transcript
Erik Darling here with Darling Data. And if you can’t tell by the big smile on my face that it is time for Office Hours, well, you haven’t watched enough Office Hours, or I haven’t recorded enough Office Hours, Hourses yet. But anyway, before we do Office Hours, let’s talk a little bit about my channel. If you would like to support it, you can do that. If not, totally fine. You can do other stuff, like like and comment and subscribe and ask questions on Office Hours. It’s a great deal. If you would like to ask me questions in exchange for actual money, and have me fix stuff in exchange for actual money, my rates are reasonable and we can make arrangements to do all of these things. It’s a wonderful setup being a consultant. If you would like to get some training from me in the long form, in the streaming video variety, not on YouTube, you want to feel real close and intimate with me, you can get all 24 hours of my performance tuning training content for about 150 US dollars. Good for the rest of your life. That link, that discount code again down there in the old video description. You can also catch me handing out lunches and making sure everyone’s happy at SQL Saturday New York City coming up May 10th 2025 at the Microsoft offices in Times Square. If you’ve never been there, I can’t recommend Times Square.
They got a Sbarro, they got a Sbarro, they got a Ruby Tuesday, they got, what’s the other one, I think they still have a Bubba Gump shrimp, shrimp, shrimp boat, house, something. You could also walk a few blocks away and get real food that’s good too. Whatever you’re into. Some people don’t have any real sense of food. So, delete anything. Probably explains a lot. But anyway, let’s answer some questions. Alright. So, the first one up here that we’re going to deal with is…
Hi, Eric. Hi. How’s it going? I recently read a post. You recently read a post from 2017. Good, good, good. Stating that not defragmenting your indexes lead to more expensive plans, therefore ignoring fragmentation is a bad idea. What do you think about this? Well, going by memory, because I don’t want to click on any links here.
Going by memory, that post was written by a very smart fella named John Cahias. And, you know, we’ve got nothing bad to say about John. He’s been a valuable contributor to SQL Server stuff for a very long time. Very smart guy. I hope one can’t say a bad thing about him. I hope he has all the great weekends money can buy.
And I remember that post. And I think you should probably read the comments on that post before you get too married to it. And I have no doubt in my mind that that post stemmed from an actual problem that Mr. Cahias ran into. Where I recall feeling, at the time I read it, that the post fell a little short.
Is in maybe framing the problem a little bit better. You know, I think from remembering like the tables, like the one was highly fragmented because of something with GUIDs and there had like the, the problem wasn’t logical fragmentation.
Like, like the type of like when you’re saying fragmentation and like the post didn’t make a good distinction with the type of fragmentation that was the problem. So like every index script that you would go and run to find fragmentation would still be looking for logical fragmentation that goes for all this stuff that goes for that stupid thing in the tiger tool box, toolkit, whatever. Unless you write a custom script that goes and looks for physical fragmentation, which requires a higher, like, or rather a higher level of detail when you are gathering fragmentation metrics on your indexes than finding logical fragmentation does.
Like when you’re finding logical fragmentation, you can use that, whatever that table valued function is with like limited. And you can find pages that are out of order. That’s not the kind of fragmentation that caused a problem here. The problem that got caused in this one was physical fragmentation.
That was there being a lot of empty space on data pages because of the fragmentation. There was also a thing where, um, I think that the, the two demo queries, uh, neither one of them had a where clause on them. They were just like two group, like it was just a group by of two like string columns from the different tables.
So, um, I don’t, I don’t know how much I’d like, like go with that is like, oh, you must defragment the indexes. Uh, like I’m sure that like that was an actual problem that, you know, Jonathan or someone at SQL, SQL skills ran into that. Jonathan blogged about, but, uh, there there’s, there’s sort of a lot of stuff in there that is very specific to the setup of that.
And not necessarily, I think you could use to take as general advice, uh, about how to like either decide if your queries were, um, having problems, like, or your query plans were changing because of this or not. The other thing that I remember about that post is that, um, the, the query that hit the table with fragmentation got a parallel execution plan and ran like three or four times as fast as the query that hit the, the, the non fragmented, uh, table. And, uh, because the one that hit the non fragmented query was considered cheaper by the optimizer and got a serial execution plan.
In my performance tuning life, the majority of the time where I have had a gripe about, uh, parallel plan choice has mostly been in the other direction. It’s mostly been, man, I really wish SQL Server would choose a parallel plan here. Why the beep is it chewing, choosing a, a serial plan here?
Uh, and me trying to figure out a, um, a supported way to, uh, get SQL Server to choose a parallel execution plan rather than a serial execution plan. Um, that, that there’s probably about a hundred to one ratio. There are times where I’m like, damn it, SQL Server.
Why, like, like, why aren’t you choosing a parallel plan? They’re very much, much smaller number of times have I been like, damn it, SQL Server. Why did you choose a parallel plan instead of a serial plan?
I want to, I want a serial plan for this. Um, of course, when, if you want a serial plan, it’s a whole lot easier to apply a max.1 hint than it is to apply a go parallel hint. Because setting, if you set max.8 to 8, that doesn’t force the query to go to max.8.
That just says you can go up to max.8 if you choose. Right? It is not min.Dop.
It is max.Dop. Uh, and the two ways that you can, uh, for, try to force a parallel plan with trace flag. 86.49 or the enable parallel plan preference, uh, option use hint. They’re both very specifically not supported by SQL Server.
And the fact they say, don’t use these in production. They’re not min. Because who knows? So, um, I’d be a little careful with that one. Uh, read, read, read, read the full post and the comments.
I don’t, not often you’ll hear someone on the internet say, oh, read the comments. But no, go ahead and read the comments. All right. That one is done.
Let’s go on to the next question. Question two of five. How can I get more out of SolarWinds DPA? People like it so much that I really don’t know what I’m missing. What is it great for?
Well, the best way to get the most out of SolarWinds DPA is to uninstall it and then call up SolarWinds and say, hey, I want to get SQL Sentry licenses instead of SolarWinds DPA licenses. Please, for the love of God, give me SQL Sentry licenses. I don’t want DPA anymore.
Uh, DPA is a trash heap of a, of a, of a monitoring tool. I can’t say enough bad things about it. Like every time I’ve tried to use it, it has been endless frustration and annoyance. Uh, avoid it at all costs.
So, there we go. And here we say, do, do, do, do. When tables get large and the default sample rate inflates estimates, do you generally recommend increasing the sample rate to lower the estimates or doing a full scan and disabling stats updates or something else? Well, boy, oh boy.
There’s, there’s, there’s a lot, there’s a lot to think about here, isn’t there? Um, if, if, I think if, if you’re able to, uh, sufficiently isolate the process, you’ve got a problem to, to, to this and you, you’ve come up with a solution.
I mean, what, what, what more do you want me to give you on this? You’ve already, you’ve figured it out already. So, but you’ve, you’ve, you’ve come to a, you’ve come, actually come to a very good point in your career. Congratulations.
Where you were able to look at a, a, a performance problem, uh, identify that the default, uh, auto, like stats update, whether it’s auto stats or like a manual stats update with the default sampling rate was causing a problem. Right. It was inflating estimates for some portion of queries that were hitting the table and it was giving you a bad execution plan.
So, you know what? Good job. Like, great. That’s fantastic.
I don’t like, I don’t have a general recommendation here because things like situations like this are so unique and, um, have so many moving parts that there’s not like a good general piece of advice here. When you’ve gotten to the point where this is, these are the types of issues that are hitting your workload because you’ve cleared up like many of the other, like simpler, more easy to identify things. Then you’ve got to, this is, this is the kind of stuff that you kind of have to figure out based on all the local factors that apply to you.
Um, I will say that I’ve been in situations where certainly I’ve, I’ve had to do full scan stats updates to, uh, prevent, um, cardinality estimation issues of the variety you’re talking about. I’ve been in situations where I’ve disabled auto stats updates because the auto stats updates that happened were not good. Um, on newer versions of SQL Server, you can do something a little bit cooler and you can actually, uh, preserve the, the stats update, uh, percentage.
So you can create statistics or update statistics and you can tell SQL Server every time you update these statistics, you have to use X percentage sampling rate up to a hundred. So you can like control that a little bit better now, but, uh, really congratulations. You’ve solved a weird, hard problem.
Um, but I don’t have general advice on this. I have very specific advice that I would figure out and give based on what’s happening with the server, but you’ve done that. There’s, there’s, I’m not, I’m not going to have anything better because you, my friend have figured out the problem.
Congratulations to you. All right, let’s answer this one. Uh, let’s see.
Ah, there we go. When viewing a long running query in SP who is active, how can I retrieve the parameter values of this query? Uh, so I’m going to assume you mean the runtime parameter values of this, because you’re saying, I thought I could get them with get plans equals one and then examining the parameter compile value in the execution plan. But unfortunately that is the pram value for the cash plan that it is using, not the current param value that is causing it to run slow.
I’m on SQL Server 2019 and have query store enabled. Uh, so you can’t do it with SP who is active unless you’re on SQL Server 2022 or one of the cloudy builds like SQL DB or managed instance. There’s a database scope configuration called force runtime parameter collection.
There’s a big note in the, in the docs for it that says, this is not for extended use. You use this for a limited time for troubleshooting. Uh, so like, don’t leave this on forever.
Cause it, that it’ll be a mess. So, uh, you could do it if you were on SQL Server 2022, but you’re on 2019. So there’s no joy for you. You would have to use extended events, uh, to capture that.
And you would have to figure out like, you know, um, like it’s, it’s a, I guess it’s sort of unclear to me if this is like a store procedure or like a, like an ORM query using SP executes equal or something. But, uh, if you use my store procedure, SP underscore human events, there is, uh, uh, a class of event you can use called where it’ll collect runtime query information. This isn’t going to tell you like for currently executing queries, like the query does have to finish for it to get logged to the extended event.
Uh, but, uh, that like extended events can capture the parameter runtime values. Uh, one of the things that my tool captures is my tool captures, sorry, uh, is the, um, the post execution show plan. So that would show you, uh, in the, in the plan XML, uh, what the parameter compile and runtime values were.
If you don’t, you can skip collecting that. And one of the, uh, and like some of the other extended events will show you the actual call with, um, uh, with the parameter values. Uh, it occurs to me while I’m answering this, that one other, one other thing you could try with SP who is active is that, that sometimes work.
It won’t always work depending on how parameterized things are. But if you’re looking at like a store procedure call where it’s like, you know, it’s calling the store procedure and you can, and like, like the application is passing, passing in literal values and not just passing in like another set of parameters. That like, like, like your, like, like the, the store procedure parameters equal.
So if it’s like, like store, like store procedure parameter equals literal value, you could do it. But if it’s like store procedure parameter equals some other parameter value from the ORM, you couldn’t do it. Uh, there’s, uh, another SP who is active parameter called get outer command.
And if you set that equal to one, you’ll get like the full thing that called the query you’re looking at. And you might be able to see the parameter values there. Uh, if it’s not there, then you have to go to extended events.
All right. Final question for this week’s episode of office hours is, wow, it’s not, not SQL Server related at all. It’s a very personal question.
Uh, if you could only have one watch from your current collection, what would it be? Well, this one, uh, this is the, this is, this was, this was my, my business is going well watch. So this was the one that I would, this is the one that I would keep.
I would, I do want to be like upfront. My watch collection is two watches. It is not an extensive collection. I do not have a wall of watches spinning on winders. Then I, and I have to like painstakingly choose which one is going to go best with my Adidas shirt for the day.
I have two. I have the, like a stainless steel watch that I wear to like the gym and the pool and the beach and other stuff where I don’t want to like mess up a gold watch. And then I have my gold watch, which I wear for everything else.
So I don’t have a lot. Uh, it’s not a huge collection. Uh, but the question here is that, I mean, is probably worth answering is also what’s a grail watch that you think a lot about, but can’t afford justify get ahold of. Uh, and then PS, if you can only answer one, please answer my serious SQL, SQL related question.
I don’t know what your SQL related question was. It doesn’t tie like users to questions in any way. So, uh, I hope I answered it, but, um, maybe it’s, maybe it’s one of the ones before, maybe it’s one of the ones after, but, uh, the watch that I would love to get if, if so.
So, it’s kind of a sad story because the person who I worked with to get my watches with was, uh, the, the watch world is weird, right? Like you have to like suck up to people and like make friends and do all sorts of like jump through all sorts of hoops to buy like, like nicer watches. Uh, unless you just go to like a gray market dealer and, um, like, like you just like, you know, spend whatever money because they don’t, they don’t care.
Right. Uh, but like the, like the, like no watch store really is, is very few watch stores are owned by the watch company. They’re all sort of like franchises. The like, so like, like a lot of places, it’ll be like a jewelry store or something that opens up boutiques for watches because, you know, that watches are part of that.
But when you want to sell the nicer watches, not secondhand or something, then you have to like, have like a branded boutique to sell them out of. So, um, the person who I buy my watches from currently, this, the, the jewelry store they work for was opening up a Patek boutique. And I was very excited because there was exactly one Patek that I wanted.
It was a Nautilus 5980, uh, full gold, the black face, it’s a gorgeous watch. Uh, and I was like, man, as soon, as soon as, as soon as that opens up, like I’m putting my name in for that. And then like three months before that, that boutique opened up, Patek discontinued making that watch.
So now you can only get it on the gray market. And now like, like the low end on the gray market, it’s like 180, 250,000 for the thing. And there’s ain’t no way that’s happened.
Like, I would have to win Powerball before I start thinking about that. You know, retail was like, like less than, like way less than half of that. Like, like, like if I, you’ve got it from the store, but you know, that, that opportunity is, has passed me by. So, um, yeah, anyway, that’s, that’s it for there.
Uh, I’m not going to talk anymore about that because it gets obnoxious pretty quickly. But anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something about, well, maybe about watches, if not about SQL Server. And, uh, I will see you for the next round of Office Hours questions. Uh, the next, the next, next five. Anyway, 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.