SQL Server Performance Office Hours Episode 54


 

To ask your questions, head over here.

Summary

In this video, I dive into some of the most pressing questions submitted during my office hours session, covering topics like `sp_whoisactive` and its parameters, memory grants and their management in SQL Server, query performance variability, and when to step in with manual interventions. I also discuss the nuances of parameter sensitivity and how it can affect query performance. Whether you’re a seasoned DBA or just starting out, these insights should help you navigate common challenges in SQL Server performance tuning. If you found this content valuable and want more, consider supporting my channel by subscribing, liking the video, and sharing with your colleagues. And if you need personalized assistance, I’m available for consulting services—no monitoring tool mogul lifestyle here!

Chapters

Full Transcript

Erik Darling here with Darling Data, and we’re going to take some valuable time away from my new career as a monitoring tool mogul to answer some of your very important office hours questions, because, I don’t know, why not? I still like you. You know, I have not went and joined the monitoring tool glitterati. We’re partying with starlets in limousines, with champagne, and I don’t know, whatever else people do in limousines that probably just make the toes curl. Anyway, down in the video description, you’ll find all sorts of helpful links, and you’ll also see useful things like, I don’t know, what do you call it there, summaries and chapters, which the robot sometimes gets right and doesn’t hallucinate the videos being two and a half hours long, and other times, other times, it’s not so good. Anyway, if you would like to hire me as a consultant, I still, you know, again, my monitoring tool mogul career has not quite eclipsed my consulting career, so if you would like to hire me to help you with your SQL Server performance problems, I am available in all sorts of ways, and you can see the ways that I’m available down in the links.

You can also find ways to purchase my training, become a supporting member of the channel. If you like this content so deeply and feel strongly motivated to spend $4 a month, you can do that. You can also find links to ask me the office hours questions, and of course, as always, if you like this content, but, I don’t know, perhaps you’re not financially motivated to give me a little hug and kiss, you can always like, subscribe, and tell a friend, so some channel numbers go up. And I can feel some sense of joy in my day-to-day life, aside from watching the downloads and GitHub of the monitoring tool, which, speaking of which, if you want to check that out, it is completely free, it is completely open source. There’s no email requirement, there’s no application phoning home to tell me all about your dirty server secrets.

It’s just a whole bunch of T-SQL collectors running on a schedule, collecting all sorts of important performance data, supplying you with a Nox-style dashboard and all sorts of pretty charts and graphs, and there’s even, if you’re, you know, becoming one with the robots, there is even an opt-in, optional, built-in MCP server, so you can ask questions about your performance data and see what the robots think about your server crisis. And it’s a wonderful little thing, so maybe the finest thing that I’ve contributed to the SQL Server community, aside from my handsome face, which will get better looking when I officially become a monitoring tool titan and get a whole bunch of crazy plastic surgery. If you’d like to see this handsome face out in public in the world, I will be at a variety of events coming up over the next portions of time.

Data Tune, down in Nashville, March 6th and 7th. Data Saturday, Chicago, March 13th and 14th. SQL Day, Poland, May 11th to 13th.

And, man, jeez, that one’s a dash, the other ones are ands. Whoa, why are these? I gotta beat up my PowerPoint guy. And, of course, Data Saturday, Croatia, June 12th and 13th.

I will be doing pre-cons at all of these events about advanced T-SQL. I don’t know, maybe I should do advanced SQL monitoring at this point. Might be fun.

But you should go to all of those. Each and every one of them does travel around the world catching me. For now, we are still trapped in the miserable doldrums of February, waiting for March to arrive and springle our springs. But, man, I hate it outside right now.

I’m miserable everywhere I go. So, let’s stay inside and answer some questions. What do you think about that, huh? All right. So, let’s see.

SP, who is active? Do you like at getplans equals 1 or at getplans equals 2 and why? I threw in an and there. I apologize for my editorial interjection into your question.

But I usually just run with getplans equals 1. Getplans equals 2 is a little known thing. Getplans is not a bit.

It is, I believe, a tiny int. But you can choose getplans equals 1 or getplans equals 2. And I’m going to get maybe a little deeper on this than most people will care about. If you are running, I would say, an older version of SQL Server, getplans equals 2 would return all of the plans associated with a batch.

So, kind of like if you look in the plan cache and you look for a stored procedure and you open it up and it’s all of the query plan for the stored procedure. It’s like a big, long thing.

getplans equals 2 would give you that. getplans equals 1 would just get you the query plan for the query that’s currently running on the batch. However, if you’re on a newer version of SQL Server and SP who is active prefers the new query plan DMV that gives you the sort of in-flight query plans or like the live query plan, it just returns the statement level 1 anyway.

So, me, I just go with getplans equals 1 because usually the only plan I care about is the plan that is currently up for the query running. You might find different uses for getplans than I do.

That’s totally okay, but that’s my thing. All right, question number two. How do I know when it is time to reduce the memory grant limit on the default pool below 25%?

Is it determined based on how much RAM I have or is it some other factor like memory weights? Well, I will typically start messing with this as soon as RAM becomes an appreciable number, somewhere around the 128 gig mark.

And the reason for that is because SQL Server by default and what this question is really asking about is using Resource Governor to reduce the default max memory grant for queries.

And you can do that with Resource Governor. And you can even do that with standard edition of SQL Server 2025, which now has Resource Governor, which is so lovely.

But the reason why is because by default, 25% can be a pretty big memory grant. That’s 25% of your max server memory setting. And boy howdy, the more memory you give SQL Server, the bigger that 25% gets.

