SQL Server 2016 未使用 2012 年使用的索引

2016計劃在這裡

2012計劃在這裡

我有幾乎相同的表，行數差異很小。一個在 2012 年，另一個在 2016 年。索引是相同的。這些 VM 與升級後的作業系統和 SQL Server 版本處於完全相同的環境中。相同數量的 vcore、相同的記憶體、相同的伺服器設置（最大並行度 = 8，並行度的成本門檻值 = 30）。

這對使用單列進行過濾和單列返回的單條記錄的簡單查詢。where 過濾器中的列是索引中的唯一列。

2016 版有 8254356 行

2012 版有 8254427 行

它們是相同的查詢。2016 缺少索引並無緣無故進行全表掃描。2012 在索引掃描後對錶進行 RID 查找（堆）。

我在 2016 年的伺服器上嘗試過WITH (index = CONTACT_RC_NUI1)，成本從 991 躍升至 1889。2012 年的成本是 29。

我嘗試添加AND 1 = (SELECT 1)，但沒有任何區別。

我嘗試通過使用消除參數嗅探作為可能的問題OPTION (RECOMPILE)，但這沒有任何區別。

DBA 在恢復數據庫後執行索引重建。兩台伺服器都有相當近期的索引統計更新（我們執行 Ola 的索引更新腳本）。並且可以肯定的是，我在 2016 年重建了索引，這對 2016 年的解釋計劃沒有影響。

我在查詢下面添加了提示…

select address1_stateorprovince 
from dla.dcrm.CONTACT_RC WITH (index = CONTACT_RC_NUI1)
where wv_partyid = 343083;

這導致了從 991 到 1889 的成本，儘管它顯示與 2012 解釋計劃幾乎相同（2016 只是增加了並行性（收集流））。

2016 年似乎正在做的是僅花費 1% 的索引，但 RID 查找為 99%。2012 年，這種情況發生了逆轉。似乎 2016 使用索引掃描所有條目，然後查找表中的每個 RID？那可能是真的嗎？我認為 2016 年的優化器一直在吸毒。

• wv_party_id是nvarchar(100)

您會看到不同的計劃，因為從 SQL Server 2014 開始，SQL Server 中有一個新的基數估計器。然後他們為新的 CE為 SQL Server 2016 添加了一些新功能。

首先是一些測試數據來重現你所看到的。

create table dbo.T(C1 char(10) default '', C2 varchar(11));

go

insert into dbo.T(C2)
select top(800000) row_number() over(order by (select null))
from sys.columns as c1, sys.columns as c2, sys.columns as c3

go

create index IX_T_C2 on dbo.T(C2)

以及將為您生成兩個不同計劃的查詢，以便您可以在同一版本的 SQL Server 中比較它們。

-- Table scan version
select C1
from dbo.T with (index = 0)
where C2 = 100000
option (maxdop 1);

-- Index Scan version
select C1
from dbo.T with (index = IX_T_C2)
where C2 = 100000
option (maxdop 1);

SQL Server 2012 中的表掃描版本掃描所有行並返回一個。那裡並不奇怪。

SQL Server 2012 的索引掃描版本掃描索引中的所有行並返回一行。有一些東西需要進一步研究，但現在您應該額外查看 Index Scan 運算符的 Estimated Number of Rows。

SQL Server 2016 的 Table Scan 版本與 2012 中的版本沒有什麼不同。它掃描所有行返回一行。

Index Scan 版本看起來與 2012 年相同，但成本要高得多，這是因為 Estimated Number of Rows 比 2012 年高得多

所以 SQL Server 現在認為它必須執行 80000 RID 查找才能返回 1 行，這就是為什麼它選擇 SQL Server 2016 中的表掃描和新的基數估計器。

新的估計器看到謂詞where CONVERT_IMPLICIT(int,C2) = 100000並放棄。它對相等謂詞使用 10% 選擇性的標準**猜測，其中 800,000 行的 10% = 80,000。**最初的估計器使用更複雜的邏輯來產生對一行的非猜測（準確！）估計。

現在討論索引掃描的問題。那可能不是你想要的。您希望 SQL Server 執行 Index Seek 以查找您要查找的行。目前 SQL Server 不能這樣做，因為您在 where 子句中有類型不匹配，並且您確實在查詢計劃中收到了有關它的警告。修復這個問題，您應該會在兩個版本的 SQL Server 中看到一個帶有索引查找和 RID 查找的計劃。

另請注意，執行計劃中的成本百分比始終基於優化器估計，而不是實際執行時資訊。

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