Home Dashboard Directory Help
Search

dm_exec_cached_plans Bloat by Dave Ballantyne


Status: 

Closed
 as Fixed Help for as Fixed


16
0
Sign in
to vote
Type: Bug
ID: 526485
Opened: 1/22/2010 2:29:12 AM
Access Restriction: Public
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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'
Details
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.

Thanks
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 Microsoft 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.

Thanks,

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

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

Thanks,

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.
Sign in to post a workaround.