About these ads

Archive for the ‘Query Tuning’ Category

XML Plans Saga–Breaking Dawn Part 2   2 comments


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!!

About these ads

XML Plans Saga – Breaking Dawn Part 1   1 comment


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   2 comments


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)

Read the rest of this entry »

Follow

Get every new post delivered to your Inbox.

Join 1,247 other followers