Eliminating Recompilation

Congratulations to Narasimhan Jayachandran, a senior DBA for First Data Resources in Omaha, Nebraska and Chris Watson, a senior developer for the Auckland University of Technology in Auckland, New Zealand. Narasimhan won first prize of $100 for the best solution to the January Reader Challenge, "Eliminating Recompilation." Chris won second prize of $50. Here’s a recap of the problem and the solution to the January Reader Challenge.

Problem:

Sam is a database architect in a company that auctions products online. The database is hosted primarily on SQL Server 2000. The web site for the auctions is transactional in nature. While investigating a performance problem, Sam notices that there are lots of stored procedures that perform various operations with temporary tables. By using Profiler, Sam is able to determine that the performance problem of several large stored procedures is due to excessive recompilations during execution. A sample stored procedure that performs similar operations as one of the problematic stored procedures in production is shown below:

USE NorthwindGOIF object_id('dbo.ReportOrdersByProduct') IS NOT NULL    DROP PROCEDURE dbo.ReportOrdersByProductGOCREATE PROCEDURE dbo.ReportOrdersByProductASBEGIN    CREATE TABLE #od1 ( OrderID int, ProductID int )    INSERT INTO #od1 ( OrderID, ProductID )    SELECT od.OrderID, od.ProductID      FROM "Order Details" AS od    SELECT p.ProductName, count(*) AS #Orders      FROM #od1 AS od      JOIN Products AS p        ON p.ProductID = od.ProductID  GROUP BY p.ProductName    HAVING count(*) >= 25ENDGO

The stored procedure in question contains logic similar to above with several SELECT statements using the temporary table. The behavior in production can be observed by setting up a profiler trace with the following events:

SP:StartingRPC:StartingSP:StmtStartingSP:RecompileSP:CompletedRPC:CompleteObjects:Auto Stats 

For the purpose of the problem, observe the events upon first execution of the stored procedure since subsequent executions will use the cached plan due to the simplicity of the sample code. The recompilation of the stored procedure when it hits the SELECT statement mimics the behavior in the production system.

Assuming that Sam wants to retain the temporary table logic and make minimal changes to the stored procedure, how can he reduce or eliminate the recompilation of the stored procedure for the SELECT statements accessing the temporary tables?

Solution:

Sam can reduce the recompilation issues for the SELECT statements by using sp_executesql to execute the SELECT statement. The following stored procedure, ReportOrdersByProduct2, contains a modified version that uses sp_executesql for the SELECT statement.

USE NorthwindGOIF object_id(‘dbo.ReportOrdersByProduct2’) IS NOT NULL    DROP PROCEDURE dbo.ReportOrdersByProduct2GOCREATE PROCEDURE dbo.ReportOrdersByProduct2ASBEGIN    CREATE TABLE #od1 ( OrderID int, ProductID int )    INSERT INTO #od1 ( OrderID, ProductID )    SELECT od.OrderID, od.ProductID      FROM "Order Details" AS od    EXEC sp_executesql N'    SELECT p.ProductName, count(*) AS #Orders      FROM #od1 AS od      JOIN Products AS p        ON p.ProductID = od.ProductID  GROUP BY p.ProductName    HAVING count(*) >= 25    'ENDGO

Next, Sam can observe the profiler events for the execution of this stored procedure and confirm that only one recompilation event occurs during the execution of the stored procedure. Sam can also reduce the recompilation by moving the SELECT statements involving the temporary tables to smaller stored procedures. By doing this, he can restrict the recompilation to the smaller stored procedures which will give some performance benefits.

FEBRUARY READER CHALLENGE:

Now, test your SQL Server savvy in the February Reader Challenge, "Avoiding Application Failure" (below). Submit your solution in an email message to [email protected] by January 20. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

David, a developer for a company that sells firewall and ad-blocking applications, develops applications that use Microsoft SQL Server 2000 Desktop Engine (MSDE) for storing various configuration metadata and activity logs. David notices that the application startup can fail if the MSDE service hasn't recovered the application database completely even though the SQL Server service has started successfully. How can David avoid application failure if the database isn't recovered completely? What can David do in the application code to start the application gracefully?