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

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

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