Troubleshooting SQL Server performance problems associated with low memory conditions – should I use sys.dm_os_ring_buffers
I recently received a question through this blog about the use of the sys.dm_os_ring_buffers DMV with regard to troubleshooting performance problems and low memory conditions. Here are some excerpts – edits from my reply which might provide some ideas for you.
Here's an interesting article you might like to read about monitoring memory usage with ring buffers:
https://blogs.msdn.com/slavao/archive/2005/02/19/376714.aspx
In terms of my personal preferences regarding performance monitoring in SQL Server 2005, I find it rare that I have to use the ring buffers. They are of course undocumented externally (which is why you are having trouble finding information on them!), and so I tend to discuss with customers the usage of published documented tools as in 99% of cases these are enough to diagnose memory problems (and most others).
I would potentially take a different approach. As I often write in my blog I like to use the SQLDIAG tool to collect a variety of data when I am experiencing / monitoring a problem. In this case it might be useful to follow my post about collecting the DBCC MEMORYSTATUS output, in addition to the standard data when you are experiencing this problem. If you have experiencing a memory issue somewhere, this will give you the breakdown to diagnose it pretty much in all cases, and all the results of this output are publically documented. Alternatively though you could change the script which collects DBCC MEMORYSTATUS to collect some other hybrid selection of DMV based output, or even the ring buffers output.
The other benefit of this approach is that you collect a whole set of data which allows you to keep an open mind (which I like to do) about what the cause of the performance problem really is.
So in summary, should a customer come to me with a rough issue description like this (I have performance problems and I believe I am experiencing low memory conditions, my initial action plan would be as follows:
Run SQLDIAG for 30 minutes when the problem was occurring
Collect the SQL 2005 perfstats scripts
Collect DBCC MEMORYSTATUS add in
Collect a lightweight profiler (do not include statement level events or showplans)
Collect perfmon with all SQL Server counters like Buffer Manager added in .
And then I would review all the data. You could of course consider using SQLNEXUS to do this, or alternatively review the output manually as its all from documented in sources.
Here is a particularly good article which covers a lot of these documented issues, and has an excellent section of memory troubleshooting.
https://technet.microsoft.com/en-us/library/cc966540.aspx
Finally I just remembered that there is this public article as well which does document a good use for the sy.dm_os_ring_buffer output for a specific condition.
How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
Update: another more detailed post about resource manager and the ring buffer records it writes is now available here:
That said my comments still remain the same when you are going into a more generic performance problem.
Graham