SQL Server Query Caching – SYS.DM_EXEC_CACHED_PLANS Adhoc Plans (SYSCACHEOBJECTS)

On investigating memory pressure on a SQL Server, i started looking into the cached plans and whether memory was being utilised badly by the plan cache.

Here are some queries i used to see whether plans were being used once only.

-- How many plans are in the cache
SELECT COUNT(*) FROM sys.dm_exec_cached_plans;
-- How much space is being used by the plan cache
SELECT (SUM(size_in_bytes)/1024)/1024 as 'MB'  
FROM sys.dm_exec_cached_plans;
-- Returns a breakdown of the memory used by all compiled plans in the cache.
SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject, 
    omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes 
FROM sys.dm_exec_cached_plans AS ecp 
JOIN sys.dm_os_memory_objects AS omo 
    ON ecp.memory_object_address = omo.memory_object_address 
    OR ecp.memory_object_address = omo.parent_address
WHERE cacheobjtype = 'Compiled Plan';
GO
-- Plans which have only been used once
SELECT * FROM sys.dm_exec_cached_plans 
WHERE objtype = 'ADHOC' and usecounts < 2;
-- This statement will show you how much of your cache is 
-- allocated to single use plans
SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 
        THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 
                AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) 
                AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
go

The output from the above is useful in analysing how much space is being consumed in memory by plans which have only ever been used once.

image

The result set above shows a large quantity of all the plans in the cache have only been used once (11074) and are consuming 793 MB!

--Details for plans with usecount of 1
SELECT    bucketid, refcounts, usecounts, (size_in_bytes)/1024 as 'Size in KB', 
        cacheobjtype, objtype, [text] 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE objtype = 'ADHOC' and usecounts < 2;

As the below graphic shows, a lot of the single use plans are the same, apart from the ID criteria in the WHERE clause. If this happens, then it suggests that there are a lot of SQL compilations\sec which means that SQL Server Is having to build execution plans on the fly – a relatively CPU Intensive. This usually means SQL is being compiled dynamically by the application which can be seen in sys.dm_exec_cached_plans. For example:

image

So we have thousands of compiled ad hocs queries which are technically the same with different ID’s. To maintain the same plan, paramatize the query:

DECLARE @id bigint
Set @ID = 813232101024130

UPDATE tbl SET sold=1, lastChecked=GetTimeStamp() WHERE ID=@ID

On Investigation, SQL compilations per second are around 34% of the Batch Requests per second. Microsoft’s SQL Customer Advisory Team’s Top SQL 2005 OLTP Performance Issues says that if Compilations/sec is more than 10% of Batch Requests/sec, you may be experiencing CPU pressure because SQL Server has to build execution plans. However, on this occasion i didn’t believe that the server was under CPU pressure (Based on other performance stats) and also because the actual batches per second were quite low.

OPTIMISE FOR AD HOC WORKLOADS option

Starting from SQL Server 2008, there is an option to optimize the cache for workloads which have a large percentage of Adhoc Compiled plans – or in other words, one use plans.

When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected.

sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE;
GO

SYSCACHEOBJECTS

Does more or less the same as sys.dm_exec_cached_plans. It is a SQL 2000 system table which is included as a view in 2005/2008 for backward compatibility.

4 different DMV’s now do the job of SYSCACHEOBJECTS. These are:

sys.dm_exec_cached_plans

sys.dm_exec_plan_attributes

sys.dm_exec_sql_text

sys.dm_exec_cached_plan_dependent_objects

Follow

Get every new post delivered to your Inbox.

Join 129 other followers