Script to Monitor SQL Server Memory Usage

Introduction:

This post will takes you through the T-SQL Script to monitor SQL Server Memory Usage. In previous blog post we have explained the parameters involved in understanding sql server memory usage. There are total 7 scripts to monitor SQL Server Memory Usage.

• Buffer Pool Usage
• System Memory Information
• SQL Server Process Memory Usage Information
• Buffer Usage by Database
• Object Wise Buffer Usage
• Top 25 Costliest Stored Procedures – Logical Reads
• Top Performance Counters

Script to Monitor SQL Server Memory Usage: Buffer Pool Usage

Results:

BPool_Committed_MB: Actual memory committed/used by the process (SQL Server).

BPool_Commit_Tgt_MB: Actual memory SQL Server tried to consume.

BPool_Visible_MB: Number of 8-KB buffers in the buffer pool that are directly accessible in the process virtual address space (SQL Server VAS).

Analysis:

BPool_Commit_Tgt_MB > BPool_Committed_MB: SQL Server Memory Manager tries to obtain additional memory

BPool_Commit_Tgt_MB < BPool_Committed_MB: SQL Server Memory Manager tries to shrink the amount of memory committed

If the value of BPool_Visible_MB is too low: We might receive out of memory errors or memory dump will be created.

/*********************************************/
--Script: Captures Buffer Pool Usage
--Works On: 2008, 2008 R2, 2012, 2014, 2016
/*********************************************/

-- SQL server 2008 / 2008 R2

SELECT
     (bpool_committed*8)/1024.0 as BPool_Committed_MB,
     (bpool_commit_target*8)/1024.0 as BPool_Commit_Tgt_MB,
     (bpool_visible*8)/1024.0 as BPool_Visible_MB
FROM sys.dm_os_sys_info;

-- SQL server 2012 / 2014 / 2016
SELECT
      (committed_kb)/1024.0 as BPool_Committed_MB,
      (committed_target_kb)/1024.0 as BPool_Commit_Tgt_MB,
      (visible_target_kb)/1024.0 as BPool_Visible_MB
FROM  sys.dm_os_sys_info;

Script to Monitor SQL Server Memory Usage: System Memory Information

Results:

total_physical_memory_mb: Actual Physical Memory installed in OS

available_physical_memory_mb: Available Physical Memory

total_page_file_mb: Pagefile size on OS

available_page_file_mb: Available page file size

Percentage_Used: Physical Memory Percentage used

system_memory_state_desc: Memory current Health status

Analysis:

available_physical_memory_mb: Should be some positive sign based on total physical memory

available_page_file_mb: Should be some positive sign based on your total page file

Percentage_Used: 100% for a long time indicates a memory pressure

system_memory_state_desc: should be Available physical memory is high / steady

/*********************************************************************/
--Script: Captures System Memory Usage
--Works On: 2008, 2008 R2, 2012, 2014, 2016
/*********************************************************************/

select
      total_physical_memory_kb/1024 AS total_physical_memory_mb,
      available_physical_memory_kb/1024 AS available_physical_memory_mb,
      total_page_file_kb/1024 AS total_page_file_mb,
      available_page_file_kb/1024 AS available_page_file_mb,
      100 - (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3))) 
      AS 'Percentage_Used',
      system_memory_state_desc
from  sys.dm_os_sys_memory;

Script to Monitor SQL Server Memory Usage: SQL Server Process Memory Usage

Results:

physical_memory_in_use: Indicates the process working set in KB, as reported by operating system, as well as tracked allocations by using large page APIs

locked_page_allocations: Specifies memory pages locked in memory

virtual_address_space_committed: Indicates the amount of reserved virtual address space that has been committed or mapped to physical pages.

available_commit_limit: Indicates the amount of memory that is available to be committed by the process (SQL server)

page_fault_count: Indicates the number of page faults that are incurred by the SQL Server process

Analysis:

physical_memory_in_use: We can’t figure out the exact amount of physical memory using by sqlservr.exe using task manager but this column showcase the actual amount of physical memory using by SQL Server.

locked_page_allocations: If this is > 0 means Locked Pages is enabled for SQL Server which is one of the best practice

available_commit_limit: This indciates the available amount of memory that can be committed by the process sqlservr.exe

page_fault_count: Pages fetching from the page file on the hard disk instead of from physical memory. Consistently high number of hard faults per second represents Memory pressure.

