I'm struggling to understand how the following two queries could be blocking each other.
Running query (could be almost anything though): insert bulk [Import].[WorkTable] ...
I'm 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 query is blocked by the first query and shows a LCK_M_IS as wait info. Import information is that the temporary table @indexPart contains one record of an index on a completely different table. My expectation is that the cross apply tries to run the stats on that one index which has nothing to do with the other query running.
Thanks
EDIT (NEW):
After several more tests I think I found the culprit but again can't explain it.
- Bulk Insert Session has an X lock on table [Import].[WorkTable]
- The query above is checking for an Index on table [Import].[AnyOtherTable] BUT is requesting an IS lock on [Import].[WorkTable]. I've verified again and again that the query above (when running the stuff without cross apply) is only returning an index on table [Import].[AnyOtherTable].
- Now here comes the magic, changing the CROSS APPLY to an OUTER APPLY runs through just fine without any locking issues.
I hope someone can explain this to me ...
Aucun commentaire:
Enregistrer un commentaire