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?

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)

StackExchange Database Administrators Q#11139, answer score: 2