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
ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);

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
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) = '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.