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 :
Buffer management has two mechanisms :
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.
Compilations/sec
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.
Before going on to the memory monitoring of SQL, we should have answers to the below questions :
- What does SQL do with the memory ?
- Does it handle memory in the same way as other applications do ?
- Caching raw data
- Working on your queries
- Caching execution plans
Buffer management has two mechanisms :
- 'Buffer manager' to access and update database pages
- 'Buffer cache/pool' to reduce database file I/O
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.
Compilations/sec
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.
Comments
Post a Comment