T-SQL Tuesday #49: Why was SQL waiting?

Posted on December 10, 2013 by Amit Banerjee

A lot of my customer engagements involve identifying waits experienced by SQL Server queries on a production environment. Since this month’s topic is WAITS… I see this as a good opportunity to talk about how I go about the analysis of the collected data to identify the blocking chains.

Before I start rambling off, a note of thanks to my friend Robert Davis [B|T] for hosting this month’s T-SQL Tuesday! He couldn’t have chosen a better topic! 🙂

In this post, I will be talking about how to slice and dice the diagnostic data collected by SQL Perf Stats script that is imported into a SQL Nexus database.

The tables of interest in the SQL Nexus database are:

1. tbl_Requests – This table holds the combined output of the snapshots collected by the Perf Stats script from the sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_connections and sys.dm_os_tasks

2. tbl_NotableActiveQueries – This table holds the output sys.dm_exec_sql_text and sys.dm_exec_query_stats snapshots collected by the Perf Stats script.

The file that needs to be present when SQL Nexus is imported the collected diagnostic data is <server name>__SQL_2008_Perf_Stats_Startup.OUT. The plethora of information available in this file can be mind boggling at times. So pushing all this information into a database makes a lot of sense.

The first order of the day is to determine if there was actually blocking experienced during the data collection. The output of the query will give you collected snapshots where blocking was detected.

<span style="font-size: small;">select runtime,count (*) as blocked_processes
from tbl_requests
where blocking_session_id > 0 and session_id <> blocking_session_id
group by runtime
having count(*) > 1
order by runtime

The next order of the day is to find out which sessions were blocked. The SQL Nexus reports do a good job of drilling down into each blocking session observed. But if you want a collated view of all the blocking along with the list of head blockers, then the following T-SQL script could prove useful especially when the .OUT file being imported is a few hundred MBs in size! This would help you slice and dice the MBs or GBs of data that was imported into the SQL Nexus database tables and get a consolidated view of the blocking chains.

/*
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.

Author: Amit Banerjee

Modified: December 10, 2013

Description:

This T-SQL script extracts information about the blocking chains from the SQL Nexus database

*/

set nocount on
print '================== Blocking Analysis ====================='
print ''
print 'Runtime Information'
select min(runtime) as [Start Time], max(runtime) as [End Time] from tbl_Requests

if ((select count(*) from tbl_requests where blocking_session_id <> 0) > 0)
begin
print 'Blocking found'
print ''
print '============= Blocking Chains (Runtimes considered where blocking_session_id processes > 5) ==================='
-- Show a summary of the # of blocked sessions for every snapshot collected by the PerfStats script
select runtime,count (*) as blocked_processes
from tbl_requests
where blocking_session_id > 0 and session_id <> blocking_session_id
group by runtime
having count(*) > 1 -- The threshold can be changed as appropriate
order by runtime

print ''
print ''
print '================= Head Blocker Information ==================='
if (object_id('tmp_runtimes') <> NULL)
begin
drop table tmp_runtimes
end

-- Create a table to store the runtimes
CREATE TABLE tmp_runtimes(
[row_num] [bigint] IDENTITY(1,1) NOT NULL,
[runtime] datetime
) ON [PRIMARY]

-- Create a list of the distinct runtimes
insert into tmp_runtimes
select distinct (runtime)
from tbl_requests
where blocking_session_id <> 0
order by runtime

-- Start a while loop to fetch the data for the head blocker and blocked sessions
declare @count int,@loop int,@runtime datetime
select @count = count(*) from tmp_runtimes
set @loop = 1
while (@loop <> @count)
begin
select @runtime = runtime from tmp_runtimes where row_num = @loop

print '~~~~~~~~~~~~~~~ Runtime: '
select @runtime
print ''
print '~~~ Head Blocker'

select a.session_id,a.ecid,a.blocking_ecid,wait_type,wait_duration_ms,command,b.dbname,b.procname,b.stmt_text
from tbl_requests a
left outer join tbl_notableactivequeries b
on a.runtime = b.runtime and a.session_id = b.session_id
where blocking_session_id = 0 and a.runtime = @runtime and a.session_id in
(select blocking_session_id
from tbl_requests where blocking_session_id <> 0 and runtime = @runtime)

print ''
print '~~~ Blocked Session Information'
select *
from tbl_requests a
left outer join tbl_notableactivequeries b
on a.runtime = b.runtime and a.session_id = b.session_id
where a.blocking_session_id <> 0
and a.runtime = @runtime
order by a.session_id,a.blocking_session_id

set @loop = @loop + 1

end

drop table tmp_runtimes

end

else
begin
print 'No Blocking found'
end

set nocount off

With the help of the above output, you can get a collated view of all the blocking chains encountered during the data collection period. CSS engineers are Microsoft use SQL Nexus extensively for analyzing diagnostic data collected for SQL Server performance issues. I hope you find this script useful in analyzing blocking chains and do keep the feedback coming in. The above script is available for download on SkyDrive as well.

