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:

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.

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

• Post Optimization Rewrite – Few Outer Rows Optimization

• Author
• Recent Posts

Dmitry Piliugin

Dmitry is a SQL Server enthusiast from Russia, Moscow. He started his journey to the world of SQL Server more than ten years ago. Most of the time he was involved as a developer of corporate information systems based on the SQL Server data platform.

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