為什麼一個簡單的查詢在 MS SQL Server 2014 或 2014SP3 上的性能很差，而在其他版本上卻表現良好？

我在 MS SQL Server 2014（以及 2014SP3）上執行以下查詢

SET NOCOUNT ON

CREATE TABLE #GUIDs(
 PartyNames_GUID  UNIQUEIDENTIFIER NULL,
 Party_GUID UNIQUEIDENTIFIER  NULL,
 FirstName  NVARCHAR(255)  NULL
)

insert #GUIDs(Party_GUID)
select top 1 Party_GUID 
FROM Party a 
join PartyNames b on a.Party_ID = b.Party_ID 

--Give the optimizer all kinds of choices. 
create  index i1 on #GUIDs(PartyNames_GUID)
create  index i2 on #GUIDs(Party_GUID)
create  index i3 on #GUIDs(Party_GUID, PartyNames_GUID)
create  index i4 on #GUIDs(PartyNames_GUID,Party_GUID)
update statistics #GUIDs


SELECT  PartyNames.PartyNames_ID,       PartyNames.LastName,        PartyNames.FirstName
FROM Party  INNER JOIN PartyNames   ON PartyNames.Party_ID = Party.Party_ID  
           INNER JOIN #GUIDs       ON Party.Party_GUID = #GUIDs.Party_GUID                     --Hard Match on Party_GUID
                                                    AND 
     (#GUIDs.PartyNames_GUID IS NULL OR PartyNames.PartyNames_GUID = #GUIDs.PartyNames_GUID )  --Optional Match
                                                   AND 
           (#GUIDs.FirstName IS NULL OR PartyNames.FirstName = #GUIDs.FirstName )          --Optional Match

drop table #GUIDs

Party 和 PartyNames 表在它們的主 _ID 上都有一個聚集索引，在它們各自的 GUID 上有一個非聚集唯一索引，而 PartyNames 在來自 Party 的外鍵上有一個索引。這些表的 DDL 包含在末尾，以免混淆問題的描述。Party 有大約 1.9M 行，PartyNames 有 1.3M。PartyNames 每個 Party 最多可以有 3 條記錄。上面的查詢需要幾百毫秒才能執行。但相同的查詢在 SQL Server 2012、2016 和 2019 上執行時間為 15 毫秒或更短。所有版本的架構都相同，完全相同的數據在 BCP 完成後和執行查詢之前更新。這是 2014 年執行計劃的外觀 這是其他 SQL Server 版本的執行計劃，2012、2016、2019

為什麼 2014 年會產生如此糟糕的計劃，它掃描 PartyNames 的主鍵而不是尋找

$$ PartyNames $$.[ix_PartyNames_Party_ID？或者更確切地說，高於和低於 2014 的版本如何設法提出一個好的計劃，而 2014 沒有？其他版本生成的計劃顯然更好，因為它消耗更少的 CPU 和更少的 IO。是 2014 年的某些伺服器設置導致了這種情況嗎？SQL Server 2014 優化器的成本計算與之前或之後的版本有很大不同嗎？任何幫助或指針表示讚賞。我不願意認為這是 SQL Server 2014 中的一個缺點。有趣的是，即使記錄數減少到幾千條，SQL Server 2014 仍會繼續生成相同的錯誤計劃。但是刪除最後一個 AND 子句 (#GUIDs.FirstName IS NULL OR PartyNames.FirstName = #GUIDs.FirstName ) 會導致 SQL Server 2014 生成與其他版本相同的良好計劃。這當然是人為的複制。在 MS SQL Server 2014 上執行我們產品的客戶抱怨性能不佳，而在其他版本上則沒有。對問題進行故障排除會導致這種簡單的再現。 這是用於模式生成的 DDL

CREATE TABLE [dbo].[Party](
   [Party_ID] [int] IDENTITY(1,1) NOT NULL,
   [Party_GUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
CONSTRAINT [pk_Party_Party_ID] PRIMARY KEY CLUSTERED ( [Party_ID] ASC),
CONSTRAINT [uq_Party_Party_GUID] UNIQUE NONCLUSTERED ( [Party_GUID] ASC)
) 
GO
ALTER TABLE [dbo].[Party] ADD  CONSTRAINT [df_Party_Party_GUID]  DEFAULT (newid()) FOR [Party_GUID]
GO




CREATE TABLE [dbo].[PartyNames](
   [PartyNames_ID] [int] IDENTITY(1,1) NOT NULL,
   [PartyNames_GUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
   [Party_ID] [int] NOT NULL,
   [FirstName] [nvarchar](255) NULL,
   [MiddleName] [nvarchar](255) NULL,
   [LastName] [nvarchar](255) NULL,
CONSTRAINT [pk_PartyNames_PartyNames_ID] PRIMARY KEY CLUSTERED ([PartyNames_ID] ASC),
CONSTRAINT [uq_PartyNames_PartyNames_GUID] UNIQUE NONCLUSTERED ([PartyNames_GUID] ASC)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PartyNames] ADD  CONSTRAINT [df_PartyNames_PartyNames_GUID]  DEFAULT (newid()) FOR [PartyNames_GUID]
GO

ALTER TABLE [dbo].[PartyNames]  WITH NOCHECK ADD  CONSTRAINT [fk_PartyNames_Party_ID] FOREIGN KEY([Party_ID])
REFERENCES [dbo].[Party] ([Party_ID])
GO

ALTER TABLE [dbo].[PartyNames] CHECK CONSTRAINT [fk_PartyNames_Party_ID]
GO
CREATE NONCLUSTERED INDEX [ix_PartyNames_Party_ID] ON [dbo].[PartyNames]
(
   [Party_ID] ASC
)
GO

您需要啟用記錄和支持的跟踪標誌 4199（啟用查詢優化器修復）來糾正此問題。

您可以在查詢級別啟用它來測試它，但這需要係統管理員權限（希望您的應用程序沒有）。可以提出一個非常有說服力的論點來全域打開它（作為啟動跟踪標誌），您可以在此處閱讀一些討論：跟踪標誌 4199 - 全域啟用？

---

我能夠使用以下測試數據重現您的問題：

INSERT INTO dbo.Party
   (Party_GUID)
SELECT TOP (1900000)
   NEWID()
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2;

INSERT INTO dbo.PartyNames
   (Party_ID, PartyNames_GUID)
SELECT TOP (1300000)
   Party_ID,
   NEWID()
FROM dbo.Party;

獲取測試查詢的估計計劃會產生錯誤的計劃：

優化器選擇掃描聚群索引，dbo.PartyNames因為出於某種原因，它認為其中的每一行都dbo.PartyNames將與從中檢索的一行匹配dbo.Party。

添加OPTION (QUERYTRACEON 4199)到查詢的末尾可以糾正這個錯誤的推理並產生好的計劃：

有關 TF 4199 的詳細資訊，請參閱此 KB：SQL Server 查詢優化器修補程序跟踪標誌 4199 服務模型

---

另一種解決方案是使用帶有跟踪標誌 9481 的遺留基數估計器，這也產生了良好的計劃。

引用自：https://dba.stackexchange.com/questions/264128