SQL Server – dm_exec_query_plan – shredding – Part 1

Background

As it is apt to happen daily these days, I was reviewing WordPress Stats and found one of the postings that someone had clicked on.

It is titled “SQL Server – Query Stats – What Queries are dragging your system down“. And, it is available here.

SQL Server – Query Stats – What Queries are dragging your system down?

The crust of the blog is the SQL Code pasted below.

Code


select
      tblQueryStat.total_worker_time
    , tblQueryStat.total_elapsed_time                 
    , tblQueryStat.total_rows  
    , tblQueryStat.total_physical_reads
    , tblQueryStat.total_logical_reads      
    , tblQueryStat.total_logical_writes
    , tblSQLText.text
    , [numberofRowsEstimated]
              = tblQueryPlan.[query_plan].value
             (
                    '(//@EstimateRows)[1]'
                  , 'varchar(128)'
             ) 
    , tblQueryStat.last_rows as nunberofRowsActual
    , tblQueryStat.execution_count
    , tblQueryStat.plan_generation_num
 
from   sys.dm_exec_query_stats tblQueryStat
 
cross apply sys.[dm_exec_sql_text](tblQueryStat.sql_handle) 
      as tblSQLText
 
cross apply sys.[dm_exec_query_plan](tblQueryStat.plan_handle) 
      as tblQueryPlan
 
order by
     tblQueryStat.total_worker_time desc

What does the Code do:

  1. It accesses the following DMVs
    • sys.dm_exec_query_stats
      • Query Stats
    • sys.dm_exec_sql_text
      • SQL Text
    • sys.dm_exec_query_plan
      • Query Plan
  2. Orders the results based on
    •  sys.dm_exec_query_stats.total_worker_time

 

Focus

The one area that I will like to dig a bit further into is:

 

    [numberofRowsEstimated]
        = tblQueryPlan.[query_plan].value ( '(//@EstimateRows)[1]' , 'varchar(128)' ) 

