Article ID: 263889 - Last Review: November 24, 2010 - Revision: 5.0

Description of SQL Server blocking caused by compile locks

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

This article was previously published under Q263889

SUMMARY

In Microsoft SQL Server, only one copy of a stored procedure plan is generally in cache at a time. Enforcing this requires serialization of some parts of the compilation process, and this synchronization is accomplished in part by using compile locks. If many connections are concurrently running the same stored procedure and a compile lock must be obtained for that stored procedure every time that it is run, system process IDs (SPIDs) might begin to block one another as they each try to obtain an exclusive compile lock on the object.

Back to the top

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 to eliminate the recompiles. For an explanation of the most common reasons that a stored procedure may have to be recompiled and for some useful information on reducing the frequency of recompiles, see the following Microsoft Knowledge Base article:

243586 (http://support.microsoft.com/kb/243586/ ) Troubleshooting stored procedure recompilation

Another scenario in which compile locks occur is when the following conditions are true:

The user who 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 by using 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. Therefore, SQL Server cannot be sure that the cached plan for dbo.mystoredproc is the correct one to execute.) SQL Server then obtains an exclusive compile lock on the procedure and makes preparations to compile the procedure. This includes resolving the object name to an object ID. Before SQL Server compiles the plan, SQL Server uses this object ID to perform a more precise search of the procedure cache and can locate a previously compiled plan even without 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 Server to perform a second cache lookup and obtain an exclusive compile lock before the program determines that the existing cached execution plan can be reused. Obtaining the lock and performing lookups and other work that is needed to reach this point can introduce a delay for the compile locks that leads to blocking. This is especially true if many users who are not the stored procedure's owner concurrently run the procedure without supplying the owner's name. Be aware that even if you do not see SPIDs waiting for compile locks, lack of owner-qualification can introduce delays in stored procedure execution and cause unnecessarily high CPU utilization.

The following sequence of events will be recorded in a SQL Server Profiler trace when this problem occurs. (To trace cache-related events, you must enable advanced events. To do this, click Options on the Tools menu, and then select All event classes.)

Collapse this tableExpand this table

Event class	Text
RPC:Starting	mystoredproc
SP:CacheMiss	mystoredproc
SP:ExecContextHit	mystoredproc
SP:Starting	mystoredproc
...	...

SP:CacheMiss occurs when the cache lookup by name fails. The following SP:ExecContextHit indicates that a matching cached plan was ultimately found in cache after the ambiguous object name was resolved to an object ID. Depending on the circumstances, SP:CacheHit may appear instead of SP:ExecContextHit.

The solution to this problem of compile locking is to make sure that references to stored procedures are owner-qualified. (Instead of exec mystoredproc, use exec dbo.mystoredproc.) While owner-qualification is important for performance reasons, you do not have 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 by using blocking scripts such as those that are defined in the following Microsoft Knowledge Base articles:

251004 (http://support.microsoft.com/kb/251004/ ) INF: How to monitor SQL Server 7.0 blocking

271509 (http://support.microsoft.com/kb/271509/ ) INF: How to monitor SQL Server 2000 blocking

The 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.
Although the duration of the blocking incident may be long, there is no single SPID that is blocking the other SPIDs for a long time. There is rolling blocking. As soon as one compilation is complete, another SPID takes over the role of head blocker for a several seconds or less, and so on.

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. Be aware that this object ID belongs to a stored procedure, not to a table (despite the "TAB" lock type).

Notes

If you are using SQL Server 2005, many of the system tables from 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.

If your stored procedure name starts 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 start with "sp_".

If an owner-qualified procedure is executed with a different case than the owner-qualified procedure was created as, the owner-qualified procedure can obtain 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 cause a "blocking chain" situation if there are many SPIDs trying to execute the same procedure with a different case than the procedure was created as. This is true regardless of the sort order or collation that is being used on the server or on the database. The reason for this behavior is that the algorithm that is 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 the procedure is executed by the application. You can also make sure that the procedure is executed from all applications that use the same case.

If you try to execute a stored procedure as a Language Event instead of as an RPC, SQL Server must parse and compile the language event query, determine 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 in which SQL Server 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."

Back to the top

Known issues

Here are some known issues that can prevent plan caching:

You use BLOB variables as a Stored Procedure parameter. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2380435 (http://support.microsoft.com/kb/2380435/ ) FIX: The query plan for a stored procedure is not cached if the stored procedure uses a BLOB variable and the variable is used in a string function in Microsoft SQL Server 2008
You use OPEN SYMMETRIC KEY in a Stored Procedure/Query Batch. For more information, see the following MSDN blog entry:
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/09/08/open-symmetric-key-command-prevents-query-plan-caching.aspx (http://blogs.msdn.com/b/sqlserverfaq/archive/2010/09/08/open-symmetric-key-command-prevents-query-plan-caching.aspx)

Back to the top