SQL Server – Cached Plans – Memory Utilization Per Object Type

Background

Let us review the type of objects in our cached plan.

 

Code

Stored Procedure

cachedplan.sp_MemoryUtilizationByPlanObjectType

 



use [master]
go

if schema_id('cachedplan') is null
begin

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

end
go

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

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

end
go


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

begin

    ; with cteSQLInstance
    as
    (
        select
              [memoryUsedBySqlserverInMB]
                = (physical_memory_in_use_kb/1024) --Memory_usedby_Sqlserver_MB,

            , [lockedPagesUsedBySqlserverInMB]
                = (locked_page_allocations_kb/1024 ) --Locked_pages_used_Sqlserver_MB

            /*

                , [totalVirtualAddressSpaceUsedInMB]
                    = (total_virtual_address_space_kb/1024 )

            */
            , [totalVirtualAddressSpaceReservedInMB]
                = (virtual_address_space_reserved_kb/1024 )

            , [totalVirtualAddressSpaceCommittedInMB]
                = (virtual_address_space_committed_kb/1024 )

            , [processMemoryLow]
                = case
                    when (process_physical_memory_low=1) then 'Yes'
                    else 'No'
                  end

            , [processVirtualMemoryLow]
                = case
                    when (process_virtual_memory_low=1) then 'Yes'
                    else 'No'
                  end	

        from sys. dm_os_process_memory
    )
    , ctePlanDB
    (
          [plan_handle]
        , [dbid]
        , [database]
    )
    as
    (

        select
              [plan_handle]
                 = cplan.[plan_handle]

            , [dbid] 
                = cast(dbPA.[value] as int)

            , [database]
                    = case
                            when ( cast(dbPA.[value] as int) = 32767) then 'Resource DB'
                            else db_name
                                    (
                                        cast(dbPA.[value] as int)
                                    )
                      end	

        FROM sys.dm_exec_cached_plans cplan

        CROSS APPLY sys.dm_exec_plan_attributes(cplan.plan_handle) dbPA 

        where dbPA.[attribute] = 'dbid'


    )
    SELECT 

              --[dbid] = ctePDB.[dbid]

              [database]
                = ctePDB.[database]

            , [objectType]
                = cplan.objtype

            , [totalMB]
                = cast
                    (
                        sum
                        (
                            cast(size_in_bytes as float)
                        )
                         /
                         ( 1024 * 1024)

                      as decimal(30, 2)

                    )

            , [%]
                = cast
                    (
                        sum
                        (
                            (
                                cast(size_in_bytes as float) /
                                    ( 1024 * 1024)
                            ) * 100.00
                            / cteSQLIns.[memoryUsedBySqlserverInMB]
                        )

                        as decimal(30, 2)

                    )

            , [totalPlans]
                =count_big(*)

           , [averageUseCounts]
                = avg(cplan.usecounts)

            , [totalMBPlansUsedMultipleTimes]
                = cast
                  (
                      sum
                      (
                        cast
                        (
                            (
                                CASE 
                                    WHEN (usecounts != 1) THEN cplan.[size_in_bytes]
                                    ELSE 0 
                                END
                            ) as decimal(18,2)
                        )
                    )
                    /
                    ( 1024 * 1024)

                    as decimal(30, 2)
                )

            , [countPlansUsedMultipleTimes]
                = sum(
                    CASE 
                        WHEN usecounts != 1 THEN 1 
                        ELSE 0 END
                ) 

            , [totalMBPlansUsedOnlyOnce]
                = cast
                  (

                    sum
                      (
                        cast
                        (
                            (
                                CASE WHEN usecounts = 1 THEN size_in_bytes 
                                    ELSE 0 
                                END
                            ) as decimal(18,2)
                        )
                     )

                     /

                     ( 1024 * 1024)

                    as decimal(30, 2)

                )

            , [countPlansPlansUsedOnlyOnce]
                = sum(
                    CASE 
                        WHEN usecounts = 1 THEN 1 
                        ELSE 0 END
                ) 

    FROM sys.dm_exec_cached_plans cplan

    inner join ctePlanDB ctePDB

        on cplan.plan_handle = ctePDB.plan_handle

    cross apply cteSQLInstance cteSQLIns

    where ctePDB.[dbid] = case

                            when ( @dbid is null ) then ctePDB.[dbid]
                            else @dbid
    
                         end


    GROUP BY 
                ctePDB.[dbid]
              , ctePDB.[database]
              , cplan.objtype

    ORDER BY 
            sum
            (
                cast(size_in_bytes as float)
             )
            /
            ( 1024 * 1024)
              desc

end
go

Invoke


exec [master].[cachedplan].[sp_MemoryUtilizationByPlanObjectType]

 

Output

Summary

Quick Notes

  1. Cached Plans
    • Prepared Statements
      • One can still experience high memory utilization with prepared statements
      • We noticed an upper limit of about 10% on systems that make heavy size of prepared statements

 

Reference

  1. Wiki > TechNet Articles > SQL Server Memory and Troubleshooting
    • SQL Server Memory and Troubleshooting
    • Authors
      • Shashank Singh ( Shanky )
        (MVP, Microsoft Partner, Microsoft Community Contributor)
    • Link
      Link
  2. Docs / SQL / Relational databases / System dynamic management views
    • sys.dm_os_process_memory (Transact-SQL)
      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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s