Does my SQL server require memory upgrade ?

               The memory usage of SQL servers is always a hot topic among the system administrators. Any system administrator who handles a SQL server in their environment would have definitely faced this issue.

Before going on to the memory monitoring of SQL, we should have answers to the below questions :

  1. What does SQL do with the memory ?
  2. Does it handle memory in the same way as other applications do ?
SQL server uses memory for :

  • Caching raw data
  • Working on your queries
  • Caching execution plans
The memory management of SQL is different when compared to other applications. SQL is an application with intense I/O operations. Since I/O operations on disk is time consuming, the SQL server uses 'Buffer Management' mechanism.

Buffer management has two mechanisms : 

  • 'Buffer manager' to access and update database pages
  • 'Buffer cache/pool' to reduce database file I/O
Buffer manager is responsible for handling all read/write operations in the buffer, whereas the normal select, update etc..operations are handled by the database manager.

So when does a system administrator confirms that a memory upgrade is required?

There are performance counters which helps system administrator on this. Will discuss about the main performance counters for analyzing this. (Performance monitors can be accessed by running 'perfmon' in run)

SQLServer:Buffer Manager

Buffer cache hit ratio

Represents the percentage of pages found in the buffer cache without having to read from disk. A higher value for BCHR normally represents a better memory management. When this value dips, we should upgrade the RAM. The value is expected to be 100%.

Page life expectancy

This metric represents the number of seconds a page will stay in the buffer pool without references. In other words, this amount represents cached raw data. Higher the value, better it is. The metric is measured in secs. As a thumb rule, the value should be above 300s. 

SQLServer:SQL Statistics

Batch requests/sec 

This metric represents the number of batch requests the server is receiving per second. In other words, how busy is my sql server due to incoming requests. 


This value represents the number of times SQL Server compiles an execution plan per second. We cannot judge the performance with this metrix alone. The performance is deteremined when this metric is compared with Batch requests/sec.  As a thumb rule, if the value is less than 10% of the Batch requests/sec, then the performance is desirable. Whereas if the sql server is forced to compile more than 10% of Batches requests/sec then we should understand that there is not enough memory to store the execution plans and we should consider a memory upgrade.

For example: If your Batch requests/sec is 2000 and if your compilations/sec is less than 200 , then the value is desirable.

SQLServer:Memory Manager

Memory grants pending 

Represents the number of queries that are waiting for memory before they can even start. Any value greater than 0 represents a memory issue.

Target Server Memory

This metric represents the ideal amount of memory the server can consume. This value is almost equal to the RAM (if no limit is set using the SQL management studio).

Total server memory

This metric represents the amount of memory the server has committed using the memory manager. This counter shows what is actually used and the value will be low upon starting, and will increase gradually as the SQL server brings pages to  its buffer pool and finally reaches a steady state. When this reaches steady state, this will be almost equivalent to the Target Server Memory. Once it reaches the steady state, the value is not expected to dip. If this dips, it represents the memory deallocation due to memory requirement from OS or other applications. If this value is higher than target, then your server could benefit from additional RAM.

All these counters are just direction pointers. Please don't rely on one counter alone to confirm the requirement of memory upgrade. 


Popular posts from this blog

VMware and Windows Interview Questions: Part 2

VMware and Windows Interview Questions: Part 3

Active Directory FSMO Roles