THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul White: Page Free Space

A technical SQL Server blog from New Zealand. See also my articles on SQLperformance.com

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!

© 2011 Paul White

SQLkiwi@gmail.com
@SQL_Kiwi

Published Wednesday, September 21, 2011 5:22 AM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Thiago Dantas said:

awsome!

September 20, 2011 11:40 AM
 

csm said:

How #@~€&^ do you know this kind of things?   :-)

Cooool!!!  

September 20, 2011 11:42 AM
 

Grant Fritchey said:

Hilarious! I just posted one talking about the fact that this information wasn't stored in the plan itself. Maybe we need to coordinate so I can send people over to you more directly. Ha!

Nice work.

September 20, 2011 12:08 PM
 

Paul White said:

Hey Grant,

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.

Paul

September 20, 2011 12:20 PM
 

Paul White said:

csm,

I go looking for them :)

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...?

Paul

September 20, 2011 12:57 PM
 

Alejandro Mesa said:

Paul,

You know what, I have no comment.

Outstanding post!!!

--

AMB

September 20, 2011 2:10 PM
 

Luan Moreno said:

Paul White,

Your posts is always a great opportunity to descover new things. I'm big fan.

Tks Internal Man.....

September 20, 2011 2:36 PM
 

Paul White said:

@AMB, ha!  I have no reply to that!

@Luan, thanks so much.  Internal Man << that's great :)

September 20, 2011 3:01 PM
 

GrumpyOldDBA said:

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.

September 21, 2011 3:02 AM
 

John Alan said:

Paul,

Maybe you should consider renaming the blog from PFS to 'Page 1, 9'

It is becoming mandatory reading!

September 21, 2011 5:11 AM
 

Grant Fritchey said:

For some reason you were caught by the spam filter. I fixed it. Thanks again for posting this and at the blog.

September 21, 2011 6:14 AM
 

Ellon Peterkin said:

Great post. Just what I was looking for . Thanks!

September 21, 2011 3:51 PM
 

Paul White said:

@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.

@John: That's an excellent suggestion, I will consider it :c)

@Grant: How odd.  Thanks for fixing it up!

@Ellon: Cheers - I really appreciate your feedback.

September 23, 2011 9:04 AM
 

ALZDBA said:

Great post, Paul.

That querytraceon hint is what I've been looking for for a long time.

I know ... undocumented = handle with caution ... but will come in handy on test instances.

September 25, 2011 3:25 AM
 

Fabiano Amorim said:

Hi Paul, I've just blogged about that... there is another way... take a look here...

http://blogfabiano.com/2012/07/03/statistics-used-in-a-cached-query-plan/

Regards

Fabiano Amorim

July 3, 2012 5:19 PM
 

Paul White said:

Very cool Fabiano!  Thanks for sharing!

July 3, 2012 5:29 PM
 

Paul White: Page Free Space said:

Ask anyone what the primary advantage of temporary tables over table variables is, and the chances are

August 14, 2012 11:22 AM
 

Brett Shearer said:

Hi Paul,

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.

3000 for isnull

11500 for coalesce

13000 real matches

The stats details returned by the trace flags were identical...

select count(*) from jobvoydestination

where

coalesce(JB_A_ARV, JB_E_ARV)>='1900-01-01 00:00:00.000'

AND coalesce(JB_A_ARV, JB_E_ARV)<'2012-11-16 00:00:00.000'

option (recompile, QUERYTRACEON 3604,QUERYTRACEON 9292,QUERYTRACEON 9204)

select count(*) from jobvoydestination

where

isnull(JB_A_ARV, JB_E_ARV)>='1900-01-01 00:00:00.000'

AND isnull(JB_A_ARV, JB_E_ARV)<'2012-11-16 00:00:00.000'

option (recompile, QUERYTRACEON 3604,QUERYTRACEON 9292,QUERYTRACEON 9204)

Stats header loaded: DbName: OdysseyAPOTRN, ObjName: jobvoydestination, IndexId: 7, ColumnName: JB_E_ARV, EmptyTable: FALSE

Stats loaded: DbName: OdysseyAPOTRN, ObjName: jobvoydestination, IndexId: 7, ColumnName: JB_E_ARV, EmptyTable: FALSE

Stats header loaded: DbName: OdysseyAPOTRN, ObjName: jobvoydestination, IndexId: 8, ColumnName: JB_A_ARV, EmptyTable: FALSE

Stats loaded: DbName: OdysseyAPOTRN, ObjName: jobvoydestination, IndexId: 8, ColumnName: JB_A_ARV, EmptyTable: FALSE

Stats header loaded: DbName: OdysseyAPOTRN, ObjName: jobvoydestination, IndexId: 15, ColumnName: JB_E_ARV, EmptyTable: FALSE

One thing is odd though - there appears to be two statistics stored on JB_E_ARV (7 and 15)?.

November 14, 2012 9:31 PM
 

James Lupolt said:

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.

December 22, 2013 3:43 AM
 

James Lupolt said:

Sorry, feel free to disregard my last comment. I see what you mean about an official vs undocumented way of getting this information now. : )

December 22, 2013 3:46 AM
 

Paul White said:

Hi James,

2014 uses a new cardinality estimator; new trace flag 2363 shows a lot of information, including statistics being loaded.

Paul

December 22, 2013 4:56 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement