从 “盲调” 到 “精准优化”：SQL Server 表统计信息实战指南

本文核心要旨在于：SQL Server 表统计信息作为元数据对象，宛如数据分布的 "指南针"，精准存储着数据分布信息，为查询优化器提供关键依据，助力其生成高效的查询执行计划。在维护方面，统计信息更新有手动与自动两种模式可供选择。手动更新可通过特定指令精准把控，自动更新则借助数据库引擎的智能机制自动完成，而更新频率需综合考量数据波动性等多方面因素。

在采样策略上，不同规模的表有其适配方案。小表数据量有限，采用 FULLSCAN 能全面且精准地收集信息；中表以 50% 的采样率，在效率与准确性间寻得平衡；大表至少 25% 的采样率，可在庞大的数据量中高效获取关键特征。

统计信息管理也有诸多最佳实践，如启用自动更新以保证信息实时性，将统计信息维护与索引维护同步进行，提升整体性能。

数据库统计信息是 SQL Server 达成最优查询性能的根基。深入理解其工作机制，正确开展维护工作并精准把握更新时机，能大幅提升数据库性能。定期维护统计信息应成为数据库管理员（DBA）日常工作的重要组成，合理的管理能让优化器获取精准数据，生成高效执行计划，实现查询响应加速与资源利用优化。

从 "盲调" 到 "精准优化"：SQL Server 表统计信息实战指南

一、引言

SQL Server 表统计信息指导优化器生成高效的查询计划，数据库管理员（DBA）必须保持其更新，以避免因数据过时导致的性能问题。

二、什么是 SQL Server 表统计信息？

1. 定义与重要性 在 SQL Server 中，表统计信息是元数据对象，用于存储表中一列或多列的数据分布信息。这些统计信息对查询优化器至关重要，优化器通过它进行基数估计，这是生成优质执行计划的基础。例如，当查询过滤列存在数据分布倾斜时，优化器可借助统计信息选择更高效的访问方式。保持统计信息更新是维持查询性能的核心，底层数据大量变化会使统计信息过时，导致优化器做出错误基数估计，生成低效执行计划。可通过UPDATE STATISTICS手动更新或启用AUTO_UPDATE_STATISTICS自动更新功能。
2. 统计信息包含的核心内容 统计信息是轻量级对象，由头部信息和数据分布直方图组成，存储表中的总行数、平均键长度、列间的数据分布情况、最常见值和最不常见值的信息等元数据。
3. 统计信息对优化器的作用 优化器通过统计信息估算查询可能返回的行数、判断哪些索引可能适用、确定最优的连接策略。

三、统计信息在查询性能中的关键作用

统计信息对数据库性能至关重要，能帮助查询优化器生成高效执行计划、决定何时使用特定索引、实现准确的基数估计、指导连接策略和连接顺序的选择。缺乏准确统计信息，查询优化器会生成次优执行计划，导致性能下降。

四、统计信息如何提升查询性能？

查询优化器利用统计信息估算查询谓词的选择性。以SELECT * FROM Customers WHERE Region = 'North' AND AnnualSpend > 50000查询为例，若Region和AnnualSpend列统计信息准确，优化器可估算返回行数、选择合适访问方式和确定最优连接顺序。

五、统计信息采样及其重要性

统计信息采样是 SQL Server 收集数据分布信息的方式。对于大型表，采用统计采样算法、分析数据子集构建直方图，在准确性和性能影响之间取得平衡。默认采样率由 SQL Server 自动确定，也可手动指定，如UPDATE STATISTICS Sales.Orders WITH SAMPLE 50 PERCENT;。

六、统计信息维护：多久更新一次？

1. 更新频率影响因素 当底层数据发生显著变化时，统计信息会过时。更新频率需根据数据波动性、查询性能要求和维护窗口确定。
2. 通用维护指南 高波动性 OLTP 系统每日更新；中等变化的数据仓库每周更新；执行批量操作后和查询性能突然下降时也需更新。
3. 自动更新触发条件 当启用自动更新统计信息（默认开启）、约 20% 的行发生变化（阈值随表大小调整）、表基数从 0 变为大于 0 时，SQL Server 会自动更新统计信息。

七、实践示例：统计信息的创建、查询与分析

步骤 1：创建测试表并插入数据
创建测试数据库StatsDemo，创建测试表dbo.OrderData并插入带有倾斜分布的 100,000 行数据。

