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

How to Drop One Plan from Cache

While presenting this weekend at SQL Saturday #117 in Columbus, OH (great event, if you missed it, you missed it), I had what I thought was a little piece of throw-away code, but several people from the audience asked about it. Here it is:

DBCC FREEPROCCACHE(0x05000700618F532C40E190CE000000000000000000000000) ;

Not much to it is there?

The trick is, starting with SQL Server 2008, you can use the FREEPROCCACHE command to drop a single plan from the cache rather than completely clearing out the cache. I use it to show compile times & bad parameter sniffing and other things. You can use it to get rid of a plan in cache for whatever you might need to do that. You certainly don’t need to drop the entire procedure cache as people so frequently do. The only trick to using this is that you need to get the plan handle, that long, meaningless string inside the parentheses above. You can do that using this query (or several others):

SELECT  decp.plan_handle
FROM    sys.dm_exec_cached_plans AS decp
        CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
WHERE   dest.[text] LIKE 'CREATE PROC dbo.spAddressByCity%';

I’m joining the plans from cache that are displayed through sys.dm_exec_cached_plans to the query text through sys.dm_exec_sql_text and, in this case, searching for the CREATE PROCEDURE statement to find the one I’m interested in. That’s a quick & dirty way to get the job done. Simple stuff, but hopefully helpful.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...