Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from Jan 24 '13 at 20:59

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

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
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.

share|improve this answer
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 posting your answer, you agree to the privacy policy and terms of service.

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