The Optimization Process - Inside the SQL Server Query Optimizer - page 145

The sys.dm_exec_query_transformation_stats DMV returns the transformation
rules currently defined in the system, of which there are 377 for the current release of SQL
Server 2008 R2, and includes what is known as their promise information , which tells
the Query Optimizer how useful a given transformation rule might be. The first field in
the results output is the name of the rule; for example, the first three listed are JNtoNL
(Join to Nested Loops Join), LOJNtoNL (Left Outer Join to Nested Loops Join), and
JNtoSM (Join to Sort Merge Join), which is the academic name of the SQL Server Merge
Join operator.
The same issues shown for the sys.dm_exec_query_optimizer_info DMV
regarding collecting data also apply to the sys.dm_exec_query_transformation_
stats DMV, so the following query can help you to isolate the optimization information
for a specific query, while avoiding data from related queries as much as possible. The
query is based on the succeeded column, which keeps track of the number of times a
transformation rule was used and successfully produced a result.
- - optimize these queries now
- - so they do not skew the collected results
GO
SELECT *
INTO before_query_transformation_stats
FROM sys . dm_exec_query_transformation_stats
GO
SELECT *
INTO after_query_transformation_stats
FROM sys . dm_exec_query_transformation_stats
GO
DROP TABLE after_query_transformation_stats
DROP TABLE before_query_transformation_stats
- - real execution starts
GO
SELECT *
INTO before_query_transformation_stats
FROM sys . dm_exec_query_transformation_stats
GO
- - insert your query here
SELECT * FROM dbo . DatabaseLog