/**************************************************************/
-- Script: SQL Server Process Memory Usage
-- Works On: 2008, 2008 R2, 2012, 2014, 2016
/**************************************************************/
select
      physical_memory_in_use_kb/1048576.0 AS 'physical_memory_in_use (GB)',
      locked_page_allocations_kb/1048576.0 AS 'locked_page_allocations (GB)',
      virtual_address_space_committed_kb/1048576.0 AS 'virtual_address_space_committed (GB)',
      available_commit_limit_kb/1048576.0 AS 'available_commit_limit (GB)',
      page_fault_count as 'page_fault_count'
from  sys.dm_os_process_memory;

Script to Monitor SQL Server Memory Usage: Database Wise Buffer Usage

Results:

db_name: Name of the database in the given SQL server Instance

db_buffer_pages: Total number of corresponding database pages that are in buffer pool

db_buffer_Used_MB: Database wise Buffer size used in MB

db_buffer_Free_MB: Database wise Buffer Size Free (sum of free space on all pages) in MB.

db_buffer_percent: Database wise percentage of Buffer Pool usage

Analysis:

We can quickly find out the top databases which are consuming more Memory / Buffer Pool from the given SQL server Instance

/**************************************************************/
--Script: Database Wise Buffer Usage
--Works On: 2008, 2008 R2, 2012, 2014, 2016
/**************************************************************/

DECLARE @total_buffer INT;
SELECT  @total_buffer = cntr_value 
FROM   sys.dm_os_performance_counters
WHERE  RTRIM([object_name]) LIKE '%Buffer Manager' 
       AND counter_name = 'Database Pages';

;WITH DBBuffer AS
(
SELECT  database_id,
        COUNT_BIG(*) AS db_buffer_pages,
        SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM    sys.dm_os_buffer_descriptors
GROUP BY database_id
)
SELECT
       CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END AS 'db_name',
       db_buffer_pages AS 'db_buffer_pages',
       db_buffer_pages / 128 AS 'db_buffer_Used_MB',
       [mbempty] AS 'db_buffer_Free_MB',
       CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer) AS 'db_buffer_percent'
FROM   DBBuffer
ORDER BY db_buffer_Used_MB DESC;

Script to Monitor SQL Server Memory Usage: Object Wise Buffer Usage

Results:

Object: Name of the Object

Type: Type of the object Ex: USER_TABLE

Index: Name of the Index

Index_Type: Type of the Index “Clustered / Non Clustered / HEAP” etc

buffer_pages: Object wise number of pages is in buffer pool

buffer_mb: Object wise buffer usage in MB

Analysis:

From the previous script we can get the top databases using memory. This script helps you out in finding the top objects that are using the buffer pool. Top objects will tell you the objects which are using the major portion of the buffer pool.If you find anything suspicious then you can dig into it.

/**************************************************************/
--Script: Object Wise Buffer Usage
--Works On: 2008, 2008 R2, 2012, 2014, 2016
/**************************************************************/

;WITH obj_buffer AS
(
SELECT
       [Object] = o.name,
       [Type] = o.type_desc,
       [Index] = COALESCE(i.name, ''),
       [Index_Type] = i.type_desc,
       p.[object_id],
       p.index_id,
       au.allocation_unit_id
FROM
       sys.partitions AS p
       INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id
       INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id]
       INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id
WHERE
       au.[type] IN (1,2,3) AND o.is_ms_shipped = 0
)
SELECT
       obj.[Object],
       obj.[Type],
       obj.[Index],
       obj.Index_Type,
       COUNT_BIG(b.page_id) AS 'buffer_pages',
       COUNT_BIG(b.page_id) / 128 AS 'buffer_mb'
FROM
       obj_buffer obj 
       INNER JOIN sys.dm_os_buffer_descriptors AS b ON obj.allocation_unit_id = b.allocation_unit_id
WHERE
       b.database_id = DB_ID()
GROUP BY
       obj.[Object],
       obj.[Type],
       obj.[Index],
       obj.Index_Type
ORDER BY
       buffer_pages DESC;

Script to Monitor SQL Server Memory Usage: Top 25 Costliest Stored Procedures by Logical Reads

Results:

SP Name: Stored Procedure Name

TotalLogicalReads: Total Number of Logical Reads since this stored procedure was last compiled

AvgLogicalReads: Average Number of Logical Reads since this stored procedure was last compiled

