Regnerating an execution plan - is this a valid approach?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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) 

Answer 4

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

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE