# Regnerating an execution plan - is this a valid approach?

If I am optimizing a query, in order to ensure that this query does NOT uses cached execution plan I use OPTION (RECOMPILE) with the query. Is it a good approach?

-

## migrated from stackoverflow.comJan 21 '12 at 15:00

This question came from our site for professional and enthusiast programmers.

Standard procedure is to run...

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS


Recompile just updates the query plan for the specified procedure, doesn't clear cached pages etc.

-
@JNK: Doesn't updating query plan give the same effect, because query will not be using the cached execution plan rather will be generating a new plan? –  imak Feb 16 '11 at 17:27
@imak - Updating the query plan will still used cached data pages... –  JNK Feb 16 '11 at 17:37
@JNK:OK, I think I am confused so let's take an example. Let's say I have this query SELECT * FROM MyTable WHERE SomeColumn = 'some value' When I run it, the plan is cached for parameter SomeColumn against value 'some value' Now Let's say i run this query SELECT * FROM MyTable WHERE SomeColumn LIKE 'some%' Even though the parameter value has been changed this time, above query will use existing execution plan with parameter value from first query. –  imak Feb 16 '11 at 17:52
@imak it is creating a new execution plan. I am referring to the actual data pages that are held in memory after you run the query the first time. the first query incurs the cost but subsequent queries do not which may skew your testing times. –  JNK Feb 16 '11 at 18:55
Note, this will affect all queries –  gbn Jan 21 '12 at 12:32

OPTION (RECOMPILE) can change the execution plan.

For example if you try the below you will see a different plan with and without the hint commented out (on SQL 2008 SP1 CU5 and later) .

EXEC sp_executesql N'
SELECT COUNT(*)
FROM master..spt_values
WHERE @number IS NULL OR number =@number
--OPTION (RECOMPILE)',
N'@number INT', @number=1


If you connect via the DAC then the plans seem to be neither used from the cache nor saved to the cache without this optimisation side effect of RECOMPILE (which may be a practical solution if you are developing against your own instance).

Or failing that on 2008 you can at least be more surgical about just removing the specific plan as shown below.

DECLARE @plan_handle varbinary(64),
@RC INT

SELECT @plan_handle = cp.plan_handle
FROM   sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
WHERE  t.text LIKE '%@number IS NULL%' AND t.text NOT LIKE '%this_query%'
/*Needs to match some unique text in the query. Add a commented out GUID to it
if there is no obvious candidate.*/

SET @RC = @@ROWCOUNT

IF @RC = 1
DBCC FREEPROCCACHE (@plan_handle);
ELSE
RAISERROR('Cache not cleared, %d matching rows found',11,1,@RC)

-

OPTION (RECOMPILE) tells SQL Server to discard the execution plan

You have no further need to clear buffers or caches: you are testing a query, not the IO stack to read data back into memory. If you're tuning a query that runs frequently, then data will be in cache most likely in real life. If it runs Sunday 3am once, who cares...?

And maybe you can't run these DBCC commands anyway unless you have sa rights...

Edit, Jan 2012

For SQL Server 2008+, you also have the OPTIMIZE FOR UNKNOWN hint (also see this) which gives a more general query plan

-

Be aware of the impact that this will have on others on your system (don't do this in production!):

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

-