06 | April | 2012 | TroubleshootingSQL

Previously we talked about graphical execution plans (XML plans) in XML Plans Saga –Twilight. Continuing the saga, I will discuss the use of XML plans using T-SQL commands and how to decipher the information from the XML plans obtained.

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

Before I begin the New Moon episode… I do want to point out that reading XML can be a lot of fun and can lead to hair loss! So if you want to make sure that you don’t suffer from premature hair loss, then read on. Last time I discussed how the Management Studio UI can be used to examine graphical query plans and draw conclusions regarding your query performance and plan efficiency! This time around I shall fall back to using plain old T-SQL to generate XML plans and how to make sense out of them!

Continue reading →