The info like total optimization time, the time spent on each stage of optimization, the number of alternative plans and the cost of each alternative plan. Is there any way to get these info using sql server 2008?

migrated from stackoverflow.com Jan 24 '13 at 20:59

This question came from our site for professional and enthusiast programmers.

  • 2
    See the Query Optimizer Deep Dive series. Particularly the trace flags showing the memo output would help you I think. – Martin Smith Jan 23 '13 at 20:47
  • Thanks Martin. I have read the series but what confuses me is the memo output. There's seldom explanation of the memo output in the series. Do you have any idea where I can refer to for the explanation of the memo output? – Zhan Li Jan 23 '13 at 21:42
  • 2
    Only SQL Kiwi's blog that I know off or playing around with simple queries and working up from there. This is all undocumented AFAIK. – Martin Smith Jan 23 '13 at 21:52
  • Also regarding time spent on each stage see TF 8675 – Martin Smith Feb 3 '13 at 12:54
  • @PaulWhite Hi Paul, thanks for your tips. I'd like to see the number of logical and physical plans during optimization. The reason is that I'd like to find out the relationship between # alternative plans and optimization time. I'd appreciate it if you can help me with that. – zli89 Feb 25 '13 at 18:34

You should check out the SQL Management Studio

It will let you see many things about your query. I know it will show you the cost of executing the query. It will also show you the cost of each step of the execution.



  • Hi Stanislav, thanks for you tips. But is there any way to see all the alternative plans? The methods you provided just displays the final execution plan. I'd like to see the specific steps of the optimizer, what are the alternatives, their cost, when the optimizer discard them. Something like that. – Zhan Li Jan 23 '13 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.