/*
-----------------------------------------------------------------
Memory Queries
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Interrogating memory configuration.
-- Determine if this is a 32- or 64-bit SQL Server edition
DECLARE @ServerAddressing AS TINYINT
SELECT @serverAddressing = CASE WHEN CHARINDEX('64',
CAST(SERVERPROPERTY('Edition')
AS VARCHAR(100))) > 0
THEN 64
ELSE 32
END ;
SELECT cpu_count / hyperthread_ratio AS SocketCount ,
physical_memory_in_bytes / 1024 / 1024 AS physical_memory_mb ,
virtual_memory_in_bytes / 1024 / 1024 AS sql_max_virtual_memory_mb ,
-- same with other bpool columns as they are page oriented.
-- Multiplying by 8 takes it to 8K, then / 1024 to convert to mb
bpool_committed * 8 / 1024 AS buffer_pool_committed_mb ,
--64 bit OS does not have limitations with addressing as 32 did
CASE WHEN @serverAddressing = 32
THEN CASE WHEN virtual_memory_in_bytes / 1024 /
( 2048 * 1024 ) < 1
THEN 'off'
ELSE 'on'
END
ELSE 'N/A on 64 bit'
END AS [/3GB switch]
FROM sys.dm_os_sys_info
-- Is NUMA enabled
SELECT CASE COUNT(DISTINCT parent_node_id)
WHEN 1 THEN 'NUMA disabled'
ELSE 'NUMA enabled'
END
FROM sys.dm_os_schedulers
WHERE parent_node_id <> 32 ;
-- Show memory consumption details
DBCC MEMORYSTATUS
-- System memory usage.
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,
system_memory_state_desc
FROM sys.dm_os_sys_memory
-- Memory usage by the SQL Server process.
SELECT physical_memory_in_use_kb,
virtual_address_space_committed_kb,
virtual_address_space_available_kb,
page_fault_count,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory
-- Get total buffer usage by database
SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8 / 1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- exclude system databases
AND database_id <> 32767 -- exclude ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC ;
-- Breaks down buffers by object (table, index) in the buffer pool
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName],
p.index_id,
COUNT(*) / 128 AS [Buffer size(MB)],
COUNT(*) AS [Buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID() -- Change to suit
AND p.[object_id] > 100 -- exclude system objects
GROUP BY p.[object_id],
p.index_id
ORDER BY buffer_count DESC ;
-- Buffer Pool Usage for instance
SELECT TOP ( 20 )
[type],
SUM(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC ;
-- Which queries have requested, or have had to wait for, large memory grants?
-- Shows the memory required by both running (non-null grant_time)
-- and waiting queries (null grant_time)
-- SQL Server 2008 version
SELECT DB_NAME(st.[dbid]) AS [DatabaseName],
mg.requested_memory_kb,
mg.ideal_memory_kb,
mg.request_time,
mg.grant_time,
mg.query_cost,
mg.dop,
st.[text]
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE mg.request_time < COALESCE(grant_time, '99991231')
ORDER BY mg.requested_memory_kb DESC ;
-- Shows the memory required by both running (non-null grant_time)
-- and waiting queries (null grant_time)
-- SQL Server 2005 version
SELECT DB_NAME(st.[dbid]) AS [DatabaseName],
mg.requested_memory_kb,
mg.request_time,
mg.grant_time,
mg.query_cost,
mg.dop,
st.[text]
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE mg.request_time < COALESCE(grant_time, '99991231')
ORDER BY mg.requested_memory_kb DESC ;
-- Return the cache counters.
SELECT [type],
name,
single_pages_kb,
multi_pages_kb,
single_pages_in_use_kb,
multi_pages_in_use_kb,
entries_count,
entries_in_use_count
FROM sys.dm_os_memory_cache_counters
ORDER BY [type],
name ;
-- Investigating the use of the plan cache.
SELECT name,
[type],
entries_count,
entries_in_use_count
FROM sys.dm_os_memory_cache_counters
WHERE [type] IN ( 'CACHESTORE_SQLCP', 'CACHESTORE_OBJCP' )
--ad hoc plans and object plans
ORDER BY name,
[type]
-- AWE allocated memory
SELECT SUM(awe_allocated_kb) / 1024 AS [AWE allocated, Mb]
FROM sys.dm_os_memory_clerks
-- Single + Multi Page Memory Allocation
SELECT type,
SUM(virtual_memory_reserved_kb) AS [VM Reserved],
SUM(virtual_memory_committed_kb) AS [VM Committed],
SUM(awe_allocated_kb) AS [AWE Allocated],
SUM(shared_memory_reserved_kb) AS [SM Reserved],
SUM(shared_memory_committed_kb) AS [SM Committed],
SUM(multi_pages_kb) AS [MultiPage Allocator],
SUM(single_pages_kb) AS [SinlgePage Allocator]
FROM sys.dm_os_memory_clerks
GROUP BY type
-- amount of mem allocated though multipage allocator interface
SELECT SUM(multi_pages_kb)
FROM sys.dm_os_memory_clerks
-- amount of mem allocated though multipage allocator interface DETAILED
SELECT type,
SUM(multi_pages_kb) AS sizeinkb
FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb != 0
GROUP BY type
-- Detailed memory info per componant
DECLARE @total_alloc BIGINT
DECLARE @tab TABLE
(
type NVARCHAR(128) COLLATE database_default,
allocated BIGINT,
virtual_res BIGINT,
virtual_com BIGINT,
awe BIGINT,
shared_res BIGINT,
shared_com BIGINT,
topFive NVARCHAR(128),
grand_total BIGINT
) ;
-- note that this total excludes buffer pool committed memory as it represents the largest
-- consumer which is normal
SELECT @total_alloc = SUM(single_pages_kb + multi_pages_kb
+ ( CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0
END ) + shared_memory_committed_kb)
FROM sys.dm_os_memory_clerks
PRINT 'Total allocated (including from Buffer Pool): '
+ CAST(@total_alloc AS VARCHAR(10)) + ' Kb'
INSERT INTO @tab
SELECT type,
SUM(single_pages_kb + multi_pages_kb) AS allocated,
SUM(virtual_memory_reserved_kb) AS vertual_res,
SUM(virtual_memory_committed_kb) AS virtual_com,
SUM(awe_allocated_kb) AS awe,
SUM(shared_memory_reserved_kb) AS shared_res,
SUM(shared_memory_committed_kb) AS shared_com,
CASE WHEN ( ( SUM(single_pages_kb + multi_pages_kb
+ ( CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0
END ) + shared_memory_committed_kb) )
/ ( @total_alloc + 0.0 ) ) >= 0.05 THEN type
ELSE 'Other'
END AS topFive,
( SUM(single_pages_kb + multi_pages_kb
+ ( CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0
END ) + shared_memory_committed_kb) ) AS grand_total
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY ( SUM(single_pages_kb + multi_pages_kb
+ ( CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0
END ) + shared_memory_committed_kb) ) DESC
SELECT *
FROM @tab
Enjoy!
Chris
