Blog – Fabiano Neves Amorim

Today I was working on the material for the on-demand (online) training we’ll offer in next few days at Sr.Nimbus when I find-out a very interesting thing.

“The statistics used in a query plan are stored in a cached execution plan, and you can see them using the special trace flag 8666 (there is always a traceflag).”

It is also possible to identify which statistics were used in a query plan compilation if you run the query using traceflags 9292 and 9204 as Query Optimizer Guru Paul White (blog|twitter) showed here.

What I’m saying is that you can also see in the XMLPlan the used statistics.

Let’s see a small sample of what I’m saying…

First let’s clear the cache plan memory area:

 1: DBCC FREEPROCCACHE()

Now let’s run a small query on the Northwind database:

 1: USE Northwind

 2: GO

 3: SELECT Employees.FirstName, COUNT(Orders.OrderID)

 4:   FROM Orders

 5:  INNER JOIN Employees

 6:     ON Orders.EmployeeID = Employees.EmployeeID

 7:  WHERE Employees.FirstName = 'Steven'

 8:  GROUP BY Employees.FirstName

 9: GO

This is the plan:

Running the same query with TF 8666 on, we’ve the following information in the xml query plan (right click in the select operator and select properties):

This new element InternalInfo in the XML plan is documented on the current schema http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd

The information we can see here are confuse and used by Microsoft people to debug query plans, but, one specific tag is more interesting than others… This is the ModTrackingInfo tag. There we can see the statistics used information, let’s dig in:

In the image above we can see two “Recompile” tags (collapsed), in fact we’ve one tag for each table in the query (in our sample, table orders and table employees).

Isn’t that nice or what?

Could be this be even more interesting? Yes.

All this information are also stored in the cached plans. That means if you query the Cached Plans DMVs with the TF turned on, we’ll be able to use xquery to query the used statistics.

For instance:

 1: DBCC TRACEON(8666)

 2: GO

 3: WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as p)

 4: SELECT qt.text AS SQLCommand,

 5:        qp.query_plan,

 6:        StatsUsed.XMLCol.value('@FieldValue','NVarChar(500)') AS StatsName

 7: FROM sys.dm_exec_cached_plans cp

 8:     CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

 9:     CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) qt

 10:     CROSS APPLY query_plan.nodes('//p:Field[@FieldName="wszStatName"]') StatsUsed(XMLCol)

 11:  WHERE qt.text LIKE '%Steven%'

 12:    AND qt.text NOT LIKE '%sys.%'

 13: GO

 14: DBCC TRACEOFF(8666)

 15: GO

Will Return:

I’m sure you can do better xquery than I to query this from cacheplan.

Now the boring part of the this… First of all and the most important. This traceflag IS NOT DOCUMENTED so please be very very very careful to use it, and use only for tests purposes, never use it in a production environment. Also when I tried to use it on SQL Server 2008R2 sometimes I’ve got nasty errors generating dump and closing my session.

Since I‘m aware, it works fine on SQL Server 2012, at least I didn’t received any error on using it.

Let me know if you try it on an SQL 2005 instance… And also about bugs and discovers you may find with it .

Happy query.