為什麼一個簡單的查詢在 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 的遺留基數估計器,這也產生了良好的計劃。