XML Plans Saga – Eclipse | TroubleshootingSQL

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)

When configuring the profiler trace from the GUI, you can choose to save the extracted XML plans in a separate folder. This is only possible when using the client side profiler UI and cannot be done when you are using a server side trace. If you configure the profiler trace to extract the XML plans and then script out the trace definition, you will notice that the .sql file containing the trace definition has the following: “– Client side File and Table cannot be scripted”. So as mentioned, the extraction option is available when using the UI only.

Reference on how to save the XML plans separately:

Save Showplan XML Statistics Profile Events Separately (SQL Server Profiler)
Save Showplan XML Events Separately (SQL Server Profiler)

I shall use the same query for this post as well:

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

I captured a profiler trace with all the three events and lets look through each one. Enter the Showplan XML Query Compile event. This is one event that cannot be generated using T-SQL or directly from the Management Studio. You need to capture this using a profiler trace preferably a server side trace. Now if you look at Screenshot 1, you will see that the event was generated before the SQL: BatchStarting event. Now the plan in the Profiler UI looks the same as the one see in the Management Studio.

Now if you look at Screenshot 2, you will notice that the right-click context menu provides an option: Extract Event Data. This option can be used to save the plan onto your file system with the extension .sqlplan which will open up as a Graphical Query Plan in the Management Studio UI. Now I had already configured the trace to extract the profiler events for the XML plans into a separate folder. Note that the Profiler UI will not show the Missing Index details for the query, if any. You will either have to extract the information by extracting the plan into a .sqlplan file or by insert into a database table and querying the XML data. When I open the saved .sqlplan in the Management Studio UI, I do see the Missing Index recommendation (refer previous posts). The XML plan is similar to the estimated plan and the QueryPlan node of the XML document will have the Compile time, memory and CPU usage details. However, the StmtSimple node will not have the SQL query.

The query text can be retrieved if you open extract the event data and save it as a .sqlplan document. When you open the document in profiler, you can see the query in the UI. Additionally, the profiler event for all XML plans will have a Transaction ID which you can use to tie it back to your SQL statement/batch/procedure events captured in the profiler trace. You can use the T-SQL queries in a previous post of mine to extract the compile statistics of a query from either a profiler trace or from the procedure cache. The Showplan XML profiler event is similar in nature and gives you similar statistics and information as the estimated execution plan. The most interesting is the Showplan XML Statistics Profile event which can be captured and supersedes the other two XML plan events for situations where you are troubleshooting query slowness issues and NOT when the query doesn’t complete or there are timeouts. In such cases you would need Showplan XML events. For the old school folks, Showplan XML Statistics Profile is the equivalent of the Showplan Statistics Profile event but comes with a fancy well-formed XML document. This gives you the same information as the Actual Execution Plan or the plan generated with SET STATISTICS XML ON option.

All the XML data will be stored in the TextData column value if the profiler data is imported into a SQL Server database table.

Now for the fun part. Let’s see how we can use T-SQL to parse these XML plans and retrieve from useful information. With the query below, I can get the Statement Compilation and Set Options details. Also, I can get the parameters used to compile and execute the query (works only for actual execution plans). And lastly, I get the missing index information along with the CREATE INDEX which is a modification of Jonathan’s post on fetching missing index information from cached plans. See Screenshot 3 for the output details.

/*
This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
*/
SET NOCOUNT ON

GO

declare @xmlplan xml = N'' -- Insert XML plan here. Replace single quote (') with double quotes ('') using REPLACE option</pre>

-- In case you already have a plan handle
-- select @xmlplan=query_plan from sys.dm_exec_query_plan(<insert plan handle>)

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)

SELECT

-- Compilation and execution statistics

-- -- -1 denotes that the value was not found

ISNULL(stmt.stmt_details.value('(./sp:QueryPlan/@DegreeOfParallelism)[1]', 'int'),-1) as [Degree of Parallelism],

-- -1 denotes that the value was not found

ISNULL(stmt.stmt_details.value('(./sp:QueryPlan/@MemoryGrant)[1]', 'bigint'),-1) as [Memory Grant (KB)],

stmt.stmt_details.value('(./sp:QueryPlan/@CompileMemory)[1]', 'bigint') as [Compile memory (KB)],

stmt.stmt_details.value('(./sp:QueryPlan/@CompileTime)[1]', 'bigint') as [Compile time (ms)],

stmt.stmt_details.value('(./sp:QueryPlan/@CompileCPU)[1]', 'bigint') as [Compile cpu (ms)],

stmt.stmt_details.value('(./sp:QueryPlan/@CachedPlanSize)[1]', 'bigint') as [Cache Plan Size],

stmt.stmt_details.value('(../sp:StmtSimple/@StatementOptmLevel)[1]', 'varchar(25)') as [Optimization Level],

ISNULL(stmt.stmt_details.value('(../sp:StmtSimple/@StatementOptmEarlyAbortReason)[1]', 'varchar(50)'),'Not available') as [Optimization Level],

-- SET options

