How do I recompile proc?
How do I recompile proc?
To recompile a stored procedure by using sp_recompile Select 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 does Option recompile do when added to a stored procedure?
When executing a stored procedure with a WITH RECOMPILE option in the EXECUTE statement, a new query execution plan is created and used for this specific execution, but it’s not stored in cache. If there is already a plan in cache for this specific stored procedure, it’s intact.
What is option recompile in SQL?
The oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance.
How to recompile a stored procedure in SQL Server?
To recompile a stored procedure by using the WITH RECOMPILE option. Connect to the Database Engine. From the Standard bar, click New Query. Copy and paste the following example into the query window and click Execute. This example creates the procedure definition.
How do I use the recompile option in a SQL query?
There are three ways to do this: WITH RECOMPILE option in the procedure definition or when the procedure is called, the RECOMPILE query hint on individual statements, or by using the sp_recompile system stored procedure.
What is SP_recompile system stored procedure?
It also describes using the sp_recompile system stored procedure to recompile an existing procedure. When a procedure is compiled for the first time or recompiled, the procedure’s query plan is optimized for the current state of the database and its objects.
How do I recompile a stored procedure in adventureworks?
To recompile a stored procedure by using the WITH RECOMPILE option. Select New Query, then copy and paste the following code example into the query window and click Execute. This executes the procedure and recompiles the procedure’s query plan. USE AdventureWorks2012; GO EXECUTE HumanResources.uspProductByVendor WITH RECOMPILE; GO