SQL Server – v2005 – Single Use Plans

Background

In MS SQL Server v2005, MSDB queries were submitted adhoc.

This can cause Plan Cache bloat and use up a lot of memory.

Diagnostic

Plans Aggregated by Cache Type

Kimberly L. Tripp has the most widely used query. It is shared here.

And, here it is.

Code


/*
	Kimberly Tripp
	Plan cache and optimizing for adhoc workloads
	http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
	2010-04-22
*/
SELECT 
		  [CacheType]
			= objtype

		, [Total Plans]
			= COUNT_BIG(*)

		, [Total MBs]
			= SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 

		, [Avg Use Count]
			= AVG(usecounts) 

		, [Total MBs – USE Count 1]
			= SUM(
				CAST(
						(
							CASE WHEN usecounts = 1 THEN size_in_bytes
								ELSE 0
							END
						) AS DECIMAL(18, 2)
					)
			) / 1024 / 1024 

		, [Total Plans – USE Count 1]
			= SUM
				(
					CASE WHEN usecounts = 1 THEN 1
						 ELSE 0
					END
				) 

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Total MBs – USE Count 1] DESC

GO

Output

PlansAggregatedByCacheType-20160512

Tally Single Use Plans


select

		 [Database]
			= case 
					when st.dbid = 32767 then 'Resource DB'
					else DB_NAME(st.dbid)
			  end

		, [DatabaseID]
			= st.[dbid]

		, [Number of Plans]
			= COUNT(*)

		, [SizeInMB]
			= cast
				(
					SUM( cast ( cp.size_in_bytes as bigint) ) 
					/ ( 1024 * 1024)
					as bigint
				)

from    sys.dm_exec_cached_plans cp

cross apply sys.dm_exec_sql_text(cp.plan_handle) st

WHERE cp.usecounts < 3

GROUP BY
	   st.[dbid]
         , DB_NAME(st.[dbid])

order by
	[SizeInMB] desc

Output

singleQueryPlansInEachDB - Prior

Compare Single Use Plans Vs All Plans



select

		 [Database]
			= case 
					when st.dbid = 32767 then 'Resource DB'
					else DB_NAME(st.dbid)
			  end

		, [DatabaseID]
			= st.[dbid]

		--, cp.objtype

		, [Number of Plans - Single]
			= sum
				(
					case
						when (cp.usecounts &amp;lt; 3) then 1
						else 0
					end
				)

		, [Number of Plans]
			= COUNT(*)

		, [%Number of Single Plans]
			= cast
				(
					sum
					(
						case
							when (cp.usecounts &amp;lt; 3) then 1
							else 0
						end
					) * 100.00
					/ COUNT(*)
					as decimal(10,2)
				)

		, [SizeInMB - Single]
			= 
				sum
				(
					case
						when (cp.usecounts &amp;lt; 3) 
							then cast(cp.size_in_bytes as bigint)
						else 0
					end							
				)
				/ ( 1024 * 1024)

		, [SizeInMB]
			=
				sum
				(
					cast(cp.size_in_bytes as bigint)
				)
				/ ( 1024 * 1024)

		, [%Number Single Plans Storage]
			=
				cast
					(
						sum
						(
							case
								when (cp.usecounts &amp;lt; 3) 
									then cast(cp.size_in_bytes as bigint)
								else 0
							end							
						) * 100.00
						/
						sum
						(
							cast(cp.size_in_bytes as bigint)
						)
					as decimal(10,2)
				)

from    sys.dm_exec_cached_plans cp

cross apply sys.dm_exec_sql_text(cp.plan_handle) st

GROUP BY
	  st.[dbid]
       , DB_NAME(st.[dbid])
--	, cp.objtype

order by
	    [SizeInMB] desc
	  , DB_NAME(st.[dbid]) asc



Output

singleQueryPlansInEachDBPercentile-Prior

 

Explanation

  1. Database = NULL ( plan is not compiled in a specific database )
    • Count
      • Single use plan count :- 37527
      • Total Plan Count :- 37886
      • Percentile :99.05%
    •  Size
      • Single use plan size :- 2.6 GB
      • Total Plan Cache Size :- 2.732 GB
      • Percentile :- 98.14%

 

Remediate

Force parameterization on specific databases


ALTER DATABASE [MSDB] SET PARAMETERIZATION FORCED;

 

Review

I will say wait a couple of days and see if your plan count stables up a bit.

Here is what we saw once we set parameterization to forced.

Plans Aggregated by Cache Type

Output

CachedPlanStatistics

Tally Single Use Plans

Output

SingleUsePlans-After-v2

 

Compare Single Use Plans Vs All Plans

Output

SingleUsePlansPercentile-After-v2

 

Connect Items

  1. Topic :- sql 2005 sql agent (msdb) is filling up procedure cache with adhoc queries
    • ID :- 445041
    • Opened By :- Geoffrey Crombez
    • Date Opened :- 5/8/2009
    • Status : Closed

Summary

In the images shown above, the database id is null, and we had to look at the detailed query text to determine they were msdb/sql server agent gated.

Also, the after effect shown above were taken just a few minutes later.

To truly measure the efficacy of these type of changes, please wait a few days or a week or two.

These problems take time to build-up and so durability of remedy warrant timeline and steadiness.

One thought on “SQL Server – v2005 – Single Use Plans

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