Today's script is a collection of a few memory queries I've collected from various sources over the years. These queries are by no means comprehensive but are ones I have certainly used the most.
----------------------------------------------------------------- ----------------------------------------------------------------- For more SQL resources, check out SQLServer365.blogspot.com ----------------------------------------------------------------- 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') 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 / SELECT CASE COUNT(DISTINCT parent_node_id) WHEN 1 THEN 'NUMA disabled' FROM sys.dm_os_schedulers WHERE parent_node_id <> 32 ; -- Show memory consumption details 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, 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, 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], 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 ORDER BY buffer_count DESC ; -- Buffer Pool Usage for instance SUM(single_pages_kb) AS [SPA Mem, Kb] FROM sys.dm_os_memory_clerks 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], 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], 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. FROM sys.dm_os_memory_cache_counters -- Investigating the use of the plan cache. FROM sys.dm_os_memory_cache_counters WHERE [type] IN ( 'CACHESTORE_SQLCP', 'CACHESTORE_OBJCP' ) --ad hoc plans and object plans SELECT SUM(awe_allocated_kb) / 1024 AS [AWE allocated, Mb] FROM sys.dm_os_memory_clerks -- Single + Multi Page Memory Allocation 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 -- 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 SUM(multi_pages_kb) AS sizeinkb FROM sys.dm_os_memory_clerks WHERE multi_pages_kb != 0 -- Detailed memory info per componant DECLARE @total_alloc BIGINT type NVARCHAR(128) COLLATE database_default, -- 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 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' 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 END ) + shared_memory_committed_kb) ) / ( @total_alloc + 0.0 ) ) >= 0.05 THEN type ( SUM(single_pages_kb + multi_pages_kb + ( CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb END ) + shared_memory_committed_kb) ) AS grand_total FROM sys.dm_os_memory_clerks ORDER BY ( SUM(single_pages_kb + multi_pages_kb + ( CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb END ) + shared_memory_committed_kb) ) DESC Enjoy!
Chris