February 14, 2012 at 10:54 am
The execution plan is usually (or always a GUI), right? And the query optimizer is a sub-component of this execution plan? Or are individual components of the DBMS all blurry in SSMS 2008?
February 14, 2012 at 11:28 am
The execution plan, as shown to you in SSMS is a visual rendering of XML. The optimizer which comes up with the plan is not a part of that plan as shown but is the source of creating that plan. You can glean some information about what the optimizer was "thinking" when looking at the properties of the components in the plan. You can also see how long it took the optimizer to come up with that plan by executing the query with SET STATISTICS TIME ON. Look at the parse and compile time in the output.
Grant Fritchey (@ScaryDBA) has a good free book on the execution plan to get you started. I think it is up on the Simple-Talk website.
Hope this helps to partially answer your question.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 14, 2012 at 12:09 pm
Execution plans can be in text format or XML format. Behind the scenes, there's going to be a binary format, but I don't think there's a way (or reason) to view that directly.
The optimizer isn't part of the plan, it's what creates the plan. Calling it part of the plan would be like calling you part of the execution plan, if you wrote the query. You decided what the query should do, the optimizer figures out how it should be done in the engine, and the plan is the result of the optimizer (usually, there are ways to force the plan).
The visual representation of the plan, when you select "Display Estimated Execution Plan" or "Include Actual Execution Plan" is in the GUI, and is derived from the XML version of the plan. That isn't the plan itself, any more than selecting rows from a table is the table. It's a visual representation of the plan.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2012 at 5:21 am
You can also see the compile time values in the execution plan itself. Always check the first operator (SELECT for a query, INSERT/UPDATE/DELETE, as appropriate). That shows compile time parameter values, whether or not the optimizer completed successfully or timed out, all kinds of great information. All that is in the properties.
And yeah, if you're interested in more, the book is a little long in the tooth and has some errors, but it'll get you started. Dead tree version is linked below or you can do a search to find the free digital version.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply