34

I've looked all over for this command....what's the command to reset the SQL Server's execution plan?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Brandon
  • 381
  • 1
  • 4
  • 6

5 Answers5

54

For clarity..........

Executing sp_recompile will "mark" the given stored procedure for recompilation, which will occur the next time it is executed.

Using the WITH RECOMPILE option will result in a new execution plan being generated each time the given stored procedure is executed.

To clear the entire procedure cache execute

DBCC FREEPROCCACHE
Masoud
  • 8,020
  • 12
  • 62
  • 123
John Sansom
  • 41,005
  • 9
  • 72
  • 84
  • Can DBCC FREEPROCCACHE be executed just for a specific procedure? – CodeMilian Sep 23 '14 at 20:42
  • 1
    @CodeMilan: Yes you can evict a specific plan_handle (the query plan for a given stored procedure for example) from the plan/procedure cache. See BOL for an example: http://msdn.microsoft.com/en-us/library/ms174283.aspx – John Sansom Sep 25 '14 at 07:54
5

For stored procedures, you use the WITH RECOMPILE option.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • +1 AHA !! Now things begin to make sense :-) You're better at deciphering unclear user messages, I must see... – marc_s Nov 03 '09 at 16:22
  • 1
    Please notes that this method will recompile the given stored procedure each and every time it is executed, so if you wish to just force a one off recompile then use the system stored procedure sp_recompile. – John Sansom Nov 03 '09 at 17:45
4

If you want to reset QEP for a stored procedure, you shall use sp_recompile

Rubens Farias
  • 57,174
  • 8
  • 131
  • 162
3

It's not entirely clear from your question what you're after. But in addition to the other suggestions, DBCC FREEPROCCACHE clears all cached execution plans.

HTTP 410
  • 17,300
  • 12
  • 76
  • 127
1

sp_recompile will dump the existing query plan and recompile the procedure. Or you can restart SQL and that will clear the entire execution plan cache.

WITH RECOMPILE is going to generate a new plan EVERY time you execute it.

eric
  • 352
  • 1
  • 7
  • 7
    A small but important distinction, executing sp_recompile will not recompile a given stored procedure but will instead mark it for recompilation the next time it is executed. – John Sansom Nov 03 '09 at 17:58