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

Archives: January 2010

Cleaning up sys.dm_exec_cached_plans

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

0 comments, 1,070 reads

Posted in Dave Ballantynes blog on 27 January 2010

Microsoft – Follow best practices – Part 2

In addition to my previous post, another best practice is to not use NOLOCK and READ UNCOMMITTED transaction isolation level.

Here’s an excerpt from a profiler trace

BEGIN TRAN UpdateMediaTables


DECLARE @BackupSetId int, @MediaSetId int, @LogDevName varchar(512)


Read more

0 comments, 533 reads

Posted in Dave Ballantynes blog on 25 January 2010

Microsoft – Follow best practices!

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

SELECT sum(size_in_bytes

Read more

0 comments, 997 reads

Posted in Dave Ballantynes blog on 22 January 2010