主页 / dba / 问题 / 153455
Accepted
i-one
Asked: 2016-10-27 23:39:55 +0800 CST

用于并行索引扫描的 STATISTICS IO

  • 772

假设有一个带聚簇索引的表

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)

sql-server sql-server-2014

1 个回答

  1. Best Answer

    如果您向表中添加TABLOCK或READUNCOMMITTED提示，您将获得分配顺序扫描，它将报告与表中页数相同的逻辑读取数。

    通过分配顺序扫描，SQL Server 使用分配结构来驱动页面在线程之间的分配。IAM 页面访问不计入STATISTICS IO.

    对于非分配顺序扫描，SQL Server 使用索引键将扫描分解为子范围。查找新页面或页面范围（基于键范围）并将其分发给工作线程的每个内部操作都需要访问 b 树的上层。这些访问由计算STATISTICS IO，工作线程在定位其当前范围的起点时进行的上层访问也是如此。所有这些额外的上层阅读解释了您看到的差异。

    在我看来，I/O 统计数据被赋予了过多的权重。根据对您和您的工作量重要的内容调整您的查询。这通常是经过的时间，但资源利用率也可能是一个因素。没有一种指标是“最佳”的——您应该采取平衡的观点。

    • 7

相关问题