RDS SQL Server - 专题分享 - 巧用执行计划缓存之统计信息缺失警告-阿里云开发者社区

开发者社区> 风移> 正文

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

简介: # 问题引入 SQL Server 数据库查询优化器对执行计划成本的评估是基于统计信息的,换句话说,统计信息的准确与否直接关系着查询语句是否能够高效运行。那么,在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

查询重现

查询测试,这里请打开实际执行计划选项,方法如下截图:
01.png

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

统计信息缺失

查询语句执行完毕后,实际执行计划截图如下:
02.png

从执行计划截图,我们可以发现以下规律:
实际行数与预估行数相差甚远:实际满足条件行数为0,而执行计划预估满足条件行数为905,说明统计信息不准确。
统计信息缺失警告:存在WHERE语句中的字段ItemID,UserID缺少统计信息警告。
将执行计划图形化界面生成XML格式,XML格式中的统计信息缺失警告如下截图:
03.png

发现问题

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

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)

执行查询语句的部分结果截图展示如下:
04.png

解决问题

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

自动创建统计信息

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL超时参数以及相关数据集成、DataX数据同步案例分享
一、背景 MySQL系统变量提供关于服务器的一些配置和能力信息,大部分变量可在mysqld服务进程启动时设置,部分变量可在mysqld服务进程运行时设置。合理的系统变量设值范围,是保障MySQL稳定提供服务的重要因素。
12890 0
《大数据原理:复杂信息的准备、共享和分析》一一2.3 注册唯一对象标识符
本节书摘来自华章出版社《大数据原理:复杂信息的准备、共享和分析》一 书中的第2章,第2.3节,作者:[美] 朱尔斯 J. 伯曼(Jules J. Berman)著 ,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1465 0
C# Redis Server分布式缓存编程(二)
在Redis编程中, 实体和集合类型则更加有趣和实用 namespace Zeus.Cache.Redis.Demo { public class Person { public int Id { get; set; } public ...
846 0
VisualSVN Server仓库迁移到Linux(包含所有版本, 权限,用户信息)
公司开发服务器从Windows换成CentOS,所以要把原服务都转移到Linux下,MySQL、SMB的迁移都很顺利,但是SVN的转移却遇到了些问题,花费了三天时间,走了不少弯路,现在总算解决了SVN迁移问题,记录下: 在Windows上我们是用VisualSVN Server作SVN服务的,Linux上是编译安装的subversion 1.7.7。
1465 0
Android系统应用信息中存储和缓存的计算方法
进行如下操作: 设置->应用->选择一个应用->应用信息 会到达如下界面: 可以看到这个应用占用的磁盘空间。 先说结果,这几项会计算哪些文件(夹)。
770 0
一款消息队列的客户端框架——启明信息车联网MQ演进实践分享
一款消息队列的客户端框架——启明信息车联网MQ演进实践分享 分享人:阿里云MVP曾宪宇,2014开始 就职于启明信息,负责车联网平台的架构和建设,坐标吉林长春。 分享内容:结合主流MQ,介绍一款基于Java的开源消息队列客户端框架。
2258 0
如何通过SQL Server执行系统命令?
本文讲的是如何通过SQL Server执行系统命令?,渗透测试过程中,大家经常会碰到通过MSSQL来进行提权或执行系统命令之类的操作,通常我们经常会使用xp_cmdshell来进行执行系统命令,但是当xp_cmdshell不能使用的时候,我们还有什么别的方式么?本文将介绍与分享一下我自己学到的一些姿势。
2812 0
+关注
风移
阿里云数据库专家,负责SQL Server数据库产品线。SQL Server从业10年,经历过SQL 2000、SQL 2005、SQL 2008、SQL 2008R2、SQL 2012、SQL 2014、SQL 2016和SQL on Linux各个版本。
75
文章
46
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载