2

One of our 3rd Party Production system went through a major upgrade and with sp_whoisactive I'm now seeing a lot of queries - 1 transaction deep - that are blocking any of my attempts to read dm_db_missing_index_details.

I've tried setting the TRANSACTION ISOLATION LEVEL and using NOLOCK, but SQL just laughs in my face.

Hannah Vernon was able to replicate this behavior here.

Does anyone know how to get round this issue?

We experienced some odd blocking with the system last week and Redgate Intellisense was part of the blocking chain, are there any other DMVs that also have the same issue?

2
  • @AaronBertrand the 3rd Party system has encrypted SPs, so I can't see all the code in the Transaction. Thanks for the advice, I'll raise it with the supplier.
    – pacreely
    Sep 27, 2021 at 12:31
  • @AaronBertrand I'll roll up my sleeves and dig-in to sys.dm_tran_locks. I'll post what I find out.
    – pacreely
    Sep 27, 2021 at 13:00

1 Answer 1

5

System tables and DMVs are always accessed at READ COMMITTED isolation level.

In Hannah Vernon's example the blocking session had run DDL in a transaction, which is expected to block dm_db_missing_index_details because the created-but-not-committed indexes cannot be read by another session.

So check if your blocking sessions have also created/dropped/altered tables or indexes. It could be that they are blocked too, and that's the real problem.

3
  • So can a transaction that's doing DDL work in TempDB cause this issue? Would that explain why the query can retrieve about 10 rows before it gets blocked and hangs? The DMV pulls data across the Instance once it hits a tempdb row it will be block.
    – pacreely
    Sep 27, 2021 at 12:36
  • Yes. If you run Hannah's repro in TempDb, sys.dm_db_missing_index_details in other databases will become blocked. But the question is still why those other sessions have long-running transactions. Sep 27, 2021 at 12:48
  • Redgate Intellisense was trying to work with TempDB when it was being blocked. This is adding up.
    – pacreely
    Sep 27, 2021 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.