Take the 2-minute tour ×

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 stackoverflow.com 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
The memo is just a hierarchy of groups. If you start at the root group, it's not hard to follow: each group references other groups and there may be one of more alternatives within each group. The memo structure is pruned and expensive alternatives discarded during optimization, so you cannot generally see complete alternatives. There are ways to prevent this and generate more detail, but this is very dangerous so I do not publicize it at all. If the question were expanded to explain exactly what you want to know and why, I might go into more detail in an answer. –  Paul White Feb 24 '13 at 1:35
show 1 more comment

1 Answer

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
add comment

Your Answer


