XML Plans Saga –New Moon

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