非常相似的查詢，性能大不相同

June 20, 2019

我有兩個非常相似的查詢

第一個查詢：

SELECT count(*)
FROM Audits a
   JOIN AuditRelatedIds ari ON a.Id = ari.AuditId
WHERE 
   ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1'
   and a.TargetTypeId IN 
   (1,2,3,4,5,6,7,8,9,
   11,12,13,14,15,16,17,18,19,
   21,22,23,24,25,26,27,28,29,30,
   31,32,33,34,35,36,37,38,39,
   41,42,43,44,45,46,47,48,49,
   51,52,53,54,55,56,57,58,59,
   61,62,63,64,65,66,67,68,69,
   71,72,73,74,75,76,77,78,79)

結果：267479

計劃：https ://www.brentozar.com/pastetheplan/?id=BJWTtILyS

第二個查詢：

SELECT count(*)
FROM Audits a
   JOIN AuditRelatedIds ari ON a.Id = ari.AuditId
WHERE 
   ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1'
   and a.TargetTypeId IN 
   (1,2,3,4,5,6,7,8,9,
   11,12,13,14,15,16,17,18,19,
   21,22,23,24,25,26,27,28,29,
   31,32,33,34,35,36,37,38,39,
   41,42,43,44,45,46,47,48,49,
   51,52,53,54,55,56,57,58,59,
   61,62,63,64,65,66,67,68,69,
   71,72,73,74,75,76,77,78,79)

結果：25650

計劃：https ://www.brentozar.com/pastetheplan/?id=S1v79U8kS

第一個查詢大約需要一秒鐘才能完成，而第二個查詢大約需要 20 秒。這對我來說完全違反直覺，因為第一個查詢的計數比第二個高得多。這是在 SQL Server 2012 上

為什麼會有這麼大的區別？如何加快第二個查詢與第一個查詢一樣快？

這是兩個表的創建表腳本：

