Finding the Statistics Used to Compile an Execution Plan

In this post, I show you how to determine the statistics objects used by the query optimizer in producing an execution plan.

Note: This technique only applies to queries compiled using the original (70) cardinality estimation model.

Trace Flags

We will need three undocumented trace flags:

• 3604: Redirects trace output to the client.
  Output appears in the SSMS messages tab.
• 9292: Reports statistics objects considered ‘interesting’ by the query optimizer when compiling, or recompiling the query. For potentially useful statistics, just the stats header is loaded.
• 9204: Shows the ‘interesting’ statistics that end up being fully loaded, and used to produce cardinality and distribution estimates for some plan alternative or other. 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 query using the QUERYTRACEON query hint (as demonstrated below).

Sample Query

This query is written against the AdventureWorks sample database:

DBCC FREEPROCCACHE;
GO
SELECT 
    P.[Name],
    total_quantity = SUM(TH.Quantity)
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
    ON TH.ProductID = P.ProductID
WHERE
    TH.ActualCost >= $5.00
    AND P.Color = N'Red'
GROUP BY
    P.[Name]
ORDER BY
    P.[Name]
OPTION
(
    QUERYTRACEON 3604,
    QUERYTRACEON 9292,
    QUERYTRACEON 9204,
    QUERYTRACEON 9481
);

The DBCC FREEPROCCACHE is there to empty the plan cache so we get a fresh compilation. You can also evict the current plan from cache if you know its handle (SQL Server 2008 onwards) or use a RECOMPILE query hint.

Documented trace flag 9481 is not required. It is there to ensure the test query is compiled using the original (70) cardinality estimation model, so the other trace flags produce output.

Using OPTION (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

The query above will produce output like the following (lightly reformatted for display):

Stats header loaded: 
DbName: AdventureWorks2014, 
ObjName: Production.Product, 
IndexId: 1, 
ColumnName: ProductID, 
EmptyTable: FALSE

Stats loaded: 
DbName: AdventureWorks2014, 
ObjName: Production.Product, 
IndexId: 1, 
ColumnName: ProductID, 
EmptyTable: FALSE

Stats header loaded: 
DbName: AdventureWorks2014, 
ObjName: Production.Product, 
IndexId: 3, 
ColumnName: Name, 
EmptyTable: FALSE

Stats loaded: 
DbName: AdventureWorks2014, 
ObjName: Production.Product, 
IndexId: 3, 
ColumnName: Name, 
EmptyTable: FALSE

Stats header loaded: 
DbName: AdventureWorks2014, 
ObjName: Production.Product, 
IndexId: 12, 
ColumnName: Color, 
EmptyTable: FALSE

Stats loaded: 
DbName: AdventureWorks2014, 
ObjName: Production.Product, 
IndexId: 12, 
ColumnName: Color, 
EmptyTable: FALSE

Stats header loaded: 
DbName: AdventureWorks2014, 
ObjName: Production.TransactionHistory, 
IndexId: 2, 
ColumnName: ProductID, 
EmptyTable: FALSE

Stats loaded: 
DbName: AdventureWorks2014, 
ObjName: Production.TransactionHistory, 
IndexId: 2, 
ColumnName: ProductID, 
EmptyTable: FALSE

Stats header loaded: 
DbName: AdventureWorks2014, 
ObjName: Production.TransactionHistory, 
IndexId: 6, 
ColumnName: ActualCost, 
EmptyTable: FALSE

Stats loaded: 
DbName: AdventureWorks2014, 
ObjName: Production.TransactionHistory, 
IndexId: 6, 
ColumnName: ActualCost, 
EmptyTable: FALSE

Versions

Trace flag 9204 works at least as far back as SQL Server 2005.

Both 9204 and 9292 work from SQL Server 2008 onwards.

Neither is functional when the query is compiled using the ‘new’ cardinality estimator first released in SQL Server 2014.

Note:
Statistics used by the optimizer are available in execution plans from SQL Server 2016 SP2 onwards. A suitably modern version of SSMS may be required.

See SQL Server 2017 Showplan enhancements by Pedro Lopes of the SQL Server Tiger Team for details.

Enjoy!

© Paul White
email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi