tips >> performance tuning >> Tips for Using Performance Monitor Memory Counters ...

Tips for Using Performance Monitor Memory Counters

By : Brad McGehee Aug 23, 2005

This counter Memory Object: Pages/Sec, measures the number of pages per second that are paged out of RAM to disk, or paged into RAM from disk. The more paging that occurs, the more I/O overhead your server experiences, which in turn can decrease the performance of SQL Server.

Assuming that SQL Server is the only major application running on your server, then this figure should average near zero over a 24 hour period, except for occasional spikes, which are normal. If this is not the case, and this counter averages greater than 1, but less than 20, you still won't notice much of a performance degradation in SQL Server. But if the counter averages over 20 in a 24 hour period, then your server most likely needs more RAM. The more RAM a server has, the less paging it has to perform.

When paging spikes do occur, this generally is a result of database backups or restores, transaction log backups and restores, checkpoints, BCP or DTS activity, and other similar tasks. These spikes can be safely ignored.

Generally, on a physical server dedicated to SQL Server with an adequate amount of RAM, paging will average near zero. An adequate amount of RAM for SQL Server is a server that has a Buffer Hit Cache Ratio (described in more detail later) of 99% and higher. If you have a SQL Server that has a Buffer Hit Cache Ratio of 99% or higher for a period of 24 hours, but you are getting an average paging level of over 1, this generally means that you may be running other applications on the physical server other than SQL Server. If this is the case, you should remove those applications, allowing SQL Server to be the only major application on the physical server.

If your SQL Server is not running any other applications, and paging exceeds 1 on average for a 24 hour period, this could mean that you have changed the SQL Server memory settings. SQL Server should be configured so that it is set to the "Dynamically configure SQL Server memory" option, and the "Maximum Memory" setting should be set at the highest level. For optimum performance, SQL Server should be allowed to take as much RAM as it wants for its own use without having to compete for RAM with other applications. [6.5, 7.0, 2000] Updated 4-2-2004

*****

Another way to check to see if your SQL Server has enough physical RAM is to check the Memory Object: Available Bytes counter. This counter can be viewed from Performance Monitor or from the NT Server, Windows 2000, or Windows 2003 Task Manager (see the Performance tab). This value should be greater than 5MB. If not, then your SQL Server needs more physical RAM.

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. When the amount of available bytes is less than 4MB, most likely SQL Server is also paging (which it shouldn't) and is experiencing a performance hit. When this happens, you either need to increase the amount of physical RAM in the server, reduce the load on the server, or change your SQL Server's memory configuration settings appropriately. [7.0, 2000] Updated 4-2-2004

*****

Consider watching these two counters: SQLServer:Memory Manager: Total Server Memory (KB) and SQLServer:Memory Manager: Target Server Memory (KB). The first counter, SQLServer:Memory Manager: Total Server Memory (KB), tells you how much the mssqlserver service is currently using. This includes the total of the buffers committed to the SQL Server BPool and the OS buffers of the type "OS in Use".

The second counter, SQLServer:Memory Manager: Target Server Memory (KB), tells you how much memory SQL Server would like to have in order to operate efficiently. This is based on the number of buffers reserved by SQL Server when it is first started up.

If, over time, the SQLServer:Memory Manager: Total Server Memory (KB) counter is less than the SQLServer:Memory Manager: Target Server Memory (KB) counter, then this means that SQL Server has enough memory to run efficiently. On the other hand, 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. [7.0, 2000] Updated 5-25-2005