THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Clearing a Single Plan from Cache

Caching and reusing query plans is both a good thing and a bad thing. Saving costs of recompilation for a query that is run repeatedly can be a good thing. Using a cached plan that is not appropriate can be a very bad thing.

What do you do if you have plans in cache that should not be there?

SQL Server 2000 and 2005 provide a way to clear all the plans from plan cache with DBCC FREEPROCCACHE. There is also a way to clear all plans from a single database using DBCC FLUSHPROCINDB(<db_id>)

Rumor has it that SQL Server 2008 will provide a way to clear a single plan from cache. The details of exactly how this will be accomplished, and how you can specify which plan to remove are not available yet. However, in SQL Server 2005 there is a way to remove a single plan using the new SQL Server 2005 Plan Guide feature. This trick only works for plans where the objtype value for the plan in sys.syscacheobjects is 'prepared' and is not 'adhoc'. It is not needed for 'proc' plans, because 'proc' plans can be easily removed by simply using the ALTER PROC command. Prepared plans, whether they are created through autoparameterization, by using sp_executesql, or by using prepare and execute methods in your application can be removed by creating a plan guide that matches the parameterized query. You can then drop the plan guide if you don't want to continue using it.

Here's an example:

First create a copy of the Sales.SalesOrderHeader table in the AdventureWorks database, and then build a nonclustered, nonunique index on the new table.

USE AdventureWorks
GO
SELECT * INTO Sales FROM Sales. SalesOrderHeader
GO
CREATE INDEX Contact_indx on Sales(ContactID)
GO

Next, create a prepared query which selects all rows less than a particular very restrictive value (one that only returns a few rows.)

DBCC FREEPROCCACHE
GO
EXEC sp_executesql
N'SELECT * FROM Sales WHERE ContactID < @ID',
N'@ID int',
@ID = 3
GO

A second query, which uses a much less restrictive value, will use the same plan.

EXEC sp_executesql
N'SELECT * FROM Sales WHERE ContactID < @ID',
N'@ID int',
@ID = 200
GO

The following query verifies that the same plan was used for both queries:

SELECT usecounts as uses, sql FROM sys.syscacheobjects
WHERE dbid = db_id('AdventureWorks')
     AND objtype = 'Prepared'
GO

I get the following results:

uses sql
---- --------------------------------------------------
2    (@ID int)SELECT * FROM Sales WHERE ContactID < @ID

If you realize that you don’t want this plan in cache because you don’t want to always use the plan for the nonselective value, you can create the following plan guide 

EXEC sp_create_plan_guide
@name = N'RemovePlan',
@stmt = N'SELECT * FROM Sales WHERE ContactID < @ID',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@ID int',
@hints = N'OPTION(RECOMPILE)';

As soon as the plan guide is created, it will remove the plan for this query from cache, and you can confirm that by running the query that inspects sys.syscacheobjects.

If you leave the plan guide in the database, the RECOMPILE hint will force SQL Server to always come up with a new plan for queries that have the same form as the query in the @stmt variable. If you just want to remove the existing plan, but not keep the plan guide around, you can remove the plan guide with the following statement:

EXEC sp_control_plan_guide N'drop', N'RemovePlan'

Have fun!

~Kalen

Published Saturday, September 29, 2007 12:18 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James Luetkehoelter said:

Great post - I didn't know you could pull a single plan out like that. I've just started hammering on plan guides and such, and I already know of 3 or 4 situations where this could be a real help. Thank you!

September 30, 2007 7:14 AM
 

david wei said:

Kalen,

The command to clear plan in single DB is

DBCC FlUSHPROCINDB (<db_id>)

Good Post.

Cheers.

------------------------------------------------------------------------------------------

SQL Server 2000 and 2005 provide a way to clear all the plans from plan cache with DBCC FREEPROCCACHE. There is also a way to clear all plans from a single database using DBCC FREEPROCINDB(<db_id>)

------------------------------------------------------------------------------------------

October 2, 2007 7:03 PM
 

Kalen Delaney said:

Thanks David... I was typing too fast. I've made the correction.

October 6, 2007 10:47 AM
 

Anil said:

Very useful tip! Thanks for posting.

May 28, 2008 4:35 PM
 

Michael Swart said:

Any update now that (SQL Server 2008 is out) on how it's done using the method provided by SQL 2K8?

October 31, 2008 10:34 AM
 

Kalen Delaney said:

I just today found out about something that was in plain sight in the Books Online... but the documentation

October 31, 2008 7:43 PM
 

Kalen Delaney said:

October 31, 2008 7:49 PM
 

Kalen Delaney : Geek City: Clearing a Single Plan From Cache in SQL Server 2008 said:

October 31, 2008 7:58 PM
 

GP said:

Thanks for your post!

November 25, 2008 12:43 AM
 

Jeff Paul Internet Business said:

Great post! My firm does internet marketing for companies and so a lot of my e-newsletter, blog and seminar content revolve around for promotion. Thanks!!!

March 3, 2009 4:27 PM
 

SQL said:

Hi Kalen,

I have many executable plan for a particular SQL and each executable plan uses 800+ pages. How can I find out why it is using so much memory?

March 10, 2010 1:08 AM
 

SQL said:

Hi Kalen,

I have many executable plan for a particular SQL and each executable plan uses 800+ pages. How can I find out why it is using so much memory?

March 10, 2010 1:12 AM
 

Kalen Delaney said:

I know Friday afternoon isn't the best time for blogging, as everyone is going home now, and by Monday

May 21, 2010 8:46 PM
 

Kalen Delaney said:

I know Friday afternoon isn't the best time for blogging, as everyone is going home now, and by Monday

May 21, 2010 8:47 PM
 

MDH said:

Thanks a lot for your helpful post

but could I run this on main server because of the large number of plans in the main server cache?

October 12, 2013 8:41 AM
 

Velu said:

Experiencing timeout issues on queries that have been running without any issues.I have a .Net application that uses ADO.NET to pull data every 3 hrs from a another source. The query as been working perfectly fine until a few days back,timing out. Run the same query in Sql query analyzer completes within seconds.We thought the timeout could be due to an indexing issue and re-indexed the source table,that didn't help.

Thought that the execution plan could have been corrupted and removed a condition in the query that would not still end up returning the same data. Seems like this change Fixed the issue and the query is now working completely fine which is Good?.

What is not good is i am bound to experience this situation again and want to be able to understand what could have caused this.

Any Help on this highly appreciated.

What could be causing this issue

December 12, 2013 11:16 AM
 

Tips: Avoid single-use cached plan with ‘optimize for ad hoc workloads’ in the SQL Server 2008 or later | 123 said:

August 18, 2014 1:52 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement