RDS SQL Server - 專題分享 - 巧用執行計劃緩存之統計信息缺失警告

問題引入

SQL Server 數據庫查詢優化器對執行計劃成本的評估是基於統計信息的，換句話說，統計信息的準確與否直接關係著查詢語句是否能夠高效運行。那麼，在SQL Server中，表對象中統計信息的缺失是一個影響查詢語句性能的風險點，我們如何能夠通過非常自動化的方式來偵查，發現統計信息的缺失呢？這個問題的答案就是我們今天這篇文章要分享的內容－使用執行計劃緩存來發現統計信息的缺失警告。
關於統計詳情，參見我的另外一篇文章：SQL Server幕後英雄－統計信息。

場景重現

為了模擬統計信息缺失的場景，我們創建測試數據庫，創建測試表，執行查詢語句，然後通過執行計劃圖像化界麵發現統計信息缺失警告。

創建測試數據庫

創建測試數據庫並且關閉該數據庫的自動創建統計信息的選項設置。

USE master
GO

-- Create testing database
IF DB_ID('TestDb') IS NULL
    CREATE DATABASE TestDb;
GO

ALTER DATABASE TestDb 
SET AUTO_CREATE_STATISTICS OFF;
GO

創建測試表

創建測試表，並初始化2萬條數據。

USE TestDb
GO

-- create demo table SalesOrder
IF OBJECT_ID('dbo.SalesOrder', 'U') IS NOT NULL
BEGIN
    TRUNCATE TABLE dbo.SalesOrder
    DROP TABLE dbo.SalesOrder
END
GO

CREATE TABLE dbo.SalesOrder
(
    RowID INT IDENTITY(1,1) NOT NULL
    , OrderID UNIQUEIDENTIFIER NOT NULL
    , ItemID INT NOT NULL
    , UserID INT NOT NULL
    , OrderQty INT NOT NULL
    , Price DECIMAL(8,2) NOT NULL
    , OrderDate DATETIME NOT NULL 
        CONSTRAINT DF_OrderDate DEFAULT(GETDATE())
    , LastUpdateTime DATETIME NULL
    , OrderComment NVARCHAR(100) NULL
    , CONSTRAINT PK_SalesOrder PRIMARY KEY(
        OrderID
    )
);

-- data init for 20 thousand records.
;WITH a 
AS (
    SELECT * 
    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a)
), RoundData
AS(
SELECT TOP(20000)
    OrderID = NEWID()
    ,ItemIDRound = abs(checksum(newid()))
    ,Price = a.a * b.a * 10
    ,OrderQty = a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h
), DATA
AS(
SELECT 
    OrderID
    ,ItemID = cast(ROUND((1300 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 101), 0) as int)
    ,UserID = cast(ROUND((500 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 10000), 0) as int)
    ,OrderQty
    ,Price = cast(Price AS DECIMAL(8,2))
    ,OrderDate = dateadd(day, -cast(ROUND((50 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 1), 0) as int) ,GETDATE())
FROM RoundData
)
INSERT INTO dbo.SalesOrder(OrderID, ItemID, UserID, OrderQty, Price, OrderDate, LastUpdateTime, OrderComment)
SELECT 
    OrderID
    , ItemID
    , UserID
    , OrderQty
    , Price
    , OrderDate
    , LastUpdateTime = OrderDate
    , OrderComment = N'User ' + CAST(UserID AS NVARCHAR(8)) + N' purchased item ' + CAST(ItemID AS NVARCHAR(8))
FROM DATA;
GO

查詢重現

查詢測試，這裏請打開實際執行計劃選項，方法如下截圖：

或者使用快捷鍵Ctrl + m，然後執行下麵的查詢語句。

USE testdb
GO

SELECT * FROM dbo.SalesOrder WITH(NOLOCK)
WHERE UserID = 10058 and ItemID = 254

SELECT * FROM dbo.SalesOrder WITH(NOLOCK)
WHERE UserID = 10059 and ItemID = 255;

統計信息缺失

查詢語句執行完畢後，實際執行計劃截圖如下：

