SQL Server COMPILE Locking and Encryption Keys

3 min read

Sep 19, 2008 12:00:00 AM

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)

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:

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.
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.
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:
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.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

SQL Server COMPILE Locking and Encryption Keys

Share this

Share this

More resources

JSP Cache Issues in 11i and R12

EBS Forms Compilation Errors in Large Terminal Windows: Size Does Matter!

Sleuthing for Temporary Space Spendthrifts

Ready to unlock value from your data?