SQL Server COMPILE Locking and Encryption Keys

Introduction

The morning begins with this page: "a large number of sessions are blocked on one of your managed SQL Server 2005." So you go and check out the Activity Monitor, and you can tell something unusual is going on: 

Blocking chain of 200 procedures, your server is crawling with high CPU and requests are coming out a funnel. Now, don't worry, the waitresource column provides us with the information to start zooming in on our problem. In this case, we have value "TAB: 7:357576312 [[COMPILE]]". Disregarding the fact that the resource description says TAB, run the following query with the resource information to get the database and procedure being locked:

select db_name(7)
 
 Use [PerformanceReports] -- the database name we got from the select above
 GO
 
 select object_name(357576312)

The common causes of Compile locking

Knowing the procedure, it's then a matter of discarding possibilities. The common causes of COMPILE locking are well documented on the Microsoft KB, "Description of SQL Server blocking caused by compile locks", so if you have a chance, go on and read that. If you don't, then this is the gist of it:

1. Ensure all references to stored procedures in your code are owner-qualified. Do not call 'exec mysp', call 'exec john.mysp'. If you don't, then you can get COMPILE locking and a Cache Miss.
2. Do not begin your stored procedures' names with 'sp_'. This makes the engine go looking into the master database for the stored procedure, and will get you a Cache Miss (or godforbid, someone created a procedure in master with the same name and you are actually executing that). There are many options: 'usp_', 'appsp_', etc. Whatever works for you.
3. If you do owner-qualify your call, but you execute the procedure with a different case than what it was created as, then you could get a Cache Miss or a COMPILE lock. I tested this and got Cache Hits, but why take the chance? Use the same case.The last one is not included in the KB; I found out about it when working this specific case:
4. Do not open and close an encryption key inside a stored procedure in order to use encryption routines that will be called concurrently in heavy volume. This will get you Cache Miss and COMPILE locks too.

CREATE PROCEDURE dbo.LookupByID @ValueID int
 AS
 
 SET NOCOUNT ON;
 
 OPEN SYMMETRIC KEY PythianSymmetricKey DECRYPTION BY ASYMMETRIC KEY PythianAssymKey
 
 SELECT CAST(DecryptByKey(encryptedField) AS VARCHAR(30)) FROM EncryptedValues
 where ID = @ValueID
 
 CLOSE SYMMETRIC KEY PythianSymmetricKey

If you don't follow the points above, will you get the blocking chain I showed before? Not necessarily -- if the server is fast enough, it will acquire and release those compile locks before you notice anything. As your workload increases, however, the issue will start coming to the surface until it materializes as a full-blown blocking chain. Here is a detailed look through Profiler. I traced Lock, Batch, and Cache Hit events, and removed from the output the events that were not relevant to this problem. This is the output from the call with no owner specified and our encryption OPEN and CLOSE statements inside the procedure:

As you can see, we are getting an initial Cache Miss that corresponds to just the actual call 'exec LookupByID 7'. After that, we get the real Cache Miss from the procedure, the Lock Acquired event with COMPILE type and locking for both encryption keys. Now, let's qualify the owner, take out the OPEN and CLOSE SYMMETRIC KEY instructions from the procedure, and just leave the call to DecryptByKey inside:

At first glance, it looks like the same thing. Sure, there is no more encryption key locking, but compile locks are being acquired, and our plan is not getting a Cache Hit. But now we got the Cache Insert fired. And that means that our next run of the procedure looks like this:

And that is ideally how it should look all the time: no compilation locking, cache hit and only the shared lock to the table we are querying. So remember, follow the known Microsoft recommendations, OPEN and CLOSE the key once and you can execute as many times as you want the stored procedure calling DecryptByKey (one approach for this can be found in A Simple Approach to SQL Server 2005 Encryption by Mike Good). Not only will you avoid the COMPILE locks, but also CPU overhead. Back to work.