execution_count: Number of Times SP got executed since it was compiled

total_elapsed_time: Total elapsed time for this proc since last time compiled

avg_elapsed_time: Average elapsed time

cached_time: Time at which the stored procedure was added to the cache.

Analysis:

• This helps you find the most expensive cached stored procedures from a memory perspective
• You should look at this if you see signs of memory pressure
• More number of logical reads means you need to check execution plan to find the bottleneck

/**************************************************************/
--Script: Top 25 Costliest Stored Procedures by Logical Reads
--Works On: 2008, 2008 R2, 2012, 2014, 2016
/**************************************************************/

SELECT  TOP(25)
        p.name AS [SP Name],
        qs.total_logical_reads AS [TotalLogicalReads],
        qs.total_logical_reads/qs.execution_count AS [AvgLogicalReads],
        qs.execution_count AS 'execution_count',
        qs.total_elapsed_time AS 'total_elapsed_time',
        qs.total_elapsed_time/qs.execution_count AS 'avg_elapsed_time',
        qs.cached_time AS 'cached_time'
FROM    sys.procedures AS p
        INNER JOIN sys.dm_exec_procedure_stats AS qs 
                   ON p.[object_id] = qs.[object_id]
WHERE
        qs.database_id = DB_ID()
ORDER BY
        qs.total_logical_reads DESC;

Script to Monitor SQL Server Memory Usage: Top Performance Counters – Memory

Results:

Total Server Memory: Shows how much memory SQL Server is using. The primary use of SQL Server’s memory is for the buffer pool, but some memory is also used for storing query plans and keeping track of user process information.

Target Server Memory: This value shows how much memory SQL Server attempts to acquire. If you haven’t configured a max server memory value for SQL Server, the target amount of memory will be about 5MB less than the total available system memory.

Connection Memory (GB): The Connection Memory specifies the total amount of dynamic memory the server is using for maintaining connections

Lock Memory (GB): Shows the total amount of memory the server is using for locks

SQL Cache Memory: Total memory reserved for dynamic SQL statements.

Optimizer Memory: Memory reserved for query optimization.

Granted Workspace Memory: Total amount of memory currently granted to executing processes such as hash, sort, bulk copy, and index creation operations.

Cursor memory usage: Memory using for cursors

Free pages: Amount of free space in pages which are commited but not currently using by SQL Server

Reserved Pages: Shows the number of buffer pool reserved pages.

Stolen pages (MB): Memory used by SQL Server but not for Database pages.It is used for sorting or hashing operations, or “as a generic memory store for allocations to store internal data structures such as locks, transaction context, and connection information.

Cache Pages: Number of 8KB pages in cache.

Page life expectancy: Average how long each data page is staying in buffer cache before being flushed out to make room for other pages

Free list stalls / sec: Number of times a request for a “free” page had to wait for one to become available.

Checkpoint Pages/sec: Checkpoint Pages/sec shows the number of pages that are moved from buffer to disk per second during a checkpoint process

Lazy writes / sec: How many times per second lazy writer has to flush dirty pages out of the buffer cache instead of waiting on a checkpoint.

Memory Grants Outstanding: Number of processes that have successfully acquired workspace memory grant.

Memory Grants Pending: Number of processes waiting on a workspace memory grant.

process_physical_memory_low: Process is responding to low physical memory notification

process_virtual_memory_low: Indicates that low virtual memory condition has been detected

Min Server Memory: Minimum amount of memory SQL Server should acquire

Max Server Memory: Maximum memory that SQL Server can acquire from OS

Buffer cache hit ratio: Percentage of pages that were found in the buffer pool without having to incur a read from disk.

Analysis:

Total Server Memory is almost same as Target Server Memory: Good Health

Total Server Memory is much smaller than Target Server Memory: There is a Memory Pressure or Max Server Memory is set to too low.

Connection Memory: When high, check the number of user connections and make sure it’s under expected value as per your business

Optimizer Memory: Ideally, the value should remain relatively static. If this isn’t the case you might be using dynamic SQL execution excessively.

Higher the value for Stolen Pages: Find the costly queries / procs and tune them

Higher the value for Checkpoint Pages/sec: Problem with I/O, Do not depend on Automatic Checkpoints and use In-direct checkpoints.

Page life expectancy: Usually 300 to 400 sec for each 4 GB of memory. Lesser the value means memory pressure

