Paweł Domański
321 lines
12 KiB
Transact-SQL
321 lines
12 KiB
Transact-SQL
--=========================================--
|
||
/*** SQL SERVER MEMORY MONITORING QUERIES ***/
|
||
--=========================================--
|
||
-- Supported Versions SQL server 2008 and higher
|
||
-------------------------------------------------
|
||
|
||
----------------------------------
|
||
/*** 1. System Memory Status ***/
|
||
----------------------------------
|
||
|
||
SELECT total_physical_memory_kb / 1024 AS
|
||
[Total Physical Memory],
|
||
available_physical_memory_kb / 1024 AS
|
||
[Available Physical Memory],
|
||
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 AS
|
||
[Memory State]
|
||
FROM sys.dm_os_sys_memory;
|
||
|
||
|
||
---------------------------------------
|
||
/*** 2. SQL Server's memory Status ***/
|
||
---------------------------------------
|
||
-- this will show how much memory allocated to SQL Server , Buffer pool commit.
|
||
|
||
-- SQL server 2008 and earlier
|
||
|
||
SELECT
|
||
(bpool_committed*8)/1024.0 as [Buffer Pool Committed (MB)],
|
||
(bpool_commit_target*8)/1024.0 as [Buffer Pool Committed Targer (MB)]
|
||
FROM sys.dm_os_sys_info;
|
||
|
||
-- SQL Server 2012 and later
|
||
|
||
SELECT
|
||
(committed_kb)/1024.0 as [Buffer Pool Committed (MB)],
|
||
(committed_target_kb)/1024.0 as [Buffer Pool Committed Targer (MB)]
|
||
FROM sys.dm_os_sys_info;
|
||
|
||
|
||
----------------------------------------------
|
||
/*** 3. Physical Memory Used By SQL Sever***/
|
||
----------------------------------------------
|
||
-- Find the actual Memory used by SQL Server
|
||
|
||
select
|
||
convert(decimal (5,2),physical_memory_in_use_kb/1048576.0) AS 'Physical Memory Used By SQL (GB)',
|
||
convert(decimal (5,2),locked_page_allocations_kb/1048576.0) As 'Locked Page Allocation',
|
||
convert(decimal (5,2),available_commit_limit_kb/1048576.0) AS 'Available Commit Limit (GB)',
|
||
page_fault_count as 'Page Fault Count'
|
||
from sys.dm_os_process_memory;
|
||
|
||
------------------------------------------
|
||
/*** 4. Buffer Pool Usage By Databases ***/
|
||
------------------------------------------
|
||
|
||
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 'DataBase Name',
|
||
db_buffer_pages AS 'DB Buffer Pages',
|
||
db_buffer_pages / 128 AS 'DB Buffer Pages Used (MB)',
|
||
[mbempty] AS 'DB Buffer Pages Free (MB)',
|
||
CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer) AS 'DB Buffer Percentage'
|
||
FROM DBBuffer
|
||
ORDER BY [DB Buffer Pages Used (MB)] DESC;
|
||
|
||
|
||
--------------------------------------------
|
||
/*** 5. Memory Used By Database Objects ***/
|
||
--------------------------------------------
|
||
|
||
;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;
|
||
|
||
|
||
----------------------------------------
|
||
/*** 6. Costliest Stored Procedures ***/
|
||
----------------------------------------
|
||
-- Based on Logical reads
|
||
|
||
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;
|
||
|
||
|
||
----------------------------------------------
|
||
/*** 7. Top Performance Counters – Memory ***/
|
||
----------------------------------------------
|
||
|
||
-- 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 Counter,
|
||
( 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;
|