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


Execution Plans


Execution Plans

Author
Message
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58773 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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148575 Visits: 19445
Fair enough, I will reword the question.

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
Marios Philippopoulos
Marios Philippopoulos
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12954 Visits: 3766
Jamie Longstreet (2/19/2008)
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.



Here is one good use of execution plans:
Looking for hash-join/index-spool operators.

A hash join/index spool indicates missing indexes on one or more join columns.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
gone2mt-908760
gone2mt-908760
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 143
[quote]GSquared (2/21/2008)

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 ..."
I try to hold use of English to the same standards that I hold use of SQL (or any other programming language).

Actually, the exact question was "Can you ensure that you get the actual execution plan SQL Server will use without executing the query?"
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2759 Visits: 1189
I also got wrong.thinking that asked about esimated execution .it will run without the query.I also ran this estimated execution plan.But i forgot to check Actual execution plan.This question is helpfull.learned one new point.

Malleswarareddy
I.T.Analyst
MCITP(70-451)
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