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

野性酷女 2022-08-29 10:09 177阅读 0赞

### 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  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70]

# 试验 #

### 前提及说明 ###

##### 表的创建、索引定义 #####

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');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 1]

##### 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得到下面，即还没有更新  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 2]  
后面尝试到添加到600行，也没有更新，怀疑是该索引没有被使用到，所有没有更新  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 3]

### 试验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)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 4]  
即：表中有1条数据后再建立索引，自动建立的统计信息非null

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 5]  
修改数已到阈值500，但没有更新，尝试使用下索引的sql，再查看统计信息：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 6]  
即当n <= 500时，更新阈值为500，到达阈值后，当使用到该统计信息时，会触发统计信息的更新；  
![在这里插入图片描述][0a2b601130fd4ef2ad921741fe3b62f1.png]

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

现在n=501  
![在这里插入图片描述][e2bd888c782c4d3f83ce6777398e8181.png]  
即 min (500 + 501 \* 0.2), sqrt(1000 \* 501)) = min (600.2, 707.81) = 600.2  
插入600条数据，看统计信息：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 7]  
发现没有更新，再插入1条数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 8]  
发现600.2即601为阈值，使用到统计信息时，会触发统计信息更新；

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

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

插入720条、再插入1条，分别查看统计信息更新情况：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 9]  
发现阈值并不是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，并且打开了自动更新  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 10]

插入数据到501行，修改数为500时，统计信息还是没更新  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 11]

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');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 12]  
即达到阈值后，并不会更新统计信息。再插入一条记录

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');

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70]: /images/20220829/2a9e7cfd40e94761982066a3537b0364.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 1]: /images/20220829/edb1d997169948a99d11d4c04fd30db8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 2]: /images/20220829/08d97d7e31d044bc8aef1010d638508a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 3]: /images/20220829/9929685120be4a299a5fefacb38ab54a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 4]: /images/20220829/18a909ea12c74637ae7d4b1c89ecf07b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 5]: /images/20220829/192afa3cb5c74a0786f9c209c5182804.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 6]: /images/20220829/3a88135931934285a59520d371b53a55.png
[0a2b601130fd4ef2ad921741fe3b62f1.png]: /images/20220829/5f02032c5c2c461d88ce05b9e0e13d9f.png
[e2bd888c782c4d3f83ce6777398e8181.png]: /images/20220829/e0f10c501fe643afbf35d04683acbd8a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 7]: /images/20220829/905834f6680e47d39e0b3e193e56592a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 8]: /images/20220829/4a18d1b4e92a41618e090dbf0ebd9c4d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 9]: /images/20220829/d705ededd61a4a7b8c9b7086c9cfe2e8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 10]: /images/20220829/a3d91d579e794bb5b6eb3322effe4733.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 11]: /images/20220829/d42c6abd5d194769a4d8422dda5a4140.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 12]: /images/20220829/fd16307acbdc46ba98e168462f79a422.png