意外的索引性能（一切都一樣）

我想看看在計算列上設置索引的效果，所以我創建了一個像這樣的表：

CREATE TABLE [Domain\UserName].[CompColIndexing](
   [a] [int] NOT NULL,
   [nonIndexedNonPersisted]  AS ([a]+(1)),
   [nonIndexedPersisted]  AS ([a]+(1)) PERSISTED,
   [IndexedNonPersisted]  AS ([a]+(1)),
   [IndexedPersisted]  AS ([a]+(1)) PERSISTED
) ON [DATA]

我已經800,000為此添加了行，其值為a循環0到9.

添加了以下索引：

CREATE NONCLUSTERED INDEX [IX_DJB_CompNonPersisted] ON [Domain\UserName].[CompColIndexing] 
(
   [IndexedNonPersisted] 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 [DATA]
GO

CREATE NONCLUSTERED INDEX [IX_DJB_CompPersisted] ON [Domain\UserName].[CompColIndexing] 
(
   [IndexedPersisted] 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 [DATA]
GO

然後我跑了一些ORDER BY子句來看看我會得到什麼性能差異，然後計劃看看改變值a會如何影響事情。

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY a

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedNonPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY nonIndexedPersisted

SELECT *
FROM [EMEA\BanksD].[CompColIndexing]
ORDER BY IndexedPersisted

但出乎意料的是，我發現每個查詢都得到完全相同的結果：

我至少期望SORT第一個查詢的操作會更慢，因為那個查詢沒有索引。

這裡發生了什麼事？

基數是故意低的，實際上，我實際上需要對三個不同的值進行排序。

我正在使用Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)

實際執行計劃見： https ://www.brentozar.com/pastetheplan/?id=S10MTuxGg

您擁有的表定義導致一些非常奇怪的優化器行為。我懷疑您遇到了此SE 文章中記錄的問題。為了避免這個問題，我將只使用

$$ a $$和$$ IndexedPersisted $$列。 查詢提示有助於找出優化器未選擇您期望的計劃的原因。在這裡，您希望使用索引，但 SQL Server 沒有使用它。讓我們並排查看兩個查詢計劃：

SELECT *
FROM [dbo].[CompColIndexing]
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

SELECT *
FROM [dbo].[CompColIndexing] WITH (INDEX([IX_DJB_CompPersisted]))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

查詢優化器認為表掃描後的排序比 800000 次 RID 查找便宜。也許它是錯誤的，所以讓我們執行查詢並比較它們的性能指標。

cpu_time    total_elapsed_time  logical_reads   reads
1204        1395                3087            1485
1516        1570                851798          0

在單獨的會話中執行查詢並檢查“執行後丟棄結果”後，我通過查看 sys.dm_exec_sessions 獲得了這些數字，因此我不必等待返回給客戶端的行。

這些數字在我看來是合理的。僅僅因為可以使用索引並不意味著應該使用它，特別是如果 SQL Server 需要使用索引讀取整個表。這是我能想到的最糟糕的索引案例。當從表中選擇一小部分行或當它們覆蓋索引時，索引可能非常有用。

如果我只選擇

$$ IndexedPersisted $$柱子。在這種情況下，SQL Server 認為使用索引比進行表掃描更便宜。比較兩種方法的程式碼：

-- force table scan
SELECT [IndexedPersisted]
FROM [dbo].[CompColIndexing] WITH (INDEX(0))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

SELECT [IndexedPersisted]
FROM [dbo].[CompColIndexing] WITH (INDEX([IX_DJB_CompPersisted]))
ORDER BY [IndexedPersisted]
OPTION (MAXDOP 1);

以下是性能數據：

cpu_time    total_elapsed_time  logical_reads   reads
1171        1212                2727            1088
343         406                 1798            0

現在索引是覆蓋索引，它是比表掃描更好的表訪問路徑。

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