There is some argument regarding this baseline. The historical rule of thumb is to use temp tables when storing result sets greater than 1000, and table variables when less than 1000. In some situations these may be a better choice. It should be noted that table variables inherently do not cause recompilation.
This will surely cause a recompile right?ĮXEC spGetPresident nothing appears in the trace. Now we will execute our procedure once more (this will create one more recompile since we just changed the procedure)ĮXEC spGetPresident let’s execute it passing in a parameter of 10000. OPTION (KEEPFIXED PLAN) –Does not allow recompilation How do you do this? Using Option Keep Fixed Plan. A statement that recompiles vs one that does not could be the difference between 5 milliseconds and 500 milliseconds. But if you are, then you can reap some significant benefits. Is there a way not to allow the query engine to compile the query you ask? Why yes there is! However you better be sure you know what you’re doing. However letting the Query Engine recompile when it sees fit is the best method. Is it right? sometimes yes, sometimes no. Why did it recompile? Because the Query Engine does not think the cached execution plan will accommodate this query. Let’s change the parameter to 2000, and execute the procedure once again.ĮXEC spGetPresident we see a recompilation. Why? Because the SQL Server engine has determined that the cached execution plan used in our last execution, will also suffice for this execution. Execute the procedure now selecting 12 rowsĮXEC spGetPresident still do not see any compilations. Next, we will change the parameter to 12 and see if there are any recompiles. Unselect all the checkboxes then check on the checkbox below stating ‘show all events’. In management studio, go to tools -> SQL Server ProfilerĬonnect to your server then choose the ‘Events Selection’ Tab. Next, before we execute the procedure, we will set a trace up to determine if we are recompiling. We’re going to use a temp table for purposes of this example.
#Stored procedure recompile how to#
In this example, we will create an example showing recompilation, how to detect it, and how to prevent it.įirst, we’ll create a procedure that selects a parameterized number of rows from a table. This is especially beneficial to stored procedures that contain many statements. This is advantageous because when input parameters change in a stored procedure the majority of the stored procedure can still utilize the cached execution plan. With SQL Server 2005+, recompilation now occurs at the individual statement level rather than stored procedure level. A similar sort of phenomenon also happens in temp tables.
#Stored procedure recompile update#
When the tables (in which underlying queries rely on) change significantly, SQL Server detects this change (using auto update statistics) and marks the dependent queries to be recompiled so the next execution can create an optimal execution plan. This happens because temp tables are treated just like regular tables by the SQL Server Engine. If you landed on this article, then you most likely know that temp tables can cause recompilation.
0 kommentar(er)
