SQL Server 2000 Performance Guidelines - Execution Plan Recompiles

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, Sharon Bjeletich and Alex Mackman


Contents

Use Stored Procedures or Parameterized Queries

The server saves execution plans for stored procedures and parameterized queries under most circumstances. This allows them to be reused on later calls.


Use Sp_executesql for Dynamic Code

If you must use dynamic code in your application, try to wrap it in the sp_executesql system stored procedure. This system stored procedure permits you to write parameterized queries in T-SQL and saves the execution plan for the code. If the dynamic code has little chance of being called again, there is no value in saving the execution plan because the execution plan will eventually be removed from the cache when the execution plan expires. Evaluate whether an execution plan should be saved or not. Note that wrapping code in the sp_executesql system stored procedure without using parameters does not provide compile time performance savings.

Dynamic code is often used for query builder applications, it is often resource-intensive, and it is often reused in this scenario. Using the sp_executsql system stored procedure to wrap this code can help improve performance.

Avoid Interleaving DDL and DML in Stored Procedures, Including the Tempdb database DDL

Interleaving data definition language (DDL) and data manipulation language (DML) in stored procedures is one of the most common causes of stored procedure recompiles. A common scenario is to create a temporary table, to insert data into that table, to create an index, and then to select data from the table. This sequence of events typically causes a recompile. To avoid recompiles, put all the DDL at the beginning of the stored procedure, and put the DML after the DDL.

The following code shows a stored procedure that creates a table (DDL), inserts data into that table (a DML statement), creates an index (a DDL statement), and then selects data from the table (another DML statement):


  CREATE PROCEDURE RecompileExample @employeeID int
  AS
  SET NOCOUNT ON
  CREATE TABLE #EmployeeOrders(OrderID int not null)
  INSERT #EmployeeOrders
  SELECT OrderID from Northwind.dbo.Orders WHERE EmployeeID = @EmployeeID
  CREATE CLUSTERED INDEX EC ON #EmployeeOrders(OrderID)
  SELECT * FROM #EmployeeOrders ORDER BY OrderID
  GO

By running SQL Profiler and capturing the SP:Recompile events, you can see a recompile every time the procedure that interleaves DDL and DML is run. This is shown in Figure 14.11. The recompiles that occur for this simple sample code are not likely to take much time. However, more complex queries may result in significant cost for the recompiles.


image: SQLProfilerShowingRecompiles.gif


Figure 14.11: SQL Profiler showing recompiles


The following code puts all the DDL at the beginning so that there is no interleaving of DDL and DML. This means that a recompile is not required.


  CREATE PROCEDURE NoRecompileExample @employeeID int
  AS
  SET NOCOUNT ON
  CREATE TABLE #EmployeeOrders (OrderID int not null)
  CREATE CLUSTERED INDEX EC ON #EmployeeOrders(OrderID)
  INSERT #EmployeeOrders
  SELECT OrderID from Northwind.dbo.Orders WHERE EmployeeID = @EmployeeID
  SELECT * FROM #EmployeeOrders ORDER BY OrderID
  GO

The SQL Profiler trace shown in Figure 14.12 for the revised code no longer shows a recompile.


image: SQLProfilerShowingNoRecompiles.gif


Figure 14.12: Profiler output with no recompiles


Avoid Cursors over Temporary Tables

A cursor that has a DECLARE statement that selects data from a temporary table almost always causes a recompile. As a result, avoid using cursors over temporary tables.


References

For more information about query recompilation, see "Query Recompilation in SQL Server 2000" on MSDN at http://msdn.microsoft.com/library/en-us/dnsql2k/html/sql_queryrecompilation.asp.

Personal tools