Script to Monitor 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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*********************************************/
--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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*********************************************************************/
--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.
1
2
3
4
5
6
7
8
9
10
11
/**************************************************************/
-- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/**************************************************************/
--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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
/**************************************************************/
--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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/**************************************************************/
--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
Top Performance Counters - Memory
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
/**************************************************************/
--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;
http://udayarumilli.com/script-to-monitor-sql-server-memory-usage/