September 10, 2009 at 12:59 pm
I have noticed some long running queries and decided to look set up a Blocked Process Report trace. I seem to be catching a few events and they all have the same Mode 5 - X, which is an exclusive lock I think. in the textdata I see the wait resource reported like this:
waitresource="OBJECT: 7:535672956:0 [COMPILE]"
Does the [COMPILE] indicate waiting for a compile lock? The object referenced is a table.
September 10, 2009 at 1:17 pm
check sp_lock. it will tell u db id.object id and page that is locked.
compilation should not cause this. blocking mostly happens beacuse on cannot get resouce cpu , disk or memory. check in perf mon if you have bottleneck of resources. also double check for disk queue length. look for object that is causing it.
:crazy: :alien:
Umar Iqbal
September 10, 2009 at 11:50 pm
That is quite obviously a compilation lock - it says so 😉
See SQL Server blocking causes by COMPILE locks
I have seen this in the past in several very different circumstances - including one memorable one involving encryption. This produced COMPILE locks on tables - lots of them. The solution was to open the key in a separate (one-line!) procedure.
A blog about a similar case can be seen here - though I think a slightly different solution was used.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply