Geek City: Clearing a Single Plan from Cache

Kalen Delaney — Sat, 29 Sep 2007 18:18:00 GMT

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

Kalen Delaney : syscacheobjects

Geek City: Clearing a Single Plan from Cache