A SQL Server monitoring tool is warning you that you’re getting memory dumps, and you’re wondering what that means.
This isn’t normal: you’re encountering a situation where SQL Server is so freaked out that it’s unable to proceed normally with a query. In order to help you (and really, Microsoft) debug the problem, it’s calling a time-out, writing the contents of some of its memory to disk, and then proceeding.
Think of it like going to a gas station, getting a questionable hot dog, and then realizing that you need to make an urgent trip to the bathroom to … undo that decision. So reading a dump file is kinda like…actually, this analogy is more apt than I’d originally thought!
Start by querying sys.dm_server_memory_dumps. Run this query:
FROM sys.dm_server_memory_dumps AS dsmd
ORDER BY creation_time DESC;
That’ll tell you how many times it’s happened, and where the dump files live:
If you’re comfortable with a debugger, read the post Intro to Debugging a Memory Dump by Microsoft’s Adam Saxton. Adam writes:
Having some development experience is also helpful. While you may not need to look at Code directly in a dump, you are looking at the results of code. The concept of Pointers is sometimes hard for someone to grasp that doesn’t necessarily have a programming background, but when dealing with a memory dump, or debugging, the understanding of how Pointers work can be very helpful. Even when we are debugging a Managed (.NET) application instead of a Native (C++ or non-managed) Application. Another good book is Windows via C/C++ by Jeffrey Rickter and Christophe Nasarre.
Don’t feel bad if that sounds terrifying – it’s terrifying to me too. I don’t read dump files either.
If you’re not comfortable with that, patch your SQL Server, then call Microsoft. I dunno about you, but I’ve never been particularly good with a debugger, and I certainly don’t wanna learn when I’m facing a production SQL Server that’s freaking out so badly that it’s dumping memory contents to disk.
Hit up SQLServerUpdates.com to grab the latest patch for your SQL Server and apply that. You’d be pleasantly surprised at how often Microsoft fixes bugs that cause memory dumps, and the bug you’re hitting has probably already been fixed. (I’d hate to have you waste money on a support call only to hear that the bug is already fixed.)
After applying the patch, if the memory dumps continue, open a support case with Microsoft. I know: you may have had rough support experiences in the past where you were told to defrag your turn signal fluid and reboot the firewall, but repeated memory dumps are different. Memory dumps are much easier for them to analyze because they see ’em more often, and they have more specialized tools at their disposal. (For a brief moment in time, you could upload your memory dumps for free in SSMS, but Microsoft took that down due to GDPR security concerns since the dumps can contain personally identifiable information like queries and data.)
Don’t dilly-dally: memory dumps are not a small deal. You wouldn’t wanna be making repeated trips to the bathroom after multiple gas station hot dogs – that’s no way to live your life. Your SQL Server deserves better.