SQL DBA learning curve

Hi There,

today we will see ..ways to retrieve estimated Execution plan through different option available ..

I’m using 2008 R2 Adventure works database which is below  available link

http://msftdbprodsamples.codeplex.com/releases/view/59211

(for other downloadable database option use THIS)

execution plans can be either estimated or it can be actual execution plan ..

     estimated execution plan :

its how the optimizer thinks will be the best possible way to implement a particular query .

which operators and in what sequence to access the tables …

so the query completes in fastest possible time ,having lowest cost

COST –> CPU cost + IO cost

actual execution plan : is how the storage engine actually executed the query it got from relational engine

relational engine passes on the estimated execution plan to the storage engine …

which uses it as reference to execute the submitted query by user …

Actual execution plan may differ due to the fact .. that statistics are out

of date, execution of some part of query caused recompilation or the

storage engine choose to execute the query in parallel mode …

estimated execution plans are stored in plan cache from where it can be reused …

different ways to retrieve execution plans

A. while we access/retrieve  the estimated execution plan ..

the query is never actually executed ,only estimated

plan is produced by the optimizer..

ways to get estimated execution plan

 1> set Showplan_all ON  (give a text based output of estimated execution plan .. has quite a information )

set Showplan_all OFF (to switch off)

  2> SET  SHOWPLAN_text ON (gives a text based estimated execution plan ,which has lesser information )

compared to  Showplan_ALL,but this is usefull for tools like oSQL.exe which will ready consume the output

set Showplan_Text OFF

3> set Showplan_XML ON (to get the XML based execution plan which much more detailed)

set Showplan_XML OFF

once you click on the SHOWPLANXML link in the result tab .. you will below screen .. with more tooltips

             4> forth option to get the estimated execution plan is SSMS based .. you need to click a button within GUI or

go to SSMS – > Query -> display estimated execution plan or Control + L

in the next post .. we will see ways to retrieve actual execution plan ..

im some cases actual plan may differ from estimated execution plan ..

Thanks for reading ……. ENJOY 🙂