從 “盲調(diào)” 到 “精準(zhǔn)優(yōu)化”：SQL Server 表統(tǒng)計(jì)信息實(shí)戰(zhàn)指南

本文核心要旨在于：SQL Server 表統(tǒng)計(jì)信息作為元數(shù)據(jù)對象，宛如數(shù)據(jù)分布的 "指南針"，精準(zhǔn)存儲著數(shù)據(jù)分布信息，為查詢優(yōu)化器提供關(guān)鍵依據(jù)，助力其生成高效的查詢執(zhí)行計(jì)劃。在維護(hù)方面，統(tǒng)計(jì)信息更新有手動與自動兩種模式可供選擇。手動更新可通過特定指令精準(zhǔn)把控，自動更新則借助數(shù)據(jù)庫引擎的智能機(jī)制自動完成，而更新頻率需綜合考量數(shù)據(jù)波動性等多方面因素。

在采樣策略上，不同規(guī)模的表有其適配方案。小表數(shù)據(jù)量有限，采用 FULLSCAN 能全面且精準(zhǔn)地收集信息；中表以 50% 的采樣率，在效率與準(zhǔn)確性間尋得平衡；大表至少 25% 的采樣率，可在龐大的數(shù)據(jù)量中高效獲取關(guān)鍵特征。

統(tǒng)計(jì)信息管理也有諸多最佳實(shí)踐，如啟用自動更新以保證信息實(shí)時(shí)性，將統(tǒng)計(jì)信息維護(hù)與索引維護(hù)同步進(jìn)行，提升整體性能。

數(shù)據(jù)庫統(tǒng)計(jì)信息是 SQL Server 達(dá)成最優(yōu)查詢性能的根基。深入理解其工作機(jī)制，正確開展維護(hù)工作并精準(zhǔn)把握更新時(shí)機(jī)，能大幅提升數(shù)據(jù)庫性能。定期維護(hù)統(tǒng)計(jì)信息應(yīng)成為數(shù)據(jù)庫管理員（DBA）日常工作的重要組成，合理的管理能讓優(yōu)化器獲取精準(zhǔn)數(shù)據(jù)，生成高效執(zhí)行計(jì)劃，實(shí)現(xiàn)查詢響應(yīng)加速與資源利用優(yōu)化。

從 "盲調(diào)" 到 "精準(zhǔn)優(yōu)化"：SQL Server 表統(tǒng)計(jì)信息實(shí)戰(zhàn)指南

一、引言

SQL Server 表統(tǒng)計(jì)信息指導(dǎo)優(yōu)化器生成高效的查詢計(jì)劃，數(shù)據(jù)庫管理員（DBA）必須保持其更新，以避免因數(shù)據(jù)過時(shí)導(dǎo)致的性能問題。

二、什么是 SQL Server 表統(tǒng)計(jì)信息？

定義與重要性 在 SQL Server 中，表統(tǒng)計(jì)信息是元數(shù)據(jù)對象，用于存儲表中一列或多列的數(shù)據(jù)分布信息。這些統(tǒng)計(jì)信息對查詢優(yōu)化器至關(guān)重要，優(yōu)化器通過它進(jìn)行基數(shù)估計(jì)，這是生成優(yōu)質(zhì)執(zhí)行計(jì)劃的基礎(chǔ)。例如，當(dāng)查詢過濾列存在數(shù)據(jù)分布傾斜時(shí)，優(yōu)化器可借助統(tǒng)計(jì)信息選擇更高效的訪問方式。保持統(tǒng)計(jì)信息更新是維持查詢性能的核心，底層數(shù)據(jù)大量變化會使統(tǒng)計(jì)信息過時(shí)，導(dǎo)致優(yōu)化器做出錯(cuò)誤基數(shù)估計(jì)，生成低效執(zhí)行計(jì)劃。可通過UPDATE STATISTICS手動更新或啟用AUTO_UPDATE_STATISTICS自動更新功能。
統(tǒng)計(jì)信息包含的核心內(nèi)容 統(tǒng)計(jì)信息是輕量級對象，由頭部信息和數(shù)據(jù)分布直方圖組成，存儲表中的總行數(shù)、平均鍵長度、列間的數(shù)據(jù)分布情況、最常見值和最不常見值的信息等元數(shù)據(jù)。
統(tǒng)計(jì)信息對優(yōu)化器的作用 優(yōu)化器通過統(tǒng)計(jì)信息估算查詢可能返回的行數(shù)、判斷哪些索引可能適用、確定最優(yōu)的連接策略。

三、統(tǒng)計(jì)信息在查詢性能中的關(guān)鍵作用

統(tǒng)計(jì)信息對數(shù)據(jù)庫性能至關(guān)重要，能幫助查詢優(yōu)化器生成高效執(zhí)行計(jì)劃、決定何時(shí)使用特定索引、實(shí)現(xiàn)準(zhǔn)確的基數(shù)估計(jì)、指導(dǎo)連接策略和連接順序的選擇。缺乏準(zhǔn)確統(tǒng)計(jì)信息，查詢優(yōu)化器會生成次優(yōu)執(zhí)行計(jì)劃，導(dǎo)致性能下降。

