用於並行索引掃描的 STATISTICS IO

假設有一個帶有聚集索引的表

create table [a_table] ([key] binary(900) unique clustered);

和一些數據

insert into [a_table] ([key])
select top (1000000) row_number() over (order by @@spid)
from sys.all_columns a cross join sys.all_columns b;

通過檢查該表的儲存統計資訊

select st.index_level, page_count = sum(st.page_count)
from sys.dm_db_index_physical_stats(
   db_id(), object_id('a_table'), NULL, NULL, 'DETAILED') st
group by rollup (st.index_level)
order by grouping_id(st.index_level), st.index_level desc;

有人能看見

index_level page_count
----------- ----------
8           1
7           7
6           30
5           121
4           487
3           1952
2           7812
1           31249
0           125000
NULL        166659

該表總共需要 166659 頁。

但是表掃描

set nocount on;
set statistics io, time on;
declare @cnt int;
select @cnt = count(1) from [a_table];
set statistics io, time off;

生產

Table 'a_table'. Scan count 5, logical reads 484367, ...
CPU time = 1757 ms,  elapsed time = 460 ms.

與表佔用的空間相比，邏輯讀取次數幾乎高出三倍。當我檢查查詢計劃時，我注意到 SqlServer 使用了並行索引掃描。這就是問題的第一部分出現的地方。

如何執行並行索引掃描以使 SqlServer 執行如此多的邏輯讀取？

指定option (maxdop 1)抑制並行性

set nocount on;
set statistics io, time on;
declare @cnt2 int;
select @cnt2 = count(1) from [a_table] option (maxdop 1);
set statistics io, time off;

導致

Table 'a_table'. Scan count 1, logical reads 156257, ...
CPU time = 363 ms,  elapsed time = 367 ms.

在這種情況下比較並行和非並行索引掃描的統計數據得出的結論是，有時最好避免並行索引掃描。這就是問題的第二部分出現的地方。

我什麼時候應該擔心並行索引掃描？什麼時候應該避免/抑制它？最佳實踐是什麼？

---

以上結果是在

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

如果向表中添加TABLOCKorREADUNCOMMITTED提示，您將獲得分配順序掃描，它將報告與表中的頁數相同的邏輯讀取數。

通過分配順序掃描，SQL Server 使用分配結構來驅動執行緒之間的頁面分佈。IAM 頁面訪問不計入STATISTICS IO.

對於非分配順序掃描，SQL Server 使用索引鍵將掃描分解為子範圍。查找新頁面或頁面範圍（基於鍵範圍）並將其分發到工作執行緒的每個內部操作都需要訪問 b 樹的上層。這些訪問按計數STATISTICS IO，工作執行緒在定位其目前範圍的起點時進行的上層訪問也是如此。所有這些額外的上層讀取解釋了您所看到的差異。

在我看來，I/O 統計數據太重要了。根據對您和您的工作量重要的內容調整您的查詢。這通常是經過的時間，但資源使用率也可能是一個因素。沒有一個指標是“最好的”——你應該採取平衡的觀點。

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