/* by yours.tools - online tools website : yours.tools/zh/generatematicwallets.html */
-- 创建测试数据库
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'StatsDemo')
BEGIN
    CREATE DATABASE StatsDemo;
END
GO

USE StatsDemo;
GO

-- 创建测试表
IF OBJECT_ID('dbo.OrderData', 'U') IS NOT NULL
    DROP TABLE dbo.OrderData;

CREATE TABLE dbo.OrderData
(
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    Region VARCHAR(20) NOT NULL,
    ProductCategory VARCHAR(50) NOT NULL,
    Amount DECIMAL(12,2) NOT NULL
);

-- 插入带有倾斜分布的测试数据（共100,000行）
-- 注意：Region列80%为'East'，Amount列按地区有不同分布
DECLARE @i INT = 1;
DECLARE @regions TABLE (Region VARCHAR(20), Probability DECIMAL(5,2));
INSERT INTO @regions VALUES ('East', 0.80), ('West', 0.10), ('North', 0.05), ('South', 0.05);

DECLARE @categories TABLE (Category VARCHAR(50), Probability DECIMAL(5,2));
INSERT INTO @categories VALUES 
    ('Electronics', 0.30), 
    ('Clothing', 0.25), 
    ('Home Goods', 0.20),
    ('Groceries', 0.15),
    ('Sporting Goods', 0.10);

BEGIN TRANSACTION;

WHILE @i <= 100000
BEGIN
    -- 按概率生成Region
    DECLARE @region VARCHAR(20) = (
        SELECT TOP 1 Region 
        FROM @regions 
        ORDER BY CASE WHEN RAND() <= Probability THEN 0 ELSE 1 END, NEWID()
    );
    
    -- 按概率生成ProductCategory
    DECLARE @category VARCHAR(50) = (
        SELECT TOP 1 Category 
        FROM @categories 
        ORDER BY CASE WHEN RAND() <= Probability THEN 0 ELSE 1 END, NEWID()
    );
    
    -- 按地区生成Amount（不同地区分布不同）
    DECLARE @amount DECIMAL(12,2);
    IF @region = 'East' SET @amount = 500 + (RAND() * 500);
    ELSE IF @region = 'West' SET @amount = 1000 + (RAND() * 1000);
    ELSE IF @region = 'North' SET @amount = 750 + (RAND() * 250);
    ELSE SET @amount = 250 + (RAND() * 750);
    
    -- 插入数据
    INSERT INTO dbo.OrderData (CustomerID, OrderDate, Region, ProductCategory, Amount)
    VALUES (
        CAST((RAND() * 1000) AS INT), -- CustomerID（1-1000）
        DATEADD(DAY, -CAST((RAND() * 365) AS INT), GETDATE()), -- 过去一年的随机日期
        @region,
        @category,
        @amount
    );
    
    SET @i = @i + 1;
END

COMMIT;

步骤 2：无统计信息时运行查询并分析性能
禁用自动统计信息并清除现有统计信息，运行查询并查看执行计划。

/* by yours.tools - online tools website : yours.tools/zh/generatematicwallets.html */
-- 临时禁用自动创建和更新统计信息
ALTER DATABASE StatsDemo SET AUTO_CREATE_STATISTICS OFF;
ALTER DATABASE StatsDemo SET AUTO_UPDATE_STATISTICS OFF;

-- 删除现有统计信息（仅删除自动创建的_WA开头统计信息）
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + 'DROP STATISTICS dbo.OrderData.' + name + ';'
FROM sys.stats
WHERE object_id = OBJECT_ID('dbo.OrderData')
AND name LIKE '_WA%';

EXEC sp_executesql @sql;

-- 启用SSMS中的实际执行计划
-- 开启IO和时间统计
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM dbo.OrderData
WHERE Region = 'West' AND Amount > 1500;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

步骤 3：创建统计信息并对比性能
为WHERE子句中的列创建统计信息，再次运行相同查询，性能提升原因是优化器能更准确估算返回行数并选择更优执行计划。

-- 为WHERE子句中的列创建统计信息（全表扫描收集数据）
CREATE STATISTICS Stats_Region ON dbo.OrderData(Region) WITH FULLSCAN;
CREATE STATISTICS Stats_Amount ON dbo.OrderData(Amount) WITH FULLSCAN;

-- 再次运行相同查询
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM dbo.OrderData
WHERE Region = 'West' AND Amount > 1500;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