從執行計劃截圖，我們可以發現以下規律：
實際行數與預估行數相差甚遠：實際滿足條件行數為0，而執行計劃預估滿足條件行數為905，說明統計信息不準確。
統計信息缺失警告：存在WHERE語句中的字段ItemID，UserID缺少統計信息警告。
將執行計劃圖形化界麵生成XML格式，XML格式中的統計信息缺失警告如下截圖：

發現問題

在“場景重現”小節，我們是通過手動分析執行計劃來發現統計信息缺失（我們可以叫手動模式），我們如何實現無人值守，自動偵查，自動發現統計信息缺失呢？我們稱之為自動模式。要實現統計信息缺失的自動發現和跟蹤，我們可以通過搜索執行計劃緩存的方式來實現，代碼如下：

Use master
GO

;WITH XMLNAMESPACES
    (DEFAULT 'https://schemas.microsoft.com/sqlserver/2004/07/showplan')
,PlanCache 
AS(
    SELECT 
        EQP.query_plan,
        EQS.plan_handle,
        EST.text, 
        EQS.creation_time, 
        EQS.last_execution_time,
        EQS.execution_count,

        EQS.total_worker_time,
        EQS.last_worker_time,
        EQS.min_worker_time,
        EQS.max_worker_time,

        EQS.total_physical_reads,
        EQS.last_physical_reads,
        EQS.min_physical_reads,
        EQS.max_physical_reads,

        EQS.total_logical_writes,
        EQS.last_logical_writes,
        EQS.min_logical_writes,
        EQS.max_logical_writes,

        EQS.total_logical_reads,
        EQS.last_logical_reads,
        EQS.min_logical_reads,
        EQS.max_logical_reads,

        EQS.total_elapsed_time,
        EQS.last_elapsed_time,
        EQS.min_elapsed_time,
        EQS.max_elapsed_time,

        EQS.total_rows,
        EQS.last_rows,
        EQS.min_rows,
        EQS.max_rows
    FROM sys.dm_exec_query_stats AS EQS
      CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST
      CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP
)
SELECT
    batch.stmt.value('(@StatementText)[1]', 'varchar(max)') AS stmt,
    cast(T.C.query('local-name(..)') as varchar) AS warning, 
    total_Worker_time,
    Refer_Columns = 
        T.C.value('(@Database)[1]', 'sysname') + '.' +
        T.C.value('(@Schema)[1]', 'sysname') + '.' +
        T.C.value('(@Table)[1]', 'sysname') + '.' +
        T.C.value('(@Column)[1]', 'sysname'),
    QP.*
FROM PlanCache AS QP
    CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/Warnings/ColumnsWithNoStatistics/ColumnReference') T(C)
    CROSS APPLY QP.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)

執行查詢語句的部分結果截圖展示如下：

解決問題

我們通過自動化的方式來跟蹤和發現了統計信息缺失的問題，我們將如何解決這個問題呢？

自動創建統計信息

由於為了場景重現統計信息缺失的目的，在數據庫創建完畢後，我們手動關閉了數據庫統計信息自動創建的功能，為了解決統計信息缺失的問題，我們需要打開這個選項（當然創建數據庫係統默認是自動打開的）。這個選項打開後，SQL Server在發現查詢語句有統計信息缺失的情況下，會自動為相應的字段創建統計信息。打開選項的方法如下：

USE master
GO

ALTER DATABASE TestDb 
SET AUTO_CREATE_STATISTICS ON;
GO

手動創建統計信息

在我們的工作過程中，我們發現在極少情況下，即使自動創建統計信息選項是打開的，也會出現統計信息缺失的情況，在這種場景下，就需要我們根據“發現問題”小節的方法（字段名為Refer_Columns）找到統計信息缺失的字段，手動創建統計信息。比如：

USE testdb
GO
CREATE STATISTICS ST_SalesOrder_ItemID ON dbo.SalesOrder(ItemID)
;

CREATE STATISTICS ST_SalesOrder_UserID ON dbo.SalesOrder(UserID)
;

最後總結

這篇文章分享了如何通過執行計劃緩存來查找統計信息缺失的方法，並提出來解決這類問題的途徑，從而解決了因為統計信息缺失而導致SQL Server優化器對執行計劃評估不準確的風險點，保證數據庫係統高效率運行。

最後更新：2017-10-17 17:34:25