Please note that the PerfStats script collects data at 10 second intervals by default! If your blocking chains are for shorter durations then you will need to change the waitfor delay interval. If your blocking duration is lesser than a second, then data collection using the DMVs would not be of much help and you would need to go down the path of Extended Events tracing or other invasive forms of data collection (like XPerf utility).

XML Plans Saga–Breaking Dawn Part 2

Posted on April 16, 2012 by Amit Banerjee

I just completed this series and now there are four posts which points out four different ways of tracking down XML plans for troubleshooting or monitoring query performance:

XML Plans Saga –Twilight – A gist of what graphical execution plans look like.
XML Plans Saga –New Moon – Getting your hands dirty with the actual XML document representing the query plan
XML Plans Saga – Eclipse – Retrieving information about query plans using profiler trace events
XML Plans Saga – Breaking Dawn Part 1 – Fetching information from the cached plans in the SQL Server procedure cache

In this blog post (Breaking Dawn Part 2), I shall talk about the latest release in the SQL family… SQL Server 2012. With the new avatar of the Management Studio, you now have an Extended Event (XEvents) configuration wizard. Even though Extended Events have been around since SQL Server 2008, there wasn’t a wizard available for the configuration of the same. I am going to explain how XEvents can be used to capture execution plans and the existing XEvent Viewer in SQL Server 2012 Management Studio (SSMS) gives you a nice view.

I configured a XEvent trace with an asynchronous ring buffer target and captured only the query_post_execution_showplan event. The XEvent defition is shown below:

CREATE EVENT SESSION [QueryPlanCapture]
ON SERVER ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1)
ACTION(sqlos.cpu_id, sqlos.scheduler_id, sqlos.worker_address, sqlserver.database_name, sqlserver.is_system, sqlserver.nt_username, sqlserver.plan_handle, sqlserver.request_id, sqlserver.server_principal_name, sqlserver.session_id, sqlserver.session_nt_username, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.tsql_frame, sqlserver.tsql_stack, sqlserver.username))
ADD TARGET package0.ring_buffer WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=ON, STARTUP_STATE=OFF)
GO

I used the Watch Live Data option in SSMS (Screenshot 1) to view the data being captured by the configured XEvent session active on the server. Now the XEvent viewer gives you a lot of options to aggregate and group data collect by the XEvent session. There are three things that I did with the GUI:

1. I first used the Choose Columns option to pick the columns that I wanted to be available in the events pane.

2. Then I picked the Grouping option to group on the object_name column value. You can only group on events which are visible in the events pane.

3. Then I picked the Aggregation option to provide me with a SUM of duration and cpu_time values for each of the event occurrences.

You can see the final output in Screenshot 2 below.

You can now see that I have all the execution plans for the execution of the stored procedure sp_helpdb. The Details pane actually gives you the option to view the graphical query plan. Another tip is that if you double-click on any of the events in the details pane, then either open up in a new pop-up window or a new query window depending on the field (eg. showplan_xml).

As you see from the Screenshot below, there is hordes of information I can pickup from the output without having to start a profiler trace. Since you get the plan_handle and the actual query plan, you can use the queries from the previous posts in the series to get the set options, missing indexes, compilation and execution statistics for the query as well. However, the aggregation option in the XEvent Viewer gives you a head start on a lot of this activity already.

Hope all of you have some fun reading these posts on XML plans and put all this info to good use in your SQL environments!

Till next time Ciao and happy query plan spotting!!

XML Plans Saga – Breaking Dawn Part 1

Posted on April 15, 2012 by Amit Banerjee

Previously in the series, we saw three parts:

In this post, I am going to talk about the use of Dynamic Management Functions to extract the query plans from the cache using sys.dm_exec_query_plan.

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

Now the first thing that you need to keep in mind is that the cached query plans obtained from the procedure cache using the DMV sys.dm_exec_cached_plans are estimated plans. So you will not be able to fetch runtime information from the cached query plan but you can definitely loads of useful information from even the estimated plan as discussed in my earlier posts in the series. But one of the benefits of writing T-SQL queries is that you have the benefit of DMVs and DMFs that you can leverage and without having to shred XML plans, you can view a large amount of statistics regarding these “cached” plans. Since only information about cached plans are maintained by the database engine, you will not be able to view statistics and plans of queries which were executed but their plans were either not cached or have been flushed out of the cache.

So… Let’s begin the fun… This time around, I shall use a stored procedure to demonstrate the benefit of generating plans from the plan cache and shredding them. You could use sys.dm_exec_query_stats to get various runtime statistics for the cached plans in the SQL Server procedure cache. However, the compilation statistics is something that needs to be fetched from the XML plan itself. If you are interested in the SET options, then these are available from the ouptut of the sys.dm_exec_plan_attributes function.

The stored procedure that I am using to retrieve the information from the procedure cache is shown below:

CREATE PROCEDURE usp_searchNumber
@searchNumber int
AS
SELECT TOP 100 chardata
FROM dbo.tblTechEd1
WHERE id < @searchNumber

