Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Execution Plans Expand / Collapse
Author
Message
Posted Thursday, February 21, 2008 7:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #458555
Posted Thursday, February 21, 2008 9:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:05 PM
Points: 33,165, Visits: 15,299
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
Post #458631
Posted Thursday, February 21, 2008 9:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
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.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #458652
Posted Thursday, April 16, 2009 8:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:01 AM
Points: 4, Visits: 130
[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?"
Post #698534
Posted Monday, August 23, 2010 3:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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)
Post #973283
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse