So far in the two parts released, I have discussed graphical execution plans in XML Plans Saga –Twilight. In the last part: XML Plans Saga –New Moon, I talked about making sense of the large amount of XML that is displayed when you actually look into the XML plan. In this part, I shall talk about using SQL Server Profiler and fetching the same information.
- Selecting Display Estimated Execution Plan or Include Actual Execution Plan from the query editor toolbar in SQL Server Management Studio
- Using the Transact-SQL Showplan SET statement options SHOWPLAN_XML (for estimated plan) and STATISTICS XML (actual execution plan)
- Selecting the SQL Server Profiler event classes Showplan XML (for estimated plan), Showplan XML for Query Compile, and Showplan XML Statistics Profile (actual execution plan) for tracing
- Using the sys.dm_exec_query_plan dynamic management function
Remember that capturing XML plans using profiler traces for extended periods of time does have a performance overhead. And if you are using the client side profiler GUI, then the performance overhead is even greater! When a troubleshooting scenario dictates that XML plans are needed to drill down into the root cause and there are no other options to obtain the same information, then obviously you will be left with no choice but to capture XML Plan profiler events. However, you can minimize the amount of data collected by selecting the appropriate XML plan profiler event. Now that I have cautioned you sufficiently of the performance implications, I will start part three of this saga.
There are three in the family:
Showplan XML For Query Compile: Displays the query plan when the query is compiled. (Trace Event ID = 168)
Showplan XML: Displays the query plan with full data collected during query optimization. This event is generated only when a query plan is optimized. This is equivalent to our estimated plan. (Trace Event ID = 122)
Showplan XML Statistics Profile: Displays the query plan with full run-time details in XML format and can be thought of as the actual execution plan of the query. (Trace Event ID = 146)