Microsoft KB Archive/65411

INF: Forcing a Stored Procedure to Recompile

PSS ID Number: Q65411 Article last modified on 02-03-1994

1.00 1.10 OS/2

Question
SQL Server has the desirable property of selecting a table scan instead of an index scan for small tables when compiling a query on a table having indexes, even if the select list and WHERE clauses refer to index columns. However, this poses a problem when the query is contained in a stored procedure. The query is compiled upon the first execution of the stored procedure. Often, the table is initially empty and then grows dramatically. As a result of the table scan selection, subsequent executions may be significantly slower than when using dynamic SQL. I currently seem to have the following choices: 1. Create the stored procedure with the WITH RECOMPILE option, and incur the SQL compilation overhead for every invocation of the stored procedure. Alternatively, periodically execute the stored procedure with the WITH RECOMPILE option. 2. Manually populate the table with a large amount of simulated data prior to the first execution of the application, in order to force the selection of an index scan during SQL compilation. 3. Periodically drop and re-create the stored procedure. Is there any other way to force a recompile of a stored procedure?

Response
A table changing in size will not force a recompile of the dependent stored procedures, even if an UPDATE STATISTICS is performed on the table. There are several other events that will force a stored procedure to recompile, such as the following: 1. Adding a rule or default on the table 2. Dropping an index on the table, but adding an index does not have the same effect 3. Dropping and re-creating the table 4. Loading the database containing the associated table However, in the case described above, none of these options would be particularly useful. A stored procedure is also recompiled if it has aged due to lack of use and is no longer resident in the procedure cache, or if the current version in the cache is in use. However, these are not easily predictable events, and therefore, this is not a good solution for this situation. Listed below is another method to force recompilation of the stored procedures that are dependent on a particular table. As mentioned above, a forced recompilation of stored procedures occurs whenever you add a rule or default to one of the associated tables. The system keeps track of this information in the sysobjects table for the database. The SCHEMA column for the table is incremented whenever a rule or default is added. To increment this value, you can create a stored procedure similar to the following: create procedure bump_table @tabname varchar(50) as update sysobjects set schema = schema + 1 where id in (select id from sysobjects where name = @tabname) This will force all stored procedures associated with the table to be recompiled the next time they are executed. There are several things, which are listed below, that you must consider in order to use a stored procedure that updates the SCHEMA column of sysobjects: 1. The server must be configured to allow updates to system tables at the time the procedure is initially created. This option then can be turned back off for security purposes. 2. The SCHEMA column in sysobjects is defined as “smallint” and has a maximum value of 32,767. The procedure to increment the SCHEMA column should, therefore, not be invoked too frequently. If it is called once per day, this column maximum will not be encountered for more than 89 years, so it is certainly a viable solution to this problem. This procedure could be used in conjunction with UPDATE STATISTICS, or called by your original stored procedure after every  executions.

=
================================================================ Copyright Microsoft Corporation 1994.