當目標表具有聚集索引時插入速度要慢得多

我只需要調試一個讓我徹底困惑的問題。

我們的開發數據倉庫上的 ETL 轉換流程在每天成功執行數月後剛剛失敗。相同的 SSIS 作業呼叫相同的儲存過程，具有相同的表架構、索引和數據在生產中執行良好。

此步驟通常需要不到 2 分鐘。今天，4 小時後，這項工作還沒有完成，但也沒有失敗。沒有報告錯誤。SQL 日誌中沒有任何內容，sp_who2也沒有顯示任何阻塞。

• 這是查詢執行良好時估計計劃的連結。
• 這是查詢未完成時的估計計劃的連結。

該作業截斷一個臨時表，然後插入大約 600,000 行數據。ETL 程序對錶具有獨占訪問權。當我檢查時，我能看到的只是 waits on CXPACKET。

我已將故障追溯到一個唯一的聚集索引。

該表在標識列上有一個非聚集主鍵（見下文）

CREATE TABLE [dbo].[Transform_JobCosting_Transaction](
   [ETL_TransformKey] [int] IDENTITY(1,1) NOT NULL,
   [TransactionId] [varchar](255) NOT NULL,
   [KeyType] [varchar](255) NOT NULL,
   [FinancialYear] [varchar](255) NOT NULL,
   [Job] [varchar](255) NOT NULL,
   [Subjob] [varchar](255) NOT NULL,
   [AnalysisCode] [varchar](255) NULL,
   [etc] [varchar](255) NOT NULL,
   [etc] [varchar](255) NOT NULL,
   [etc] [varchar](255) NOT NULL
    CONSTRAINT [PK_Transform_JobCosting_Transaction] PRIMARY KEY NONCLUSTERED 
(
   [ETL_TransformKey] 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 UNIQUE CLUSTERED INDEX [IDX_Unique] ON [dbo].[Transform_JobCosting_Transaction]
(   [FinancialYear] ASC,
   [KeyType] ASC,
   [TransactionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

有一個第三個非聚集索引根本不影響插入。

我們兩個人已經為此工作了 4 個小時。我已經刪除並重新添加了索引 20-30 次嘗試不同組合的選項。

摘要：聚集索引塊插入。非集群工作正常。

我們嘗試過：

• 伺服器重新啟動沒有任何區別。
• 表被截斷並從 SSMS 手動執行 sp 沒有差異。（有 SA 權限）
• 重建索引沒有幫助。
• 如果我刪除聚集索引，則插入工作。
• 在上面插入之後，我可以添加聚集索引而不會出錯。
• 如果將索引刪除並重新添加為非聚集索引，則它可以工作。
• 我檢查了數據，它是由這 3 個欄位分組的唯一的。
• 更改索引以使其不唯一併沒有任何區別。
• 添加/刪除with tablock提示沒有幫助。
• 我嘗試在插入之前對數據進行排序，但沒有任何區別。

執行：Windows Server 2012 R2 Standard 6.3 上的 Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) Developer Edition（64 位）

任何想法或建議將不勝感激。

讓我們退後一步，忘記所有圍繞聚集索引的故障排除。您有一個INSERT查詢，該查詢曾經在合理的時間內完成，但現在不會在幾個小時後完成。為什麼現在該查詢可能很慢？讓我們看一下預計的計劃：

從右向左讀取，計劃是先從 掃描單行，在內側Extract_DW_Control_Finance用掃描的做一個循環連接，根據目標表的聚群鍵對數據進行排序，再用一個循環連接內側Extract_JCS_Trans掃描。Extract_GL_Jnl_Trans第一次加入可能不是問題。該計劃實際上不能從並行性中受益，但是對於外部結果集中的單行，掃描Extract_JCS_Trans應該只發生一次。但是，優化器估計單行將從該連接中出來。如果該行估計是錯誤的，那麼您最終可能會在Extract_GL_Jnl_Trans.

執行良好的查詢的查詢計劃使用不同的策略。行估計有很大不同，它執行雜湊連接：

我懷疑如果您修復行估計，優化器將為性能不佳的查詢選擇不同的計劃。如果Extract_DW_Control_Finance表始終只有一行，您可以考慮將其移入局部變數並可能使用RECOMPILE提示。這可能會導致更好的估計。

至於為什麼刪除聚集索引會導致問題，我懷疑優化器會在Extract_GL_Jnl_Trans沒有聚集索引的情況下進行雜湊連接。散列連接不保留外部輸入的順序，但循環連接確實保留了順序。優化器對單行進行排序和執行循環連接的成本可能低於對 356566 行執行雜湊連接和稍後執行排序的成本。但是，如果不需要排序，則執行散列連接的成本可能低於循環連接。這可能都歸結為修正你的基數估計。

如果您需要在慢速查詢執行時進行更多故障排除，如果您使用的是 SQL Server 2016 SP1 ，則可以考慮跟踪標誌 7412 。這應該為您提供有關 SQL Server 在查詢計劃中“卡住”的位置的線索。如果您能夠要求實際計劃或直接在 SSMS 中執行查詢，則可以使用 sys.dm_exec_query_profiles 或實時查詢統計功能。

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