This post is for the “optimizer” fan people. For those who are not afraid when they hear: “query transformation rules”. It this is you and you are interested in the optimizer internals – welcome.
Consider the following DB – opt. And the following query:
join t2 on t1.a = t2.b
t1.c = 10
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 Tries
TF 8607 – Physical operators tree
From 2012 – 8619, 8620, 8621 – applied rules tree.
All this – is printed in the message tab.
Recently, I’ve found one more approach, that is a little bit different.
To keep it short, I’ll describe it step by step.
- dbcc traceon(8666,8628) for the session within the query
- Create a session in Profiler (or x Events) “Showplan XML for Query Compile”.
- Compile the query and grab the compile event (this will be the one with the plan)
- Get the plan from the Profiler (not from SSMS)
- Open as XML
- Observe the details about the optimization and applied rules:
Here 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!
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 you are guided by MS.
Next article in this series:
Currently he works as a database developer lead, responsible for the development of production databases in a media research company. He is also an occasional speaker at various community events and tech conferences. His favorite topic to present is about the Query Processor and anything related to it. Dmitry is a Microsoft MVP for Data Platform since 2014.
View all posts by Dmitry Piliugin
Latest posts by Dmitry Piliugin (see all)
- SQL Server 2017: Adaptive Join Internals - April 30, 2018
- SQL Server 2017: How to Get a Parallel Plan - April 28, 2018
- SQL Server 2017: Statistics to Compile a Query Plan - April 28, 2018