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](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png)
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.
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 PythianSymmetricKeyIf 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 |
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 |
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 |
Share this
You May Also Like
These Related Stories
How to Fix the “Triggers on Memory-Optimized Tables Must Use WITH NATIVE_COMPILATION” Azure SQL Data Sync Error
![](https://www.pythian.com/hubfs/Imported_Blog_Media/shutterstock_2060584484-scaled.jpeg)
How to Fix the “Triggers on Memory-Optimized Tables Must Use WITH NATIVE_COMPILATION” Azure SQL Data Sync Error
Jul 21, 2022
1
min read
JSP Cache Issues in 11i and R12
JSP Cache Issues in 11i and R12
Mar 4, 2009
2
min read
DBD::Oracle and Windows 64bit
DBD::Oracle and Windows 64bit
Apr 20, 2010
3
min read
No Comments Yet
Let us know what you think