alter table tablename rebuild基于统计更新的注意事项

栏目：云星空知识作者：金蝶来源：金蝶云社区发布：2024-09-16浏览：2

背景知识： sys.dm_db_stats_properties：返回当前 SQL Server 数据库中指定数据库对象（表或索引视图）的统计信息属性。总结： 1：对一个表中的数据，执行truncate/insert后后，查看dm_db_stats_properties 是不会变化的，除非主动进行索引优化 2：alater table的方式对表进行重建，需要分别对表，索引进行重建，否则只影响到表本身，索引不会变化,索引需要非常注意（如果对数据启用压缩也是类似问题） 3：使用dbcc dbreindex（表名）会对表，索引等相关的信息，进行重建（注意，是不带索引名的重建，否则也只会重建指定索引）测试过程如下：环境：表索引定义（测试用途，忽略索引字段的选择） index_name index_description index_keys ------------------------------ ------------------------------ ------------------------------ idx_001 clustered located on PRIMARY fid idx_0011 nonclustered located on PRIMAR fid 1：对表执行truncate table操作：查看统计相关情况脚本 SELECT ss.name, ss.stats_id, shr.* FROM sys.stats ss INNER JOIN sys.stats_columns sc ON ss.stats_id = sc.stats_id AND ss.object_id = sc.object_id INNER JOIN sys.all_columns ac ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr where ss.object_id=object_id('teststat') 查询结果，可以看到采样行和行数，已经最后更新的日期没有变化，所以数据删除后，这个表的记录是不会更新的。 name stats_id object_id stats_id last_updated rows rows_sampled steps unfiltered_rows modification_counter ------------------------------ ----------- ----------- ----------- --------------------------- -------------------- -------------------- ----------- -------------------- -------------------- idx_001 1 1634104862 1 2022-01-17 16:34:15.3200000 7365120 7365120 2 7365120 7365320 idx_0011 2 1634104862 2 2022-01-17 16:40:40.6900000 7365120 7365120 2 7365120 7365320 执行dbcc showcontig，可以看到数据为空，说明truncate table操作会释放空间，并且统计信息会被调整，说明dbcc 的命令是符合预期的。 DBCC SHOWCONTIG 正在扫描 'teststat' 表... 表: 'teststat' (1634104862)；索引 ID: 2，数据库 ID: 6 已执行 LEAF 级别的扫描。 - 扫描页数................................: 0 - 扫描区数..............................: 0 - 区切换次数..............................: 0 - 每个区的平均页数........................: 0.0 - 扫描密度 [最佳计数:实际计数].......: 100.00% [0:0] - 逻辑扫描碎片 ..................: 0.00% - 区扫描碎片 ..................: 0.00% - 每页的平均可用字节数.....................: 0.0 - 平均页密度(满).....................: 0.00% DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 DBCC SHOWCONTIG 正在扫描 'teststat' 表... 表: 'teststat' (1634104862)；索引 ID: 1，数据库 ID: 6 已执行 TABLE 级别的扫描。 - 扫描页数................................: 0 - 扫描区数..............................: 0 - 区切换次数..............................: 0 - 每个区的平均页数........................: 0.0 - 扫描密度 [最佳计数:实际计数].......: 100.00% [0:0] - 逻辑扫描碎片 ..................: 0.00% - 区扫描碎片 ..................: 0.00% - 每页的平均可用字节数.....................: 0.0 - 平均页密度(满).....................: 0.00% DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。对表进行重建：alter table teststat rebuild with(data_compression=row,maxdop=2,online=on) 查询结果,可以看到只影响了聚集索引，而非索引并没有发生变化 name stats_id object_id stats_id last_updated rows rows_sampled steps unfiltered_rows modification_counter ------------------------------ ----------- ----------- ----------- --------------------------- -------------------- -------------------- ----------- -------------------- -------------------- idx_001 1 1634104862 1 NULL NULL NULL NULL NULL NULL idx_0011 2 1634104862 2 2022-01-17 16:40:40.6900000 7365120 7365120 2 7365120 7365320 只对表进行重建，是不会影响到非聚集索引统计信息,索引对非聚集索引也进行类似的操作对索引进行重建：alter index idx_0011 on teststat rebuild with(data_compression=row,maxdop=2,online=on) 查询结果,可以看到得到了预期的结果 name stats_id object_id stats_id last_updated rows rows_sampled steps unfiltered_rows modification_counter ------------------------------ ----------- ----------- ----------- --------------------------- -------------------- -------------------- ----------- -------------------- -------------------- idx_001 1 1634104862 1 NULL NULL NULL NULL NULL NULL idx_0011 2 1634104862 2 NULL NULL NULL NULL NULL NULL 2：对表进行insert操作，查看统计更新情况插入100条数据 insert into teststat values(100) go 100 检查数据采样情况 name stats_id object_id stats_id last_updated rows rows_sampled steps unfiltered_rows modification_counter ------------------------------ ----------- ----------- ----------- --------------------------- -------------------- -------------------- ----------- -------------------- -------------------- idx_001 1 1634104862 1 NULL NULL NULL NULL NULL NULL idx_0011 2 1634104862 2 NULL NULL NULL NULL NULL NULL 执行dbcc dbreindex(teststat)，可以看到，同时会更新表主键和非聚集索引的统计信息 name stats_id object_id stats_id last_updated rows rows_sampled steps unfiltered_rows modification_counter ------------------------------ ----------- ----------- ----------- --------------------------- -------------------- -------------------- ----------- -------------------- -------------------- idx_001 1 1634104862 1 2022-01-17 16:59:41.8730000 100 100 1 100 0 idx_0011 2 1634104862 2 2022-01-17 16:59:41.8770000 100 100 1 100 0