SQL Server – Cached Plans – Memory Utilization Per Plan

Background

Let us go in and review memory used by each plan in our cached plans.

Code Stolen

  1. Stack Exchange
    • Plan cache size and reserved memory
      Link

      • Contributors
        • Questioner
          • GordonLiddy
        • Narrator
          • Shanky
        • Answered By
          • Solomon Rutzky

Code

Stored Procedure

[master].[cachedplan].[sp_MemoryUtilizationByPlan]


use [master]
go


if object_id('dbo.sp_planMemoryUtilization') is not null
begin

    drop procedure dbo.sp_planMemoryUtilization

end
go


if schema_id('cachedplan') is null
begin

    exec('create schema [cachedplan] authorization [dbo]')

end
go

if object_id('[cachedplan].[sp_planMemoryUtilization]') is not null
begin

    drop procedure [cachedplan].[sp_planMemoryUtilization]

end
go

if object_id('[cachedplan].[sp_MemoryUtilizationByPlan]') is null
begin

    exec('create procedure [cachedplan].[sp_MemoryUtilizationByPlan] as ')

end
go


alter procedure [cachedplan].[sp_MemoryUtilizationByPlan]
(
    @dbid int = null
)
as 

begin

    /*
        Based on

            a) Plan cache size and reserved memory
               https://dba.stackexchange.com/questions/139214/plan-cache-size-and-reserved-memory

               Contributors :

                  Asked:
                    GordonLiddy

                  a) Shanky
                        i) Top 2%

                 Answered:
                    a) Solomon Rutzky
               

    */
    ; with cteMemoryObject
    (
          [page_allocator_address]
        , [allocatedBytes] 
        , [memObject]

    )
    as
    (

        SELECT
         
              [page_allocator_address]
                 = obj.page_allocator_address

            , [allocatedBytes]
                 = SUM(obj.[pages_in_bytes]) 

            , [memObject] =
                (
                    SELECT 
                        [memory_object_address]
                            = CONVERT
                            (
                                    VARCHAR(30)
                                , objInternal.memory_object_address
                                , 1
                            )
             
                        , objInternal.pages_in_bytes
                        , objInternal.page_size_in_bytes
                        , objInternal.[type]

    
                    FROM   sys.dm_os_memory_objects objInternal
    
                    WHERE  obj.page_allocator_address
                                = objInternal.page_allocator_address
                    
                    FOR XML RAW(N'object'), ROOT(N'memory_objects'), TYPE

                )  
            
        FROM   sys.dm_os_memory_objects obj

        group by
            obj.page_allocator_address

    )
    , cteSQLStatementDiscard
    (
        [sqlText]
    )
    as
    (
        select [sqlText]
                = 'SELECT (select schema_name()) AS [DefaultSchema]'

        union all

        select 'SET LOCK_TIMEOUT 10000'

        union all

        select 'SET STATISTICS XML ON'

        union all

        select 'SET STATISTICS XML OFF'

        union all

        select 'set statistics io on'

        union all

        select 'SELECT (select schema_name()) AS [DefaultSchema]'  

    )
    select 

           [poolID]
            = cplan.pool_id
       
        /*
         , [bucketID]
            = cplan.bucketid
        */

          , [refCounts]
            = cplan.refcounts

          , [useCounts] 
            = cplan.usecounts


          /*

            , [memoryObjectAddres]
                = cplan.memory_object_address

          */

          , [cacheObjType]
            = cplan.cacheobjtype

          , [objType]
            = cplan.objtype

          , [planHandle]
            =  cplan.plan_handle

          , [---]
            = '---'

          , [queryPlan]
            = qrypln.[query_plan]

          , [sqlText]
            = sqltext.[text]

          ,[---]
            = '---'

         , [pageInBytes]
            = planobj.pages_in_bytes

        , [BaseSingleStatementPlanKB]
            = planobj.pages_in_bytes / 1024 

        , [sizeInBytes]
            =  cplan.size_in_bytes

        , [===] 
            = '===' 

        , cteMO.[allocatedBytes]

        , cteMO.[memObject]
        
    FROM   sys.dm_exec_cached_plans cplan

    OUTER APPLY sys.dm_exec_sql_text(cplan.[plan_handle]) sqltext

    OUTER APPLY sys.dm_exec_query_plan(cplan.[plan_handle]) qrypln

    INNER JOIN sys.dm_os_memory_objects planobj
            ON cplan.memory_object_address = planobj.memory_object_address

    INNER JOIN  cteMemoryObject cteMO
        on planobj.page_allocator_address = cteMO.[page_allocator_address] 

    LEFT OUTER JOIN cteSQLStatementDiscard cteSD
        on ltrim(rtrim(sqltext.[text])) = ltrim(rtrim(cteSD.[sqlText]))


    CROSS APPLY sys.dm_exec_plan_attributes(cplan.plan_handle) dbPA 

    where dbPA.[attribute] = 'dbid'

    and   dbPA.[value] = case

                            when ( @dbid is null ) then dbPA.[value] 
                            else @dbid
    
                        end
 
    and    cplan.parent_plan_handle IS NULL

    AND    cplan.cacheobjtype IN 
                (
                      N'Compiled Plan'
                    , N'Compiled Plan Stub'
                )

    and cplan.objtype in (N'Adhoc')

    and sqltext.[text] not like '%sys.%'

    and not exists
        (

            select [sqlText]

            from    cteSQLStatementDiscard  cteSD

            where   ltrim(rtrim(cteSD.[sqlText]))
                     = ltrim(rtrim(sqltext.[text]))

        )

    and cteSD.[sqlText] is null

    order by
            cteMO.[allocatedBytes] desc


end
go

<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>

Invoke


declare @dbid int

set @dbid = db_id()

exec [master].[cachedplan].[sp_MemoryUtilizationByPlan]
		@dbid = @dbid

Output

Output – Grid

Output – queryPlan

Output – memoryObject

 

Acknowledge & Crediting

  1. Solomon Rutzky
    • Solomon Rutzky top 0.43% overall ( Link )I can usually be found lurking in the following places:
      • Sql Quantum Lift ( SQL# and soon OmniExec and others )
      • Sql Quantum Leap (my blog)
      • SQL Server Central ( Stairway to SQLCLR series )
      • CLR Performance Testing (on Simple Talk)
      • ASK.SQLServerCentral
      • Module Signing Info
  2. Shanky
    • A SQL Server architect, Microsoft SQL Server MVP, MCC and multiple times Microsoft TechNet Guru award winner. I like working on SQL server performance troubleshooting, SQL Server Memory SQL Server cluster and issues related to SQL Server installation. I love database engine internals and often try to demystify various features.
      Link

 

References

  1. Stack Exchange
    • Plan cache size and reserved memory
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s