MSSQL统计数据知识点备忘

统计数据

  1. 使用dbcc show_statistics('数据库名', 统计数据名)查看特定统计数据的详细信息
  2. 重点观察统计数据的histogram部分信息，此处决定估算的时候的行数和内存
  3. 查询的时候需要分配内存，如果统计数据的估算行数和实际的行数相差过大则会导致空间浪费或者将数据溢出到tempdb中（溢出行为非常影响性能，可以通过查询计划当中的感叹号部分分析）
  4. 建议开启自动数据更新，如果2016及以上版本建议开启动态的更新（增加更新频率）
  5. 建议使用异步统计数据的更新，如果是同步的，则会导致当前的查询被阻塞，反之则使用过期的统计数据查询，统计数据后台异步更新
  6. 索引的统计数据只包含最左边列的统计数据，所以如果查询的时候使用了非最左侧的列，则可能会自动创建列级别的统计数据（自动创建的统计数据通常以_WA开头，尽量改名，方便维护），然后根据列级别的统计数据来生成查询计划（注意这种情况仅出现在其他索引包含了搜索列的时候）
  7. 统计数据对于没有在直方图当中出现的键值会极低的估算行数，从而导致选择非常差的查询计划（比如出现大量的key lookup操作）
  8. 开启数据库的T2371追踪标记（对于大表默认的更新策略非常保守，导致会出现很差的查询计划）
  9. 索引会自动创建统计信息
  10. 需要手动删除非必要的统计信息
  11. 直方图当中最多有200个桶

常用的sql

  1. 使用
1
2
3
4
5
6
SELECT 
    name,
    is_auto_create_stats_on,
    is_auto_update_stats_async_on
FROM sys.databases
WHERE name = 'Test';

查看数据库是否开启了自动更新和异步更新统计数据

  1. 使用
1
2
3
4
5
ALTER DATABASE Test
SET AUTO_CREATE_STATISTICS ON;

ALTER DATABASE Test
SET AUTO_UPDATE_STATISTICS_ASYNC ON;

开启1所示的功能
3. 使用

1
2
3
4
5
6
7
8
9
10
11
12
select 
    s.stats_id as [Stat ID], sc.name + '.' + t.name as [Table], s.name as [Statistics] 
    ,p.last_updated, p.rows, p.rows_sampled, p.modification_counter as [Mod Count] 
from 
    sys.stats s join sys.tables t on 
        s.object_id = t.object_id 
    join sys.schemas sc on 
        t.schema_id = sc.schema_id 
    outer apply 
        sys.dm_db_stats_properties(t.object_id,s.stats_id) p 
where 
    sc.name = 'dbo' and t.name = 'Books';

来分析从上一次统计数据更新到现在为止有多少次数据修改，如果过多可以手动触发统计数据的更新，查看mod count列