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.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')

                                                     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

Rate

Share

Share

Rate