SQL Server查詢優化器對於執行計劃成本的評估是基於資料庫統計資訊的。所以，資料庫統計資訊直接影響到資料庫查詢效率，是資料庫系統快速響應，低延遲特性的幕後英雄，但是我們又經常忽視資料庫統計資訊的存在和維護，懷著為英雄正名和喚醒大家對幕後英雄尊重的目的寫作這篇文章。

什麼是統計資訊

SQL Server查詢優化器使用統計資訊來評估表或索引檢視的一個或多個列中值的分佈，這個分佈資訊提供了用於建立高質量的執行計劃的基礎（稱為基數）。更為通俗一點說，SQL Server的執行計劃是基於統計資訊來評估的，優化器最終會選擇最優的執行計劃來為資料庫系統提供資料存取功能。這位躲在幕後的英雄便是統計資訊。

統計資訊的作用

在關係型資料庫系統（RDBMS）中，統計資訊非常重要，當然MSSQL Server也不例外，它的準確與否直接影響到執行計劃的優劣，資料庫系統查詢效率是否高效。具體表現在以下幾個方面：
查詢優化器需要藉助統計資訊來判斷是否使用索引。
查詢優化器需要根據統計資訊來判斷是使用巢狀迴圈連線，合併連線還是雜湊連線。
查詢優化器根據表統計資訊來找出最佳的執行順序。

統計資訊包含的內容

前面章節我們講的都是比較枯燥的理論知識，這一小節我們來具體揭露幕後英雄的廬山真面目，它包含了哪些內容。
查詢SQL Server統計資訊非常簡單，只需要使用DBCC命令傳入表名字和統計資訊名稱即可，DBCC SHOW_STATISTICS(`Table_Name`,`Statistics_name`)。我們以AdventureWorks2008R2資料庫下表Sales.SalesOrderDetail中統計資訊AK_SalesOrderDetail_rowguid為例：

USE AdventureWorks2008R2
GO
--DBCC SHOW_STATISTICS(`Table_Name`,`Statistics_name`)
DBCC SHOW_STATISTICS(`Sales.SalesOrderDetail`,`AK_SalesOrderDetail_rowguid`)

揭曉幕後英雄的真實面貌如下：

檢視統計資訊的設定

關於統計資訊設定，有四個重要的選項。
Auto Create Statistics：SQL Server是否自動建立統計資訊，預設開啟。
Auto Update Statistics：SQL Server是否自動更新統計資訊，預設開啟。
Auto Update Statistics Asynchronously：SQL Server是否採用非同步方式更新統計資訊，預設關閉。
Auto Create Incremental Statistics：SQL Server是否自動建立增量統計資訊，這個選項是SQL Server 2014以來新增選項，預設關閉。
檢驗模版資料庫Model統計資訊設定，新增資料庫會以這個資料庫為模版。

SELECT
    database_name = name
    ,[IsAutoCreateStatistics?] = 
        CASE is_auto_create_stats_on
            WHEN 1 THEN `Yes`
            ELSE `No`
        END
    ,[IsAutoUpdateStatistics?] = 
        CASE is_auto_update_stats_on
            WHEN 1 THEN `Yes`
            ELSE `No`
        END
    ,[IsAutoUpdateStatsaAyncOn?] = 
        CASE is_auto_update_stats_async_on
            WHEN 1 THEN `Yes`
            ELSE `No`
        END
    ,[IsAutoCreateStatisticsIncremental?] = 
        CASE is_auto_create_stats_incremental_on
            WHEN 1 THEN `Yes`
            ELSE `No`
        END
FROM sys.databases
WHERE   name = `model`

結果展示如下：

我們以AdventureWorks2008R2為例來觀察SQL Server資料庫關於統計資訊的設定。除了上面使用的檢查方法以外，我們還可以使用下面的方法。

