dm_exec_cached_plans Bloat - by Dave Ballantyne

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 526485 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 1/22/2010 2:29:12 AM
Access Restriction Public


Best practice is to use parametrized queries.  It would seem that in many places SQLAgent does not follow this advice.  Presently our live systems procedure cache has 550mb of irrelevant plans.

To get the total count...

SELECT sum(size_in_bytes)
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
where Objtype = 'Adhoc'
  and text like '%msdb.%'

To get a summary of the queries...

SELECT sum(size_in_bytes),count(*),substring(text,1,100)
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
where Objtype = 'Adhoc'
  and text like '%msdb.%'
group by substring(text,1,100) order by 1 desc

Additionally the SQL executed seems to be peppered with (NOLOCK) 's and 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED'.  Again this is not best practice.

BEGIN TRAN UpdateMediaTables


DECLARE @BackupSetId int, @MediaSetId int, @LogDevName varchar(512)

SELECT @MediaSetId = media_set_id
FROM msdb..backupmediafamily AS bmf WITH (NOLOCK)
WHERE substring(bmf.physical_device_name,5,36) = '80A2E6DE-3E95-4645-B476-09E37306FF8C'
Sign in to post a comment.
Posted by Microsoft on 10/13/2010 at 2:36 PM
Thanks for the update. Unfortunately, we don't maintain a public bug fix list for major releases- primarily because of the housekeeping required to separate existing bug fixes and bug fixes which occur for new code developement. We do have these lists for cumaltive updates and service packs.

Since the issue is resolved in 2008R2, I'm closing the bug. Do let us know if you have further suggestions for improvement in this area.

Robann Mateja
Microsoft Sql Server PM
Posted by Dave Ballantyne on 5/11/2010 at 8:06 AM
Hi ,

A fix seems to know be in place for 2008r2, looks good.

Is there a "bug fix" list existing anywhere ? What else may of been fixed but not publicised ?
Posted by Amy [MSFT] on 2/24/2010 at 12:22 PM
Dave -

We looked into this and we did make a fix for this issue in SQL2008. It was a partial fix but we changed the queries that were using the most space in the cache.

I would recommend upgrading since we will not be making this fix for SQL2005.

I will keep this bug open to investigate if we should parameterize more queries for our next release.


Amy Lewis
Posted by Amy [MSFT] on 1/26/2010 at 8:17 AM
Dave -

Thanks for the feedback - we'll look into this and get back to you.


Amy Lewis
Posted by AaronBertrand on 1/22/2010 at 6:39 AM
Agreed, the engine and other components really shouldn't have any ad hoc queries baked in.