SQL Server COMPILE Locking and Encryption Keys

Sep 19, 2008 / By Warner Chaves

Tags:

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:

compilelockactivitymonitorthumbnail.jpg

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)

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.

I am talking about something like this:

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:

EventClass TextData
SP:CacheMiss exec lookupByID @valueid=7
SQL:BatchStarting exec lookupByID @valueid=7
Lock:Acquired [COMPILE]
Lock:Acquired object_id = 309576141
Lock:Released object_id = 309576141
…. ….
Lock:Released [COMPILE]
Lock:Acquired symmetric_key_id
Lock:Acquired asymmetric_key_id
Lock:Released asymmetric_key_id
Lock:Released symmetric_key_id
…. ….
SQL:BatchCompleted exec lookupByID @valueid=7

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:

EventClass TextData
SP:CacheMiss exec lookupByID @valueid=7
SQL:BatchStarting exec lookupByID @valueid=7
Lock:Acquired [COMPILE]
Lock:Acquired object_id = 309576141
Lock:Released object_id = 309576141
…. ….
SP:CacheInsert LookupByID
Lock:Released [COMPILE]
…. ….
SQL:BatchCompleted exec lookupByID @valueid=7

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:

EventClass TextData
SP:CacheMiss exec dbo.LookupByID @ValueID=7
SQL:BatchStarting exec dbo.LookupByID @ValueID=7
SP:Cache Hit
Lock:Acquired 1:606904
Lock:Released 1:606904
SQL:BatchCompleted exec dbo.LookupByID @ValueID=7

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.

4 Responses to “SQL Server COMPILE Locking and Encryption Keys”

  • Gary Pitkis says:

    Interesting and usefull

  • SQL Lion says:

    To get more information about Isolation levels in SQL Server including Locks and its impact on database in detail with examples, refer the below link:
    http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/

  • Billy says:

    For the issue w/ the executing the procedure with a different case than what it was created as. You mentioned this wasn’t in the KB but you stumbled across it in your own situation. It looks like you were about to explain it but left it out?

    Also, are you talking about case such as “AbCdefGH” or a setting in SQL Server?

    Thanks!

  • Warner says:

    Hi Billy, yes I meant case as in the “AbCdefGH” type. The official KB suggests that if the case is different on the calling code compared to the actual object name then there is a possibility of a cache miss but I couldn’t replicate this behavior.

    This could be legacy information on the documentation and no longer the case as I could not confirm it on SQL 2005. To be on the safe side I suggest just choosing a standard and sticking to it so the different case scenario is not a possibility, specially if working on SQL 2000.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>