Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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, 475 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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

SELECT…

Read more

0 comments, 259 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, 635 reads

Posted in Dave Ballantynes blog on 22 January 2010