• Grey Twitter Icon
  • Grey Facebook Icon
  • Grey LinkedIn Icon
  • Grey Google+ Icon

© 2016 - 2019 All rights reserved. PigeonSQL.com | Filip Holub

How to identify memory bottlenecks in SQL Server with DMV?

December 19, 2018

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.

 

 

Please reload

RECENT POST
Please reload

  • Facebook Social Icon
  • Google+ Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon