SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execution Plans


Execution Plans

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85517 Visits: 41081
... and when it comes to % of batch, it can still lie!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3131 Visits: 836
This one threw me. I use CTRL - L constantly for the estimated execution plan and never once tried to run an actual execution plan. There is no need to run the query to get an estimated plan. Anyone else?

Jamie
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10292 Visits: 13687
Drat, you got me. I over thought myself, I knew it was an estimated plan, but then I was thinking surely most times the estimated plan is the actual plan used, so perhaps the answer was yes.

So how often is the actual plan different to the estimated, and how often does it need to be different (and by how much) to say not actually running the query cannot give you the execution plan?Wink

---------------------------------------------------------------------
matt stockham
matt stockham
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3178
Although I chose no, sometimes the answer is yes; DMVs can give you the cached plan, which will be the actual plan if nothing forces a recompile and the statement will use that cached plan.
John Rowan
John Rowan
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6008 Visits: 4550
I missed this one too. I was under the impression that SET SHOWPLAN ALL gave you the actual (not estimated) execution plan without executing the query and returning results.

From BOL:
Causes Microsoft® SQL Server™ not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.


Can anyone shed some light on this?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23371 Visits: 9730
Yet again, the person asking the question didn't think it through.

Yes, you can get the actual execution plan without executing the query. For example, get the execution plan from the system views. For example, if the estimated plan ends up being the actual plan (more often than not true on simple queries). So, yes you can. Just not in the way he/she thought.

- 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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62152 Visits: 19102
You cannot get the actual execution plan in all cases, so the statement is false. Even if you query the system views, you get the estimated execution plan. That's still estimated, and could change if conditions on the server change between your estimated run and the actual query execution.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
matt stockham
matt stockham
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 3178
Well the question didn't say always, but OK.

You can still use plan guides, query hints etc to force a plan. Unless the schema has changed to make it an invalid plan, that will be the plan that executes.
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3131 Visits: 836
The question brought to light that circumstances exist where the estimated plan and the actual plan can be different. I'd be interested in seeing more topics on how to actually implement the plan for purposes other than what I currently use it for - which is to check on whether there is an existing index available for a given query.

Jamie
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23371 Visits: 9730
Steve Jones - Editor (2/19/2008)
You cannot get the actual execution plan in all cases, so the statement is false. Even if you query the system views, you get the estimated execution plan. That's still estimated, and could change if conditions on the server change between your estimated run and the actual query execution.


The exact question was, "Can you get the actual execution plan SQL Server will use without executing the query?" It does not say, "is it possible in all cases to get the actual execution plan without running the query?", it says "Can you..." That means, in standard English parsing, "is it ever possible ..."

For example, to argue the case, if I ask, "On a trampoline, can you jump more than 3 feet vertically?", the answer is "yes". The fact that not all jumps will necessarily be more than 3 feet does not change the answer to "no". It doesn't even matter if you never get on a trampoline, or never jump higher than 2 inches if you do get on one, the very fact that it is possible answers the question of "can you" as "yes".

That's standard use of the English language.

If the question required an answer about all possible cases, it should have been worded differently.

I try to hold use of English to the same standards that I hold use of SQL (or any other programming language).

If someone handed you a query:

select *
from dbo.Table
where Col1 = 0

and asked if that query will give you the rows with 0 in Col1, would you say, "no", because there might not be any rows with 0 in that column? No. You'd say, "yes", because that's what the query is obviously designed to do. It's very precise, very exact, and has a definite meaning.

Using "can you" as "Is there a way to always", is the same as using:

select *
from dbo.Table
if Col1 = 0

It can be read by a human being as having a syntactically identical meaning, but in the language it is being presented in, it's incorrect. (It's always amusing to me that some of the very people who will stand up firmly for standards in their code use and database design, will reject the idea that standards apply to English. Not saying that applies here, just an amusing observation.)

- 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search