February 17, 2014 at 10:14 pm
Comments posted to this topic are about the item Understanding Graphical Execution Plans - Part 1: Explaining the Plan
February 18, 2014 at 4:42 am
Nicely done. Absolutely excellent first article.
If I may, I'd like to clarify a couple of points. I wouldn't classify XML as a separate plan type (although I probably did in older editions of my book). The XML plan and the graphical plan are one and the same. It's just how they're displayed.
For when estimated and actual plans differ, again, nice job, but you might have made the point a little differently. The examples you show are likely to have the plan itself be the same between estimated and actual, but with differences in the details. The main cause of differences between estimated and actual plans, when the plans change, is recompiles.
On the parallelism, that's a mistake I made in my first book. It's wrong. There are not two plans. There is one. Sorry.
Again, nice job. Looking forward to the other articles.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 18, 2014 at 8:05 am
Nice post.
Is there a way to determine memory granted to query just before execution and the actual memory it used during runtime. Can we get this information from execution plans.
February 18, 2014 at 9:10 am
Well done on the first article. Also, very useful information from Grant.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 18, 2014 at 10:08 am
"If an object in the query does not exist, the validation fails and no estimated plan is created"
Is something executed in this case, i.e. is there an actual execution plan?
February 20, 2014 at 10:57 am
Thanks for the clarifications, Grant. After researching available material on execution plans, I can appreciate the amount of work that you had to put in to write your book. It is the only real source there is. The material from Microsoft on the topic is almost non-existent.
February 20, 2014 at 11:04 am
rchantler (2/18/2014)
"If an object in the query does not exist, the validation fails and no estimated plan is created"Is something executed in this case, i.e. is there an actual execution plan?
No. You don't get a plan. That's an earlier part of the process than plan creation, so nothing is done from there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2014 at 11:08 am
"If an object in the query does not exist, the validation fails and no estimated plan is created"
If you try to create an estimated execution plan in this case, you will get an error that the object does not exist and no estimated plan will be created. If you specify to include the actual plan when executing, then the SQL will be executed and the actual plan will be displayed.
February 20, 2014 at 11:14 am
"Is there a way to determine memory granted to query just before execution and the actual memory it used during runtime. Can we get this information from execution plans. "
You can determine the memory to some degree. The actual plan will list the estimated row size, the estimated rows and the actual rows. From this you can calculate an estimated amount of memory used by the data returned.
February 13, 2015 at 2:02 am
Nice.
I would like to read about how to use the execution plans to understand what to change in my sql-query, or in the table, to make it better.
I can read and understand a graphical execution plan but I don't understand where, what and how to tweak to make it better.
February 15, 2015 at 5:47 am
kkp40 (2/13/2015)
Nice.I would like to read about how to use the execution plans to understand what to change in my sql-query, or in the table, to make it better.
I can read and understand a graphical execution plan but I don't understand where, what and how to tweak to make it better.
Reading the plans should lead to an understanding of the choices made by the optimizer. Since those choices are determined by the indexes, statistics, constraints and the T-SQL code binding it all together, it should then lead to determining what to do about it. For lots more detail, see the two books in my links below.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 15, 2015 at 10:44 pm
Really nice article 🙂
March 12, 2017 at 7:15 am
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply