SQL Server 2012 query blocked with LCK_M_IS
I am trying to understand how the following two requests can block each other.
Run a query (can be almost anything): insert the [Import] volume. [WorkTable] ...
I am trying to run the following SELECT query at the same time:
SELECT *
FROM ( SELECT * FROM @indexPart ip
JOIN sys.indexes i (NOLOCK)
ON i.object_id = ip.ObjectId
and i.name = ip.IndexName) i
CROSS
APPLY sys.dm_db_index_physical_Stats(db_id(), i.object_id,i.index_id,NULL,'LIMITED') ps
WHERE i.is_disabled = 0
The second request is blocked by the first request and shows LCK_M_IS as pending information. The information import is that the @indexPart temp table contains one index entry in a completely different table. My guess is that cross apply is trying to run statistics on one index, which has nothing to do with the other query being run.
thank
EDIT (NEW):
After a few tests, I think I have found the culprit, but again I cannot explain it.
- The Bulk Insert Session has an X lock on the [Import] table. [WorkTable]
- The above query checks the index on the [Import] table. [AnyOtherTable] BUT requests an IS lock in [Import]. [WorkTable]. I have verified over and over that the above query (when run without applying a cross) only returns an index on the [Import] table. [AnyOtherTable].
- Now the magic has come, changing CROSS APPLY to INTERNAL APPLICABILITY, done just fine without any blocking issues.
I hope someone can explain this to me ...
source to share
The problem might be with the where clause you were using. It must be inside an inline table. The next change might make a difference.
FROM ( SELECT * FROM @indexPart ip
JOIN sys.indexes i (NOLOCK)
ON i.object_id = ip.ObjectId
and i.name = ip.IndexName
WHERE i.is_disabled = 0) i
If you do this, it can reduce the number of records passed to the cross apply statement.
source to share