Databases Reference
In-Depth Information
Optimizer. Similar to the
sys.dm_exec_query_optimizer_info
DMV, you can also
use it to get optimization information for a particular query or workload by taking two
snapshots of the DMV (before and after optimizing your query), and manually finding the
difference between them.
To start looking at this DMV, run the following query:
SELECT
*
FROM
sys
.
dm_exec_query_transformation_stats
Listing 5-4.
Table 5-4 contains a sample output in my test system using SQL Server 2008 R2, showing
the first 10 records out of 377, and edited to fit the page.
Name
promise_avg
Promised
built_substitute
Succeeded
JNtoNL
49
2
0
0
LOJNtoNL
456.1428571
7
7
7
LSJNtoNL
0
0
0
0
LASJNtoNL
0
0
0
0
JNtoSM
454
2
2
2
FOJNtoSM
0
0
0
0
LOJNtoSM
454
7
7
0
ROJNtoSM
454
7
7
0
LSJNtoSM
0
0
0
0
RSJNtoSM
0
0
0
0
Table 5-4.