Free list stalls / sec: High value indicates that the server could use additional memory.

Memory Grants Outstanding: Higher value indicates peak user activity

Memory Grants Pending: Higher value indicates SQL Server need more memory

process_physical_memory_low & process_virtual_memory_low: Both are equals to 0 means no internal memory pressure

Min Server Memory: If it is 0 means default value didnt get changed, it’ll always be better to have a minimum amount of memory allocated to SQL Server

Max Server Memory: If it is default to 2147483647, change the value with the correct amount of memory that you can allow SQL Server to utilize.

Buffer cache hit ratio: This ratio should be in between 95 and 100. Lesser value indicates memory pressure

/**************************************************************/
--Script: Top Performance Counters - Memory
--Works On: 2008, 2008 R2, 2012, 2014, 2016
/**************************************************************/

-- Get size of SQL Server Page in bytes
DECLARE @pg_size INT, @Instancename varchar(50)
SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'

-- Extract perfmon counters to a temporary table
IF OBJECT_ID('tempdb..#perfmon_counters') is not null DROP TABLE #perfmon_counters
SELECT * INTO #perfmon_counters FROM sys.dm_os_performance_counters;

-- Get SQL Server instance name as it require for capturing Buffer Cache hit Ratio
SELECT  @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) 
FROM    #perfmon_counters 
WHERE   counter_name = 'Buffer cache hit ratio';


