Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execution Plans


Execution Plans

Author
Message
Site Owners
Site Owners
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: 10095 Visits: 1
Comments posted to this topic are about the item Execution Plans
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45394 Visits: 39942
... 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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jamie-2229
Jamie-2229
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2957 Visits: 831
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
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6362 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
Right there with Babe
Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)

Group: General Forum Members
Points: 770 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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4074 Visits: 4514
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
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-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36336 Visits: 18752
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
Right there with Babe
Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)

Group: General Forum Members
Points: 770 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
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2957 Visits: 831
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
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