locked
generating an execution plan before executing it. RRS feed

  • Question

  • Hi Guys,

     I am trying to capture execution plan through SQL query. I am using the DMV  "sys.dm_exec_cached_plans" to capture the plan. However, I am able to capture the plan when the stored procedure is atleast run once or when i click "display estimated execution plan" only...

    We are planning to capture plans of thousands of stored procedures in our DB. Most of the plans are not in cache. We can't run all the procedures also to generate plans. Is it possible to specify the option of "display estimated execution plan" through programatically???

    Wednesday, April 27, 2011 6:47 PM

Answers

  • Yes, there are three session set options

    ShowPlan_Text, ShowPlan_All, and ShowPlan_XML

    If you set one of them ON, and then execute your stored proc (or any other sql statement), it won't actually execute the code, instead it will just produce and return a representation of the plan.  ShowPlan_Text displays a brief version of the plan, ShowPlan_All does a much more verbose version with lots of additional info, and ShowPlan_XML returns a XML document with all the information.  Clicking "display estimated execution plan" turns on the showplan_xml option, then SSMS gets the XML document and produces the pretty graphics.  AFAIK, you can't get the pretty graphics except by clicking that button because that code is internal to SSMS. 

    You need a "go" after setting one of these options on since that set statement must be the only statement in the batch.  Also, for best results only turn one of them on at the same time.  If you have multiple ones turned on, the results will be confusing.

    Tom

    • Marked as answer by ramireddy Wednesday, April 27, 2011 7:32 PM
    Wednesday, April 27, 2011 7:21 PM

All replies

  • Yes, there are three session set options

    ShowPlan_Text, ShowPlan_All, and ShowPlan_XML

    If you set one of them ON, and then execute your stored proc (or any other sql statement), it won't actually execute the code, instead it will just produce and return a representation of the plan.  ShowPlan_Text displays a brief version of the plan, ShowPlan_All does a much more verbose version with lots of additional info, and ShowPlan_XML returns a XML document with all the information.  Clicking "display estimated execution plan" turns on the showplan_xml option, then SSMS gets the XML document and produces the pretty graphics.  AFAIK, you can't get the pretty graphics except by clicking that button because that code is internal to SSMS. 

    You need a "go" after setting one of these options on since that set statement must be the only statement in the batch.  Also, for best results only turn one of them on at the same time.  If you have multiple ones turned on, the results will be confusing.

    Tom

    • Marked as answer by ramireddy Wednesday, April 27, 2011 7:32 PM
    Wednesday, April 27, 2011 7:21 PM
  • Tom,

    Thanks a lot....Got it.........

     

    Wednesday, April 27, 2011 7:32 PM