SQL Server 2000 Performance Guidelines - Execution Plan Recompiles
From Guidance Share
- J.D. Meier, Srinath Vasireddy, Ashish Babbar, Sharon Bjeletich and Alex Mackman
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.
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.
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.
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.