Free SQL Server Performance Monitoring Memory Graphs

Posted on by Erik Darling

Free SQL Server Performance Monitoring Memory Graphs

Summary

In this video, I dive into the memory monitoring features of my free SQL Server performance monitoring tool, Darling Data. I cover how memory gets utilized in SQL Server, including an overview of memory grants and the plan cache, highlighting their importance and common issues. I also explain various memory clerks such as the buffer pool and lock manager, showing how they impact overall server performance. The video includes detailed explanations of the charts and graphs available for monitoring memory usage, helping you to better understand and manage SQL Server’s memory consumption. If you’re interested in checking out the tool or have any questions, head over to code.erikdarling.com where you can download it for free from my GitHub repository.

Chapters

Full Transcript

Erik Darling here with Darling Data, monitoring tool moguling my way to, well, fame and, well, I think I’ve made like 150 bucks so far, so that’s cool. If anyone out there is feeling generous and wants to sponsor open source projects, well, I’m wide open on that one, so I don’t know, maybe you’re listening, maybe you’re not, I don’t know, it’s hard to tell. Anyway, this video we’re going to talk a little bit about the memory monitoring stuff that’s available in my free SQL Server performance monitoring tool. We’ve got sort of an overview of how memory gets used generally, memory grants that your server has seen, how the plan cache is being used, which, you know, as much as I hate the plan cache, I do like pointing out how crappy the plan cache is.

So, seeing those numbers like rise and fall is always kind of like, ah, look at that, eh, that thing stinks, use query store. How various, so memory clerks, which is sort of various bibs and bobs inside of SQL Server that use memory. So, you know, you have your buffer pool, you have, you know, like lock manager, you have like different, like the plan cache, different things like that.

So, I like to surface those. Um, and also tell you if there are any memory pressure events going on, which is stuff that comes from ring buffer notifications. Uh, there are, of course, some MCP tools built in, so you can talk to all of this data with the LLM of your dreams.

Um, I find Claude the dreamiest, but you might have bad taste and like another one. Um, so all of these things sort of tie in together and I kind of group them all together because a lot of people, I think, overlook how important memory is and how it gets used in SQL Server is, um, often not, I would say not, not exactly straightforward to reason about. Um, you know, one of the biggest things that I see generally day to day is, I mean, like servers, like mostly don’t have enough memory in them.

Um, but I think what people kind of don’t understand is how vital having data cached in memory is and the tug of war that happens between your buffer pool and other memory consumers, particularly query memory grants. So having all this stuff sort of trended in, like obvious to you, makes it a little bit easier than like, you know, hitting F5 on a script, running it, seeing a snapshot of results and being like, oh, well, you know, you know, I mean, I guess it’s okay.

And, you know, like this all ties into, you know, like sort of like the weight stats graph as well, because if you’ve got a lot of page IO latch up there, then that’s going to be a sign that, you know, you’re constantly just reading pages from disk. And so there’s a lot to sort of get into and, um, there’s a lot that the graphing and the monitoring tool makes, uh, for a very easy and often compelling story to tell about what sort of problems a SQL Server is having. So, um, if you want to check it out, it’s all at code.erikdarling.com.

Um, that will take you to my GitHub repo. You can download, uh, the performance monitoring tool stuff there for free. Uh, it’s all going to be in the latest releases.

So if you go there, grab a zip file, uh, you can just open that zip file up and run the stuff you need and start monitoring your SQL Server. Very, very low friction stuff. But let’s talk a little bit about, uh, what’s going on with memory in SQL Server, which is, you know, a pretty smart thing to talk about.

So, uh, we have a few different things that we trend here. Um, looking for any sort of buffer pool pressure, which would be like, um, it’s a little hard to see, but if there were, there’s a little dotty line there. So if there were pressure, we would see some dots up on the screen.

Thankfully, I don’t have, uh, too much memory, too many, many, too many memory problems going on here. Um, I have total memory, uh, which is sort of like, you know, how much SQL Server has available to do stuff with. Um, the buffer pool, the plan cache, and available physical memory.

So, um, that tells you sort of like how much SQL Server has left to give. So if you see that start to like really drop off, then, you know, you might, you might get worried there. Uh, over in this tab, we have memory grants.

So this will tell you, um, sort of broken down by pool. Uh, so every SQL Server has at least, uh, an internal and a default pool. Um, some of them might have more pools if you set up resource governor.

Uh, but this will tell you, like, how much memory has been used from those various pools. Uh, down here, we just have memory grant activity over time. Um, so this will tell you sort of like, um, like when memory grants happened.

Up there, that’ll tell you sort of like, like, like how big they were when they happened. Down here just gives you like, you know, if you had any at all. And there are some like, you know, very moderate spikes in here.

There’s not, there’s like one there. There’s like two there. They weren’t very big memory grants. So the, the charts and stuff up here aren’t really like budging all that hard. That’s okay.

It’s just example data for you to get a feel for the tool with. Uh, memory clerks. Uh, so by default, um, I just grabbed the top five by usage. Um, this might not be like the most useful five that you will ever, ever see in your life, uh, in this example, but this is just what’s going on at my server at the time.

And if we look over here, we can see that this is the memory, memory clerk for the buffer pool, and it’s kind of going up and down a little bit as stuff gets read in and knocked out of memory and like the buffer pool shrunk down there and then it grew back there and then, uh, went up there and then it, uh, I guess it dropped off a bit there.

So sort of, uh, different ways to, um, like the little legend down here will line up with whatever is selected up there. You can, if there’s other stuff that you want to look at, you can, of course, click in, click boxes and get stuff and be happy.

And everyone, you know, knows more about SQL Server. Uh, then there’s the plan cache. Um, basically what I’m trending here is just single and multi-use plans. Um, you know, like, again, like my, my server isn’t crazy busy.

So, you know, um, you know, just like sort of like having this stuff in there, you can just kind of like, and this is in megabytes. Like you can just kind of see over time, like, you know, Oh, like what happened with my plan cache?

Why are things terrible? Why did, why did everything, why did everything get cleared out here? And now I needed to compile everything again. And that was, that was awful too. So, uh, just some useful things to help you sort of track down, uh, SQL Server issues. Lastly, over here, I have, uh, memory pressure events right now.

I don’t have anything. I don’t have any memory pressure on my server because I have a very well taken care of SQL server. I am so good at SQL Server, uh, running in a VM on my laptop that I don’t have any memory pressure at the moment.

But if I did, you would see some cool stuff in here. Um, anyway, uh, just a quick overview of. Sort of the, the memory, uh, graphs and what gets collected in the, uh, performance monitoring tools that I offer.

Uh, again, these are all free. They’re over on GitHub. You can go download them and use them. And, uh, if you have any problems or issues, uh, or questions or anything like that feedback, uh, just file a GitHub issue for it and I will get it taken care of just as soon as humanly possible.

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.

Related Posts