SELECT  
    [IsAutoCreateStatistics?] = 
    CASE 
        WHEN DATABASEPROPERTYEX(`AdventureWorks2008R2`, `IsAutoCreateStatistics`) = 1  
            THEN `Yes`  
            ELSE `No`  
    END,  
    [IsAutoUpdateStatistics?] = 
    CASE
        WHEN DATABASEPROPERTYEX(`AdventureWorks2008R2`, `IsAutoUpdateStatistics`) = 1  
            THEN `Yes`  
            ELSE `No`  
    END,  
    [IsAutoUpdateStatsaAyncOn?] = 
    CASE 
        WHEN DATABASEPROPERTYEX(`AdventureWorks2008R2`, `Is_Auto_Update_stats_async_on`) = 1  
            THEN `Yes`  
            ELSE `No`  
    END
    ,[IsAutoCreateStatisticsIncremental?] = 
    CASE 
        WHEN DATABASEPROPERTYEX(`AdventureWorks2008R2`, `IsAutoCreateStatisticsIncremental`) = 1  
            THEN `Yes`  
            ELSE `No`  
    END    
GO

結果顯示與Model資料庫保持一致，如下：

當然我們也可以使用SSMS GUI方式檢視：Right Click On Database => Properties => Options

統計資訊對查詢的影響

為了看清楚SQL Server統計資訊是如何影響查詢的，我們在AdventureWorks2008R2庫下建立一個測試表dbo.tb_TestStats，並向測試表中插入10000條資料。

USE AdventureWorks2008R2
GO
IF OBJECT_ID(`dbo.tb_TestStats`, `U`) IS NOT NULL
BEGIN
    TRUNCATE TABLE dbo.tb_TestStats
    DROP TABLE dbo.tb_TestStats
END
GO

CREATE TABLE dbo.tb_TestStats
(
    RowID INT IDENTITY(1,1) NOT NULL
    ,refID INT NOT NULL
    ,anotherID INT NOT NULL
    ,CONSTRAINT PK_tb_TestStats PRIMARY KEY
    (RowID)
);

USE AdventureWorks2008R2
GO

SET NOCOUNT ON
DECLARE
    @do int = 0
    ,@loop int = 10000
;
WHILE @do < @loop
BEGIN
    IF @do < 100
        INSERT INTO dbo.tb_TestStats(refID,anotherID) VALUES(@do, @do);
    ELSE
        INSERT INTO dbo.tb_TestStats(refID,anotherID) VALUES(200, 200);

    SET @do = @do + 1;
END;

無統計資訊的執行計劃

為了防止統計資訊在執行計劃評估階段自動建立造成對我們測試的影響，手動關閉Auto Create Statistics選項。

USE master
GO

ALTER DATABASE AdventureWorks2008R2 
SET AUTO_CREATE_STATISTICS OFF;
GO

接下在SSMS中選擇顯示實際的實行計劃，然後執行下面的查詢語句。

USE AdventureWorks2008R2
GO
SELECT *
FROM dbo.tb_TestStats WITH(NOLOCK)
WHERE anotherID = 100;

從實際的執行計劃來看，實際滿足條件的記錄數沒有，即Actual Numbers of Rows為0，而預估滿足條件的記錄數Estimated Numbers of Rows為1000條，差異巨大，並且存在統計資訊缺失的警告。這個巨大的差異足以導致SQL Server優化器對執行計劃評估不準確，從而選擇了次優的執行計劃，最終影響資料庫查詢效率。

有統計資訊的執行計劃

無統計資訊的執行計劃是從反面看統計資訊對執行計劃的影響，現在我們從正面看有統計資訊對執行計劃的影響。當我們手動建立統計資訊以後，再看看實際的執行計劃。

USE AdventureWorks2008R2
GO
CREATE STATISTICS st_anotherID ON dbo.tb_TestStats(anotherID)
GO

再次執行查詢，這裡需要特別注意，為了防止執行計劃快取對測試結果的影響，在執行查詢語句前，我們需要清空執行計劃快取，執行查詢語句後，我們將Auto Create Statistics設定恢復為預設值。

