通过

SQL Server memory related performance counters

  • 项目

Some of the important performance counters related to memory in SQL Server are:

SQL Server Cache Hit Ratios: A consistent value below 90% indicates that more physical memory is needed on the server.

Memory Object: Available Bytes: On a server dedicated to SQL Server, SQL Server attempts to maintain from 4-10MB of free physical memory. The remaining physical RAM is used by the operating system and SQL Server.

Memory Object: Pages/Sec: Measures the number of pages per second that are paged out from RAM to disk. Higher the value, higher will be I/O activities and will result in decrease in performance. If you have only SQL server application running on the server then in most cases this value should be near zero. However you don’t see much performance degradation until it is 20, when SQL Server is not the only application. Above 20, it is an indication to have more RAM on the server.

SQLServer:Memory Manager: Total Server Memory (KB) :Tells you how much memory your SQL Server is currently using.

SQLServer:Memory Manager: Target Server Memory (KB) :Tells you howmuch memory SQL Server would like to use to operate efficiently. If the SQLServer:Memory Manager: Total Server Memory (KB) counter is more or equal than the SQLServer:Memory Manager: Target Server Memory (KB) counter, this indicates that SQL Server may be under memory pressure and could use access to more physical memory.

SQL Server Buffer Manager Object: Cache Size (pages) : Multiply this value by 8K to get the value of physical RAM devoted to SQL Server data cache. If this value is less than what you expect than SQL Server is starving on the memory and is not utilizing the available physical RAM.

Process: Working Set: shows the amount of memory used by process. If this number is consistently below Min Server Memory and Max Server Memory then SQL Server is configured to use too much memory.

When the system is running out of memory, it will have higher paging and disk I/O. You can measure the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging. You can also monitor SQL Server:Buffer Manager:Page reads/sec and SQL Server:Buffer Manager:Page writes/sec performance counters. Check more at: https://msdn2.microsoft.com/en-us/library/ms175903.aspx.

There is a relationship between SQL Server disk I/O and CPU usage. Check the article Monitoring CPU Usage - https://msdn2.microsoft.com/en-us/library/ms178072.aspx for more details.

Following are other very useful articles for troubleshooting SQL Server performance.

SQL Server Memory Monitoring - https://msdn2.microsoft.com/en-us/library/ms176018.aspx. This article also contains the list of performance counters for monitoring SQL Server memory. The Process: Working Set counter shows the amount of memory that is used by a process.

Troubleshooting Performance Problems in SQL Server 2005 - https://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

Comments

  • Anonymous
    December 19, 2008
    Hi. I was reading this blog and noticed that many of the recommendations you make in it are different that from other sources, including other Microsoft sources. I am curious as to where these numbers come from. Are they based directly on research done at Microsoft, and are they the "official guidelines" from Microsoft for these particular counters?