CREATE TABLE [dbo].[AuditRelatedIds](
   [AuditId] [bigint] NOT NULL,
   [RelatedId] [uniqueidentifier] NOT NULL,
   [AuditTargetTypeId] [smallint] NOT NULL,
CONSTRAINT [PK_AuditRelatedIds] PRIMARY KEY CLUSTERED 
(
   [AuditId] ASC,
   [RelatedId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_AuditRelatedIdsRelatedId_INCLUDES] ON [dbo].[AuditRelatedIds]
(
   [RelatedId] ASC
)
INCLUDE (   [AuditId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

ALTER TABLE [dbo].[AuditRelatedIds]  WITH CHECK ADD  CONSTRAINT [FK_AuditRelatedIds_AuditId_Audits_Id] FOREIGN KEY([AuditId])
REFERENCES [dbo].[Audits] ([Id])

ALTER TABLE [dbo].[AuditRelatedIds] CHECK CONSTRAINT [FK_AuditRelatedIds_AuditId_Audits_Id]

ALTER TABLE [dbo].[AuditRelatedIds]  WITH CHECK ADD  CONSTRAINT [FK_AuditRelatedIds_AuditTargetTypeId_AuditTargetTypes_Id] FOREIGN KEY([AuditTargetTypeId])
REFERENCES [dbo].[AuditTargetTypes] ([Id])

ALTER TABLE [dbo].[AuditRelatedIds] CHECK CONSTRAINT [FK_AuditRelatedIds_AuditTargetTypeId_AuditTargetTypes_Id]

CREATE TABLE [dbo].[Audits](
   [Id] [bigint] IDENTITY(1,1) NOT NULL,
   [TargetTypeId] [smallint] NOT NULL,
   [TargetId] [nvarchar](40) NOT NULL,
   [TargetName] [nvarchar](max) NOT NULL,
   [Action] [tinyint] NOT NULL,
   [ActionOverride] [tinyint] NULL,
   [Date] [datetime] NOT NULL,
   [UserDisplayName] [nvarchar](max) NOT NULL,
   [DescriptionData] [nvarchar](max) NULL,
   [IsNotification] [bit] NOT NULL,
CONSTRAINT [PK_Audits] PRIMARY KEY CLUSTERED 
(
   [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

SET ANSI_PADDING ON

CREATE NONCLUSTERED INDEX [IX_AuditsTargetId] ON [dbo].[Audits]
(
   [TargetId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

SET ANSI_PADDING ON

CREATE NONCLUSTERED INDEX [IX_AuditsTargetTypeIdAction_INCLUDES] ON [dbo].[Audits]
(
   [TargetTypeId] ASC,
   [Action] ASC
)
INCLUDE (   [TargetId],
   [UserDisplayName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

ALTER TABLE [dbo].[Audits]  WITH CHECK ADD  CONSTRAINT [FK_Audits_TargetTypeId_AuditTargetTypes_Id] FOREIGN KEY([TargetTypeId])
REFERENCES [dbo].[AuditTargetTypes] ([Id])

ALTER TABLE [dbo].[Audits] CHECK CONSTRAINT [FK_Audits_TargetTypeId_AuditTargetTypes_Id]

Tl;博士在底部
為什麼選擇了糟糕的計劃
選擇一個計劃而不是另一個計劃的主要原因是Estimated total subtree成本。
與表現較好的計劃相比，糟糕計劃的成本更低。
壞計劃的總估計子樹成本：
更好執行計劃的估計子樹總成本
運營商估計成本
某些運營商可以承擔大部分成本，並且可能是優化器選擇不同路徑/計劃的原因。
在我們更好的執行計劃中，大部分是在&執行連接Subtreecost時計算的：index seek``nested loops operator
而對於我們糟糕的查詢計劃，Clustered index seek運營商成本更低
這應該解釋為什麼可以選擇其他計劃。
（並且通過添加參數來30增加壞計劃的成本，使其高於871.510000估計成本）。 估計猜測™
更好的執行計劃
糟糕的計劃
這會把我們帶到哪裡？
此資訊為我們提供了一種在範例中強制執行錯誤查詢計劃的方法 （請參閱 DML 以幾乎複製 OP 的問題，了解用於複製問題的數據）
通過添加INNER LOOP JOIN連接提示
SELECT count(*)
FROM Audits a
  INNER LOOP JOIN AuditRelatedIds ari ON a.Id = ari.AuditId
WHERE 
   ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1'
   and a.TargetTypeId IN 
   (1,2,3,4,5,6,7,8,9,
   11,12,13,14,15,16,17,18,19,
   21,22,23,24,25,26,27,28,29,
   31,32,33,34,35,36,37,38,39,
   41,42,43,44,45,46,47,48,49,
   51,52,53,54,55,56,57,58,59,
   61,62,63,64,65,66,67,68,69,
   71,72,73,74,75,76,77,78,79)
它更接近，但有一些連接順序差異：
重寫
我的第一次重寫嘗試可能是將所有這些數字儲存在臨時表中：
CREATE TABLE #Numbers(Numbering INT)
INSERT INTO #Numbers(Numbering)
VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),
(36),(37),(38),(39),(41),(42),(43),(44),(45),(46),(47),(48),(49),(51),(52),
(53),(54),(55),(56),(57),(58),(59),(61),(62),(63),(64),(65),(66),(67),(68),
(69),(71),(72),(73),(74),(75),(76),(77),(78),(79);
然後添加一個JOIN而不是大IN()
SELECT count(*)
FROM Audits a
  INNER LOOP JOIN AuditRelatedIds ari ON a.Id = ari.AuditId
  INNER JOIN #Numbers
  ON Numbering = a.TargetTypeId
WHERE 
   ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1';
我們的查詢計劃不同但尚未修復：
巨大的估計運AuditRelatedIds營商成本
這是我注意到的地方
我無法直接重新創建您的計劃的原因是優化的點陣圖過濾。
我可以通過使用 traceflags 7497&禁用優化的點陣圖過濾器來重新創建您的計劃7498
SELECT count(*)
FROM Audits a 
  INNER JOIN AuditRelatedIds  ari ON a.Id = ari.AuditId 
  INNER JOIN #Numbers
  ON Numbering = a.TargetTypeId
WHERE 
   ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1'
OPTION (QUERYTRACEON 7497, QUERYTRACEON 7498);
有關優化點陣圖過濾器的更多資訊，請點擊此處。
這意味著，如果沒有點陣圖過濾器，優化器認為最好先加入#number表然後加入AuditRelatedIds表。
強制執行命令時， OPTION (QUERYTRACEON 7497, QUERYTRACEON 7498, FORCE ORDER);我們可以看到原因：
&
不好
刪除與 maxdop 1 並行的能力
添加MAXDOP 1查詢時執行速度更快，單執行緒。
並添加此索引
CREATE NONCLUSTERED INDEX [IX_AuditRelatedIdsRelatedId_AuditId] ON [dbo].[AuditRelatedIds]
(
   [RelatedId] ASC,
   [AuditId] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
在使用合併連接時。
當我們刪除強制順序查詢提示或不使用#Numbers 表並改用時也是如此IN()。
我的建議是考慮添加MAXDOP(1)並查看這是否有助於您的查詢，如果需要，可以重寫。
當然，您還應該記住，由於優化的點陣圖過濾和實際使用多執行緒效果，我的表現甚至更好：
TL; 博士
估計成本將定義選擇的計劃，我能夠複製行為並看到在我端添加的optimized bitmap filters+parallellism運算符以高性能和快速的方式執行查詢。
您可以考慮添加MAXDOP(1)到您的查詢中，以希望每次都能獲得相同的受控結果，merge join並且沒有 ‘bad’ parallellism。
升級到較新的版本並使用更高的基數估計器版本 CardinalityEstimationModelVersion="70"也可能有所幫助。
用於進行多值過濾的數字臨時表也可以提供幫助。
DML 幾乎複製了 OP 的問題
我花在這上面的時間比我想承認的要多
set NOCOUNT ON;
DECLARE @I INT = 0
WHILE @I &lt; 56
BEGIN
INSERT INTO  [dbo].[Audits] WITH(TABLOCK) 
([TargetTypeId],
   [TargetId],
   [TargetName],
   [Action],
   [ActionOverride] ,
   [Date] ,
   [UserDisplayName],
   [DescriptionData],
   [IsNotification]) 
SELECT top(500000) CASE WHEN ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 10000 = 30 then 29 ELSE ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 10000 END as rownum2 -- TILL 50 and no 30
,'bla','bla2',1,1,getdate(),'bla3','Bla4',1
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2;
SET @I +=1;
END

-- 'Bad Query matches'
INSERT INTO  [dbo].[AuditRelatedIds] WITH(TABLOCK)
   ([AuditId] ,
   [RelatedId]  ,
   [AuditTargetTypeId])
SELECT
TOP(25650)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1, 
('1DD87CF1-286B-409A-8C60-3FFEC394FDB1') , 
CASE WHEN ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 510 = 30 then 29 ELSE ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 510 END as rownum2 -- TILL 50 and no 30
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2

-- Extra matches with 30
SELECT MAX([Id]) FROM [dbo].[Audits];
--28000001 Upper value

INSERT INTO  [dbo].[Audits] WITH(TABLOCK) 
([TargetTypeId],
   [TargetId],
   [TargetName],
   [Action],
   [ActionOverride] ,
   [Date] ,
   [UserDisplayName],
   [DescriptionData],
   [IsNotification]) 
SELECT top(241829) 30 as rownum2 -- TILL 50 and no 30
,'bla','bla2',1,1,getdate(),'bla3','Bla4',1
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2;



;WITH CTE AS
(SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1, 
('1DD87CF1-286B-409A-8C60-3FFEC394FDB1') as gu , 
30 as rownum2 -- TILL 50 and no 30
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2
CROSS APPLY master.dbo.spt_values spt3
)
--267479 - 25650 = 241829
INSERT INTO  [dbo].[AuditRelatedIds] WITH(TABLOCK)
   ([AuditId] ,
   [RelatedId]  ,
   [AuditTargetTypeId])

SELECT TOP(241829) rownum1,gu,rownum2 FROM CTE
WHERE rownum1 &gt; 28000001
ORDER BY rownum1 ASC;