SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Plan cache bloat by SQLAgent (Prior to SQL2008) !

Recently I was checking the plan cache utilization with the help of the below query which I normally use for this purpose. This is written by Glenn Berry(B/T) and is available along with his SQL Server 2005 Diagnostic Information Queries.All credit goes to him.

SELECT TOP(50) [text] AS [QueryText], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE cp.cacheobjtype = N'Compiled Plan' 
AND cp.objtype = N'Adhoc' 
AND cp.usecounts = 1

This query will pull top 50 single use ad-hoc queries which are bloating the plan cache. Glenn has mentioned that SQLServer Agent will create lot of ad-hoc single use queries in SQL 2005,however I had never noticed that before.

That day when I ran the above query I received multiple statements like

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) = '98E410FA-78ED-43BE-864F-C4F014EBD055'

This confirmed the fact that SQLAgent indeed fires lot of ad-hoc queries and that was not a good sign.

I was curious to check more and see if this issue was reported anytime to Microsoft and wanted to know if SQLServer product team did fix this issue for releases after SQL 2005.

I was able to find this connect item which was dealing this issue, and on this item Microsoft confirmed that they have fixed this in SQL2008.

I’m not sure for which SQLServer 2008 SP or CU did Microsoft released this fix. Dave Ballantyne who opened the above connect item have also mentioned that this fix is in place for SQL2008 R2 too.

Yes,we have yet another reason for moving forward with upgrading your SQLServer environment to run the latest builds.

Thanks for reading.


Anup SivaDas handles the Database Engineering initiatives for Expedia, Inc. (http://www.expedia.com/), having IT experience of more than 9 years. Anup is an active blogger with SQLSailor.com, and can also be found on MSDN SQLServer forums and BeyondRelational.com. He has handled multiple SQLServer projects for various fortune 500 companies, and gained enrich proficiency within Database Administration, Database Architecture for Cloud, Consulting, Virtualization, Build, and Production Support activities. Blog | Twitter | LinkedIn


Leave a comment on the original post [sqlsailor.com, opens in a new window]

Loading comments...