SQL Server Performance Office Hours Episode 53
Summary
In this video, I dive into answering five community-submitted questions about a wide range of topics related to SQL Server and more—ranging from optimizing long-running queries to migrating between different versions of SQL Server. I also share some exciting updates on my free monitoring tools, which are now available in two versions for various needs. It’s been a chilly February, but I’m looking forward to the warmer days ahead when I can rejoin the live events circuit at data tune Nashville, Data Saturday Chicago, and more. If you have any questions or want to participate in this Office Hours session, head over to the video description where you’ll find all the links to ask me directly and learn more about my consulting services and training options.
Chapters
- *00:00:00* – Introduction and Overview
- *00:04:27* – Monitoring Tool Explanation
- *00:13:56* – Parameter Sniffing and Plan Stability
- *00:28:49* – Concurrency Safe Index Maintenance Risks <– Good job, AI
- *00:38:59* – Conclusion and Next Steps <– Good job, AI
Full Transcript
Erik Darling here with Darling Data. I’m in the mood for, well, I guess it’s Monday, so I’m in the mood for Office Hours. This is where I answer five, as long as my fingers work, five community-submitted questions about, well, you know, SQL Server, Love, Life, Fitness, Dietary Restrictions, I don’t know, about tramp stamps recently, so pretty good spread of stuff here. So, you know, just send them on in. If you want to ask me a question, if you want to partake in this circus, you can find that link down in the video description. You’ll find all sorts of wonderful, helpful links there, where you can hire me for consulting, buy my training, become a supporting member of this channel, and, like I said, ask me Office Hours questions, and, of course, you know, the magic, like, subscribe, tell a friend, we’re all happy. And in case you didn’t know, I released a free monitoring tool. I mean, actually, I mean, technically, I released two of them. There’s two versions of it, depending on what your needs are. But they’re both totally free. They are open source. They don’t, you know, you don’t need to, like, sign up via email, or they don’t phone anything home to me. It’s just a bunch of things that run, collect performance data, from SQL Server, things that you would care about, and part of a performance investigation. We got, like, a Nox style dashboard, so you can see which servers are up running, if any of them have any crazy stuff going on. And if you are AI ready, like I am, right, because, because you’ve been getting AI ready with Eric quite a bit lately, then you’ll be happy to know that there is a built in MCP server, so you can actually talk directly to your performance data. You don’t have to give the, you don’t have to give this access to, like, all of your SQL Server data, right? Like, databases and all the system, sys DMVs and stuff. It just talks to the data that it has stored, which is a nice way to have a very defined, well-defined set of data for the, to ask questions about.
So you can say things like, uh, user complained about something being slow yesterday, around noon, what was going on? And it can, it can use all the built-in read-only, no going outside the boundaries, no running weird queries, uh, by accident tools, to tell you what was going on at noon yesterday. So, uh, very excited about this. Um, it’s available at my GitHub repo. There’s a, a long link there, but, um, anyway, very exciting stuff. And, of course, you know, uh, the reason why these things can get built is because it is cold and awful outside, and I don’t want to go anywhere as soon as I walk outside and I can see my breath in the air.
I just want to, like, hide in a, uh, the, I don’t know, a couch cushion somewhere and not, never, never think about outside again. But, uh, eventually, outside will become nice and tolerable again, and I will rejoin the living. Uh, I will sprout, I will blossom from the ground, uh, and I will be at data tune Nashville, March 6th and 7th. Data Saturday, Chicago, March 13th and 14th. SQL Day, Poland, May 11th to 13th. And data Saturday, Croatia, June 12th and 13th.
And, uh, that’s going to be my, my world tour so far for the year. Uh, so I’ll be at all those places, and you should be at those places, too, because I, I will be teaching pre-cons at all of them on Advanced T-SQL. And, uh, it would be just such a joy, a treat, and a pleasure to see you there. I will sign your, your copy of the monitoring tool, autograph it for you. It’ll be fun, right? Be a good time. Anyway, it’s still February here. I still hate life a lot. So, uh, let’s, let’s answer some questions, right? Because that, that’s what keeps us warm.
And, uh, by the way, this, this is the, the, the monitoring tool of doing stuff, right? Like, look at these, look at that weight stats graph. I, I could, I don’t have anything running on here now. I was running some HammerDB, HammerDB tests on this thing earlier, right? And, uh, we got some, got some nice graphs, and we got a whole bunch of stuff about what was going on on the server, right? We, like, you got some CPU, we got some memory, we got all this fun stuff happening, right? Even got some blocking. Look at that. Oh, well, that, then that’s not that interesting, right?
Yeah, that one’s a little lame. But, uh, we have all sorts of good stuff in here. So, um, you should go get this for free and start monitoring your SQL Server for free and stop wasting money on those awful, either, well, I mean, those awful enterprise monitoring tools that are way too expensive or, um, not having monitoring at all, right? Neither one’s a very good idea. Yeah, I got all the good ideas. Anyway, um, here’s the first question now. I have a select statement running for 30 plus minutes. Sounds like you could use some professional help. Sounds like you could use the help of perhaps a young and handsome consultant with reasonable rates, 30 minute plus query there, fella.
Uh, then I manually kill it. Then it’s in a rollback for five, 10 minutes. What’s rolling back? Well, it’s a select query. You don’t have to roll back CPU. Uh, you’re probably not going to be rolling back memory. Uh, so really, uh, the, the most common thing that I see cause, uh, select queries. And I’m just going to assume that you’re a nice person and you’re not like, but it’s a select into cause that would be, that’d be annoying. Uh, so really the most common thing is if your query has done IO of some variety, that could be a spool in the query plan. It could be a spill in the query plan and, uh, that stuff has to clean up after itself. So if you were running for 30 minutes and doing all sorts of nonsense, then probably at the end of that 30 minutes, you had to do some more nonsense to revert those things.
All right. Do, do, do, do, do. Hi, Eric. Hi, you, whoever you are. You know, people never tell me their names, do they? I’m just out here, out here in the wind, fully exposed. Uh, I had a linked server query running for weeks. Where, where am I in the world? Uh, that ran in minutes locally. The remote view I got delivered had four scalar subqueries and the select list, each doing a nested loops join against a 5 million row table. Is what happens that 5 million table get pulled unfiltered for each subquery execution per row. So roughly 5 million times row count times four in data transfer. Um, I would really have to see the query plan in order to tell you what was going on, but that sounds rather unlikely. Um, most of the time, SQL Server just will pull all the data across, uh, for a remote, for a remote query.
And then sort of hit the stuff, uh, locally, but it really all does depend on what the execution plan looks like. So if, if you feel like sharing that in some form or fashion, then I can provide a better answer for you. But until then, uh, it is impossible to tell, but the optimizer usually isn’t quite that dumb. That being said, remote queries really, you get, you, you, you get what you, you get what you get with those, right? You get exact, I think more like you get what you deserve with remote queries. So, all right. Hello, Eric. Hello. Another one. Uh, watched a few of your videos on SQL performance. Oh, you know, everything now then. Uh, actually I have one issue not able to bind the solution to. Oh, do you actually? Uh, we recently migrated from SQL Server 2016 to SQL Server 2022 query on one table is taking indefinitely long earlier. It used to complete in 10, 20 minutes. Jeez. Like why? Um, I’m, I’m, I’m right here.
Uh, I can, I can, I can make these queries take less than 10 to 20 minutes, but now it’s running for three to four hours with no result. I tried optimizations like update stats with full scan, tried using index hints for join, rebuilding the index. I, I don’t know whose videos you’ve been watching. I, this is, this is, this is not my advice. I’m, I’m a little insulted here. But nothing is helping would be great if you can give some pointers to investigate the issue further. Um, so, I mean, again, uh, the, the execution plan is going to have reveal most of the mysteries to you. Um, if you, if you still have the, the 2016 server around or any data from it, or you’re still able to access it in some way, you might be able to find the query plan for your, your query over there that you could use. And you can, you can, you can compare it to the, uh, the, the, the current query plan that you’re getting, but the most valuable thing you can do is in open up SQL Server management studio, hit, hit the include actual execution plan button and start running your query.
And the reason why you’re going to do this is because even though you’re saying it runs for three, four hours and it’s no way you’re going to be able to, I mean, maybe there is, but it’d be boring. Uh, and you probably don’t want to wait for the whole thing to finish. What you can do is you can start that running in one SSMS tab.
And then over in another SSMS tab, you can run SP who is active with the get plans parameter set to one. And you can look at the, uh, in flight actual execution plan for your query and you can start to see where it’s getting jammed and glued up. So that, that’s where I would start.
Um, I would not start with all the other stuff you mentioned. You got bad advice from someone else. I don’t know who you’ve been talking to. You did not get that from my videos. All right.
Let’s see. Oh, I did that wrong. Yeah. Uh, should we prioritize plan stability or optimal plans when those goals conflict? What goals?
Um, yeah. So, oh, I, I get it. So you, you want to know if you should, um, prioritize having one execution plan that works well for everybody, or, uh, if, you know, you’re, you’re in a situation where lots of different, maybe, um, like parameter values, uh, would create lots of different execution plans.
Um, and my answer is you, you know, like it, like you don’t have to have one extreme or the other. Um, often you can like find reasonable ways to bucket these things. Um, you know, that’s sort of like what SQL servers, um, uh, parameter sensitive plan optimization does.
It attempts to bucket things, but it’s, it’s bucketing is, uh, way too coarse. Uh, the bucketing that that thing does is not fine grained enough. So what I would recommend doing is taking a look at, uh, which values you’re passing in, um, are sort of like the, the root of the evil for you.
Right. And maybe you can figure out like some sort of distribution counts for those values. And you can most likely, maybe not always, but you know, most likely in these, in these circumstances, you could find reasonable buckets of things for these, like, let’s just to throw some numbers out there, like one to 10,000, 10,000 to a hundred thousand, a hundred thousand to 500,000, 500,000 to a million.
You know, if you have values beyond that, you can go beyond that. But, you know, it doesn’t have to be, you know, either everyone gets one plan or like everyone gets every plan that they ever wanted.
You can, you can usually bucket these things, uh, and you can use like the option optimize for, uh, syntax to sort of bucket things in a way that, um, that, that, that, that would work for you.
Um, you can also use various dynamic SQL tricks to do that. Um, you know, each bucket gets its own one equals select one, two equals select two, three equals select three. Uh, you know, I’ve, I’ve, I’ve, I have a video called defeating parameter sniffing with dynamic SQL that goes into all that quite a bit.
So what does concurrency safe index maintenance actually mean and what risks remain even when following best practice? So I think you’re asking, how can I do index maintenance without blocking people?
And, uh, no, I’m, I’m, I’m just gonna, I’m just gonna say this once, once, once out there. Um, and, unless you really need to do something to your index, like, like maybe you want to add page compression to your index.
Um, you know, or I guess if you’re like deleting a lot of data and rebuilding an index once in a while would make sense. This, this really isn’t something that you should be doing in a way where you have to worry about concurrency all that much with it.
Like, you know, if you’re, if you’re, if you have a process that’s deleting a bunch of data and doing archival stuff, just do an index rebuild after that. Uh, if you want to apply page compression, you should be worrying about doing that during a maintenance window.
Um, we should not just be, uh, running scheduled index rebuilds because logical fragmentation is a thing. It’s just, it just doesn’t hold the same value, uh, for SQL Server anymore. There are of course, you know, index options like wait at low priority and all the other stuff and, you know, online equals on, but you might not be in a position to use all of those, all of those things.
So, um, if you are, that might make things a bit more concurrency safe, but, uh, really I just find, I find the whole ordeal mostly offensive. Anyway, that’s probably good for me here.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And, uh, I think, and I’m going to do some videos, uh, more in depth about the monitoring tool stuff, uh, this week to sort of help promote it, get it out there in front of people and hopefully act as some instructional guides as well.
So, uh, this week we’ll be, we’ll be talking monitoring boy. All right. Thank you for watching. Where’s my mouse? I got to close.
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.