Yet another X-Ray for the QP

This post is for the absolutely geeky people. For those that will not afraid when they hear: “query transformation rules”.

If you are surprised then look for the awesome Russian SomewhereSomehow series (http://www.somewheresomehow.ru/optimizer-part-1-simplification/) or less awesome /humor detected/ Paul White Great Series (http://sqlblog.com/blogs/paul_white/archive/2012/04/28/query-optimizer-deep-dive-part-1.aspx).

Back to the topic.

Consider the following DB – opt: /sampledbs/

And the following query:

select 
	*
from
	t1
	join t2 on t1.a = t2.b
where 
	t1.c = 10

For those of you, who are not interested in the optimizer – please, stop! The latter material will not give any useful info.

For those of you who are interested in the optimizer internals – welcome.

As we remember – the certain rules are applied on the certain optimization phase. You may know the TF:
TF 8605 – Converted tree
TF 8606 – Simplification and reordering trees
TF 8607 – Physical operators tree

From 2012 – 8619, 8620, 8621 – applied rules tree.

All this – are printed in the message tab.

My discovered approach is a little bit different.

Long story short. Step by step.

1) dbcc traceon(8666,8628) for the session within the query

2) Create a session in Profiler (or x Events) “Showplan XML for Query Compile”.

3) Compile the query and grab the compile event (this will be the one with the plan)

4) Get the plan from the Profiler (not from SSMS)

5) Open as XML

6) Observe the details about the optimization and applied rules:

Here is what you will see:

You will see:

All the stages that the query passed in TimeInfo.

Rules applied per stage, like “” Simplification ” – rules, or the stage Quick plan, or TP etc.

That is not new for those who are deep into the optimizer, but that technique is giving the opportunity to enable the TFs, grab the plans, and analyze them without influencing the queries!

DBCC – grab – analyse!

I think, this post is a good contribution to the debugging query plans, however – that is all undocumented – so, don’t use it in production, unless guided by MS!

P.S.

I should give the credits to Fabiano Neves Amorim (b, t) with his awesome post about 8666 TF – not saying that it was crucial, but definitely guiding – Fabiano, my respect, well done! (BTW, Fabiano’s book about the query operators is one of my favorite, I refer to it from time to time)

PDF