XML Plans Saga – Eclipse


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)

Continue reading

Advertisements

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

    XML Plans Saga –Twilight


    XML plans were a new addition in SQL Server 2005 and above which enabled a DBA to fetch additional information from a SQL Server query plan. In this blog post, I shall list out the useful information that can be fetched out of a query plan which can be pertinent to your performance tuning activities. I am old-skool and hence I still prefer analyzing query plans/comparing them using the text plans. However, that doesn’t mean that I ignore XML plans as they have a plethora of information which can be useful for troubleshooting a SQL Server performance problem as well as for query tuning activities.

    As you have already figured out from the post title that this is the first post in the XML Plans Saga. All the information that I need to dole out as part of this series will be covered in four blog posts with a break down as shown in the bulleted list below.

    You can generate XML Showplan output by using the following methods:

  • 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

    XML Showplans are returned in the nvarchar(max) data type for all of these methods, except when you use sys.dm_exec_query_plan. XML Showplans are returned in the xml data type when you use this dynamic management view.

    The XML schema for Showplan is available with the SQL Server installation files at the following location:

    SQL Server 2008 and 2008 R2: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\20047\showplan\showplanxml.xsd
    SQL Server 2005: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\20047\showplan\showplanxml.xsd

    For x64 platforms, the folder would be Program Files (x86) instead of Program Files.

    I am going to pick a simple query to demonstrate the use of XML plans and gather information from the graphic XML plans which are obtained from management studio. The query that I will be using is:

    select top 100 chardata
    from dbo.tblTechEd1
    where id = 1

    Now the above table doesn’t have a clustered index. The table size is approximately 781MB with 100K rows.

  • Continue reading