THE SQL Server Blog Spot on the Web

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

Page Free Space

See also my SQL Server technical articles on SQLperformance.com

How to Find the Statistics Used to Compile an Execution Plan

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
Privacy Statement