通过 sys.schema_table_statistics 视图诊断表的统计信息问题
大家好，今天我们来探讨如何利用 sys.schema_table_statistics 视图诊断 SQL Server 中表的统计信息问题。统计信息对于查询优化器至关重要，它可以帮助优化器选择最佳的查询执行计划。 不准确或过时的统计信息可能导致查询性能下降，甚至导致查询超时。
1. 理解 sys.schema_table_statistics 视图
sys.schema_table_statistics 是一个系统视图，它包含数据库中所有表的统计信息对象的信息。 我们可以通过查询这个视图来了解统计信息的创建时间、修改时间、行数等关键信息。
1.1 视图结构
该视图包含以下主要列：
| 列名 | 数据类型 | 描述 |
|---|---|---|
object_id |
int |
表或索引视图的 ID。 |
name |
sysname |
统计信息对象的名称。 |
stats_id |
int |
统计信息对象的 ID。 |
auto_created |
bit |
指示统计信息对象是否由数据库引擎自动创建。 |
user_created |
bit |
指示统计信息对象是否由用户显式创建。 |
no_recompute |
bit |
指示统计信息对象是否禁用自动重新计算。 |
is_incremental |
bit |
指示统计信息是否为增量统计信息。仅适用于 SQL Server 2014 (12.x) 及更高版本。 |
is_filtered |
bit |
指示统计信息是否基于筛选的谓词。 |
filter_definition |
nvarchar(max) |
筛选谓词的定义。仅当 is_filtered 为 1 时才适用。 |
has_filter |
bit |
指示统计信息是否具有筛选器。 |
persisted_sample_percent |
tinyint |
创建统计信息时使用的采样百分比。 |
modification_counter |
int |
从上次更新统计信息以来，表中已修改的行数。 |
sampling_percent |
real |
上次更新统计信息时使用的采样百分比。 |
last_updated |
datetime |
上次更新统计信息的时间。 |
rows |
bigint |
表中的总行数。 |
rows_sampled |
bigint |
上次更新统计信息时采样的行数。 |
unfiltered_rows |
bigint |
表中满足筛选器谓词的行数。仅当 is_filtered 为 1 时才适用。 |
partitions_sampled |
bigint |
上次更新统计信息时采样的分区数。 |
filtered_rows |
bigint |
表中满足筛选器谓词的行数。仅当 is_filtered 为 1 时才适用。 |
1.2 简单查询示例
查询数据库中所有表的统计信息：
SELECT
OBJECT_NAME(st.object_id) AS TableName,
st.name AS StatisticsName,
st.stats_id,
st.auto_created,
st.user_created,
st.no_recompute,
st.last_updated,
st.rows,
st.rows_sampled,
st.modification_counter,
st.sampling_percent
FROM
sys.schema_table_statistics AS st
ORDER BY
TableName,
StatisticsName;2. 诊断统计信息问题的关键指标
以下是一些关键指标，可以帮助您诊断表的统计信息问题：
2.1 modification_counter (修改计数器)
modification_counter 表示自上次更新统计信息以来，表中已修改的行数。 这是一个非常重要的指标，因为它反映了统计信息的陈旧程度。
问题： 如果 modification_counter 远大于表中的总行数，或者远大于上次更新统计信息时采样的行数，则统计信息可能已过时，需要更新。
诊断和解决：
-
检查
modification_counter与rows的比例： 如果modification_counter超过rows的 10-20%，通常建议更新统计信息。这个百分比只是一个经验法则，具体取决于表的访问模式和查询的敏感度。 -
检查
last_updated： 如果last_updated时间很久以前，即使modification_counter相对较小，也可能需要考虑更新统计信息，特别是对于经常更新的表。 -
手动更新统计信息： 使用
UPDATE STATISTICS命令更新统计信息。 可以选择全表扫描或采样更新。-- 全表扫描更新统计信息 UPDATE STATISTICS dbo.YourTable WITH FULLSCAN; -- 采样更新统计信息 (例如，采样 30%) UPDATE STATISTICS dbo.YourTable WITH SAMPLE 30 PERCENT; -- 更新特定统计信息对象 UPDATE STATISTICS dbo.YourTable YourStatisticsName WITH FULLSCAN; -
考虑自动更新统计信息： 确保数据库的
AUTO_UPDATE_STATISTICS选项已启用。 这允许 SQL Server 在检测到大量数据修改时自动更新统计信息。-- 检查数据库的 AUTO_UPDATE_STATISTICS 设置 SELECT name, is_auto_update_stats_on FROM sys.databases WHERE name = DB_NAME(); -- 启用数据库的 AUTO_UPDATE_STATISTICS 设置 ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS ON;
示例：
假设我们有一个名为 Orders 的表，并且我们执行以下查询：
SELECT
OBJECT_NAME(st.object_id) AS TableName,
st.name AS StatisticsName,
st.last_updated,
st.rows,
st.modification_counter
FROM
sys.schema_table_statistics AS st
WHERE
OBJECT_NAME(st.object_id) = 'Orders';查询结果可能如下所示：
| TableName | StatisticsName | last_updated | rows | modification_counter |
|---|---|---|---|---|
| Orders | _WA_Sys_0… | 2023-10-27 10:00:00.000 | 100000 | 50000 |
在这个例子中，modification_counter 为 50000，这意味着自上次更新统计信息以来，Orders 表中已修改了 50000 行。 这相当于表总行数的 50%，因此强烈建议更新 Orders 表的统计信息。
2.2 rows (行数) 和 rows_sampled (采样行数)
rows 表示表中的总行数，rows_sampled 表示上次更新统计信息时采样的行数。
问题： 如果 rows_sampled 远小于 rows，则统计信息可能不具有代表性，特别是对于数据分布不均匀的表。
诊断和解决：
- 比较
rows和rows_sampled： 如果rows_sampled太小，考虑使用FULLSCAN选项更新统计信息，以确保统计信息能够反映整个表的数据分布。 -
检查
sampling_percent：sampling_percent列显示了上次更新统计信息时使用的采样百分比。 如果采样百分比太低，可以手动指定更高的采样百分比。-- 使用 FULLSCAN 更新统计信息 UPDATE STATISTICS dbo.YourTable WITH FULLSCAN; -- 指定采样百分比更新统计信息 UPDATE STATISTICS dbo.YourTable WITH SAMPLE 50 PERCENT;
示例：
假设我们查询 Products 表的统计信息，得到以下结果：
| TableName | StatisticsName | rows | rows_sampled | sampling_percent |
|---|---|---|---|---|
| Products | _WA_Sys_0… | 1000000 | 10000 | 1 |
在这个例子中，Products 表有 100 万行，但只采样了 1 万行，采样百分比仅为 1%。 这意味着统计信息可能无法准确反映整个表的数据分布，特别是如果 Products 表的数据分布不均匀。 因此，建议使用 FULLSCAN 选项更新 Products 表的统计信息。
2.3 auto_created 和 user_created
auto_created 指示统计信息是否由数据库引擎自动创建，user_created 指示统计信息是否由用户显式创建。
问题： 过多的自动创建的统计信息可能会占用系统资源，并且可能不是最佳的。 缺少用户创建的统计信息可能会导致查询优化器错过重要的优化机会。
诊断和解决：
-
检查自动创建的统计信息： 定期检查是否存在过多的自动创建的统计信息。 如果发现某些自动创建的统计信息很少被使用，可以考虑删除它们。
-
创建自定义统计信息： 对于经常在查询中使用的列，特别是那些数据分布不均匀的列，建议手动创建统计信息。
-- 创建单列统计信息 CREATE STATISTICS YourStatisticsName ON dbo.YourTable (YourColumn); -- 创建多列统计信息 CREATE STATISTICS YourStatisticsName ON dbo.YourTable (Column1, Column2); -
审查统计信息策略： 根据应用程序的需求，制定合适的统计信息维护策略。 这可能包括定期更新统计信息、删除不必要的统计信息、以及创建自定义统计信息。
2.4 no_recompute
no_recompute 指示统计信息对象是否禁用自动重新计算。
问题： 如果 no_recompute 设置为 1，则即使表中的数据发生重大更改，统计信息也不会自动更新。 这可能会导致查询优化器使用过时的统计信息，从而导致查询性能下降。
诊断和解决：
-
确保
no_recompute设置为 0： 除非有特殊原因，否则应该确保no_recompute设置为 0，以便允许统计信息自动更新。-- 检查统计信息的 NORECOMPUTE 设置 DBCC SHOW_STATISTICS("YourTable", "YourStatisticsName") WITH HEADER; -- 启用统计信息的自动重新计算 UPDATE STATISTICS dbo.YourTable YourStatisticsName WITH NORECOMPUTE = OFF;
2.5 is_filtered 和 filter_definition
is_filtered 指示统计信息是否基于筛选的谓词，filter_definition 包含筛选谓词的定义。
问题： 如果查询使用与筛选统计信息不同的筛选谓词，则查询优化器可能无法使用筛选统计信息，从而导致查询性能下降。
诊断和解决：
-
检查筛选统计信息的谓词： 确保筛选统计信息的谓词与查询中使用的筛选谓词匹配。 如果不匹配，可以创建新的筛选统计信息，或者修改现有的筛选统计信息。
-- 创建筛选统计信息 CREATE STATISTICS YourStatisticsName ON dbo.YourTable (YourColumn) WHERE YourColumn > 100; -- 更新筛选统计信息 UPDATE STATISTICS dbo.YourTable YourStatisticsName WITH FULLSCAN; -
考虑使用非筛选统计信息： 如果查询的筛选谓词经常变化，可以考虑使用非筛选统计信息，或者创建多个筛选统计信息以覆盖不同的筛选谓词。
3. 常见的统计信息问题及其解决方案
| 问题 | 可能原因 | 解决方案 |
|---|---|---|
| 查询性能下降 | 统计信息过时、统计信息不准确、缺少统计信息、统计信息被禁用自动更新 | 更新统计信息、使用 FULLSCAN 更新统计信息、创建自定义统计信息、启用统计信息的自动更新、检查筛选统计信息的谓词 |
| 查询优化器选择错误的执行计划 | 统计信息过时、统计信息不准确、缺少统计信息、统计信息与查询不匹配 | 更新统计信息、使用 FULLSCAN 更新统计信息、创建自定义统计信息、检查筛选统计信息的谓词、使用查询提示强制查询优化器使用特定的执行计划 (慎用) |
| 统计信息维护成本高 | 过多的自动创建的统计信息、不必要的统计信息、过高的采样百分比 | 删除不必要的统计信息、调整采样百分比、禁用不必要的自动创建统计信息 |
| 统计信息与实际数据分布不符 | 数据分布不均匀、统计信息未及时更新、采样行数太少 | 使用 FULLSCAN 更新统计信息、增加采样行数、定期更新统计信息、考虑使用直方图来捕获数据分布 |
| 大量数据修改后查询性能没有明显提升 | 统计信息自动更新未及时触发、统计信息更新频率过低、自动更新统计信息的阈值设置不合理 | 调整自动更新统计信息的阈值（谨慎操作，可能影响系统性能）、手动更新统计信息、增加统计信息更新频率 |
4. 使用Extended Events 监控统计信息更新
可以使用Extended Events来监控统计信息的更新操作，以便更好地了解统计信息的维护情况。
-- 创建 Extended Event 会话
CREATE EVENT SESSION [StatisticsMonitoring] ON SERVER
ADD EVENT sqlserver.auto_stats(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.object_id,sqlserver.object_name))
ADD TARGET package0.event_file(FILENAME=N'C:StatisticsMonitoring.xel',MAX_FILE_SIZE=(50),MAX_ROLLING_FILES=(4))
WITH (STARTUP_STATE=OFF)
GO
-- 启动 Extended Event 会话
ALTER EVENT SESSION [StatisticsMonitoring] ON SERVER STATE = START;
-- 停止 Extended Event 会话
ALTER EVENT SESSION [StatisticsMonitoring] ON SERVER STATE = STOP;
-- 删除 Extended Event 会话
DROP EVENT SESSION [StatisticsMonitoring] ON SERVER;这个Extended Event 会话会捕获sqlserver.auto_stats事件，该事件在统计信息自动更新时触发。 通过分析Extended Event 的输出，可以了解统计信息自动更新的时间、对象和数据库等信息。
5. 总结
sys.schema_table_statistics 视图是诊断 SQL Server 中表统计信息问题的强大工具。 通过分析 modification_counter、rows、rows_sampled 等关键指标，可以识别过时、不准确或缺失的统计信息，并采取相应的措施来解决这些问题。 确保统计信息是最新的，并能反映表的数据分布，对于优化查询性能至关重要。 合理的统计信息维护策略是确保数据库高效运行的关键因素之一。