DBCC FREEPROCCACHE
GO

USE AdventureWorks2008R2
GO
SELECT *
FROM dbo.tb_TestStats WITH(NOLOCK)
WHERE anotherID = 100;

USE master
GO
--change back to auto create statistics 
ALTER DATABASE AdventureWorks2008R2 
SET AUTO_CREATE_STATISTICS ON;
GO

最後檢視實際執行計劃，統計資訊缺失的警告消失了，預估滿足條件的行數Estimated Numbers of Rows為1行和實際滿足條件的行數Actual Numbers of Rows為0行，已經非常接近了。說明統計資訊的存在為優化器提供了正確的資料分佈圖，給優化器選擇最優路徑帶來了積極的影響，統計資訊在此充當了SQL Server優化器的幕後英雄。

建立統計資訊

既然統計資訊對查詢的效率影響如此大，那麼我們要如何來建立和維護資料庫系統的統計資訊呢？這小節會從統計資訊的自動建立，手動建立兩個大的方面來具體闡述。

建立索引時自動建立

還是以AdventureWorks2008R2庫的測試表dbo.tb_TestStats為例，從上面建表的程式碼來看，測試表建立了一個主鍵，主鍵是一個特殊的索引，SQL Server系統會為每一個索引自動建立一個統計資訊，檢驗方法如下：

USE AdventureWorks2008R2
GO

SELECT  
    statistics_name = st.name
    ,table_name = OBJECT_NAME(st.object_id)
    ,column_name = COL_NAME(stc.object_id, stc.column_id)
FROM    sys.stats AS st WITH(NOLOCK) 
        INNER JOIN sys.stats_columns AS stc WITH(NOLOCK)
            ON st.object_id = stc.object_id  
            AND st.stats_id = stc.stats_id 
WHERE st.object_id = object_id(`dbo.tb_TestStats`, `U`)

查詢結果如下圖所示：

自動建立

在上面的例子中，當我們手動建立索引時，SQL Server會為我們手動建立一個同名的統計資訊。其實，當我們執行一個精確查詢語句時，查詢優化器會判斷謂詞中使用的到列，統計資訊是否可用，如果不可用則會單獨對每列建立統計資訊。這些統計資訊對建立一個高效的執行計劃非常必要。

--Query Test
USE AdventureWorks2008R2
GO
SELECT *
FROM dbo.tb_TestStats WITH(NOLOCK)
WHERE refID = 100;

當執行了精確查詢以後，發現多了一個名為_WA_Sys_00000002_1D114BD1的統計資訊，這個統計資訊就是SQL Server自動為我們建立的，因為我們開啟了自動建立統計資訊的選項。

手動建立

在很多時候，我們需要使用CREATE STATISTICS語句手動建立統計資訊。為了重現這種場景，我們再次手動關閉資料庫AdventureWorks2008R2的Auto Create Statistics選項，然後再執行anotherID列上的精確查詢。

USE master
GO

ALTER DATABASE AdventureWorks2008R2 
SET AUTO_CREATE_STATISTICS OFF;
GO

USE AdventureWorks2008R2
GO
SELECT *
FROM dbo.tb_TestStats WITH(NOLOCK)
WHERE anotherID = 200;

查詢語句的執行計劃會有統計資訊缺失的警告（missing statistics warnings），如下圖所示：

那麼這個時候就需要我們在anotherID欄位上手動建立統計資訊：

USE AdventureWorks2008R2
GO
CREATE STATISTICS st_anotherID ON dbo.tb_TestStats(anotherID)
GO

更新統計資訊

SQL Server除了自動更新統計資訊以外，當我們發現統計資訊過期時，也可以手動更新統計資訊。在講手動更新統計資訊之前，首先我們來看看如何發現過期的統計資訊。

何時更新統計資訊

