Tell me more ×
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. It's 100% free, no registration required.
up vote 4 down vote favorite
1

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
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 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 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 at 21:52
Also regarding time spent on each stage see TF 8675 – Martin Smith Feb 3 at 12:54
2  
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 at 1:35
show 1 more comment

migrated from stackoverflow.com Jan 24 at 20:59

1 Answer

up vote 1 down vote

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.

http://msdn.microsoft.com/en-us/library/ms178071(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms191194(v=sql.105).aspx

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 at 18:18

Your Answer

 
discard

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.