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

查询重现

查询测试,这里请打开实际执行计划选项,方法如下截图:
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