UPPER(stmt.stmt_details.value('(./sp:StatementSetOptions/@ANSI_NULLS)[1]', 'varchar(10)')) as [ANSI_NULLS],

UPPER(stmt.stmt_details.value('(./sp:StatementSetOptions/@ANSI_PADDING)[1]', 'varchar(10)')) as [ANSI_PADDING],

UPPER(stmt.stmt_details.value('(./sp:StatementSetOptions/@ANSI_WARNINGS)[1]', 'varchar(10)')) as [ANSI_WARNINGS],

UPPER(stmt.stmt_details.value('(./sp:StatementSetOptions/@ARITHABORT)[1]', 'varchar(10)')) as [ARITHABORT],

UPPER(stmt.stmt_details.value('(./sp:StatementSetOptions/@CONCAT_NULL_YIELDS_NULL)[1]', 'varchar(10)')) as [CONCAT_NULL_YIELDS_NULL],

UPPER(stmt.stmt_details.value('(./sp:StatementSetOptions/@NUMERIC_ROUNDABORT)[1]', 'varchar(10)')) as [NUMERIC_ROUNDABORT],

UPPER(stmt.stmt_details.value('(./sp:StatementSetOptions/@QUOTED_IDENTIFIER)[1]', 'varchar(10)')) as [QUOTED_IDENTIFIER],

-- The statement text will only be returned for XML plans saved from SSMS GUI

REPLACE (REPLACE (stmt.stmt_details.value('@StatementText', 'nvarchar(max)'), CHAR(10), ' '), CHAR(13), ' ') as [Query Text]

FROM (SELECT @xmlplan as showplan) as p

CROSS APPLY showplan.nodes('//sp:StmtSimple') as stmt (stmt_details)

-- Get the parameter compilation values and runtime values if exists

;WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)

SELECT
-- Limited to 255 characters for parameter name and 4000 characters for parameter values based on the query below.
-- If you use large strings as parameter names, then the output will be truncated.
stmt.param_details.value('(@Column)[1]', 'varchar(255)') as [Parameter Name],

stmt.param_details.value('(@ParameterCompiledValue)[1]', 'nvarchar(4000)') as [Compiled Parameter Value],

stmt.param_details.value('(@ParameterRuntimeValue)[1]', 'nvarchar(4000)') as [Runtime Parameter Value]

FROM (SELECT @xmlplan as showplan) as p

CROSS APPLY showplan.nodes('//sp:ColumnReference') as stmt (param_details)

--WHERE stmt.stmt_details.value('(@ParameterCompiledValue)[1]', 'varchar(10)') IS NOT NULL

WHERE param_details.exist('@ParameterCompiledValue') = 1

GO
-- Get missing index information if exists

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT

n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS [Impact],

DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS [Database_ID],

OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS [OBJECT_id],

n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)') AS [Object],

'CREATE INDEX <ncidx_<name> ON ' + n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +

n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)') + ' ( ' +

-- Equality list

(SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)')

FROM n.nodes('//ColumnGroup') AS t(cg)

CROSS APPLY cg.nodes('Column') AS r(c)

WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY'

FOR XML PATH('')

) AS equality_columns,

-- Inequality list

(SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)')

FROM n.nodes('//ColumnGroup') AS t(cg)

CROSS APPLY cg.nodes('Column') AS r(c)

WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY'

FOR XML PATH('')

) AS inequality_columns,

-- Included column list

(SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '

FROM n.nodes('//ColumnGroup') AS t(cg)

CROSS APPLY cg.nodes('Column') AS r(c)

WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'

FOR XML PATH('')

) AS include_columns

INTO #MissingIndexes

FROM (SELECT @xmlplan as showplan) AS tab (query_plan)

CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)

WHERE n.exist('QueryPlan/MissingIndexes') = 1

&nbsp;

IF EXISTS (SELECT TOP 1 impact FROM #MissingIndexes)

BEGIN

-- Get rid of trailing commas

UPDATE #MissingIndexes

SET equality_columns = LEFT(equality_columns,LEN(equality_columns)-1),

inequality_columns = LEFT(inequality_columns,LEN(inequality_columns)-1),

include_columns = LEFT(include_columns,LEN(include_columns)-1)


-- Display the missing index information

SELECT Impact, Database_ID as [Database ID], OBJECT_id as [Object ID], OBJECT as [Object Name],

'CREATE INDEX <index name>' + ' ON '+ OBJECT + ' (' + ISNULL (equality_columns,'') + 
CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL 
THEN ',' 
ELSE '' 
END + ISNULL (inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + include_columns + ')', '')

AS [Create Index Command]

FROM #MissingIndexes

END

DROP TABLE #MissingIndexes
GO
SET NOCOUNT OFF

GO

Expand the code box above to get the T-SQL script to fetch the information as shown in Screenshot 3 from a Query Plan.

Modification: April 13th, 2012: Correct trailing commas issue in the above T-SQL script

Till my next post the Breaking Dawn, where I talk about more fancy stuff using cached plans, I bid adieu!