问题引入
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 '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)执行查询语句的部分结果截图展示如下：
解决问题
我们通过自动化的方式来跟踪和发现了统计信息缺失的问题，我们将如何解决这个问题呢？
自动创建统计信息
由于为了场景重现统计信息缺失的目的，在数据库创建完毕后，我们手动关闭了数据库统计信息自动创建的功能，为了解决统计信息缺失的问题，我们需要打开这个选项（当然创建数据库系统默认是自动打开的）。这个选项打开后，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优化器对执行计划评估不准确的风险点，保证数据库系统高效率运行。
