Archives: January 2010
Following on from my previous post in which I noticed that SQLServer itself was artificially bloating sys.dm_exec_cached_plans, the next obvious question is “How can i clear this rubbish out ?”. On 2008 its simple enough, Microsoft have kindly enhanced DBCC FREEPROCCACHE to accept a plan_handle. On 2005 things are… Read more
Here’s an excerpt from a profiler trace
BEGIN TRAN UpdateMediaTables SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @BackupSetId int, @MediaSetId int, @LogDevName varchar(512) SELECT…
Best practice is to use parametrized queries to enable plan reuse. Will someone please tell Microsoft this.
Presently dm_exec_cached_plans on our live server has been bloated by 550mb of adhoc queries by SqlAgent (possibly a few other services)
Here’s how im calculating the total bloat value