Menu
I've looked all over for this command....what's the command to reset the SQL Server's execution plan?
954
Use DBCC FREEPROCCACHE to clear the plan cache carefully. Clearing the procedure (plan) cache causes all plans to be evicted, and incoming query executions will compile a new plan, instead of reusing any previously cached plan.
Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. To drop clean buffers from the buffer pool and columnstore objects from the columnstore object pool, first use CHECKPOINT to produce a cold buffer cache.
To recompile a stored procedure by using sp_recompileSelect New Query, then copy and paste the following example into the query window and click Execute. This does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the next time that the procedure is executed.
What is the RECOMPILE option? The compilation is the process when a query execution plan of a stored procedure is optimized based on the current database objects state. This query execution plan is often stored in the cache to be quickly accessed. Recompilation is the same process as a compilation, just executed again.
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 For stored procedures, you use the WITH RECOMPILE option.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
