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 Saturday, February 16, 2008 1:14 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the item Execution Plans
Post #456651
Posted Saturday, February 16, 2008 10:06 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 37,075, Visits: 31,631
... 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #456687
Posted Monday, February 18, 2008 5:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
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
Post #456865
Posted Monday, February 18, 2008 8:03 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:17 AM
Points: 5,991, Visits: 12,939
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?;)


---------------------------------------------------------------------

Post #456911
Posted Monday, February 18, 2008 8:39 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 10:45 PM
Points: 750, Visits: 3,158
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.
Post #456922
Posted Monday, February 18, 2008 9:26 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 10:01 AM
Points: 3,844, Visits: 3,841
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
Post #456956
Posted Monday, February 18, 2008 12:56 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

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


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 10:25 AM
Points: 33,267, Visits: 15,433
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
Post #457390
Posted Tuesday, February 19, 2008 8:03 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 10:45 PM
Points: 750, Visits: 3,158
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.
Post #457412
Posted Tuesday, February 19, 2008 8:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
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
Post #457425
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse