RDS SQL Server - 专题分享 - 巧用执行计划缓存之统计信息缺失警告

2022-04-30 08:45:30

问题引入

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

查询重现

查询测试，这里请打开实际执行计划选项，方法如下截图：
RDS SQL Server - 专题分享 - 巧用执行计划缓存之统计信息缺失警告

或者使用快捷键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;

统计信息缺失

查询语句执行完毕后，实际执行计划截图如下：
RDS SQL Server - 专题分享 - 巧用执行计划缓存之统计信息缺失警告

从执行计划截图，我们可以发现以下规律：
实际行数与预估行数相差甚远：实际满足条件行数为0，而执行计划预估满足条件行数为905，说明统计信息不准确。
统计信息缺失警告：存在WHERE语句中的字段ItemID，UserID缺少统计信息警告。
将执行计划图形化界面生成XML格式，XML格式中的统计信息缺失警告如下截图：
RDS SQL Server - 专题分享 - 巧用执行计划缓存之统计信息缺失警告

发现问题

在“场景重现”小节，我们是通过手动分析执行计划来发现统计信息缺失（我们可以叫手动模式），我们如何实现无人值守，自动侦查，自动发现统计信息缺失呢？我们称之为自动模式。要实现统计信息缺失的自动发现和跟踪，我们可以通过搜索执行计划缓存的方式来实现，代码如下：

Use master
GO

;WITH XMLNAMESPACES
    (DEFAULT 'http://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)

执行查询语句的部分结果截图展示如下：
RDS SQL Server - 专题分享 - 巧用执行计划缓存之统计信息缺失警告

解决问题

我们通过自动化的方式来跟踪和发现了统计信息缺失的问题，我们将如何解决这个问题呢？

自动创建统计信息

由于为了场景重现统计信息缺失的目的，在数据库创建完毕后，我们手动关闭了数据库统计信息自动创建的功能，为了解决统计信息缺失的问题，我们需要打开这个选项（当然创建数据库系统默认是自动打开的）。这个选项打开后，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优化器对执行计划评估不准确的风险点，保证数据库系统高效率运行。

上一篇：iOS6定位服务编程详解


下一篇：Spring Boot 教程 - Elasticsearch