Tag Archives: Query Plan

SQL Server 2016 Public Preview (CTP2) – Live Query Statistics

Posted on by

I have spent a lot of time at customer locations working on resolving SQL Server performance issues on business critical environments. This also involves helping customers understand how to track down the performance bottlenecks and the remediation steps that need to be taken to remove identified performance bottlenecks. This involves two kinds of troubleshooting: post-mortem analysis and troubleshooting the issue as and when it is happening! Yes, I am talking about live troubleshooting which is a scary thing to do on a production server.

So if you share my deep rooted passion for working on SQL Server performance issues, the Live Query Statistics feature in SQL Server 2016 CTP2 is definitely worth knowing more about!

The Live Query Statistics can be enabled in one of the following ways:

1. Using Management Studio

The screenshot below shows the button which enables the Live Query Statistics. This can be a very powerful tool to troubleshoot bad query performance while the query is actually executing. You actually get insights into the plan and which part of the plan is actually taking time while the query executes.

Live Query Stats button on toolbar

You get a host of information from the Live Query Statistics as seen in the screenshot below. You will be able to pin point the part of the plan which is the culprit because you will have the completion percentage statistics for each and every operator in the plan. The completed operators show you the efficient parts of the plan. Additionally, you also get the time spent in each operator which gives you statistics for identifying the most time consuming part of the plan. And what’s more, you even know how much of the query is completed. This is one of the most common questions that I used to receive from customers while troubleshooting long running queries: “How long will the query take to complete“? Well now, there is an answer!

image

2. Use Activity Monitor

A new grid has been added in Activity Monitor named “Active Expensive Queries” which allows you to right-click a query and click on the “Show Live Execution Plan” option. Live Query Stats button in Activity Monitor

However, the “Show Live Execution Plan” option will only be enabled if the application  or user:

  • Executes SET STATISTICS XML ON; or SET STATISTICS PROFILE ON; in the target session.

  • The query_post_execution_showplan extended event has been enabled. This is a server wide setting that enable live query statistics on all sessions

And if you are developer, then you can use this feature in conjunction with the Transact-SQL debugger and pin point slow parts of the execution plan while the query is running. A truly powerful way to write and optimize queries! The debugging experience is now enhanced as the live query plan can be used along with breakpoints! The screenshot below shows what the debugging experience would look like.

image

Do keep in mind that this feature is provided for troubleshooting/debugging slow running query scenarios and should not be kept enabled in production environments. There is a moderate performance impact for collecting and displaying the above mentioned statistics.

Reference:

Live Query Statistics
https://msdn.microsoft.com/en-us/library/dn831878.aspx 

Disclaimer: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Advertisement

View query plans with non admin accounts

Posted on by

A question was posted on Twitter’s #sqlhelp hashtag on how to allow non-sysadmin accounts or accounts which do not have the View Server State privilege to fetch the cached query plans. This can be a very big deal if you are a hosting company for databases. You would want to restrict access to all non-admin users but at the same time you might need to give your customers and their developers access to their query plans to assist with their query tuning efforts.

The first thing that comes to mind is to use impersonation in SQL Server. Implementing it is where the fun is!

Let’s consider this hypothetical scenario. I have a SQL Server login named Sentinel which would be granted View Server State privileges only. I have another SQL Server login named Agent which is neither a sysadmin nor a login with View Server State privileges. The Agent login need to look up plans of stored procedures which are executing in the current database.

The first task would be to grant View Server State privileges to Sentinel and impersonate privileges to Agent.

USE [master]

GO

GRANT VIEW SERVER STATE TO [sentinel];

GRANT IMPERSONATE ON LOGIN::[sentinel] to [agent];

GO

Next I create a stored procedure which allows the login agent to see the cached plans which are available for the current database context.

CREATE PROCEDURE [dbo].[usp_SeePlan]

AS

BEGIN

    SET NOCOUNT ON

    EXECUTE AS LOGIN = ‘sentinel’;

    SELECT ‘Executed as user: ‘ + SUSER_NAME()

    SELECT * FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_query_plan (plan_handle) qp

    CROSS APPLY sys.dm_exec_sql_text (plan_handle) qt

    WHERE qp.dbid = db_id();

    /* Add your diagnostic query here

    The above query is an example */

    REVERT;

    SELECT ‘Reverted back to user: ‘ + SUSER_NAME()

END

Next when I attempt to grant permissions to the login agent to execute the above stored procedure, I will get the error shown below:

Msg 15151, Level 16, State 1, Line 1
Cannot find the user ‘agent’, because it does not exist or you do not have permission.

This error is reported because there is no database user mapped to the login agent. So I create a database user for agent and grant execute permissions to the login agent for the stored procedure using the commands below:

CREATE USER [agent] FOR LOGIN [agent] WITH DEFAULT_SCHEMA=[dbo];
GRANT EXECUTE ON OBJECT::[usp_SeePlan] TO agent;

Next I will try to execute the stored procedure with the agent login. Everything should work now! But alas.. I get another error as shown below:

Msg 916, Level 14, State 1, Procedure usp_SeePlan, Line 5
The server principal “sentinel” is not able to access the database “Test” under the current security context.

This is because of the fact the login sentinel does not exist in the database Test in which the stored procedure exists. Next I will create a database user mapped to the login sentinel.

CREATE USER [sentinel] FOR LOGIN [sentinel] WITH DEFAULT_SCHEMA=[dbo]

Now when I used the stored procedure, everything works!! Screenshot of the output is shown below.

image
A few other caveats to note is that if the stored procedure is created with the WITH EXECUTE AS option, then on execution of the procedure, you will be reported with the following error:

Msg 297, Level 16, State 1, Procedure usp_SeePlan, Line 8
The user does not have permission to perform this action.

So the summary is the following sequence:

1. Grant impersonate rights to Agent for Sentinel

2. Create a stored procedure which does the work which requires View Server State privilege using the EXECUTE AS LOGIN = ‘Sentinel’ statement

3. Create two database users in the database which are mapped to Agent and Sentinel

4. Grant execute privileges on the stored procedure which was created in Step #2.

Voila… You are now ready to impersonate and view the query plan!

XML Plans Saga–Breaking Dawn Part 2

Posted on by

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

image_thumb10I 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).

image_thumb18

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. image_thumb12

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 by

Previously in the series, we saw three parts:

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

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:

image

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 by

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 →