Like 25% of 128, 25% to 256, 25% to 384, 25% to 512. Like, oh, those numbers get up there. So really the more memory you have, the smaller you want to make this.

SQL Server will ask for memory grants pretty heavily. Getting this to the right number is a somewhat different exercise because it, well, it sounds good to just drop this number down.

You know, you do have to start keeping an eye on queries to make sure that like, you don’t have important ones that start spilling off the disk. So like there is some caution that needs to be exercised here.

But one, like, so like, well, it is a good sign to look at memory weights like resource semaphore to figure out if this, you know, does this apply to me? The other thing to look at is that even if maybe, even if you’re not hitting resource semaphore weights, you might still have queries asking for pretty big memory grants.

Like even if you don’t have multiple queries fighting over memory grant space, because by default, SQL Server will give about 75% of your max server memory setting out to a bunch of queries running.

Even if you don’t have that happening, you might have queries asking for those 25% memory grants and knocking a whole bunch of important data out of your buffer pool, which is not a good time either.

So, you know, well, memory weights like resource semaphore are certainly fantastic indicators of queries having memory grant clashes. You should also keep an eye on those page IOLATCH SH weights because you may have queries that are asking for memory grants, knocking a whole bunch of data out of the buffer pool, and then other queries have to go read that data back in.

Remember that SQL Server does not work with pages on disk except to read them into memory. All right. Let’s see here. Oh, another memory question.

Jeez Louise. People love memory, huh? If SQL Server shows plenty of free memory, how can queries still suffer from memory pressure? Well, I don’t know.

What’s that free memory doing? Is it really free? I don’t know. Did you download that memory? Is that why it’s free? I don’t know. Well, that’s a very vague question. The mind wanders a little bit on this one.

I would say that if SQL Server shows plenty of free memory, in other words, memory that is not dedicated to the buffer pool, then, or like other memory clerks and consumers within SQL Server, I would say that perhaps you have had queries ask for a lot of memory, clear a lot of that space out, and SQL Server may not have recovered from that just yet.

That’s probably what’s happening there. Where I would look is at what we were kind of just talking about in the above, with the above question. I would probably look to weight stats and I would see if you have resource semaphore weights going on and I would also look at weight stats to see if you have a lot of page.io latch underscore whatever weights going on because that might inform you as to whether SQL Server has plenty of free memory because a lot of it is not being dedicated where it should because it was dedicated somewhere else temporarily and SQL Server has not filled itself back up yet, primarily in the form of the buffer pool.

That’s the best I can do with that one. All right. Next up. When should I stop trusting the optimizer and intervene manually? Probably right about the time you start asking that question.

Honestly, that’s about when I would do it. No. So, I mean, what are you trusting the optimizer with? To make every query perfect? You know, it ain’t gonna happen.

You know, it’s, you know, the SQL Server has a fantastic query optimizer but, you know, nothing’s perfect and not all modeling is perfect. You know, models are models, right?

They’re not, they are not like exactitudes. They are not, they are not certainties. They are just models that we try to make predictions about and SQL Server uses its various cost modeling and cardinality estimation to try to, try to forecast what the best query plan for your query will be.

But, you know, it, like, maybe not everything makes it, is there for it to make a correct decision. You know? So, when should you intervene manually?

Like, via, like, query hints or index hints or other things like that? Well, that’s, that’s probably last along the way. Before, well before that, there are all sorts of things you can do to make your queries a bit happier.

You know, materialization with a temp table, that’s, that’s a good one. Making sure that your indexes are aligned to the goals of your query, that’s another one. Making sure that you have adequate constraints, whether they be uniqueness constraints or foreign keys or domain constraints to let SQL Server know values that can exist in a place.

You know, there’s all sorts of things that you can and should do. You know, and, that’s just from like the, the database side, from the query side, you know, writing sorgable queries, you know, again, breaking up complex queries.

There’s all sorts of things you can do, but there would also potentially be a manual intervention that, that are not just, you know, dirty, dirty tricks with hints of, of various states of brittleness.

So, you know, when should you intervene? When, when, when things start getting bad. If, if, if you need help figuring out interventions, I, my rates are reasonable, as always.

Don’t forget that. All right, the last question. Of course, this is, why does the same query sometimes run in milliseconds and sometimes in minutes?

My friend, you have asked the most biblical of questions. I could, I could go on and on for days and hours on this one.

Most, most commonly, you, you are dealing with an issue of parameter sensitivity. That, that’s a very, that’s a, probably the most common reason. There are, there are other reasons though that might lead to variable performance within queries in your workload.

Locking would be one of them. Remember SQL servers, default, up isolation level for databases is read committed in which read queries will wait for locks from write queries and get blocked and all sorts of, might, might, might take minutes for all those locks to release.

Another thing might be a buffer, buffer pool availability of your data. I mean, if you have to read lots and lots of data into the buffer, into the buffer pool from disk, well, yeah, that can certainly introduce a lot of query variability.

All right? well, but, but most, most likely it is an issue of parameter sensitivity and once again, if you need help figuring that out, I’m, I’m here.

I’m like, like the Maytag man just hanging out with my glass of milk, my pencil, my, my, my fancy hat waiting to help people. Anyway, that’s five questions I think, right?

I’m gonna make sure one, two, three, four, five. It’s probably about good for today. So, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I’ll see you in tomorrow’s video where we are gonna talk more about my, my, my fantastic tool that monitors your SQL Server’s performance.

So, I will see you there. All right. Goodbye.

 

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.

The post SQL Server Performance Office Hours Episode 54 appeared first on Darling Data.