How to Find the Statistics Used to Compile an Execution Plan

re: How to Find the Statistics Used to Compile an Execution Plan

Thiago Dantas — Tue, 20 Sep 2011 15:40:53 GMT

awsome!

re: How to Find the Statistics Used to Compile an Execution Plan

csm — Tue, 20 Sep 2011 15:42:30 GMT

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

Cooool!!!

re: How to Find the Statistics Used to Compile an Execution Plan

Grant Fritchey — Tue, 20 Sep 2011 16:08:53 GMT

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.

re: How to Find the Statistics Used to Compile an Execution Plan

Paul White — Tue, 20 Sep 2011 16:20:36 GMT

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

re: How to Find the Statistics Used to Compile an Execution Plan

Paul White — Tue, 20 Sep 2011 16:57:15 GMT

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

re: How to Find the Statistics Used to Compile an Execution Plan

Alejandro Mesa — Tue, 20 Sep 2011 18:10:02 GMT

Paul,

You know what, I have no comment.

Outstanding post!!!

AMB

re: How to Find the Statistics Used to Compile an Execution Plan

Luan Moreno — Tue, 20 Sep 2011 18:36:10 GMT

Paul White,

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

Tks Internal Man.....

re: How to Find the Statistics Used to Compile an Execution Plan

Paul White — Tue, 20 Sep 2011 19:01:41 GMT

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

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

re: How to Find the Statistics Used to Compile an Execution Plan

GrumpyOldDBA — Wed, 21 Sep 2011 07:02:24 GMT

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.

re: How to Find the Statistics Used to Compile an Execution Plan

John Alan — Wed, 21 Sep 2011 09:11:24 GMT

Paul,

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

It is becoming mandatory reading!

re: How to Find the Statistics Used to Compile an Execution Plan

Grant Fritchey — Wed, 21 Sep 2011 10:14:56 GMT

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

re: How to Find the Statistics Used to Compile an Execution Plan

Ellon Peterkin — Wed, 21 Sep 2011 19:51:43 GMT

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

re: How to Find the Statistics Used to Compile an Execution Plan

Paul White — Fri, 23 Sep 2011 13:04:18 GMT

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

re: How to Find the Statistics Used to Compile an Execution Plan

ALZDBA — Sun, 25 Sep 2011 07:25:24 GMT

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.

re: How to Find the Statistics Used to Compile an Execution Plan

Fabiano Amorim — Tue, 03 Jul 2012 21:19:01 GMT

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

re: How to Find the Statistics Used to Compile an Execution Plan

Paul White — Tue, 03 Jul 2012 21:29:03 GMT

Very cool Fabiano! Thanks for sharing!

Temporary Tables in Stored Procedures

Paul White: Page Free Space — Tue, 14 Aug 2012 15:22:31 GMT

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

re: How to Find the Statistics Used to Compile an Execution Plan

Brett Shearer — Thu, 15 Nov 2012 02:31:43 GMT

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