Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLSailor.com

Anup SivaDas is a Lead Engineer and Microsoft SQLServer professional with iGATE, having a total IT experience of more than 8 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, Consulting, Virtualization,Build, Run and Production Support activities. Blog | Twitter | LinkedIn

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


Comments

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

Loading comments...