更新統計資訊最需要回答的第一個問題是：我什麼時候需要更新我的統計資訊呢？以下幾種場景，請考慮更新統計資訊：
查詢執行緩慢，或者查詢語句突然執行緩慢，那麼是時候更新統計資訊了。這種場景很可能是由於統計資訊沒有及時更新而遭遇了引數嗅探的問題。詳情參見文章《SQL Server · 最佳實踐 · 引數嗅探問題》中“老鳥的解決方法”章節的“方法四：更新表物件統計資訊”

當大量資料更新（INSERT/DELETE/UPDATE）到升序或者降序的列時，更新統計資訊。因為在這種情況下，統計資訊直方圖可能沒有及時更新。
強烈建議在除索引維護（當你重建、整理碎片或者重組索引時，資料分佈不會改變）外的維護工作之後更新統計資訊。
如果資料庫的資料更改頻繁，建議最低限度每天更新一次統計資訊。資料倉儲可以適當降低更新統計資訊的頻率。
當執行計劃出現統計資訊缺失警告時，需要手動建立統計資訊，在“手動建立”章節就屬於這種情況。

查詢過期的統計資訊

過期的統計資訊會引起大量的查詢效能問題，沒有及時更新統計資訊常見的影響是優化器選擇了次優的執行計劃，然後導致效能下降。有時候，過期的統計資訊可能比沒有統計資訊更加糟糕。所以，我們可以使用系統檢視sys.stats和系統函式STATS_DATE來獲取到統計資訊最後更新的時間。假如我們定義超過30天未更新的統計資訊算過期的話，那麼查詢過期的統計資訊語句如下：

USE AdventureWorks2008R2
GO
DECLARE
    @day_before int = 30
;

SELECT 
    Object_name = OBJECT_NAME(object_id)
    ,Stats_Name = [name]
    ,Stats_Last_Updated = STATS_DATE([object_id], [stats_id])
FROM sys.stats WITH(NOLOCK)
WHERE STATS_DATE([object_id], [stats_id]) <= DATEADD(day, -@day_before, getdate())
;

AdventureWorks2008R2資料庫下過期的統計資訊截圖：

更新統計資訊

查詢到過期的統計資訊以後，接下來需要手動更新統計資訊，我們可以從下面三個維度來達到目的：
更新索引級別統計資訊
更新單表級別統計資訊
更新整個資料庫級別統計資訊

USE AdventureWorks2008R2
GO
--update statistcis for a specify statistic
UPDATE STATISTICS dbo.tb_TestStats PK_tb_TestStats;
GO 

--update statistcis for a specify table
UPDATE STATISTICS dbo.tb_TestStats WITH FULLSCAN;
GO

--update statistcis for a specify database
USE AdventureWorks2008R2
GO 
EXEC sys.sp_updatestats
GO

更新例項級別統計資訊

從上面章節我們可以做到更新單索引，表級別和資料庫級別統計資訊，那麼如何快速的更新整個例項級別的所有表統計資訊呢？我們可以使用系統儲存過程sys.sp_updatestats和微軟未公開的儲存過程sys.sp_msforeachdb來遍歷更新整個例項級別統計資訊。

USE master
GO

DECLARE
    @sql NVARCHAR(MAX)
;

SET
    @sql = N`
USE [?]
IF ``?`` NOT IN(``master``, ``model``, ``msdb``, ``tempdb``, ``distribution``) 
BEGIN
    RAISERROR(N``--------------------------------------------------------------
Search on database: ?``, 10, 1) WITH NOWAIT
    EXEC sys.sp_updatestats
END
`
;

EXEC SYS.SP_MSFOREACHDB @sql,@replacechar=N`?`

寫在最後

這篇文章從什麼是統計資訊，統計資訊的作用，統計資訊對查詢的影響，如何設定資料庫統計資訊更新策略，如何建立統計資訊，以及如何更新統計資訊等角度，方方面面瞭解了SQL Server統計資訊這個躲在幕後的英雄。