Checking your Memory with XE

It is well known and understood that SQL Server requires a substantial amount of memory. SQL Server will also try to consume as much memory as possible from the available system memory – if you let it. Sometimes, there will be some contention / pressure with the memory.

When contention occurs, the users will probably start screaming because performance has tanked and deadlines are about to be missed. There are many different ways (e.g. here or here) to try and observe the memory conditions and even troubleshoot memory contention. Extended Events (XE) gives one more avenue to try and troubleshoot problems with memory.

Using XE to observe memory conditions is a method that is both geeky/fun and an advanced technique at the same time. If nothing else, it will certainly serve as a divergence from the mundane and give you an opportunity to dive down a rabbit hole while exploring some SQL Server internals.

Diving Straight In

I have a handful of events that I have picked for an event session to track when I might be running into some memory problems. Or I can run the session when I suspect there are memory problems to try and provide me with a “second opinion.” Here are the pre-picked events.

Investigating those specific events a little further, I can determine if the payload is close to what I need.

That is a small snippet of the payload for all of the pre-picked events. Notice that the large_cache_memory_pressure event has no “SearchKeyword” / category defined for it. There are a few other events that also do not have a category assigned which makes it a little harder to figure out related events. That said, from the results, I know that I have some “server” and some “memory” tagged events, so I can at least look at those categories for related events.

This query will yield results similar to the following.

If you look closely at the script, I included a note about some additional interesting events that are related to both categories “server” and “memory.”

After all of the digging and researching, now it’s time to pull it together and create a session that may possibly help to identify various memory issues as they arise or to at least help confirm your sneaking suspicion that a memory issue is already present.

When running this session for a while, you will receive a flood of events as they continue to trigger and record data to your trace file. You will want to keep a steady eye on the trace files and possibly only run the session for short periods.

Here is an example of my session with events grouped by event name. Notice anything of interest between the groups?

If the data in the session does not seem to be helpful enough, I recommend looking at adding the additional events I noted previously.

Here is another view on a system that has been monitoring these events for a while longer and does experience memory pressure.

Here we can see some of the direct results of index operations on memory as well as the effects on memory for some really bad code. Really cool is that we can easily find what query(ies) may be causing the memory pressure issues and then directly tune the offending query(ies).

The Wrap

Diving in to the internals of SQL Server can be useful in troubleshooting memory issues. Extended Events provides a means to look at many memory related events that can be integral to solving or understanding some of your memory issues. Using Extended Events to dive into the memory related events is a powerful tool to add to the memory troubleshooting toolbelt.

Try it out on one or more of your servers and let me know how it goes.

For more uses of Extended Events, I recommend my series of articles designed to help you learn XE little by little.

Interested in seeing the power of XE over Profiler? Check this one out!

This has been the ninth article in the 2018 “12 Days of Christmas” series. For a full listing of the articles, visit this page.

page 1 of 1

January 2019
« Dec    

Welcome , today is Monday, January 21, 2019