步骤 4：查看统计信息详情
查看统计信息的直方图等详情和元数据，返回信息包括直方图信息、最后更新时间、采样行数、修改计数器等。

-- 查看统计信息的直方图等详情
DBCC SHOW_STATISTICS('dbo.OrderData', 'Stats_Region');
DBCC SHOW_STATISTICS('dbo.OrderData', 'Stats_Amount');

-- 查看统计信息元数据
SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatisticsName,
    s.stats_id,
    s.auto_created, -- 是否自动创建
    s.user_created, -- 是否用户创建
    s.no_recompute, -- 是否禁止自动更新
    sp.last_updated, -- 最后更新时间
    sp.rows, -- 表总行数
    sp.rows_sampled, -- 采样行数
    sp.steps, -- 直方图步数
    sp.unfiltered_rows, -- 未过滤行数
    sp.modification_counter -- 上次更新后的修改次数
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.OrderData');

步骤 5：测试不同采样率的影响
测试不同采样率，不同采样率会导致直方图详情不同，FULLSCAN能提供最准确统计信息但消耗更多资源。

-- 测试10%采样率
UPDATE STATISTICS dbo.OrderData Stats_Region WITH SAMPLE 10 PERCENT;
DBCC SHOW_STATISTICS('dbo.OrderData', 'Stats_Region');

-- 测试全表扫描采样
UPDATE STATISTICS dbo.OrderData Stats_Region WITH FULLSCAN;
DBCC SHOW_STATISTICS('dbo.OrderData', 'Stats_Region');

步骤 6：模拟数据变化并观察统计信息过时影响
模拟数据变化，用过时统计信息运行查询可能生成次优计划，更新统计信息后性能应提升。

-- 模拟大量数据变化（插入更多'West'地区的数据，改变分布）
INSERT INTO dbo.OrderData (CustomerID, OrderDate, Region, ProductCategory, Amount)
SELECT
    CAST((RAND() * 1000) AS INT),
    DATEADD(DAY, -CAST((RAND() * 365) AS INT), GETDATE()),
    'West',
    'Electronics',
    1500 + (RAND() * 1000)
FROM sys.objects
CROSS JOIN sys.columns
WHERE object_id % 1000 = 0;

-- 查看修改计数器和变化比例
SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatisticsName,
    sp.modification_counter,
    sp.rows,
    CAST(sp.modification_counter * 100.0 / sp.rows AS DECIMAL(5,2)) AS PercentChanged
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.OrderData');

-- 用过时统计信息运行查询（可能生成次优计划）
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM dbo.OrderData
WHERE Region = 'West' AND Amount > 1500;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- 更新统计信息
UPDATE STATISTICS dbo.OrderData WITH FULLSCAN;

-- 用新统计信息运行查询（性能应提升）
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM dbo.OrderData
WHERE Region = 'West' AND Amount > 1500;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

八、统计信息管理最佳实践

1. 启用自动创建和更新：大多数环境下建议开启。

ALTER DATABASE StatsDemo SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE StatsDemo SET AUTO_UPDATE_STATISTICS ON;

1. 与索引维护同步：将统计信息维护纳入常规数据库维护。

-- 示例维护脚本（全表扫描更新）
UPDATE STATISTICS dbo.OrderData WITH FULLSCAN;

1. 针对不同表选择采样率
   • 小表（<10 万行）：使用FULLSCAN。
   • 中表：50% 采样率。
   • 大表：至少 25% 采样率。
2. 批量操作后更新

-- 批量插入或数据仓库加载后执行
UPDATE STATISTICS SchemaName.TableName;

监控统计信息时效性

-- 查看指定表的统计信息更新时间
SELECT 
    OBJECT_NAME(object_id) AS TableName, 
    name AS StatsName, 
    STATS_DATE(object_id, stats_id) AS LastUpdated
FROM sys.stats
WHERE OBJECT_NAME(object_id) = 'YourTableName'
ORDER BY LastUpdated;

高波动性表特殊处理：对修改集中的高波动表，增加更新频率。

九、总结

强调数据库统计信息对 SQL Server 查询性能的重要性，呼吁 DBA 定期维护统计信息，以实现更快查询响应和更优资源利用率。

十、扩展链接

Wyn 商业智能 ------ 选择 Microsoft SQL Server 作为主数据库

---

本文是由葡萄城技术开发团队发布，转载请注明出处：葡萄城官网