How to Find the Statistics Used to Compile an Execution Plan

라는 주제를 가지고 어떻게하면 좋을까 고민을 몇일 했는데 만철군이 찾아줬다.

역시 구글을 뒤져야해.....ㅠ.ㅠ 세상엔 똑똑한 사람이 넘 많다~ 역쉬 뒤지는게 더 빠른듯~

 

http://sqlblog.com/blogs/paul_white/archive/2011/09/21/how-to-find-the-statistics-used-to-compile-an-execution-plan.aspx

 

http://www.benjaminnevarez.com/tag/trace-flags/

 

 

A technical SQL Server blog from New Zealand.

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'

GROUP BY

    p.Name

ORDER BY

    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.

Enjoy!