从sys.dm_db_missing_index_details中选择将开始返回行，然后坐下并旋转

Question

我试图对服务器上缺少的索引进行一些分析(我以前从未在此服务器上做过)。我运行的是sp_blitzindex，它会挂掉“将数据插入#MissingIndexes”。我深入了解了这个查询，并找到了它指向sys.dm_db_missing_index_details的位置。

尝试从sys.dm_db_missing_index_details中选择将返回大约300个记录，然后它将坐下来旋转。我不想让它继续看它要多长时间才能完成，但我让它运行了两分钟，但它没有完成。

为什么要花很长时间才能回来？是什么导致的？

Answer 1

这听起来像是其他会话阻塞了动态管理视图。

CREATE TABLE和DROP TABLE 强制对内部表进行更新为sys.dm_db_missing_index_details维护。

我能够重新创建一个阻塞场景，其中打开的事务阻止针对sys.dm_db_missing_index_details的读取。在开发机器上的SSMS窗口中运行以下操作：

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

现在，在另一个SSMS窗口中，运行以下命令(您可能需要运行几次，这取决于您的速度！)

SELECT *
FROM sys.dm_db_missing_index_details;

对我来说，上面的查询一直阻塞，直到我提交或回滚事务开始于第一个查询窗口，其中包括：

COMMIT TRANSACTION

或

ROLLBACK TRANSACTION

不幸的是，在查询SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;之前运行sys.dm_db_missing_index_details并不有用；它仍然会阻塞，直到打开的事务提交或回滚为止。