And, the areas that I will like to consider are:

  1. .value( ‘(//@EstimateRows)[1]’, varchar(128)’)
    • //@EstimateRows[1] seems ambigious
    • Can we tighten it up

Approach

To play around a bit more rigorously we will offload the query plan from the DMV into a custom table.  And, target our queries at the custom table.

  1. Prepare a table [dbaDMV].[dm_exec_query_plan]
    • Create indexes on the XML Column
  2. Cache a point in time copy of sys.dm_exec_cached_plans into [dbaDMV].[dm_exec_query_plan]
  3. Launch a series of queries against [dbaDMV].[dm_exec_query_plan] and see where the bottlenecks are

 

Table – dbaDMV.dm_exec_query_plan

Here we create the table – dbaDMV.dm_exec_query_plan.


set noexec off
go

SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
GO

use [master]
go

if db_id('DBUtility') is null
begin

	exec('create database [DBUtility]')

end
go

use [DBUtility]
go

if schema_id('dbaDMV') is null
begin

	exec('create schema [dbaDMV] authorization [dbo] ')

end
go

if object_id('[dbaDMV].[dm_exec_query_plan]') is not null
begin
	set noexec on
end
go

/*
	drop table [dbaDMV].[dm_exec_query_plan]
*/
create table [dbaDMV].[dm_exec_query_plan]
(

      [identity]	bigint not null 
						identity(1,1)

	, [dbid]		smallint

	, [objectid]	int

	, [number]			smallint

	, [encrypted]	bit 

	, [query_plan]	xml

	, [plan_handle] varbinary(64)

	, constraint [PK_DM_EXEC_QUERYPLAN]
		primary key
		(
			[identity]
		)
)
go

set noexec off
go


IF NOT EXISTS 
(
    SELECT * 
    FROM   sys.indexes 
    WHERE  object_id = OBJECT_ID(N'[dbaDMV].[dm_exec_query_plan]') 
    AND    name = N'INDX_PlanHandle'
)
begin
 
    CREATE INDEX [INDX_PlanHandle] 
    ON [dbaDMV].[dm_exec_query_plan]
    (
        [plan_handle]
    )
    WITH
    (
          PAD_INDEX = OFF
        , STATISTICS_NORECOMPUTE = OFF
        , SORT_IN_TEMPDB = OFF
        , DROP_EXISTING = OFF
        , ONLINE = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
    )
    ;
 
end
GO


INDEX – XML – PRIMARY INDX_DBA_XML_MAIN

Here we index the table.

The first XML index is the primary index.



IF NOT EXISTS 
(
    SELECT * 
    FROM   sys.indexes 
    WHERE  object_id = OBJECT_ID(N'[dbaDMV].[dm_exec_query_plan]') 
    AND    name = N'INDX_DBA_XML_MAIN'
)
begin
 
    CREATE PRIMARY XML INDEX [INDX_DBA_XML_MAIN] 
    ON [dbaDMV].[dm_exec_query_plan]
    (
        [query_plan]
    )
    WITH
    (
          PAD_INDEX = OFF
        , STATISTICS_NORECOMPUTE = OFF
        , SORT_IN_TEMPDB = OFF
        , DROP_EXISTING = OFF
        , ONLINE = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
    )
    ;
 
end
GO

INDEX – XML – SECONDARY – PATH – INDX_DBA_XML_PATH



IF NOT EXISTS 
(
    SELECT * 
    FROM   sys.indexes 
    WHERE  object_id = OBJECT_ID(N'[dbaDMV].[dm_exec_query_plan]') 
    AND    name = N'INDX_DBA_XML_PATH'
)
begin
 
    CREATE XML INDEX [INDX_DBA_XML_PATH] 
    ON [dbaDMV].[dm_exec_query_plan]
    (
        [query_plan]
    )
	USING XML INDEX [INDX_DBA_XML_MAIN]
	FOR PATH    
    ;
 
end
GO
 

INDEX – XML – SECONDARY – PROPERTY – INDX_DBA_XML_PROPERTY



IF NOT EXISTS 
(
    SELECT * 
    FROM   sys.indexes 
    WHERE  object_id = OBJECT_ID(N'[dbaDMV].[dm_exec_query_plan]') 
    AND    name = N'INDX_DBA_XML_Property'
)
begin
 
    CREATE XML INDEX [INDX_DBA_XML_Property] 
    ON [dbaDMV].[dm_exec_query_plan]
    (
        [query_plan]
    )
	USING XML INDEX [INDX_DBA_XML_MAIN]
	FOR PROPERTY    
    ;
 
end
GO
 

INDEX – XML – SECONDARY – VALUE – INDX_DBA_XML_VALUE



IF NOT EXISTS 
(
    SELECT * 
    FROM   sys.indexes 
    WHERE  object_id = OBJECT_ID(N'[dbaDMV].[dm_exec_query_plan]') 
    AND    name = N'INDX_DBA_XML_Value'
)
begin
 
    CREATE XML INDEX [INDX_DBA_XML_Value] 
    ON [dbaDMV].[dm_exec_query_plan]
    (
        [query_plan]
    )
	USING XML INDEX [INDX_DBA_XML_MAIN]
	FOR VALUE    
    ;
 
end
GO
 
 
 

Cache Data – Populate – sys.dm_exec_cached_plans into dbaDMV.dm_exec_query_plan



set nocount on;

declare @iMaxNumberofRows int

set @iMaxNumberofRows = 1E6

truncate table [dbaDMV].[dm_exec_query_plan]

insert into [dbaDMV].[dm_exec_query_plan]
(
   	  [dbid]
	, [objectid]
	, [number]
	, [encrypted]
	, [query_plan]
	, [plan_handle]
)
select top (@iMaxNumberofRows)
		  tblQP.[dbid]
		, tblQP.[objectid]
		, tblQP.[number]
		, tblQP.[encrypted]
		, tblQP.[query_plan]
		, tblCP.[plan_handle]

from   sys.dm_exec_cached_plans tblCP

cross apply sys.[dm_exec_query_plan](tblCP.plan_handle) as tblQP


Metrics

dm_exec_query_plan

Explanation

  1. Took 4 minutes on our test system
  2. 9700 records was copied over
  3. The storage utilized in 83 MB

 

dbaDMV.dm_exec_query_plan – Index – Review

Metadata

 

Code

 
declare @tableName sysname
declare @objectID int
 
set @tableName = '[dbaDMV].[dm_exec_query_plan]'
set @objectID = object_id(@tableName)
 
 
select
 
          [tableName] = schema_name(tblT.schema_id)
                             + '.'
                             + tblT.[name]
 
        , [indexName] = tblI.name
 
        , [indexID]
            = tblI.index_id
 
        , [typeLit]
            =  max(tblI.type_desc)

        , [internalTableName]   
            = isNull(tblIT.name, '')
 
 
from sys.tables tblT
 
inner join sys.internal_tables tblIT

    on tblT.object_id = tblIT.parent_object_id
 
JOIN sys.indexes tblI
 
    ON tblT.object_id = tblI.object_id

 
where tblT.object_id = @objectID
 
GROUP BY
          tblT.schema_id
        , tblT.name
 
        , tblI.name
        , tblI.index_id
 
        , tblIT.object_id
        , tblIT.parent_object_id
        , isNull(tblIT.name, '')
 

order by
          tblT.schema_id
        , tblT.name
        , tblI.index_id

 

Output

Index-Metdata

 

Size

Code

declare @tableName sysname
declare @objectID int
 
set @tableName = '[dbaDMV].[dm_exec_query_plan]'
set @objectID = object_id(@tableName)
 
SELECT
 
         [ObjectId]
 
        ,[ObjectName]
 
        ,[IndexId]
 
        ,[indexName]
 
        ,[IndexType]
 
        ,indexLevel
 
        ,[IndexSize(MB)]
            = CONVERT
                (
                      DECIMAL(16, 1)
                    , (SUM([avg_record_size_in_bytes] * [record_count]) / (1024.0 * 1024))
                )
 
        ,[StatisticLastUpdated]
            = max([lastupdated])
 
        , [AvgFragmentationInPercent]
            = avg([AvgFragmentationInPercent])
 
FROM (
 
        SELECT
               ObjectID = tblInd.OBJECT_ID
 
            ,  ObjectName
                = QuoteName
                    (
                        object_schema_name
                            (tblInd.object_id)
                    )
                    + '.'
                    + 
                    QuoteName
                    (
                        object_name
                        (
                            tblInd.object_id
                        )
                    )
 
            , [indexName]
                = tblSysInd.name
 
            , IndexID = tblInd.Index_ID
            , IndexType = Index_Type_Desc
            , IndexLevel = index_level
            , avg_record_size_in_bytes
            , record_count
            , LastUpdated
                = STATS_DATE
                    (
                          tblInd.object_id
                        , tblInd.index_id
                    )
            , AvgFragmentationInPercent
                = round(Avg_Fragmentation_In_Percent, 3)
 
        FROM sys.dm_db_index_physical_stats
            (
                  db_id()
                , @objectID
                , NULL
                , NULL
                , 'detailed'
            ) tblInd
 
        inner join sys.indexes tblSYSIND
 
            on tblInd.object_id = tblSysInd.object_id
            and tblInd.index_id = tblSysInd.index_id
 
        where index_level = 0
 
    ) T
 
GROUP BY
 
      ObjectId
    , ObjectName
    , IndexId
    , [indexName]
    , IndexType
   , indexLevel
Output

indexReview

 

 

What are we looking for?

We will review the query plans and see where the Estimated Number of records is located.  Once we find a pattern we will write the necessary XPATH to codify it.

Sample Query Plans

Sample Query Plan 1

QueryPlan-RELOP-1

Explanation 1

  1. In the sample above, we searched for the first EstimateRows and it was found within the RELOP element
  2. The name of the Stored Procedure is CRM.GetNotSendRushDeliveries and it is an INSERT statement
  3. EstimatedRows is 62412.3

 

Sample Query Plan 2

QueryPlan-RELOP-2

 

  1. In the sample above, we searched for the first EstimateRows and it was found within the RELOP element
  2. The name of the Stored Procedure is Content.GetCourseStructure and it is a select statement
  3. EstimatedRows is 1

 

Queries

The generalities are that:

  1. Common Table Expression (CTE)
    • We will parcel away the essence of the query into a CTE
    • Get all top level nodes
      • Here is the filtering Clause
        • where x.i.value(‘@NodeId’, ‘int’) = 0

The actual queries will differ in the following ways:

  1. In the first query
    • We will parse dm_exec_query_plan.[query_plan], and extract nodes(‘//RelOp‘)
  2. In the second query
    • We will parse dm_exec_query_plan.[query_plan], and extract nodes(‘//RelOp[1]‘)
      • That is we will just get the first row
  3. In the third query
    • We will parse dm_exec_query_plan.[query_plan], and extract nodes(‘//RelOp[1]‘)
      • That is we will just get the first row
    • Cache the result into a table variable
    • And, access that table variable to get a small portion of the result set

Query 1


set nocount on;
go

/*

	set statistics io on;

*/

use [DBUtility]
go

declare @iNumberofRowsToInvestigate int
declare @iNumberofResultsToDisplay int

set @iNumberofRowsToInvestigate = 5000
set @iNumberofResultsToDisplay = 5

/*

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
, cteQP
(
	  queryPlan 
	, nodeName
	, query
	, [EstimatedTotalSubtreeCost]
	, [nodeID]
	, [EstimateRows]
	, [dbid]
	, [objectID]
	, [number]
	, [encrypted]
)
as
(
select 

		 top ( @iNumberofRows )

		       queryPlan 
                         = tblQP.query_plan

		  ,	nodeName
				=
					(
						cast(x.i.query('.') as xml)
					)


		 , query
				=  tblST.[text]


		  ,	[EstimatedTotalSubtreeCost]
				= 	x.i.value('@EstimatedTotalSubtreeCost', 'float')

		  ,	nodeID
				= x.i.value('@NodeId', 'int')


		  ,	[EstimateRows]
				= x.i.value('@EstimateRows', 'float')


		  , tblQP.[dbid]

		  , tblQP.[objectID]

		  , tblQP.[number]

		  , tblQP.[encrypted]

	from   sys.dm_exec_query_stats tblQS

	--cross apply sys.[dm_exec_query_plan](tblQS.plan_handle) 
	inner join [dbaDMV].[dm_exec_query_plan] tblQP
			on tblQS.[plan_handle] = tblQP.[plan_handle]

	cross apply sys.[dm_exec_sql_text](tblQS.[sql_handle]) 
				as tblST

	CROSS APPLY tblQP.[query_plan].nodes('//RelOp') 
				as x(i)

	where x.i.value('@NodeId', 'int') = 0



)

select top (@iNumberofRows) 
		cteQP.*

from   cteQP

order by
		 [EstimateRows] desc

Query 2


declare @iNumberofRowsToInvestigate int
declare @iNumberofResultsToDisplay int
 
set @iNumberofRowsToInvestigate = 5000
set @iNumberofResultsToDisplay = 5

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
, cteQP
(
	  queryPlan 
	, nodeName
	, query
	, [EstimatedTotalSubtreeCost]
	, [nodeID]
	, [EstimateRows]
	, [dbid]
	, [objectID]
	, [number]
	, [encrypted]
)
as
(
	select 

	 top ( @iNumberofRowsToInvestigate )

		   queryPlan 
				= cast
					(
						tblQP.query_plan
							as xml
					)

		  ,	nodeName
				=
					(
						cast(x.i.query('.') as xml)
					)

		 , query
				= tblST.[text]


		  ,	[EstimatedTotalSubtreeCost]
				= x.i.value('@EstimatedTotalSubtreeCost', 'float')

		  ,	nodeID
				= x.i.value('@NodeId', 'int')

		  ,	[EstimateRows]
				= x.i.value('@EstimateRows', 'float')

		  , tblQP.[dbid]

		  , tblQP.[objectID]

		  , tblQP.[number]

		  , tblQP.[encrypted]

	from   sys.dm_exec_query_stats tblQS

	--cross apply sys.[dm_exec_query_plan](tblQS.plan_handle) 
	inner join [dbaDMV].[dm_exec_query_plan] tblQP
			on tblQS.[plan_handle] = tblQP.[plan_handle]

	cross apply sys.[dm_exec_sql_text](tblQS.[sql_handle]) 
				as tblST

	--CROSS APPLY tblQP.[query_plan].nodes('//RelOp') 
	CROSS APPLY tblQP.[query_plan].nodes('//RelOp[1]') 
				as x(i)

	where x.i.value('@NodeId', 'int') = 0

)
select top (@iNumberofResultsToDisplay) 
		cteQP.*
from   cteQP
order by
		 [EstimateRows] desc


Query 3


declare @iNumberofRowsToInvestigate int
declare @iNumberofResultsToDisplay int
 
set @iNumberofRowsToInvestigate = 5000
set @iNumberofResultsToDisplay = 5

declare @tbl TABLE
(
	  queryPlan						xml
	, nodeName						xml
	, query							varchar(max)
	, [EstimatedTotalSubtreeCost]	float
	, [nodeID]						smallint
	, [EstimateRows]				decimal(30,0)
	, [dbid]						int
	, [objectID]					int
	, [number]						smallint
	, [encrypted]					bit
)

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
, cteQP
(
	  queryPlan 
	, nodeName
	, query
	, [EstimatedTotalSubtreeCost]
	, [nodeID]
	, [EstimateRows]
	, [dbid]
	, [objectID]
	, [number]
	, [encrypted]
)
as
(
select 
		--distinct 

		 top ( @iNumberofRowsToInvestigate )

		   queryPlan 
				= cast
					(
						tblQP.query_plan
							as xml
					)

		  ,	nodeName
				=
					(
						cast(x.i.query('.') as xml)
					)


		 , query
				=  tblST.[text]


		  ,	[EstimatedTotalSubtreeCost]
				= 	x.i.value('@EstimatedTotalSubtreeCost', 'float')

		  ,	nodeID
				= x.i.value('@NodeId', 'int')


		  ,	[EstimateRows]
				= x.i.value('@EstimateRows', 'float')


		  , tblQP.[dbid]

		  , tblQP.[objectID]

		  , tblQP.[number]

		  , tblQP.[encrypted]

	from   sys.dm_exec_query_stats tblQS

	--cross apply sys.[dm_exec_query_plan](tblQS.plan_handle) 
	inner join [dbaDMV].[dm_exec_query_plan] tblQP
			on tblQS.[plan_handle] = tblQP.[plan_handle]

	cross apply sys.[dm_exec_sql_text](tblQS.[sql_handle]) 
				as tblST

	--CROSS APPLY tblQP.[query_plan].nodes('//RelOp') 
	CROSS APPLY tblQP.[query_plan].nodes('//RelOp[1]') 
				as x(i)

	where x.i.value('@NodeId', 'int') = 0

)

insert into @tbl
select 
		cteQP.*

from   cteQP


select top (@iNumberofResultsToDisplay) 
		*
from   @tbl
order by
		--  [objectID] 
		 [EstimateRows] desc


go

Query Plan

Query 3

QueryPlan-3

Operator

XMLIndex

 

Explanation

  1. Here we see that the Query is seeking against a Clustered Index

Metrics

Images

Statistics Time

Query 1

Benchmark 1

Query1-20160318-0120PM

 

 

Benchmark 2

Query1-20160318-0146PM

Query 2

Benchmark 1

20160318-0134PM

 

Benchmark 2

20160318-0202PM

Query 3

Benchmark 1

Query3-20160318-0124PM

Benchmark 2

Query3-20160318-0152PM

Statistics I/O

StatisticsIO-20160317

Query 1

Benchmark 1

StatisticsIO-20160318-0227PM

 

Query 2

Benchmark 1

Query2-StatisticsIO-20160318-0301PM

Benchmark 2

Query2-StatisticsIO-20160318-0350PM

Query 3

Benchmark 1

Query3-20160318-0249PM

 

Benchmark 2

Query3-20160318-0356PM

Tabulated

Statistics I/O

Benchmark 1

 

 

Query Query 1 Query 2 Query 3
WorkTable Scan count 0, logical reads 40245, physical reads 1, read-ahead reads 0, lob logical reads 26362, lob physical reads 35, lob read-ahead reads 0 Scan count 0, logical reads 40245, physical reads 2, read-ahead reads 0, lob logical reads 26362, lob physical reads 40, lob read-ahead reads 0. Scan count 2, logical reads 173238, physical reads 0, read-ahead reads 0, lob logical reads 387019, lob physical reads 66, lob read-ahead reads 74329.
xml index nodes Scan count 85672, logical reads 805565, physical reads 9, read-ahead reads 151, lob logical reads 9, lob physical reads 0, lob read-ahead reads 9. Scan count 751321, logical reads 5697193, physical reads 0, read-ahead reads 0, lob logical reads 9, lob physical reads 0, lob read-ahead reads 9. Scan count 713443, logical reads 4430598, physical reads 0, read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 3.
dm_exec_query_plan Scan count 8412, logical reads 42461, physical reads 0, read-ahead reads 0, lob logical reads 418, lob physical reads 0, lob read-ahead reads 204. Scan count 1, logical reads 2508, physical reads 0, read-ahead reads 0, lob logical reads 414, lob physical reads 0, lob read-ahead reads 204. Scan count 1, logical reads 2508, physical reads 0, read-ahead reads 0, lob logical reads 145745, lob physical reads 2, lob read-ahead reads 70821.
 #3E52440B Scan count 0, logical reads 8841, physical reads 0, read-ahead reads 0, lob logical reads 169167, lob physical reads 0, lob read-ahead reads 0.

Scan count 1, logical reads 3742, physical reads 0, read-ahead reads 0, lob logical reads 669, lob physical reads 0, lob read-ahead reads 315.

 

Benchmark 2

 

 

Query Query 1 Query 2 Query 3
WorkTable Scan count 0, logical reads 40245, physical reads 3, read-ahead reads 0, lob logical reads 26350, lob physical reads 17, lob read-ahead reads 0. Scan count 0, logical reads 40245, physical reads 0, read-ahead reads 0, lob logical reads 26362, lob physical reads 0, lob read-ahead reads 0. Scan count 2, logical reads 188699, physical reads 0, read-ahead reads 0, lob logical reads 389500, lob physical reads 0, lob read-ahead reads 74372.
xml index nodes Scan count 116970, logical reads 1314191, physical reads 3, read-ahead reads 570, lob logical reads 9, lob physical reads 0, lob read-ahead reads 9. Scan count 758371, logical reads 5602631, physical reads 3, read-ahead reads 568, lob logical reads 9, lob physical reads 0, lob read-ahead reads 9. Scan count 695671, logical reads 4311494, physical reads 4, read-ahead reads 13001, lob logical reads 3, lob physical reads 0, lob read-ahead reads 3.
dm_exec_query_plan Scan count 9225, logical reads 47322, physical reads 0, read-ahead reads 0, lob logical reads 418, lob physical reads 0, lob read-ahead reads 204. Scan count 1, logical reads 2863, physical reads 0, read-ahead reads 175, lob logical reads 418, lob physical reads 0, lob read-ahead reads 204. Scan count 1, logical reads 2560, physical reads 11, read-ahead reads 2084, lob logical reads 145786, lob physical reads 238, lob read-ahead reads 70846.
 #3E52440B Scan count 0, logical reads 8835, physical reads 0, read-ahead reads 0, lob logical reads 169188, lob physical reads 0, lob read-ahead reads 0.

Scan count 1, logical reads 3737, physical reads 0, read-ahead reads 0, lob logical reads 670, lob physical reads 0, lob read-ahead reads 315.

 

 

Benchmark 3

 

 

Query Query 1 Query 2 Query 3
WorkTable Scan count 0, logical reads 40245, physical reads 0, read-ahead reads 0, lob logical reads 26364, lob physical reads 0, lob read-ahead reads 0. Scan count 2, logical reads 190748, physical reads 0, read-ahead reads 0, lob logical reads 389786, lob physical reads 0, lob read-ahead reads 74392.
xml index nodes Scan count 758368, logical reads 5602621, physical reads 0, read-ahead reads 0, lob logical reads 9, lob physical reads 0, lob read-ahead reads 9. Scan count 695669, logical reads 4311484, physical reads 0, read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 3.
dm_exec_query_plan Scan count 1, logical reads 2863, physical reads 0, read-ahead reads 0, lob logical reads 418, lob physical reads 0, lob read-ahead reads 204. Scan count 1, logical reads 2560, physical reads 0, read-ahead reads 39, lob logical reads 145798, lob physical reads 106, lob read-ahead reads 70862.
 #3E52440B Scan count 0, logical reads 8833, physical reads 0, read-ahead reads 0, lob logical reads 169198, lob physical reads 0, lob read-ahead reads 0.

Scan count 1, logical reads 3735, physical reads 0, read-ahead reads 0, lob logical reads 670, lob physical reads 0, lob read-ahead reads 315.

 

 

Statistics Time

 

Query Query 1 Query 2 Query 3
Statistics Time CPU time = 79467 ms, elapsed time = 134865 ms. CPU time = 319739 ms,  elapsed time = 322144 ms. SQL Server Execution Times:
CPU time = 62884 ms, elapsed time = 81788 ms.SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 977 ms.
CPU time = 77190 ms,  elapsed time = 133080 ms. CPU time = 307010 ms,  elapsed time = 307561 ms.  SQL Server Execution Times:
CPU time = 56457 ms, elapsed time = 57841 ms.SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 411 ms.

 

Disclaimer

  1. XML Indexes
    • Yes, we are matching on XML Path
    • But, we are not specifically matching on a value
      • Value
        • //author[LastName=”someName”]
  2. We split the entire XML Fragment
    • As we did not have any filtering before calling the XML Nodes function

Findings

Here is some of what we found:

  1. Splitting XML can be expensive
    • nodes(‘//RelOp’) Versus nodes(‘//RelOp[1]’)
      • //RelOp
        • Query 1 :- Scan count 85672, logical reads 805565
      • //RelOp[1]
        • Query 2 :- Scan count 751321, logical reads 5697193
        • Query 3 :- Scan count 713443, logical reads 4430598
  2. Common table expressions can behave in unexpected ways
    • And, so caching results in temporary table
    • Or abstracting away in Table Value Functions can be helpful
  3. CPU Time Versus Elapsed Time
    • What happens in the background matters
    • Query Compare
      • First Query :- CPU Time = 79467 ms, elapsed time = 134865 ms
      • Second Query :- CPU time = 307010 ms,  elapsed time = 307561 ms.
      • Third Query  :- CPU Time = 62884 ms, elapsed time = 81788 ms
  4. Statistics I/O
    • Once we take a look at Statistics I/O we can see that parsing the internals of the XML Column takes most of the work
    • For example – Table ‘xml_index_nodes_117575457_32000‘. Scan count 616827, logical reads 3856792, physical reads 0, read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 3.
      • As for other entries the tables accessed are listed. For XML fetches the name of the internal table, xml_index_nodes_117575457_32000, is listed

References

Plan  Cache

XML

  1. Fast XML / Slow XML
    http://dba.stackexchange.com/questions/27264/fast-xml-slow-xml

 

Common Table Expression

  1. CTE Very Slow When Joined
    http://stackoverflow.com/questions/22807213/cte-very-slow-when-joined
  2. T-SQL Bad Performance with CTE
    http://stackoverflow.com/questions/10751875/t-sql-bad-performance-with-cte

 

Xpath and XQuery – Tutorial

  1. XML Methods in SQL Server
    https://www.simple-talk.com/sql/learn-sql-server/the-xml-methods-in-sql-server/

 

XPath Examples

  1. Using the data() function in XQuery
    http://www.sqlskills.com/blogs/bobb/using-the-data-function-in-xquery/
  2. text(), fn:string() and fn:data() – Author: Dave Cassel
    http://blog.davidcassel.net/2011/06/text-fnstring-and-fndata/

 

Query Plans / XPath Examples

  1. Xpath Examples – .Net Framework ( current version )
    https://msdn.microsoft.com/en-us/library/ms256086(v=vs.110).aspx

 

Xpath and XQuery – Query Plan – Sample Queries – Blog

  1. Get all SQL Statements with “table scan” in cached query plan
    https://gallery.technet.microsoft.com/scriptcenter/Get-all-SQL-Statements-0622af19

 

Xpath and XQuery – Sample Queries – QA

  1. XQuery Syntax and Performance
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/34f2cc12-779f-4d39-8b85-94a37a4d9c98/xquery-syntax-and-performance?forum=transactsql
  2. Extracting Attributes from XML Fields in SQL Server 2008 Table
    http://stackoverflow.com/questions/15846990/extracting-attributes-from-xml-fields-in-sql-server-2008-table

 

SQL Queries and XML Columns

  1. Filtering SQL Queries on XML Columns by Briaon Otto
    https://blogs.msdn.microsoft.com/rscdbbiw/2006/05/20/filtering-sql-queries-on-xml-columns/

 

XML Indexes

  1. Toad World – XML Indexes Overview
    https://www.toadworld.com/platforms/sql-server/w/wiki/9649.xml-indexes-overview

One thought on “SQL Server – dm_exec_query_plan – shredding – Part 1

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s