How to identify memory bottlenecks in SQL Server with DMV?
Most of us already solved some problems in regards to performance of SQL Server. Most often we can find bottlenecks for CPU, Memory and Storage.
Today I want to show you a couple of useful Dynamic Management Views for identifying memory bottleneck.
These attributes can help for detecting problem with Memory:
Page Life Expectancy(PLE)
Wait Type - RESOURCE_SEMAPHORE
Memory Grants Pending
Get buffer usage by database
1) Page Life Expectancy(PLE) - This is number of seconds a when pages will stay in the buffer pool without references. In general, the minimal number is 300 but is not carved into stone, if you will have server with 8GB of RAM it should be OK, otherwise if you will have server with 256GB of RAM it signals that there is a lack of memory!
Script:
2) Wait Type - RESOURCE_SEMAPHORE - This is typical wait type which who tells us that the server does not have enough memory. For example in my case it looks like that:
That means that almost 30% of wait type is RESOURCE_SEMAPHORE and this is a lot of!
Script:
3) Memory Grants Pending - This is number tell us how many processes in SQL Server are waiting to be granted to Memory. If your server has enough space display 0(zero) in column Memory Grants Pending. In my case display 10 processes which means that it's not ok with memory.
Script:
4) Get buffer usage by database - This script show us how much memory (in the buffer pool) is being used by each database on the instance. In my case it looks in work like this:
As you can see, buffer pool is occupied of 99% for olny one database. HA! HA!
Also you can consider a few thinks like:
Missing and unused indexes
Statistics must be up to date
Performance tuning of queries
Etc...
All these script are from Glenn Berry a.k.a. Dr. DMV, he has the best script for diagnostic queries of SQL Server.
His scripts here: https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-december-2018/
It can be very useful for your problem with SQL Server.
If you have finished reading here, thanks a lot.