Sql-server – selecting from sys.dm_db_missing_index_details will start to return rows, and then sits and spins

sql serversql server 2014

I am trying to run some analysis on missing indexes on a server (I haven't ever done it on this server before). I was running sp_blitzindex and it would hang up on "Inserting data into #MissingIndexes." I dug into the query and found where it hits sys.dm_db_missing_index_details.

Trying to select from sys.dm_db_missing_index_details will return about 300 records, and then it'll sit and spin. I didn't want to let it keep going to see how long it would take to finish, but I let it run for over two minutes and it didn't complete.

Why would this take a long time to return? What would cause this?

Best Answer

This sounds like other sessions are blocking the dynamic management view.

CREATE TABLE and DROP TABLE force an update to the internal tables maintained for the sys.dm_db_missing_index_details.

I was able to recreate a blocking scenario where an open transaction prevents reads against sys.dm_db_missing_index_details. Run this in an SSMS window on your development machine:

BEGIN TRANSACTION
IF OBJECT_ID(N'dbo.mid_test_2', N'U') IS NOT NULL
DROP TABLE dbo.mid_test_2;
IF OBJECT_ID(N'dbo.mid_test', N'U') IS NOT NULL
DROP TABLE dbo.mid_test;
CREATE TABLE dbo.mid_test
(
    ID int NOT NULL PRIMARY KEY CLUSTERED
    , SomeVal varbinary(2000) NOT NULL DEFAULT (CRYPT_GEN_RANDOM(2000))
);

CREATE TABLE dbo.mid_test_2
(
    ID int NOT NULL FOREIGN KEY REFERENCES dbo.mid_test(ID)
    , SomeVal varbinary(2000) NOT NULL DEFAULT (CRYPT_GEN_RANDOM(2000))
);
GO

;WITH src AS 
(
    SELECT v.num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.mid_test (ID)
SELECT s1.num 
    + (s2.num * 10) 
    + (s3.num * 100) 
    + (s4.num * 1000) 
    + (s5.num * 10000) 
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5
ORDER BY 1;

;WITH src AS 
(
    SELECT v.num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.mid_test_2 (ID)
SELECT s1.num 
    + (s2.num * 10) 
    + (s3.num * 100) 
    + (s4.num * 1000) 
    + (s5.num * 10000) 
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5
ORDER BY 1;

SELECT *
FROM dbo.mid_test m1
    INNER JOIN dbo.mid_test_2 m2 ON m1.ID = m2.ID
WHERE m2.ID = 66666

Now, in another SSMS window, run the following (you may need to run it several times depending on how quick you are!)

SELECT *
FROM sys.dm_db_missing_index_details;

For me, the above query blocks until I either commit or rollback the transaction started in the first query window, with:

COMMIT TRANSACTION

or 

ROLLBACK TRANSACTION

Unfortunately, running SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; prior to querying sys.dm_db_missing_index_details does not help; it still blocks until the open transaction commits or rolls back.

Related Question