Article ID: 263889
Article Last Modified on 12/23/2005
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2005 Workgroup Edition
This article was previously published under Q263889
SUMMARY
In SQL Server 7.0 and 2000, only one copy of a stored procedure plan is generally in cache at any given time. Enforcing this requires serialization of some parts of the compilation process, and this synchronization is accomplished in part through the use of compile locks. If many connections are simultaneously running the same stored procedure, and a compile lock must be acquired for that stored procedure each time it is run, it is possible that system process IDs (SPIDs) may begin to block each other as they each try to acquire an exclusive compile lock on the object.
MORE INFORMATION
Stored procedure recompilation is one explanation for compile locks on a stored procedure or trigger. The solution in this case is to reduce or eliminate the recompiles. For an explanation of the most common reasons that a stored procedure may need to be recompiled, and some useful information on reducing the frequency of recompiles, see the following Microsoft Knowledge Base article:
243586 Troubleshooting stored procedure recompilation
An alternate cause of compile locks occurs when the following conditions are met:
- The user that runs the stored procedure is not the owner of the procedure.
- The stored procedure name is not fully qualified with the object owner's name.
For example, if user "dbo" owns object dbo.mystoredproc, and another user "Harry" runs this stored procedure with the command "exec mystoredproc," the initial cache lookup by object name fails because the object is not owner-qualified. (It is not yet known whether another stored procedure named Harry.mystoredproc exists, so SQL cannot be sure that the cached plan for dbo.mystoredproc is the right one to execute.) SQL Server then acquires an exclusive compile lock on the procedure and makes preparations to compile the procedure, including resolving the object name to an object ID. Before it compiles the plan, SQL Server uses this object ID to perform a more precise search of the procedure cache and is able to locate a previously compiled plan even without the owner qualification.
If an existing plan is found, SQL Server reuses the cached plan and does not actually compile the stored procedure. However, the lack of owner-qualification forces SQL to perform a second cache lookup and acquire an exclusive compile lock before determining that the existing cached execution plan can be reused. Acquiring the lock and performing lookups and other work that is needed to get to this point can introduce a delay that is sufficient for the compile locks to lead to blocking. This is especially true if a large number of users who are not the stored procedure's owner simultaneously run it without supplying the owner name. Note that even if you do not see SPIDs waiting on compile locks, lack of owner-qualification can introduce delays in stored procedure execution and unnecessarily high CPU utilization.
You will see the following sequence of events in a SQL Server Profiler trace when this problem is occurring (to trace the cache-related events, you must enable advanced events by clicking Options under the Tools drop-down menu and selecting All event classes):
| Event Class | Text |
|---|---|
| RPC:Starting | mystoredproc |
| SP:CacheMiss | mystoredproc |
| SP:ExecContextHit | mystoredproc |
| SP:Starting | mystoredproc |
| ... | ... |
The SP:CacheMiss event occurs when the cache lookup by name fails. The subsequent SP:ExecContextHit indicates that a matching cached plan was ultimately found in cache once the ambiguous object name had been resolved to an object ID. Depending on the circumstances, SP:CacheHit may appear in place of SP:ExecContextHit.
The solution to this problem of compile locking is to ensure that references to stored procedures are owner-qualified (instead of exec mystoredproc, use exec dbo.mystoredproc). While owner-qualification is important for performance reasons, it is not necessary to qualify the stored proc with the database name to prevent the additional cache lookup.
Blocking that is caused by compile locks can be detected with blocking scripts such as the ones defined in the following Microsoft Knowledge Base articles:
251004 INF: How to monitor SQL Server 7.0 blocking
271509 INF: How to monitor SQL Server 2000 blocking
Following are some typical characteristics of compile blocking that can be observed in the blocking script output:
- lastwaittype for the blocked and (usually) blocking SPIDs is LCK_M_X (exclusive) and waitresource is of the form "TAB: dbid:object_id COMPILE," where "object_id" is the object ID of the stored procedure.
- Blockers have waittype 0x0000, status runnable. Blockees have waittype 0x000e (exclusive lock), status sleeping.
- While the duration of the blocking incident may be long, there is no single SPID that is blocking the others for a long period of time (rolling blocking). As soon as one compilation finishes, another SPID takes over the role of head blocker for a few seconds or less, and so forth.
The following information is from a snapshot of sysprocesses during this kind of blocking:
spid blocked waittype waittime lastwaittype waitresource
---- ------- -------- -------- ------------ -------------------------
221 29 0x000e 2141 LCK_M_X TAB: 6:834102 [[COMPILE]]
228 29 0x000e 2235 LCK_M_X TAB: 6:834102 [[COMPILE]]
29 214 0x000e 3937 LCK_M_X TAB: 6:834102 [[COMPILE]]
13 214 0x000e 1094 LCK_M_X TAB: 6:834102 [[COMPILE]]
68 214 0x000e 1968 LCK_M_X TAB: 6:834102 [[COMPILE]]
214 0 0x0000 0 LCK_M_X TAB: 6:834102 [[COMPILE]]
In the waitresource column ("6:834102"), 6 is the database ID and 834102 is the object ID. Note that this object ID belongs to a stored procedure, not to a table (despite the "TAB" lock type).
Note If you are using SQL Server 2005, many of the system tables in SQL Server 2000 are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. For more information about mapping between the SQL Server 2000 system tables and the SQL Server 2005 system views, see the "Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views" topic in SQL Server 2005 Books Online.
Note If your stored procedure name begins with the "sp_" prefix and is not in the master database, you see SP:CacheMiss before the cache hit for each execution even if you owner-qualify the stored procedure. This is because the sp_ prefix tells SQL Server that the stored procedure is a system stored procedure, and system stored procedures have different name resolution rules (the "preferred" location is in the master database). The names of user-created stored procedures should not begin with "sp_."
Note If an owner-qualified procedure is executed with a different case than what the owner-qualified procedure was created as, the owner-qualified procedure can get a CacheMiss or request a COMPILE lock, but eventually use the cached plan. Therefore, this would not actually recompile the procedure and should not cause much of an overhead. But in certain situations the request for a COMPILE lock can get into a "blocking chain" situation if there are many SPIDs trying to execute the same procedure with a different case than what the procedure was created as. This is true regardless of the sort of order or collation being used on the server or on the database. The reason for this behavior is that the algorithm being used to find the procedure in cache is based on hash values (for performance reasons) which can change if the case is different.The workaround is to drop and create the procedure with the same case as it is executed by the application. You can also make sure that the procedure is executed from all applications that use the same case.
Note If you try to execute a stored procedure as a Language Event instead of as an RPC, then SQL must parse and compile the language event query, figure out that the query is trying to execute the particular procedure and then try to find a plan in cache for that procedure. To avoid this situation where SQL must parse and compile the language event, make sure that the query is sent to SQL as an RPC.
For more information, see the "System Stored Procedures" section in the Books Online article "Creating a Stored Procedure."
Keywords: kbinfo KB263889
