sp_WhoIsActive: Getting Memory Grant Details

Video Summary

In this video, I delve into using SPWhoIsActive to gain deeper insights into query memory grants on SQL Server. Specifically, I demonstrate how to leverage the new `@GetMemoryInfo` parameter to retrieve detailed information about memory grants, including requested and used memory. However, I also point out some quirks in the output format that might be a bit confusing for users, such as the way memory is reported in pages rather than more intuitive units like megabytes or gigabytes. Despite these minor inconveniences, the tool proves to be quite useful for troubleshooting scenarios where query memory grants are problematic. I encourage viewers to update SPWhoIsActive if they haven’t done so recently and explore this feature further to enhance their diagnostic capabilities.

Full Transcript

Erik Darling here with Darling Data. If you can hear anything that sounds like I’m at a dentist office on this recording, I do apologize. Someone started sawing or drilling or something outside and I don’t know, there’s just not a lot I can do about other people at this point. Someday, someday. Anyway, this video is going to be a little bit more. So, I’m going to talk about how you can use SPWhoIsActive to get additional interesting information about query memory grants. And also how the output for this is a little weird for my tastes and why. So, I’m going to use this demo, which I used in a prior memory grant video. And I’m just going to run this and open up and run SPWhoIsActive. And then I’m just going to kill this because we don’t really need anything more from this at the moment. You can ignore this top line. In case you can’t read up here, this tab is titled CDC crap. And I was trying to figure out some stuff for a client about Change Data Capture. And so, I have this Change Data Capture job constantly running on my system now. There’s a way to run this.

So, I’m going to wait for it. And it goes and does some stuff. But anyway, newer versions of SP. Now, the last version of SPWhoIsActive was released about a year, year and a half ago. So, if you haven’t updated SPWhoIsActive in the last year to year and a half, now would be a good time to do it so that you’re able to use this fabulous new parameter called at Get Memory Info. So, that’s this lovely parameter right here. Now, I’m using the output column list just to get back the columns that I care about. You don’t have to do this to see the memory grant stuff. This is just to make the results a little bit more digestible for the video that I’m recording. No other reason for that. But it is kind of a cool example of how you can use the output column list to get just the columns that you care about back for certain things. But we’re going to talk more about that in another video.

So, just like in other memory grant demos that I have, this set of queries when run in a foursome will have one query that gets stuck waiting for memory grant. So, you can see that because it is requested memory but granted memory is null. Now, where things get weird with these results is, and this is not the fault of the gentleman who added this code in, a smart fellow named Michael Fuller who’s been indispensably helpful working on who is active over the past, I don’t know, I guess, well, I guess past couple years.

There hasn’t been a lot of active development just so recently. But, so when, for all of, so these three columns are what got added to the output list. This used memory column has been in there forever.

And the logic for the used memory column is to report this in pages, KB. So, used memory is divided by eight. I don’t necessarily agree with this choice because you don’t really measure query memory grants in eight KB pages.

You measure them in megs or gigs or something that is a little bit more in tune with how you think about memory grants generally. You generally don’t think about eight KB pages when you think about query memory grants in SQL Server. So, these numbers here are always going to look a little bit weird.

Of course, we have this query down here which has requested memory but has not been granted memory and has been waiting two and a half hours. And, we’ve talked about that in other videos which were highly successful. Smash hits, they’re the reason I make billions of dollars on YouTube and no longer need to work a real job.

I’m kidding. If only, if only. But anyway, so you get used memory, max used, requested, and granted memory back in the output list here, like top-level columns.

But then you also get another top-level column called memory info. And memory info is an XML clicky column that will report this information. But, like, this stuff gets reported sort of like from the raw DMVs.

There’s no adjustment made to it. So, you get the request time, grant time, like you get requested memory, used memory, all that stuff in KB. So, like, you do have to do a little bit of KB to GB math if you want to figure this stuff out.

Generally, I don’t care about memory grants that are under the one gig mark. Generally, I don’t care about query memory grants until they hit, like, you know, several gigs or more. That’s just me, though.

You know, especially on larger systems, you know, smaller memory grants just don’t, just do not titillate the mind. But this one is interesting, sort of. But the one down here for the query that’s waiting is a little bit more interesting because there’s a little bit more information to take in.

Such as, and this is stuff that we’ve talked about in other resource semaphore videos. Go find those if you want to, you know, learn more about these columns. But these are, because this query asked for a memory grant and didn’t immediately get it, it’s sitting in the queue.

It’s the next candidate. So, the wait order for it is zero. And, you know, you can kind of get some information about the workload group that it’s in, what the max memory grant that a query can ask for is. And this 25 is based on max server memory.

And then you can get some information about that, about the workload group, like total memory, how much is available, how much has been granted out, how much has been used from that. So, there is good information in here that is usable. You know, XML isn’t always the most consumable thing, but it is an easy way to present a whole bunch of information like this all in one go.

So, like, it doesn’t help if you want to stick it into an Excel file or something, but it is a reasonable way, I think, to present like grouped information in this way. Cool. So, get a new version of SP who is active if you haven’t updated it in a long time.

And check out the get memory info column. It’s a useful doodad for if you are troubleshooting, you know, a SQL Server where query memory grants are sort of out of control. Again, other videos about memory grants, resource semaphore and stuff where you can learn more about all of that information.

I highly suggest you peruse every video on my channel so that I can get enough views to have YouTube say, you should consider monetizing your channel. And I can say, no, I’m a man of the people. I do this.

I do this for them. I don’t do this for money. I mean, I do do this for money. Just like not whatever. Anyway, you get the point. Thanks for watching.

Hope you learned something. Hope you enjoyed yourselves. And if you would be so kind as to like this video and subscribe to my channel, I would be ever so grateful. But since I don’t get paid for this, I can’t give you any kickbacks.

But just my love and affection. Maybe a kidney if you really need it. But I kind of don’t think you want my internal organs at this point.

I’ve been working with SQL Server too long. I’ve just been tremendously abused. Anyway, see you in the next video.

See you in the next video.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

sp_WhoIsActive: Getting Memory Grant Details