统计信息对执行计划的影响

时间：2022-06-06 03:50:37

最近遇到一个问题，示例表结构如下：

set statistics io on
use tempdb 
go
--drop table TB
create table TB (id int identity primary key, parentId int,name varchar(50))
go
insert into TB(parentId,name) select 0,CAST(NEWID() as varchar(50))
go 499990
 
insert into TB(parentId,name) select 499990,CAST(NEWID() as varchar(50))
insert into TB(parentId,name) select 499991,CAST(NEWID() as varchar(50))
insert into TB(parentId,name) select 499992,CAST(NEWID() as varchar(50))
insert into TB(parentId,name) select 499993,CAST(NEWID() as varchar(50))
insert into TB(parentId,name) select 499994,CAST(NEWID() as varchar(50))
insert into TB(parentId,name) select 499995,CAST(NEWID() as varchar(50))
insert into TB(parentId,name) select 499996,CAST(NEWID() as varchar(50))
insert into TB(parentId,name) select 499997,CAST(NEWID() as varchar(50))
insert into TB(parentId,name) select 499998,CAST(NEWID() as varchar(50))
insert into TB(parentId,name) select 499999,CAST(NEWID() as varchar(50))
go

有一个如下的SQL查询：

select * from TB 
where parentId in (select id from TB where parentId=499997)

速度非常的慢，明显是因为parentId字段无索引，然后，为其添加了索引：

create index ix_parentId on TB(parentId)

这时，清下执行计划缓存，再次执行该SQL查询，执行计划如下：

DBCC FREEPROCCACHE

统计信息对执行计划的影响

看到有一个Clustered Index Scan ，这个是非常耗io的，而实事上parentId=499997的ID值只有一个，为499998，而parentId =499998的行也只有一条。
IO扫描为：

Table 'TB'. Scan count 10, logical reads 3882, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

这样看来，这个index scan非常的不合理。此时，我们看一下索引ix_parentid上的统计信息：
统计信息对执行计划的影响

可以看到一共有500000行，采样为500000行，说明在建索引ix_parentid时，应该采用的是全扫描。

接下来，单独执行了一次统计信息更新：

UPDATE STATISTICS TB ix_parentId

然后，再次查看统计信息：
统计信息对执行计划的影响

此时，统计信息与之前不同，已不是全扫描，而是只采集了163584行，而且并未采集到parentid<>0的数据，再次清空执行计划缓存

DBCC FREEPROCCACHE

然后执行本例的SQL查询，结果如下：

统计信息对执行计划的影响

IO扫描为：
Table 'TB'. Scan count 2, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

这样来看，执行计划已正常。

在此案例中，由于parentid绝大多数值为0，只有极少数不为0，道理上来讲，并不应该创建索引，但实际业务中，也可能出现此类请求，何去何从？

标签：

相关文章