SELECT * FROM (
SELECT  'Total Server Memory (GB)' as Cntr,
        (cntr_value/1048576.0) AS Value 
FROM    #perfmon_counters 
WHERE   counter_name = 'Total Server Memory (KB)'
UNION ALL
SELECT  'Target Server Memory (GB)', 
        (cntr_value/1048576.0) 
FROM    #perfmon_counters 
WHERE   counter_name = 'Target Server Memory (KB)'
UNION ALL
SELECT  'Connection Memory (MB)', 
        (cntr_value/1024.0) 
FROM    #perfmon_counters 
WHERE   counter_name = 'Connection Memory (KB)'
UNION ALL
SELECT  'Lock Memory (MB)', 
        (cntr_value/1024.0) 
FROM    #perfmon_counters 
WHERE   counter_name = 'Lock Memory (KB)'
UNION ALL
SELECT  'SQL Cache Memory (MB)', 
        (cntr_value/1024.0) 
FROM    #perfmon_counters 
WHERE   counter_name = 'SQL Cache Memory (KB)'
UNION ALL
SELECT  'Optimizer Memory (MB)', 
        (cntr_value/1024.0) 
FROM    #perfmon_counters 
WHERE   counter_name = 'Optimizer Memory (KB) '
UNION ALL
SELECT  'Granted Workspace Memory (MB)', 
        (cntr_value/1024.0) 
FROM    #perfmon_counters 
WHERE   counter_name = 'Granted Workspace Memory (KB) '
UNION ALL
SELECT  'Cursor memory usage (MB)', 
        (cntr_value/1024.0) 
FROM    #perfmon_counters 
WHERE   counter_name = 'Cursor memory usage' and instance_name = '_Total'
UNION ALL
SELECT  'Total pages Size (MB)', 
        (cntr_value*@pg_size)/1048576.0 
FROM    #perfmon_counters 
WHERE   object_name= @Instancename+'Buffer Manager' 
        and counter_name = 'Total pages'
UNION ALL
SELECT  'Database pages (MB)', 
        (cntr_value*@pg_size)/1048576.0 
FROM    #perfmon_counters 
WHERE   object_name = @Instancename+'Buffer Manager' and counter_name = 'Database pages'
UNION ALL
SELECT  'Free pages (MB)', 
        (cntr_value*@pg_size)/1048576.0 
FROM    #perfmon_counters 
WHERE   object_name = @Instancename+'Buffer Manager' 
        and counter_name = 'Free pages'
UNION ALL
SELECT  'Reserved pages (MB)', 
        (cntr_value*@pg_size)/1048576.0 
FROM    #perfmon_counters 
WHERE   object_name=@Instancename+'Buffer Manager' 
        and counter_name = 'Reserved pages'
UNION ALL
SELECT  'Stolen pages (MB)', 
        (cntr_value*@pg_size)/1048576.0 
FROM    #perfmon_counters 
WHERE   object_name=@Instancename+'Buffer Manager' 
        and counter_name = 'Stolen pages'
UNION ALL
SELECT  'Cache Pages (MB)', 
        (cntr_value*@pg_size)/1048576.0 
FROM    #perfmon_counters 
WHERE   object_name=@Instancename+'Plan Cache' 
        and counter_name = 'Cache Pages' and instance_name = '_Total'
UNION ALL
SELECT  'Page Life Expectency in seconds',
        cntr_value 
FROM    #perfmon_counters 
WHERE   object_name=@Instancename+'Buffer Manager' 
        and counter_name = 'Page life expectancy'
UNION ALL
SELECT  'Free list stalls/sec',
        cntr_value 
FROM    #perfmon_counters 
WHERE   object_name=@Instancename+'Buffer Manager' 
        and counter_name = 'Free list stalls/sec'
UNION ALL
SELECT  'Checkpoint pages/sec',
        cntr_value 
FROM    #perfmon_counters 
WHERE   object_name=@Instancename+'Buffer Manager' 
        and counter_name = 'Checkpoint pages/sec'
UNION ALL
SELECT  'Lazy writes/sec',
        cntr_value 
FROM    #perfmon_counters 
WHERE   object_name=@Instancename+'Buffer Manager' 
        and counter_name = 'Lazy writes/sec'
UNION ALL
SELECT  'Memory Grants Pending',
        cntr_value 
FROM    #perfmon_counters 
WHERE   object_name=@Instancename+'Memory Manager' 
        and counter_name = 'Memory Grants Pending'
UNION ALL
SELECT  'Memory Grants Outstanding',
        cntr_value 
FROM    #perfmon_counters 
WHERE   object_name=@Instancename+'Memory Manager' 
        and counter_name = 'Memory Grants Outstanding'
UNION ALL
SELECT  'process_physical_memory_low',
        process_physical_memory_low 
FROM    sys.dm_os_process_memory WITH (NOLOCK)
UNION ALL
SELECT  'process_virtual_memory_low',
        process_virtual_memory_low 
FROM    sys.dm_os_process_memory WITH (NOLOCK)
UNION ALL
SELECT  'Max_Server_Memory (MB)' ,
        [value_in_use] 
FROM    sys.configurations 
WHERE   [name] = 'max server memory (MB)'
UNION ALL
SELECT  'Min_Server_Memory (MB)' ,
        [value_in_use] 
FROM    sys.configurations 
WHERE   [name] = 'min server memory (MB)'
UNION ALL
SELECT  'BufferCacheHitRatio',
        (a.cntr_value * 1.0 / b.cntr_value) * 100.0 
FROM    sys.dm_os_performance_counters a
        JOIN (SELECT cntr_value,OBJECT_NAME FROM sys.dm_os_performance_counters
              WHERE counter_name = 'Buffer cache hit ratio base' AND 
                    OBJECT_NAME = @Instancename+'Buffer Manager') b ON 
                    a.OBJECT_NAME = b.OBJECT_NAME WHERE a.counter_name = 'Buffer cache hit ratio' 
                    AND a.OBJECT_NAME = @Instancename+'Buffer Manager'

) AS D;

Script to Monitor SQL Server Memory Usage: DBCC MEMORYSTATUS

Finally DBCC MemoryStatus:

• It gives as much as memory usage information based on object wise / component wise.
• First table gives us the complete details of server and process memory usage details and memory alert indicators.
• We can also get memory usage by buffer cache, Service Broker, Temp tables, Procedure Cache, Full Text, XML, Memory Pool Manager, Audit Buffer, SQLCLR, Optimizer, SQLUtilities, Connection Pool etc.

Summary:

These Scripts will help you in understanding the current memory usage by SQL Server. To maintain a healthy database management system:

• Monitor the system for few business days in peak hours and fix the baselines
• Identify the correct required configurations for your database server and make the required changes
• Identify top 10 queries / procedures based on Memory and CPU usage
• Fine tune these top 10 queries / procedures

Note:

These scripts are tested on SQL Server 2008, 2008 R2, 2012 and 2014. As we always suggests please test these scripts on Dev/Test environment before using them on production systems.

References:

Would like to thank famous MVPs / MCM / bloggers (Glenn Berry, Brent Ozar, Jonathan Kehayias, John Sansom) for the tremendous explanation on sql server internals. Their articles are very informative and helpful in understanding SQL Server internals.

Also Check:

SQL Server Memory Usage

SQL Server CPU Utilization