SQL Server COMPILE Locking and Encryption Keys
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:
