Recompiling Stored Procedures

SQL Server automatically recompiles some or all statements in a stored procedure when

• Data in referenced tables is changed significantly,

• A Set statement that can change a query result is in the middle of the stored procedure (or batch), or

• Schemas of referenced objects (such as tables, functions, and stored procedures) have been changed since the stored procedure was last recompiled.

SQL Server is intelligent enough to recompile a stored procedure when a table referenced by that stored procedure changes. Unfortunately, SQL Server might or might not recompile when you add an index that might help execution of the stored procedure. It all depends on SQL Server's ability to identify and store dependent objects. To force compilation of a stored procedure, a DBA can use sp_recompile:

      Exec sp_recompile dbo.ap_OrdersByCountry_List 

This task can be very tedious if many stored procedures and/or triggers depend on a table for which an index was added. Fortunately, it is possible to name the table for which dependent objects should be recompiled:

      Exec sp_recompile Orders 

This statement will recompile all triggers and stored procedures that depend on the Orders table. When a stored procedure or a trigger is specified as a parameter, only that stored procedure or trigger will be recompiled. If you use a table or a view as a parameter, all dependent objects will be recompiled.

Tip 

Do not forget to recompile dependent objects after you add an index to a table. Otherwise, SQL Server may not be able to use tbem.

A developer might also decide to recompile a stored procedure each time it is used. A typical example is when a stored procedure is based on a query, the execution and performance of which depend on the value used as a criterion. I discussed such an example earlier, in the "Autoparameterization" section.

In that example, when a user requests orders from the USA, the selectivity of the index might be such that it is better for the query to do a table scan. If a user requests orders from a country that rarely appears in the particular database, the query engine might decide to use the index. To force SQL Server to evaluate these options every time, the developer should use the With Recompile option when designing the stored procedure:

      Create Procedure dbo.ap_OrdersByCountry_List           ©Country char(3)      With Recompile      as           Select *           from dbo.Orders           where Country =  ©Country 

The execution plan of a stored procedure created in this manner will not be cached on SQL Server.

It is also possible to force recompilation of a stored procedure during execution using the With Recompile option:

      Exec dbo.ap_OrdersByCountry_List 'USA' With Recompile 

The recompilation threshold is a number of changed rows in a table that will trigger the recompilation of a stored procedure (or a batch). It depends on the number of rows in a table and a table type. For temporary tables with up to six rows, the recompilation threshold is six; for temporary and static tables with up to 500 rows, it is 500; for larger temporary and static tables, it is 500 + 20 percent of the number of rows.

Table variables do not have a recompilation threshold, so their changes will not lead to recompilation. That is another reason to use table variables instead of temporary tables whenever possible.