SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

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)
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
where Objtype = 'Adhoc'
  and text like '%msdb.%'

 

Which can be broken down to a query by query basis of

SELECT sum(size_in_bytes),count(*),substring(text,1,100)
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
where Objtype = 'Adhoc'
  and text like '%msdb.%'
group by substring(text,1,100) order by 1 desc

Connect item here if you feel like voting.

Comments

jamiet said:

I voted!

# January 22, 2010 10:40 AM

Dew Drop – January 22, 2010 | Alvin Ashcraft's Morning Dew said:

Pingback from  Dew Drop – January 22, 2010 | Alvin Ashcraft's Morning Dew

# January 22, 2010 2:16 PM

Aaron Bertrand said:

Give us easier to read execution plans Michelle Ufford ( @SQLFool ) recently asked for help pinpointing

# January 22, 2010 8:46 PM

SQL and the like said:

The system works and its called Microsoft Connect , who would of thought it :) Following on from my previous

# May 11, 2010 2:06 PM

SQL and the like said:

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

# May 11, 2010 6:48 PM