四、統(tǒng)計(jì)信息如何提升查詢性能？

查詢優(yōu)化器利用統(tǒng)計(jì)信息估算查詢謂詞的選擇性。以SELECT * FROM Customers WHERE Region = 'North' AND AnnualSpend > 50000查詢?yōu)槔?，?code>Region和AnnualSpend列統(tǒng)計(jì)信息準(zhǔn)確，優(yōu)化器可估算返回行數(shù)、選擇合適訪問方式和確定最優(yōu)連接順序。

五、統(tǒng)計(jì)信息采樣及其重要性

統(tǒng)計(jì)信息采樣是 SQL Server 收集數(shù)據(jù)分布信息的方式。對于大型表，采用統(tǒng)計(jì)采樣算法、分析數(shù)據(jù)子集構(gòu)建直方圖，在準(zhǔn)確性和性能影響之間取得平衡。默認(rèn)采樣率由 SQL Server 自動確定，也可手動指定，如UPDATE STATISTICS Sales.Orders WITH SAMPLE 50 PERCENT;。

六、統(tǒng)計(jì)信息維護(hù)：多久更新一次？

更新頻率影響因素 當(dāng)?shù)讓訑?shù)據(jù)發(fā)生顯著變化時(shí)，統(tǒng)計(jì)信息會過時(shí)。更新頻率需根據(jù)數(shù)據(jù)波動性、查詢性能要求和維護(hù)窗口確定。
通用維護(hù)指南 高波動性 OLTP 系統(tǒng)每日更新；中等變化的數(shù)據(jù)倉庫每周更新；執(zhí)行批量操作后和查詢性能突然下降時(shí)也需更新。
自動更新觸發(fā)條件 當(dāng)啟用自動更新統(tǒng)計(jì)信息（默認(rèn)開啟）、約 20% 的行發(fā)生變化（閾值隨表大小調(diào)整）、表基數(shù)從 0 變?yōu)榇笥?0 時(shí)，SQL Server 會自動更新統(tǒng)計(jì)信息。

七、實(shí)踐示例：統(tǒng)計(jì)信息的創(chuàng)建、查詢與分析

步驟 1：創(chuàng)建測試表并插入數(shù)據(jù)
創(chuàng)建測試數(shù)據(jù)庫StatsDemo，創(chuàng)建測試表dbo.OrderData并插入帶有傾斜分布的 100,000 行數(shù)據(jù)。

-- 創(chuàng)建測試數(shù)據(jù)庫
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'StatsDemo')
BEGIN
    CREATE DATABASE StatsDemo;
END
GO

USE StatsDemo;
GO

-- 創(chuàng)建測試表
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
);

-- 插入帶有傾斜分布的測試數(shù)據(jù)（共100,000行）
-- 注意：Region列80%為'East'，Amount列按地區(qū)有不同分布
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()
    );
    
    -- 按地區(qū)生成Amount（不同地區(qū)分布不同）
    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);
    
    -- 插入數(shù)據(jù)
    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()), -- 過去一年的隨機(jī)日期
        @region,
        @category,
        @amount
    );
    
    SET @i = @i + 1;
END

COMMIT;

步驟 2：無統(tǒng)計(jì)信息時(shí)運(yùn)行查詢并分析性能
禁用自動統(tǒng)計(jì)信息并清除現(xiàn)有統(tǒng)計(jì)信息，運(yùn)行查詢并查看執(zhí)行計(jì)劃。

-- 臨時(shí)禁用自動創(chuàng)建和更新統(tǒng)計(jì)信息
ALTER DATABASE StatsDemo SET AUTO_CREATE_STATISTICS OFF;
ALTER DATABASE StatsDemo SET AUTO_UPDATE_STATISTICS OFF;

-- 刪除現(xiàn)有統(tǒng)計(jì)信息（僅刪除自動創(chuàng)建的_WA開頭統(tǒng)計(jì)信息）
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中的實(shí)際執(zhí)行計(jì)劃
-- 開啟IO和時(shí)間統(tǒng)計(jì)
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：創(chuàng)建統(tǒng)計(jì)信息并對比性能
為WHERE子句中的列創(chuàng)建統(tǒng)計(jì)信息，再次運(yùn)行相同查詢，性能提升原因是優(yōu)化器能更準(zhǔn)確估算返回行數(shù)并選擇更優(yōu)執(zhí)行計(jì)劃。

-- 為WHERE子句中的列創(chuàng)建統(tǒng)計(jì)信息（全表掃描收集數(shù)據(jù)）
CREATE STATISTICS Stats_Region ON dbo.OrderData(Region) WITH FULLSCAN;
CREATE STATISTICS Stats_Amount ON dbo.OrderData(Amount) WITH FULLSCAN;

