Category Archives: Uncategorized

Recompiling Stored Procedures

Why Recompiling Stored Procedures

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization occurs automatically the first time a stored procedure is run after SQL Server is restarted. It also occurs if an underlying table that is used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not occur until the next time that the stored procedure is run after SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time that it executes.

Forcing a Stored Procedure to Recompile

  • sp_recompile is a system stored procedure forces a recompile of a stored procedure the next time that it is run. It does this by deleting the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure is run.
  • Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled every time that it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure and cause different execution plans to be created every time. Use of this option is uncommon and causes the stored procedure to execute more slowly, because the stored procedure must be recompiled every time that it is executed.
  • You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.

If an object referenced by a stored procedure is deleted or renamed, an error is returned when the stored procedure is executed. However, if an object referenced in a stored procedure is replaced with an object of the same name, the stored procedure executes without having to be re-created.

New Feature in SQL Server 2008 R2

SQL Server 2008 R2 features statement-level recompilation of stored procedures. When SQL Server 2008 R2 recompiles stored procedures, only the statement that caused the recompilation is compiled, instead of the entire procedure. As a result, SQL Server uses the parameter values as they exist in the recompiled statement when regenerating a query plan. These values may differ from those that were originally passed into the procedure.

Reference site:

Rebuild Indexes

Periodically perform a database reorganization on all the indexes on the table in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server’s performance.

From SQL Server 2005 on wards, the indexes can be re-index ONLINE. This ensures that the underlying tables and indexes are available for other users when the re-indexing is taking place. Also Standard and Workgroup editions of SQL.

The limitation for ONLINE Re-indexing is that it cannot be run on tables which have Large Object Data type columns (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), xml). The Alter Index documentation on MSDN has more details on this.

There are many ways to find out LOB data types in a table, here is the one share by Pradeep Adiga in his blog.

SELECT as [ColumnName], as [Table Name]
    JOIN SYS.OBJECTS B ON A.object_id = B.object_id
    WHERE A.system_type_id in (35,34,241,99)
    AND B.type ='U'
    AND <>'dtproperties'
    JOIN SYS.OBJECTS B ON A.object_id = B.object_id
    WHERE A.system_type_id IN (167,231,165)
    AND A.max_length = 8000
    AND B.type ='U'
    AND <>'dtproperties'

With this script, the LOB data type columns can be identified and is useful while trying to re-index tables online.