Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jan 21 '12 at 15:00

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

add comment

4 Answers 4

up vote 0 down vote accepted

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.

share|improve this answer
    
@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
1  
@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
1  
Note, this will affect all queries –  gbn Jan 21 '12 at 12:32
show 3 more comments

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) 
share|improve this answer
add comment

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

share|improve this answer
add comment

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

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.