How to Find the Statistics Used to Compile an Execution Plan

In this post, I show you how to determine exactly which statistics objects were used by the query optimizer to produce an execution plan.

Trace Flags

We will need three undocumented trace flags. The first one (3604) is well-known – it redirects trace output to the client so it appears in the SSMS messages tab.

The second trace flag is 9292. With this enabled, we get a report of statistics objects which are considered ‘interesting’ by the query optimizer when compiling, or recompiling the query in question. For potentially useful statistics, just the header is loaded.

The third trace flag is 9204. With this enabled, we see the ‘interesting’ statistics which end up being fully loaded and used to produce cardinality and distribution estimates for some plan alternative or other. Again, this only happens when a plan is compiled or recompiled – not when a plan is retrieved from cache.

You can enable and disable these flags with the usual DBCC TRACEON and TRACEOFF commands, but it is also possible to enable them just for a particular statement using the undocumented QUERYTRACEON query hint (demonstrated below).

Sample Query

DBCC FREEPROCCACHE

SELECT

p.Name,

total_quantity = SUM(th.Quantity)

FROM AdventureWorks.Production.Product AS p

JOIN AdventureWorks.Production.TransactionHistory AS th ON

th.ProductID = p.ProductID

WHERE

th.ActualCost >= $5.00

AND p.Color = N'Red'

GROUPBY

p.Name

ORDERBY

p.Name

OPTION

(

QUERYTRACEON 3604,

QUERYTRACEON 9292,

QUERYTRACEON 9204

)

The DBCC FREEPROCCACHE is just there to empty the plan cache so we get a compilation. You can also evict the current plan from cache if you know its handle (SQL Server 2008) or use a RECOMPILE query hint. Using RECOMPILE is often convenient, but you may get a different plan compared to that obtained without the hint. Note that compiling the query is enough – we do not need to execute the query; simply requesting an ‘estimated plan’ will do. It doesn’t hurt to run it either though, just to be clear.

Sample Output

Stats header loaded:

DbName: AdventureWorks,

ObjName: AdventureWorks.Production.Product,

IndexId: 1,

ColumnName: ProductID,

EmptyTable: FALSE

Stats loaded:

DbName: AdventureWorks,

ObjName: AdventureWorks.Production.Product,

IndexId: 1,

ColumnName: ProductID,

EmptyTable: FALSE

Stats header loaded:

DbName: AdventureWorks,

ObjName: AdventureWorks.Production.Product,

IndexId: 3,

ColumnName: Name,

EmptyTable: FALSE

Stats loaded:

DbName: AdventureWorks,

ObjName: AdventureWorks.Production.Product,

IndexId: 3,

ColumnName: Name,

EmptyTable: FALSE

Stats header loaded:

DbName: AdventureWorks,

ObjName: AdventureWorks.Production.Product,

IndexId: 11,

ColumnName: Color,

EmptyTable: FALSE

Stats loaded:

DbName: AdventureWorks,

ObjName: AdventureWorks.Production.Product,

IndexId: 11,

ColumnName: Color,

EmptyTable: FALSE

Stats header loaded:

DbName: AdventureWorks,

ObjName: AdventureWorks.Production.TransactionHistory,

IndexId: 2,

ColumnName: ProductID,

EmptyTable: FALSE

Stats loaded:

DbName: AdventureWorks,

ObjName: AdventureWorks.Production.TransactionHistory,

IndexId: 2,

ColumnName: ProductID,

EmptyTable: FALSE

Stats header loaded:

DbName: AdventureWorks,

ObjName: AdventureWorks.Production.TransactionHistory,

IndexId: 5,

ColumnName: ActualCost,

EmptyTable: FALSE

Stats loaded:

DbName: AdventureWorks,

ObjName: AdventureWorks.Production.TransactionHistory,

IndexId: 5,

ColumnName: ActualCost,

EmptyTable: FALSE

There’s no sign of an official way to get this very useful information in Denali, despite it being requested many times over the years. Trace flag 9204 works at least as far back as SQL Server 2005. Both 92xx flags work in 2008, R2, and Denali CTP 3.

Yes I saw it and went to leave the TF details in a comment there, but it didn't stick for some reason. When I tried resubmitting, it said I had already left that comment. So, I gave up and jotted the details down here instead.

Thanks for posting the link on Grant's blog. I must confess I'm not sure how being able to extract the stats_ids from the plan would help Grant or Gail - they'd still need to connect to the database to check the metadata for those ids. Might as well extract the query text from the plan and use the techniques shown here...?

hmmm . I echo the others - awesome! and how do you get to learn this type of stuff? If your posts were a book I'm sure it would be a top seller, I constantly learn new stuff from your posts and I'm very grateful, thank you.

@Colin: Thank you. Sometimes I just get lucky messing about with SQL Server and find something I think worth sharing on the blog! I would love to find the time and freedom to put something longer together one day - whether that's a book or a downloadable PDF I'm not sure yet.

Just used this technique to try and understand why statistics are applied differently for IsNull and Coalesce (which I could not find any info on). IsNull returns suboptimal estimates, whereas coalesce seems to be much more accurate.

Hi there, it might be worth noting that (as you probably know by now, Paul, but readers may not) that TF 9204 and 9292 work in SQL Server 2012 now. I just tested with 11.0.3381. However, they did not work for me in SQL 2014 CTP2.