定时更新索引 更新索引2016

sql server2016的统计信息自动更新尝试

• 理论
• 试验

• 前提及说明

• 表的创建、索引定义
• sql1:查询统计信息 上次更新事件、修改次数
• sql2:插入新的数据列

• 试验1：先创建索引，再插入数据，发现一直不会更新
• 试验2：统计信息首次有数据
• 试验2：数据不断增多，统计信息更新
• 试验3：n > 500时的统计信息更新
• 试验4：继续增大数据，验证公式是否正确
• 继续试验的结果

• 结论

理论

参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/statistics/statistics?view=sql-server-2016

试验

前提及说明

表的创建、索引定义

drop table op_param_sniffing
-- 表定义，及数据插入
create table op_param_sniffing (
	id bigint IDENTITY(1,1) primary key not null,
	product_id varchar(100) not null,
	product_name varchar(100) not null
)
-- 索引
create index idx_op_param_sniffing_product_id on op_param_sniffing(product_id)

sql1:查询统计信息 上次更新事件、修改次数

SELECT  name, sp.*  
FROM sys.stats AS stat   
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE stat.object_id = object_id('op_param_sniffing');

sql2:插入新的数据列

插入5条记录

declare @id int;
set @id = 0;
while @id < 5
begin
	insert into op_param_sniffing(product_id, product_name) values('300', '商品300')
	set @id = @id + 1
end

试验1：先创建索引，再插入数据，发现一直不会更新

使用sql2一直插入500行，使用sql1得到下面，即还没有更新

后面尝试到添加到600行，也没有更新，怀疑是该索引没有被使用到，所有没有更新

试验2：统计信息首次有数据

drop table op_param_sniffing
-- 表定义，及数据插入
create table op_param_sniffing (
	id bigint IDENTITY(1,1) primary key not null,
	product_id varchar(100) not null,
	product_name varchar(100) not null
)
-- 先插入一条记录
insert into op_param_sniffing(product_id, product_name) values('300', '商品300')
-- 索引
create index idx_op_param_sniffing_product_id on op_param_sniffing(product_id)

即：表中有1条数据后再建立索引，自动建立的统计信息非null

试验2：数据不断增多，统计信息更新

修改数已到阈值500，但没有更新，尝试使用下索引的sql，再查看统计信息：

即当n <= 500时，更新阈值为500，到达阈值后，当使用到该统计信息时，会触发统计信息的更新；

试验3：n > 500时的统计信息更新

现在n=501

即 min (500 + 501 * 0.2), sqrt(1000 * 501)) = min (600.2, 707.81) = 600.2

插入600条数据，看统计信息：

发现没有更新，再插入1条数据：

发现600.2即601为阈值，使用到统计信息时，会触发统计信息更新；

试验4：继续增大数据，验证公式是否正确

n = 1102, 即 min (500 + 1102 * 0.2), sqrt(1000 * 1102)) = min (720.4, 1049.76) = 720.4, 故阈值为721.

插入720条、再插入1条，分别查看统计信息更新情况：

发现阈值并不是721，说明带小数的时候不一定是往上取！！！！！

继续试验的结果

1. n = 1822, min (500 + 1822 * 0.2), sqrt(1000 * 1822)) = 864.4, 实际阈值865
2. n = 2687, min (500 + 2687 * 0.2), sqrt(1000 * 2687)) = 1037.4,实际1037
3. n = 3724, min (500 + 3724* 0.2), sqrt(1000 * 3724)) = 1244.8,实际1244

结论

上面只是进行部分测试，边界值可以自己采用类似方法进行。表的统计信息的更新规则：

1. n <= 500, 阈值为500；
   2）n > 500, 公式min (500 + 0.2n, sqrt(1000 * n)), 对于小数的情况可能向下取整，也可能向上取整（其实统计信息更新需要时间，上面的向上取整的情况可能有误，详细的可以自己进行多次试验，实际情况下，应该也不会介意1的误差）
   3）达到阈值后，可能不会立即更新，但若调用使用该统计信息的sql，会触发更新；

说明：

上面试验在ssms 2016环境，兼容级别为130，并且打开了自动更新

插入数据到501行，修改数为500时，统计信息还是没更新

num < 6时，阈值为6，插入6条记录，再查看统计信息情况：

declare @id int;
set @id = 0;
while @id < 6
begin
	insert into op_param_sniffing(product_id, product_name) values('300', '商品300')
	set @id = @id + 1
end
-- 查看统计信息
select count(*) from op_param_sniffing

SELECT  name, sp.*  
FROM sys.stats AS stat   
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE stat.object_id = object_id('op_param_sniffing');

即达到阈值后，并不会更新统计信息。再插入一条记录

insert into op_param_sniffing(product_id, product_name) values('300', '商品300')

-- 查看统计信息
select count(*) from op_param_sniffing

SELECT  name, sp.*  
FROM sys.stats AS stat   
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE stat.object_id = object_id('op_param_sniffing');