Now the sys.dm_exec_plan_attributes will provide me with the SET options and other important attributes of the plan like the execution hits, misses and free cache entries. The SET options of the output are reported as an integer value which makes the deciphering of the same a bit of task with bit mask operations. I have modified the script used in my previous post to fetch information from the XML plan by adding additional parameters to fetch information from the cached plans.

The additional query that I am using in this episode of the series is the query shown below other than the attribute information fetched using the DMF mentioned above. The query below gives me the query plan (estimated) and the T-SQL command(s) for the query. Here I used the query to give me information about the TOP duration plan for a single database. The sys.dm_exec_query_stats DMV gives me useful information about the query plans that I am inspecting which can reduce the overhead of collecting profiler traces to gather the execution statistics. However, the execution statistics are available for plans which were cached ONLY.

SELECT TOP 1 a.refcounts,a.usecounts,a.plan_handle,a.cacheobjtype,a.objtype,
DB_NAME(b.dbid) as database_name,OBJECT_NAME(b.objectid,b.dbid) as [object_name],b.query_plan,
c.text,
d.*
FROM sys.dm_exec_cached_plans a
CROSS APPLY sys.dm_exec_query_plan(plan_handle) b
CROSS APPLY sys.dm_exec_sql_text(plan_handle) c
LEFT OUTER JOIN sys.dm_exec_query_stats d
on a.plan_handle = d.plan_handle
WHERE b.dbid = DB_ID(‘<database name>’) — Replace with correct database name
ORDER BY d.total_elapsed_time DESC

The output is shown below:

Remember that the query plan obtained is the estimated plan and not the actual execution plan. You can also end up writing canned queries to grab the information from the cached query plans by using queries similar to the following:

SELECT
TOP 5 query_stats.query_hash AS “Query Hash”,
query_stats.query_plan_hash AS “Plan Hash”,
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS “Avg CPU Time”,
MIN(query_stats.statement_text) AS “Statement Text”,
COUNT(*) AS DistinctQueries
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
– QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash, query_stats.query_plan_hash
ORDER BY 2 DESC;

The above query gives you all the different queries that were executed which have similar query plan and query text. This can only be used for SQL Server 2008 instances and above as the query plan hash and query hash features are not available in SQL Server 2005 versions and below. Once you have the above output, you can use the query plan hash to get the plan_handle(s) from sys.dm_exec_query_stats DMV output and use the values to get the XML plan and various nuggets of information from the XML using the script below.

/*
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 &quot;AS IS&quot; 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.

Author: Amit Banerjee

Date: April 10, 2012

Description: T-SQL script to fetch plan attribute information, compilation/execution statistics and missing index information from a query plan.

*/

SET NOCOUNT ON

GO

-- Insert XML plan below or modify the query below to fetch the TOP 1 plan based on database and CPU or duration or reads or writes.

-- Replace single quote (') with double quotes ('') using REPLACE option

declare @xmlplan xml = N''

declare @plan_handle varbinary (64)

-- Using CAST, since = operation cannot be used with XML datatypes

IF (CAST(@xmlplan AS varchar(10)) = '')

BEGIN

SELECT TOP 1 @xmlplan=b.query_plan, @plan_handle = d.plan_handle

FROM sys.dm_exec_cached_plans a

CROSS APPLY sys.dm_exec_query_plan(plan_handle) b

LEFT OUTER JOIN sys.dm_exec_query_stats d

on a.plan_handle = d.plan_handle

WHERE b.dbid = DB_ID('dbTechEd_1')

ORDER BY d.total_elapsed_time DESC

END

SELECT attribute,value,is_cache_key FROM sys.dm_exec_plan_attributes(@plan_handle)

SELECT TOP 1 a.refcounts,a.usecounts,a.plan_handle,a.cacheobjtype,a.objtype,

DB_NAME(b.dbid) as database_name,OBJECT_NAME(b.objectid,b.dbid) as [object_name],b.query_plan,

c.text,

d.*

FROM sys.dm_exec_cached_plans a

CROSS APPLY sys.dm_exec_query_plan(plan_handle) b

CROSS APPLY sys.dm_exec_sql_text(plan_handle) c

INNER JOIN sys.dm_exec_query_stats d

on a.plan_handle = d.plan_handle

WHERE b.dbid = DB_ID('dbTechEd_1') -- Replace with correct database name

ORDER BY d.total_elapsed_time DESC

;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

stmt.param_details.value('(@Column)[1]', 'varchar(10)') as [Parameter Name],

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

stmt.param_details.value('(@ParameterRuntimeValue)[1]', 'varchar(255)') 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

-- 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 &lt;ncidx_&lt;name&gt; 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

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 &lt;index name&gt;' + ' 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

As you have noticed, this is Part 1 of breaking dawn… The last post on the XML Plans Saga will be Breaking Dawn Part 2 and stay tuned for some more fun with XML plans in that post.

XML Plans Saga – Eclipse

Posted on April 7, 2012 by Amit Banerjee

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 →

XML Plans Saga –New Moon

Posted on April 6, 2012 by Amit Banerjee

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 →