FIX: Concurrent Execution of Stored Procedure That Recompiles May Fail To Execute All Statements (250506)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q250506
BUG #: 55251 (SQLBUG_70)

SYMPTOMS

A stored procedure may silently fail to execute all statements contained within it if the following conditions are met:

  • Multiple connections are executing the same procedure concurrently.

  • A reference is made to a temporary table created outside of this procedure.

  • One or more statements that could also cause a recompile (potentially triggered by 'auto update statistics') follow the reference to this temporary table.
All statements may execute for some of the users running the procedure, while others fail.

WORKAROUND

Create the procedure with the WITH RECOMPILE clause, or use the WITH RECOMPILE clause when executing the procedure.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

If a reference is made to a temporary table created outside the current procedure, that statement and any subsequent statements can not be compiled when the procedure is first executed. This is because no information is known about the schema of the table. Once execution reaches that statement and the schema and cardinality values are available, the procedure is recompiled using this information. To minimize in compile time, SQL Server does a "partial compile" in this situation, and only generates a query plan for statements up to this one.

Since multiple users can share a compiled plan, SQL Server 7.0 serializes recompiles to ensure that only one of the connections performs the recompile. If the other connections also need to recompile, they wait on the first connection to finish its recompile and resume execution at their next step.

If you encounter a situation where two connections are at various stages of execution, and the one that triggers the recompile first is before the reference to the temporary table created outside the procedure, it will stop compiling a plan when it reaches that statement. If a second connection is somewhere after this and also triggers a recompile while the first connection is compiling, it resumes execution and incorrectly believes that it has executed all statements in the plan.

If you use WITH RECOMPILE, either when the procedure is created or when it is executed, the two connections never share the same compiled plan and will never encounter this condition.

Where possible, it is also advisable not to reference a temporary table created outside of the current procedure, as it always requires a recompile of the procedure. Other programming practices for stored procedures can be found under the "Transact-SQL Tips" topic in SQL Books Online.

To determine when a procedure is being recompiled, you can watch the SP:Recompile event in SQL Profiler.
Modification Type:MajorLast Reviewed:3/14/2006
Keywords:kbBug kbfix KB250506
©2004 Microsoft Corporation. All rights reserved.