Tuesday 7 February 2012

A Script A Day - Day 7 - Memory Queries

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.


/*
      -----------------------------------------------------------------
      Memory Queries
      -----------------------------------------------------------------
     
      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      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

No comments:

Post a Comment