CODEWITHSUNDEEP
sql-serversql-execution-plan
Brandon
Brandon

Reputation: 391

Reset SQL Server execution plan

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

Upvotes: 35

Views: 47344

Answers (5)

John Sansom
John Sansom

Reputation: 41879

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

Upvotes: 55

HTTP 410
HTTP 410

Reputation: 17618

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.

Upvotes: 3

Rubens Farias
Rubens Farias

Reputation: 57976

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

Upvotes: 4

eric
eric

Reputation: 352

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.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416053

For stored procedures, you use the WITH RECOMPILE option.

Upvotes: 5

Related Questions