如何知道执行计划使用了那些统计信息
最近被一个客户问到了这样一个有趣的问题——执行计划在生成过程中使用了那些统计信息呢？针对这个问题，客户也做了一些研究，并给了我下面两篇博文作为切入点。
1. How to Find the Statistics Used to Compile an Execution Plan (https://sqlblog.com/blogs/paul_white/archive/2011/09/21/how-to-find-the-statistics-used-to-compile-an-execution-plan.aspx)
2. Statistics used in a cached query plan (https://blogfabiano.com/2012/07/03/statistics-used-in-a-cached-query-plan/)
在查看了这两篇博文后，我把其中的奥秘摘出来跟大家分享一下。
第一篇博文介绍了两个Trace Flag， 9204和9292。这两个Trace Flag是这样解释的：
9204 – 打印出被加载的统计信息
9292 – 打印出从元数据中得到的统计信息的头信息
当然，要看到这些信息，我们还需要打开Trace Flag 3604才能让这些显示出来。以下面的脚本为例，
USE Northwind
GO
DBCC FREEPROCCACHE()
GO
DBCC TRACEON(3604, 9292, 9204)
GO
SELECT Employees.FirstName, COUNT(Orders.OrderID)
FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
WHERE Employees.FirstName = 'Steven'
GROUP BY Employees.FirstName
GO
其返回结果为：
Stats header loaded: DbName: Northwind, ObjName: Orders, IndexId: 4, ColumnName: EmployeeID, EmptyTable: FALSE
Stats loaded: DbName: Northwind, ObjName: Orders, IndexId: 4, ColumnName: EmployeeID, EmptyTable: FALSE
Stats header loaded: DbName: Northwind, ObjName: Orders, IndexId: 5, ColumnName: EmployeeID, EmptyTable: FALSE
Stats header loaded: DbName: Northwind, ObjName: Employees, IndexId: 1, ColumnName: EmployeeID, EmptyTable: FALSE
Stats loaded: DbName: Northwind, ObjName: Employees, IndexId: 1, ColumnName: EmployeeID, EmptyTable: FALSE
Stats header loaded: DbName: Northwind, ObjName: Employees, IndexId: 4, ColumnName: FirstName, EmptyTable: FALSE
Stats loaded: DbName: Northwind, ObjName: Employees, IndexId: 4, ColumnName: FirstName, EmptyTable: FALSE
FirstName
---------- -----------
Steven 42
从这个SELECT语句中，我们不难看出查询结果应该是员工Steven的First Name和这个员工有的所有的订单Order的数量。表Employees和Orders是用过EmployeeID来联系起来的。所以，在Employees.EmployeeID和Orders.EmployeeID上建立的索引上面的统计信息会被用来产生执行计划。当然除了他们，还应该有在字段Employees.FirstName上的统计信息，因为这个字段上没有索引存在，所以系统会在这里自动生成一个统计信息。
需要注意的是，Trace Flag 9204和9292 只会在这个SELECT语句第一次被编译的时候打印出这些统计信息的记录。如果执行计划已经存在于缓存中，那么这些统计信息的记录则不会被打印出来。只有在用DBCC FREEPROCCACHE(plan_handle)清理了这条语句的缓存计划后，再次执行的时候才会再次被打印出来。
第二篇博文介绍了另一个Trace Flag，8666。这个Trace Flag是这样解释的：
8666 – 将Showplan的详细信息打印出来
这个Trace Flag会将一些关于统计信息的内部信息显示出来。例如，
USE Northwind
GO
DBCC FREEPROCCACHE()
GO
DBCC TRACEON(8666)
GO
SELECT Employees.FirstName, COUNT(Orders.OrderID)
FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
WHERE Employees.FirstName = 'Steven'
GROUP BY Employees.FirstName
GO
当你查看XML计划的时候，你可能会看到类似下面的信息。在第一行，我们可以看到有一个XML的标签叫做InternalInfo。再往下，我们会看到两个Recompile的标签，每一个标签对应这个SELECT语句中用到的一张表。在每一个Recompile标签下，又有两个ModTrackingInfo的标签，在这里统计信息被显示出来。在这个例子中，我们看到了四个统计信息的显示，可是实际被执行计划用到的只有三个（从前面介绍的方法可以得知）。也就是说，这里例举出的统计信息并不全都会被使用到，而是会被检查并判定是否被用于产生执行计划。
<InternalInfoLockClassNoHint="0"LockClassIntLockHint="0"LockClassRCIsoHint="0">
<OptmInfo>
<FieldFieldName="m_iOptStage"FieldValue="1" />
<FieldFieldName="m_cOptTask"FieldValue="382" />
<FieldFieldName="m_ullAlgPmoSize"FieldValue="56" />
<FieldFieldName="m_ullOptPmoSize"FieldValue="256" />
<FieldFieldName="m_ullAlgTotalTime"FieldValue="514" />
<FieldFieldName="m_ullAlgNetTime"FieldValue="514" />
<FieldFieldName="m_ullOptTotalTime"FieldValue="51803" />
<FieldFieldName="m_ullOptNetTime"FieldValue="37964" />
</OptmInfo>
<EnvColl>
<Recompile>
<FieldFieldName="wszDb"FieldValue="Northwind" />
<FieldFieldName="wszSchema"FieldValue="dbo" />
<FieldFieldName="wszTable"FieldValue="Employees" />
<FieldFieldName="m_cRowCount"FieldValue="9" />
<FieldFieldName="ullThreshold"FieldValue="500" />
<FieldFieldName="wszReason"FieldValue="small table" />
<FieldFieldName="m_fMissingStatsRecompile"FieldValue="0" />
<FieldFieldName="m_fVisibleOutsideXact"FieldValue="1" />
<FieldFieldName="m_dbId"FieldValue="7" />
<FieldFieldName="m_mdObjectId"FieldValue="1977058079" />
<FieldFieldName="m_mdBaseIndexId"FieldValue="1" />
<FieldFieldName="m_cRowsetId"FieldValue="1" />
<FieldFieldName="m_verStats"FieldValue="153" />
<FieldFieldName="m_fAfterTriggerDelta"FieldValue="0" />
<FieldFieldName="m_fInsteadOfDeltaPopulate"FieldValue="0" />
<FieldFieldName="m_fInsteadOfDeltaInsideTrg"FieldValue="0" />
<FieldFieldName="m_fIsSbQueue"FieldValue="0" />
<ModTrackingInfo>
<FieldFieldName="wszStatName"FieldValue="PK_Employees" />
<FieldFieldName="wszColName"FieldValue="EmployeeID" />
<FieldFieldName="m_cCols"FieldValue="1" />
<FieldFieldName="m_idIS"FieldValue="1" />
<FieldFieldName="m_ullSnapShotModCtr"FieldValue="27" />
<FieldFieldName="m_ullRowCount"FieldValue="9" />
<FieldFieldName="ullThreshold"FieldValue="500" />
<FieldFieldName="wszReason"FieldValue="small table" />
</ModTrackingInfo>
<ModTrackingInfo>
<FieldFieldName="wszStatName"FieldValue="_WA_Sys_00000003_75D7831F" />
<FieldFieldName="wszColName"FieldValue="FirstName" />
<FieldFieldName="m_cCols"FieldValue="1" />
<FieldFieldName="m_idIS"FieldValue="4" />
<FieldFieldName="m_ullSnapShotModCtr"FieldValue="27" />
<FieldFieldName="m_ullRowCount"FieldValue="9" />
<FieldFieldName="ullThreshold"FieldValue="500" />
<FieldFieldName="wszReason"FieldValue="small table" />
</ModTrackingInfo>
</Recompile>
<Recompile>
<FieldFieldName="wszDb"FieldValue="Northwind" />
<FieldFieldName="wszSchema"FieldValue="dbo" />
<FieldFieldName="wszTable"FieldValue="Orders" />
<FieldFieldName="m_cRowCount"FieldValue="830" />
<FieldFieldName="ullThreshold"FieldValue="666" />
<FieldFieldName="wszReason"FieldValue="heuristic" />
<FieldFieldName="m_fMissingStatsRecompile"FieldValue="0" />
<FieldFieldName="m_fVisibleOutsideXact"FieldValue="1" />
<FieldFieldName="m_dbId"FieldValue="7" />
<FieldFieldName="m_mdObjectId"FieldValue="21575115" />
<FieldFieldName="m_mdBaseIndexId"FieldValue="1" />
<FieldFieldName="m_cRowsetId"FieldValue="1" />
<FieldFieldName="m_verStats"FieldValue="152" />
<FieldFieldName="m_fAfterTriggerDelta"FieldValue="0" />
<FieldFieldName="m_fInsteadOfDeltaPopulate"FieldValue="0" />
<FieldFieldName="m_fInsteadOfDeltaInsideTrg"FieldValue="0" />
<FieldFieldName="m_fIsSbQueue"FieldValue="0" />
<ModTrackingInfo>
<FieldFieldName="wszStatName"FieldValue="EmployeeID" />
<FieldFieldName="wszColName"FieldValue="EmployeeID" />
<FieldFieldName="m_cCols"FieldValue="1" />
<FieldFieldName="m_idIS"FieldValue="4" />
<FieldFieldName="m_ullSnapShotModCtr"FieldValue="2490" />
<FieldFieldName="m_ullRowCount"FieldValue="830" />
<FieldFieldName="ullThreshold"FieldValue="666" />
<FieldFieldName="wszReason"FieldValue="heuristic" />
</ModTrackingInfo>
<ModTrackingInfo>
<FieldFieldName="wszStatName"FieldValue="EmployeesOrders" />
<FieldFieldName="wszColName"FieldValue="EmployeeID" />
<FieldFieldName="m_cCols"FieldValue="1" />
<FieldFieldName="m_idIS"FieldValue="5" />
<FieldFieldName="m_ullSnapShotModCtr"FieldValue="2490" />
<FieldFieldName="m_ullRowCount"FieldValue="830" />
<FieldFieldName="ullThreshold"FieldValue="666" />
<FieldFieldName="wszReason"FieldValue="heuristic" />
</ModTrackingInfo>
</Recompile>
</EnvColl>
</InternalInfo>
另外，因为这些信息存在XML计划中，所以我们还可以通过下面的语句来直接把我们需要的信息截取出来。不过需要注意的是，运行下面的语句有时候会造成访问越界，所以请慎用！
WITH XMLNAMESPACES('https://schemas.microsoft.com/sqlserver/2004/07/showplan' as p)
SELECT qt.text AS SQLCommand,
qp.query_plan,
StatsUsed.XMLCol.value('@FieldValue','NVarChar(500)') AS StatsName
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) qt
CROSS APPLY query_plan.nodes('//p:Field[@FieldName="wszStatName"]') StatsUsed(XMLCol)
WHERE qt.text LIKE '%Steven%'
AND qt.text NOT LIKE '%sys.%'
综上所述，如果想要得知执行计划在生成过程中使用了那些统计信息，比较安全的做法是用Trace Flag 9204和9292。
Comments
- Anonymous
November 27, 2013
最后面介绍的截取统计信息截取不出来啊