How to Identify: SQL Server is Designed to consume all the memory on the server. High memory utilization is often considered as normal in SQL server. Below are the few points that indicates the memory pressure in SQL server.
1. Free memory on server is less than 4% of total server memory.
2. High values of perfmon counters like Lazy Writes/Sec, Memory grant pending, Disk Reads/Sec
3. Low values of perfmon counters like PLE, Buffer cache hit ratio
4. SQL Server wait type Resource Semaphore
There could be three different scenarios of memory contention.
1. Server has enough free memory but Buffer pool is exhausted.
2. SQL server has enough memory buffer but not much left for OS and other operations.
3. Memory buffer and OS memory both are exhausted.
Let’s discuss each point.
Server has enough free memory but Buffer pool is exhausted
Enough free space on server but low value of perfmon counters like PLE and buffe cache hit ratio and high value of counters like Lazy Writes/Sec or Paging File % Usage indicates the SQL Server buffer memory pressure.
buffer cache hit ratio: This is ratio of data pages found in SQL server buffer cache and total data pages requested. Whenever any query needs data page, SQL first checks the availability of page in buffer pool. If the page is not available in buffer pool then SQL has to bring the page in the buffer pool. If the total number of page requested is 100 and 98 were satisfied by buffer pool then buffer cache hit ratio is (98/100) *100=98
Recommended Value: It should be very close to 100. A value less than 95 is not acceptable.
Page Life Expectancy(PLE): Average number of seconds a page will stay in buffer pool.
Recommended Value: I would recommend to read the blog written by Jonathan Kehayias
Lazy Writes/Sec: Number of pages flushed from buffer memory every second.
Recommended Value: Continuous non-zero value indicates the memory pressure.
SQL server has enough memory buffer but not much left for buffer and other operation
We should always leave enough memory for OS. There is a common misconception that Max Server memory restrict the memory used by SQL server. It mainly restricts the buffer memory and CLR memory. SQL server can still use the remaining memory for memory grants. You should always leave at least 10% of server memory and not more than 20% of server memory for OS.
If free memory on the server is too low and perfmon counter value of PLE, Buffer cache hit ratio is too high then there is a good possibility that you have oversized the buffer pool. You can decrease the value of Max Server memory to leave more memory for OS but you should always have enough evidence to prove that the buffer is over-sized (tricky part )
There is another possibility that buffer memory has been sized properly but SQL server is using lots of extra memory to satisfy the memory grants. You should always review the queries that demands high memory grants and try to tune these queries. You should review the query execution plan and look for the operators like sort, Hash Join and Hash match. These operators request for memory grant to perform the operation in memory. If it does not find the required memory to perform the operation then it uses tempdb to perform the task, which is known as tempdb spill. Click here to know more about tempdb spill. Below are few actions that you can take to resolve high memory grants and tempdb spill issues:
1. Creating adequate indexes can resolve the problem most of the times.
2. Review your queries and remove unnecessary order by, group by and aggregations
3. Removing the columns/Rows that are not required.
Resource Semaphore wait type: SQL transaction often request for memory grant to perform tasks like sort, join and aggregate. SQL server assign this memory to transaction if server has enough memory to satisfy the memory grant but server is in under memory and it do not have enough memory to satisfy the grant then transaction has to wait for memory to be available. SQL server assign the resource semaphore wait type to transaction waiting for memory grants.
Memory buffer and OS memory both are exhausted
1. Get the rid of unnecessary usages of memory (As discussed in point 1 and 2)
2. Increasing memory on the server.
YouTube : Sql depository