-- 再次運(yùn)行相同查詢
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：查看統(tǒng)計(jì)信息詳情
查看統(tǒng)計(jì)信息的直方圖等詳情和元數(shù)據(jù)，返回信息包括直方圖信息、最后更新時(shí)間、采樣行數(shù)、修改計(jì)數(shù)器等。

-- 查看統(tǒng)計(jì)信息的直方圖等詳情
DBCC SHOW_STATISTICS('dbo.OrderData', 'Stats_Region');
DBCC SHOW_STATISTICS('dbo.OrderData', 'Stats_Amount');

-- 查看統(tǒng)計(jì)信息元數(shù)據(jù)
SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatisticsName,
    s.stats_id,
    s.auto_created, -- 是否自動創(chuàng)建
    s.user_created, -- 是否用戶創(chuàng)建
    s.no_recompute, -- 是否禁止自動更新
    sp.last_updated, -- 最后更新時(shí)間
    sp.rows, -- 表總行數(shù)
    sp.rows_sampled, -- 采樣行數(shù)
    sp.steps, -- 直方圖步數(shù)
    sp.unfiltered_rows, -- 未過濾行數(shù)
    sp.modification_counter -- 上次更新后的修改次數(shù)
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：測試不同采樣率的影響
測試不同采樣率，不同采樣率會導(dǎo)致直方圖詳情不同，FULLSCAN能提供最準(zhǔn)確統(tǒng)計(jì)信息但消耗更多資源。

-- 測試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：模擬數(shù)據(jù)變化并觀察統(tǒng)計(jì)信息過時(shí)影響
模擬數(shù)據(jù)變化，用過時(shí)統(tǒng)計(jì)信息運(yùn)行查詢可能生成次優(yōu)計(jì)劃，更新統(tǒng)計(jì)信息后性能應(yīng)提升。

-- 模擬大量數(shù)據(jù)變化（插入更多'West'地區(qū)的數(shù)據(jù)，改變分布）
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;

-- 查看修改計(jì)數(shù)器和變化比例
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');

-- 用過時(shí)統(tǒng)計(jì)信息運(yùn)行查詢（可能生成次優(yōu)計(jì)劃）
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;

-- 更新統(tǒng)計(jì)信息
UPDATE STATISTICS dbo.OrderData WITH FULLSCAN;

-- 用新統(tǒng)計(jì)信息運(yùn)行查詢（性能應(yīng)提升）
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;

八、統(tǒng)計(jì)信息管理最佳實(shí)踐

啟用自動創(chuàng)建和更新：大多數(shù)環(huán)境下建議開啟。

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

與索引維護(hù)同步：將統(tǒng)計(jì)信息維護(hù)納入常規(guī)數(shù)據(jù)庫維護(hù)。

-- 示例維護(hù)腳本（全表掃描更新）
UPDATE STATISTICS dbo.OrderData WITH FULLSCAN;

針對不同表選擇采樣率
- 小表（<10 萬行）：使用FULLSCAN。
- 中表：50% 采樣率。
- 大表：至少 25% 采樣率。
批量操作后更新

-- 批量插入或數(shù)據(jù)倉庫加載后執(zhí)行
UPDATE STATISTICS SchemaName.TableName;

監(jiān)控統(tǒng)計(jì)信息時(shí)效性

-- 查看指定表的統(tǒng)計(jì)信息更新時(shí)間
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;

高波動性表特殊處理：對修改集中的高波動表，增加更新頻率。

九、總結(jié)

強(qiáng)調(diào)數(shù)據(jù)庫統(tǒng)計(jì)信息對 SQL Server 查詢性能的重要性，呼吁 DBA 定期維護(hù)統(tǒng)計(jì)信息，以實(shí)現(xiàn)更快查詢響應(yīng)和更優(yōu)資源利用率。

十、擴(kuò)展鏈接

Wyn 商業(yè)智能 ------ 選擇 Microsoft SQL Server 作為主數(shù)據(jù)庫

posted @ 2025-09-22 17:09 葡萄城技術(shù)團(tuán)隊(duì) 閱讀(335) 評論(0) 收藏舉報(bào)

刷新頁面返回頂部

從 “盲調(diào)” 到 “精準(zhǔn)優(yōu)化”：SQL Server 表統(tǒng)計(jì)信息實(shí)戰(zhàn)指南

從 "盲調(diào)" 到 "精準(zhǔn)優(yōu)化"：SQL Server 表統(tǒng)計(jì)信息實(shí)戰(zhàn)指南

一、引言

二、什么是 SQL Server 表統(tǒng)計(jì)信息？

三、統(tǒng)計(jì)信息在查詢性能中的關(guān)鍵作用

四、統(tǒng)計(jì)信息如何提升查詢性能？

五、統(tǒng)計(jì)信息采樣及其重要性

六、統(tǒng)計(jì)信息維護(hù)：多久更新一次？

七、實(shí)踐示例：統(tǒng)計(jì)信息的創(chuàng)建、查詢與分析