影响Query Optimizer产生执行计画的关键(统计值)

什么是统计值

SQL Server的QO(Query Optimizer)透过cost-based model来选择一个最合适计画(估算成本最低)来执行查询

　注意每个执行计画是使用CPU来做估算，使用过的执行计画一般会Cache起来已便下次使用

QO会依照基数估计(Cardinality estimation)来产生执行计画，基数估计扮演一个很重要的角色

SQL Server统计值是对于每个Index或栏位资料分布做纪录，任何型态都支援统计值资料.

过期的统计值资料导致QO误判产生不良执行计画

在我们建立Index时，统计值会自动创建。此外当栏位在查询里被使用(作为WHERE条件的一部分,group by子句,join条件)统计值会被自动建立

本篇同步发表在我的Blog

影响Query Optimizer产生执行计画的关键(统计值)

何时建立统计值?

每个索引都会有自己个统计资讯，在UI查看统计资讯如下图.

如果查询条件栏位没有统计值，Query Optimizer会在编译前将统计值建立或有门槛条件性的更新。

如下图我们使用C3没有建立索引栏位来查询，SQL-Server就会帮我们自动产生_WA_Sys_00000003_6EF57B66这个统计资讯来让QO产生执行计画时有个依据.

查询资料表统计值 & 了解统计值栏位含意

想要查询资料表索引的统计值可以输入DBCC SHOW_STATISTICS，第一个参数是查询资料表，第二个参数是查询的索引或统计值.

DBCC SHOW_STATISTICS('dbo.posts','PK_Posts')

使用上语法查询会出现三个结果集

第一个结果集

显示出此统计值的基本资讯其中有几个重要的栏位

第二个结果集

密度分布,使用常数查询,直接使用子方图进行资料笔数估计

第三个结果集

RANGE_HI_KEY:每个区域资料的分布。
RANGE_ROWS:上图列出(120 + 1) ~(126)区间的Row是57.175笔资料
EQ_ROWS:代表这个区间值。
DISTINCT_RANGE_ROWS:代表这个区间裏面有几个特殊/单一(Unique)值。
AVG_RANGE_ROWS:代表这个区间每个特殊值平均有几笔

触发统计值更新

假如有设定自动更新统计值，异动资料笔数超过 (500 + 20%)资料，会触发统计值更新

如果是大资料表容易造成统计值不準确，因为要达到自动更新门槛有点困难

在SQL2017之前版本建议启用TF2371，可以让自动更新统计值的门槛数量变平滑点

DBCC TRACEON (2371,-1)

启动后大资料就不会只使用(500 + 20%)条件来更新统计值，会依照资料表笔数来判断(如下图)

假如使用执行计画(估计值)很不準确可以查看，当前的统计值是否是正确

如果要更新统计值可以使用下面语法.

UPDATE STATISTICS dbo.T1;  --更新统计值DBCC SHOW_STATISTICS ('dbo.T1', idx1) --显示统计值

删除重複统计值资料

我们在建立索引，在下次查询时SQL-Server会帮我们建立索引的统计值资料,这时候之前建立资料变得是多余的就可以利用下面Script找寻是否有同个栏位拥有重複统计值，可建立删除Script

WITH    autostats(object_id, stats_id, name, column_id)AS (SELECT  sys.stats.object_id ,        sys.stats.stats_id ,        sys.stats.name ,        sys.stats_columns.column_idFROM    sys.stats        INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id                                        AND sys.stats.stats_id = sys.stats_columns.stats_idWHERE   sys.stats.auto_created = 1        AND sys.stats_columns.stats_column_id = 1)SELECT  OBJECT_NAME(sys.stats.object_id) AS [Table] ,sys.columns.name AS [Column] ,sys.stats.name AS [Overlapped] ,autostats.name AS [Overlapping] ,'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id) + '].[' + OBJECT_NAME(sys.stats.object_id) + '].[' + autostats.name + ']'FROM    sys.statsINNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_idAND sys.stats.stats_id = sys.stats_columns.stats_idINNER JOIN autostats ON sys.stats_columns.object_id = autostats.object_idAND sys.stats_columns.column_id = autostats.column_idINNER JOIN sys.columns ON sys.stats.object_id = sys.columns.object_idAND sys.stats_columns.column_id = sys.columns.column_idWHERE   sys.stats.auto_created = 0AND sys.stats_columns.stats_column_id = 1AND sys.stats_columns.stats_id != autostats.stats_idAND OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0;

统计值和查询记忆体分配

SQL-Server查询不同操作有不同的记忆体分配方式,例如Index Scan不用把资料存在记忆体中(因为一笔一笔取出就可以),但如果是使用Sort相关的操作,需要在执行前访问rowset

SQL-Server会依照统计值来分配合适的记忆体大小,假如统计值不准会导致记忆体分配不对,就会把资料存在TempDb造成查询效能低落.

下面这个範例来演示上面所说的

create table dbo.MemoryGrantDemo (     ID int not null,     Col int not null,     Placeholder char(8000) );  create unique clustered index IDX_MemoryGrantDemo_ID  on dbo.MemoryGrantDemo(ID);  ;with N1(C) as (select 0 union all select 0) -- 2 rows  ,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows  ,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows  ,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows  ,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows  ,IDs(ID) as (select row_number() over (order by (select null)) from N5)  insert into dbo.MemoryGrantDemo(ID,Col,Placeholder)      select ID, ID % 100, convert(char(100),ID) from IDs;   create nonclustered index IDX_MemoryGrantDemo_Col  on dbo.MemoryGrantDemo(Col); 

建立一张表MemoryGrantDemo并建立Clustered Index跟新增65,536笔资料Col介于1~100之间,最后在建立一个NonClustered Index

Col介于1~100之间会有统计值

 ;with N1(C) as (select 0 union all select 0) -- 2 rows  ,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows  ,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows  ,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows  ,N5(C) as (select 0 from N4 as T1 cross join N2 as T2) -- 1,024 rows  ,IDs(ID) as (select row_number() over (order by (select null)) from N5)  insert into dbo.MemoryGrantDemo(ID,Col,Placeholder)      select 100000 + ID, 1000, convert(char(100),ID)      from IDs      where ID <= 656;

最后在新增Col = 1000的656笔资料

因为只有新增656只有原本的1%所以不会触法更新统计值

如下图能看到IDX_MemoryGrantDemo_Col并没有Col=1000的资讯

建立好资料后我们使用statistics和打开执行计画来看看两者差别

 declare      @Dummy int  set statistics time on  select @Dummy = ID from dbo.MemoryGrantDemo where Col = 1 order by Placeholder;  select @Dummy = ID from dbo.MemoryGrantDemo where Col = 1000 order by Placeholder;  set statistics time off 

在执行计画中看到第二个查询有个惊叹号,移过去看可以发现查询出来的资料写入TempDb中

[讯息]中能看到第二个查询语法使用时间比较长

因为SQL-Server依照统计值分配记忆体大小,所以会把统计值预估外资料搬到tempdb资料库