December 21, 2011 Leave a comment
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.
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:
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
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: