Transact SQL – Queries exhausting most resources during Sort Operations

Background

Wanted to find queries that are doing the most sorts.
And, to do so we will review the Cached Plans and look for Sorts.

Query Plan

Sort Operations

To properly develop our XPath here are some sample Sort Operators.

SortOperation

 

Target Query Plans

Table

 

Code


;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
, NbrSorts
(
      objtype
    , databaseName
    , schemaName
    , objectName
    , sqlText
    , queryPlan
    , sortOperation

    , executionCount
    , [EstimatedRows]
    , [EstimatedRowSize]

    , totalPhysicalReads
    , StatementSubTreeCost
    , EstimatedTotalSubtreeCost
    , MemoryFractions

    , [OrderBySchema]
    , [OrderByTable]
    , [OrderByColumn]

    , IsMissingIndex

)
as
(
	SELECT 

	  [objtype]

        , [databaseName]
            =
	          isNull
				(
					db_name(queryText.[dbid])
					, ''
				)					

        , schemaName
            =
			  isNull
				(
					object_schema_name
					(
						  queryText.objectid
						, queryText.[dbid]
					)
					, ''
				)

        , objectName
            =
			  isNull
				(
					object_name
					(
						  queryText.objectid
						, queryText.[dbid]
					)
					, ''
				)

        , sqlText
            = (queryText.[text]) 

        , queryPlan
            =
				(
					cast
					 (
						qp.query_plan as varchar(max)
					 )
				)						

        , [sortOperation]
            =
                (
	                cast(x.i.query('.') as varchar(max))
				)

        , [executionCount]
            =
				(
					qs.execution_count
				) 

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

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

        , [totalPhysicalReads]
            =
				(
					qs.total_physical_reads
				)

      , StatementSubTreeCost
            --avg(CAST(x.i.value(N'@StatementSubTreeCost', 'float') AS decimal) )
            = 0

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

       , [MemoryFractions]
			=
				(
					x.i.query('data(MemoryFractions/@Input)')
						.value('.', 'float')
				)					

       , [OrderBySchema]
	        =
				(
					x.i.query('data(.//OrderByColumn/ColumnReference/@Schema)')
						.value('.', 'sysname')
				)						

       , [OrderByTable]
          =
				(
					x.i.query('data(.//OrderByColumn/ColumnReference/@Table)')
						.value('.', 'sysname')
				)						

       , [OrderByColumn]
          =
			(
				x.i.query('data(.//OrderByColumn/ColumnReference/@Column)')
				  .value('.', 'sysname')
			)						

        , IsMissingIndex
            =
                 CAST
                    (
			 qp.[query_plan].exist('//MissingIndexes')
                            AS TINYINT
                    )

  FROM sys.dm_exec_cached_plans cp

  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

  CROSS APPLY qp.[query_plan].nodes('//RelOp[@PhysicalOp="Sort"]') x(i)

  CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) queryText

  INNER JOIN sys.dm_exec_query_stats qs

        on cp.[plan_handle] = qs.[plan_handle]

  WHERE qp.query_plan.exist
                (
                    '
                        //RelOp
                        [
                               @PhysicalOp = "Sort"
                        ]
                    '
                ) = 1

	AND i.exist('//ColumnReference[@Schema!="[sys]"]') = 1
	AND i.exist('//ColumnReference[@Schema!=""]') = 1

)

, NbrSortsAggr
(

      databaseName
    , schemaName
    , objectName
    , [objtype]
    , sqlText
    , queryPlan
    , sortOperation
    , [NumberofPlans]
    , executionCount

    , [EstimatedRows]
    , [EstimatedRowsAvg]

    , [EstimatedRowSize]
    , [EstimatedRowSizeAvg]

    , totalPhysicalReads
    , TotalPhysicalReadsAvg

    , StatementSubTreeCost

    , EstimatedTotalSubtreeCost
    , EstimatedTotalSubtreeCostAvg

    , MemoryFractions
    , MemoryFractionsAvg

    , [OrderBySchema]
    , [OrderByTable]
    , [OrderByColumn]

    , IsMissingIndex

)
as
( 

	select 

		  databaseName
		, schemaName
		, objectName

		, [objtype]		

		, sqlText
		, queryPlan
		, sortOperation
		, [NumberofPlans]
			= count(*)

		, sum(executionCount)

		, max([EstimatedRows])
		, [EstimatedRowsAvg]
			= avg([EstimatedRows])

		, sum([EstimatedRowSize])
		, [EstimatedRowSizeAvg]
			= avg([EstimatedRowSize])

		, sum(totalPhysicalReads)
		, TotalPhysicalReadsAvg
			= avg(totalPhysicalReads)

		, sum(StatementSubTreeCost)

		, sum(EstimatedTotalSubtreeCost)
		, EstimatedTotalSubtreeCostAvg
			= sum(EstimatedTotalSubtreeCost)

		, sum(MemoryFractions)
		, MemoryFractionsAvg
			= avg(MemoryFractions)

		, max([OrderBySchema])
		, max([OrderByTable])
		, max([OrderByColumn])

		, IsMissingIndex
			= max(IsMissingIndex)

   from NbrSorts

   group by
		  databaseName
		, schemaName
		, objectName
		, [objtype]
		, sqlText
		, queryPlan
		, sortOperation

)

SELECT
      databaseName
    , schemaName
    , objectName

	, [objtype]    

    , sqlText

    , [queryPlan]
		= cast(queryPlan as xml)

	, sortOperation
		= cast(sortOperation as xml)

	, [NumberofPlans]

    , ExecutionCount

    , [EstimatedRows]
	, [EstimatedRowsAvg]

    , [EstimatedRowSize]
	, [EstimatedRowSizeAvg]

    , totalPhysicalReads
	, TotalPhysicalReadsAvg

    , totalMBs
        =  (
              (
				[EstimatedRows] * [EstimatedRowSize]
			   )
               /
               ( 1024 * 1024)
            )

    , StatementSubTreeCost

    , EstimatedTotalSubtreeCost
	, EstimatedTotalSubtreeCostAvg

    , [MemoryFractions]
	, MemoryFractionsAvg

    , [OrderBySchema]
    , [OrderByTable]
    , [OrderByColumn]

    , IsMissingIndex

FROM NbrSortsAggr srt

ORDER BY
			  TotalMBs DESC
			, [EstimatedRows] desc			

Explanation

  1. Skip queries
    • that reference the sys schema
    • as well as those do not contain schema attributes altogether
  2. To get Column Reference columns such as Schema, Table, and Column we invoked “‘data(.//OrderByColumn/ColumnReference/@Schema)'”
    • The .// indicates that we want elements irrespective of where that are in the Hierarchy
    • x.i.query(‘data(.//OrderByColumn/ColumnReference/@Schema)’)
      .value(‘.’, ‘sysname’)

 

Errors

Msg 8114

Error Message

Stuck at this error for a long time …

Textual

Msg 8114, Level 16, State 5, Line 27
Error converting data type nvarchar to numeric.

Image

CodeError

Reviewed Data

Data

 

Explanation:

  1. Found out that the we have data with e+

Took to the Net and one good work around is not to cast to decimal, but to float.

  1. Piotr Rodak – BCP And Numeric Data Field
  2. Artemakis Artemiou – Error converting data type varchar to float

BTW, my target is v2005.

Code Snippet

Original Code

CodeSQL

 

Revised Code

CodeSQLRevised

Addendum

Capture into table

Based on a comment by Rebecca, made the code into a stored procedure, in master db,  and create a tracking table in tempdb.

Make into Stored Procedure


use master
go

if object_id('dbo.sp_QueryPlan_Sorts') is null
begin

	exec('create procedure dbo.sp_QueryPlan_Sorts as select 1/0 as [shell] ')

end

go

alter procedure dbo.sp_QueryPlan_Sorts
as

	;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	, NbrSorts
	(
		  objtype
		, databaseName
		, schemaName
		, objectName
		, sqlText
		, queryPlan
		, sortOperation
	 
		, executionCount
		, [EstimatedRows]
		, [EstimatedRowSize]
	 
		, totalPhysicalReads
		, StatementSubTreeCost
		, EstimatedTotalSubtreeCost
		, MemoryFractions
	 
		, [OrderBySchema]
		, [OrderByTable]
		, [OrderByColumn]
	 
		, IsMissingIndex
	 
	)
	as
	(
		SELECT
	 
		  [objtype]
	 
			, [databaseName]
				=
				  isNull
					(
						db_name(queryText.[dbid])
						, ''
					)                   
	 
			, schemaName
				=
				  isNull
					(
						object_schema_name
						(
							  queryText.objectid
							, queryText.[dbid]
						)
						, ''
					)
	 
			, objectName
				=
				  isNull
					(
						object_name
						(
							  queryText.objectid
							, queryText.[dbid]
						)
						, ''
					)
	 
			, sqlText
				= (queryText.[text]) 
	 
			, queryPlan
				=
					(
						cast
						 (
							qp.query_plan as varchar(max)
						 )
					)                       
	 
			, [sortOperation]
				=
					(
						cast(x.i.query('.') as varchar(max))
					)
	 
			, [executionCount]
				=
					(
						qs.execution_count
					) 
	 
			, [EstimatedRows]
				=
					(
						x.i.value
						(
							  '@EstimateRows'
							, 'float'
						)
					)                   
	 
			, [EstimatedRowSize]
				=
					(
						x.i.value
							(
								  '@AvgRowSize'
								, 'float'
							)
					)                   
	 
			, [totalPhysicalReads]
				=
					(
						qs.total_physical_reads
					)
	 
		  , StatementSubTreeCost
				--avg(CAST(x.i.value(N'@StatementSubTreeCost', 'float') AS decimal) )
				= 0
	 
		   , [EstimatedTotalSubtreeCost]
				=
					(
						x.i.value('@EstimatedTotalSubtreeCost', 'float')
					)
	 
		   , [MemoryFractions]
				=
					(
						x.i.query('data(MemoryFractions/@Input)')
							.value('.', 'float')
					)                   
	 
		   , [OrderBySchema]
				=
					(
						x.i.query('data(.//OrderByColumn/ColumnReference/@Schema)')
							.value('.', 'sysname')
					)                       
	 
		   , [OrderByTable]
			  =
					(
						x.i.query('data(.//OrderByColumn/ColumnReference/@Table)')
							.value('.', 'sysname')
					)                       
	 
		   , [OrderByColumn]
			  =
				(
					x.i.query('data(.//OrderByColumn/ColumnReference/@Column)')
					  .value('.', 'sysname')
				)                       
	 
			, IsMissingIndex
				= 
					 CAST
						(
				 qp.[query_plan].exist('//MissingIndexes') 
								AS TINYINT
						)
	 
	 
	  FROM sys.dm_exec_cached_plans cp
	 
	  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
	 
	  CROSS APPLY qp.[query_plan].nodes('//RelOp[@PhysicalOp="Sort"]') x(i)
	 
	  CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) queryText
	 
	  INNER JOIN sys.dm_exec_query_stats qs
	 
			on cp.[plan_handle] = qs.[plan_handle]
	 
	  WHERE qp.query_plan.exist
					(
						'
							//RelOp
							[
								   @PhysicalOp = "Sort"
							]
						'
					) = 1
	 
		AND i.exist('//ColumnReference[@Schema!="[sys]"]') = 1
		AND i.exist('//ColumnReference[@Schema!=""]') = 1
	 
	)
	 
	, NbrSortsAggr
	(
	 
		  databaseName
		, schemaName
		, objectName
		, [objtype]
		, sqlText
		, queryPlan
		, sortOperation
		, [NumberofPlans]
		, executionCount
	 
		, [EstimatedRows]
		, [EstimatedRowsAvg]
	 
		, [EstimatedRowSize]
		, [EstimatedRowSizeAvg]
	 
		, totalPhysicalReads
		, TotalPhysicalReadsAvg
	 
		, StatementSubTreeCost
	 
		, EstimatedTotalSubtreeCost
		, EstimatedTotalSubtreeCostAvg
	 
		, MemoryFractions
		, MemoryFractionsAvg
	 
		, [OrderBySchema]
		, [OrderByTable]
		, [OrderByColumn]
	 
		, IsMissingIndex
	 
	)
	as
	( 
	 
		select
	 
			  databaseName
			, schemaName
			, objectName
	 
			, [objtype]     
	 
			, sqlText
			, queryPlan
			, sortOperation
			, [NumberofPlans]
				= count(*)
	 
			, sum(executionCount)
	 
			, max([EstimatedRows])
			, [EstimatedRowsAvg]
				= avg([EstimatedRows])
	             
	 
			, sum([EstimatedRowSize])
			, [EstimatedRowSizeAvg]
				= avg([EstimatedRowSize])
	 
			, sum(totalPhysicalReads)
			, TotalPhysicalReadsAvg
				= avg(totalPhysicalReads)
	 
			, sum(StatementSubTreeCost)
	 
			, sum(EstimatedTotalSubtreeCost)
			, EstimatedTotalSubtreeCostAvg
				= sum(EstimatedTotalSubtreeCost)
	 
			, sum(MemoryFractions)
			, MemoryFractionsAvg
				= avg(MemoryFractions)
	 
			, max([OrderBySchema])
			, max([OrderByTable])
			, max([OrderByColumn])
	 
			, IsMissingIndex
				= max(IsMissingIndex)
	 
	   from NbrSorts
	 
	   group by
			  databaseName
			, schemaName
			, objectName
			, [objtype]
			, sqlText
			, queryPlan
			, sortOperation
	 
	)
	 
	SELECT
		  databaseName
		, schemaName
		, objectName
	 
		, [objtype]    
	 
		, sqlText
                     = left(sqlText, 8000)
	 
		, [queryPlan]
			= cast(queryPlan as xml)
	 
		, sortOperation
			= cast(sortOperation as xml)
	 
		, [NumberofPlans]
	 
		, ExecutionCount
	 
		, [EstimatedRows]
		, [EstimatedRowsAvg]
	 
		, [EstimatedRowSize]
		, [EstimatedRowSizeAvg]
	 
		, totalPhysicalReads
		, TotalPhysicalReadsAvg
	 
	 
		, totalMBs
			=  (
				  (
					[EstimatedRows] * [EstimatedRowSize]
				   )
				   /
				   ( 1024 * 1024)
				)
	 
		, StatementSubTreeCost
	 
		, EstimatedTotalSubtreeCost
		, EstimatedTotalSubtreeCostAvg
	 
		, [MemoryFractions]
		, MemoryFractionsAvg
	 
		, [OrderBySchema]
		, [OrderByTable]
		, [OrderByColumn]
	 
		, IsMissingIndex
	 
	FROM NbrSortsAggr srt
	 
	ORDER BY
				  TotalMBs DESC
				, [EstimatedRows] desc         
 
go

Create Table



use tempdb
go

set noexec off
go

/*
	drop table dbo.[QueryPlanSorts]
*/
if object_id('dbo.[QueryPlanSorts]') is not null
begin

	set noexec on

end
go

create table dbo.[QueryPlanSorts]
(
		  [id]								bigint not null identity(1,1)
		, databaseName						sysname
		, schemaName						sysname
		, objectName						sysname
	 
		, [objtype]							sysname
	 
		, sqlText							varchar(8000) null
	 
		, [queryPlan]						xml
	 
		, sortOperation						xml
	 
		, [NumberofPlans]					int
	 
		, ExecutionCount					int	
	 
		, [EstimatedRows]					int
		, [EstimatedRowsAvg]				float
	 
		, [EstimatedRowSize]				int
		, [EstimatedRowSizeAvg]				int
	 
		, totalPhysicalReads				bigint
		, TotalPhysicalReadsAvg				decimal(30,3)
	 
	 
		, totalMBs							decimal(30,3)
	 
		, StatementSubTreeCost				float
	 
		, EstimatedTotalSubtreeCost			float
		, EstimatedTotalSubtreeCostAvg		float
	 
		, [MemoryFractions]					float
		, MemoryFractionsAvg				float
	 
		, [OrderBySchema]					sysname
		, [OrderByTable]					sysname
		, [OrderByColumn]					varchar(600)
	 
		, IsMissingIndex					bit

		, [dateAdded]						smalldatetime
												default getdate()

		, [addedBy]							sysname
												default system_user

		, constraint [PK_QueryPlanSorts]
			primary key
			(
				[id]
			)
)

create index INDX_totalMBs
on dbo.[QueryPlanSorts]
(
	[totalMBs]
)
go

Trial Runs


insert [tempdb].dbo.[QueryPlanSorts]
(
    [databaseName]
   ,[schemaName]
   ,[objectName]
   ,[objtype]
   ,[sqlText]
   ,[queryPlan]
   ,[sortOperation]
   ,[NumberofPlans]
   ,[ExecutionCount]
   ,[EstimatedRows]
   ,[EstimatedRowsAvg]
   ,[EstimatedRowSize]
   ,[EstimatedRowSizeAvg]
   ,[totalPhysicalReads]
   ,[TotalPhysicalReadsAvg]
   ,[totalMBs]
   ,[StatementSubTreeCost]
   ,[EstimatedTotalSubtreeCost]
   ,[EstimatedTotalSubtreeCostAvg]
   ,[MemoryFractions]
   ,[MemoryFractionsAvg]
   ,[OrderBySchema]
   ,[OrderByTable]
   ,[OrderByColumn]
   ,[IsMissingIndex]
)
exec  dbo.sp_QueryPlan_Sorts

References

  1. SQL Server Insights – SQL Swiss Army Knife #13 – Exploring the plan cache – Part 2
    http://blogs.msdn.com/b/blogdoezequiel/archive/2014/03/16/sql-swiss-army-knife-13-exploring-the-plan-cache-part-2.aspx#.VqLshPnyt7g
  2. Get All SQL Statements
    https://gallery.technet.microsoft.com/scriptcenter/Get-all-SQL-Statements-0622af19
  3. Get all SQL Statements with “table scan” in cached query plan
    https://gallery.technet.microsoft.com/scriptcenter/Get-all-SQL-Statements-0622af19
  4. Tertiary Weights
    http://speedysql.com/2015/10/04/tertiary-weights/
  5. Finding Scans
    https://sahlean.wordpress.com/2013/10/04/finding-index-scan/
  6. Built in Function T-SQL and fn_getSQL
    https://www.toadworld.com/platforms/sql-server/w/wiki/10252.built-in-functions-tsequal-and-fn-get-sql

 

Optimization Query Plan Parsing

  1. Querying Information from the Plan Cache, Simplified
    http://www.scarydba.com/2012/07/02/querying-data-from-the-plan-cache/
  2. Dennes Torres – Checking the Plan Cache Warnings for a SQL Server Database
    https://www.simple-talk.com/sql/t-sql-programming/checking-the-plan-cache-warnings-for-a-sql-server-database/

 

Cast – Decimal Versus Float

  1. BCP And Numeric Data Field
    http://sqlblogcasts.com/blogs/piotr_rodak/archive/2009/05/17/bcp-and-numeric-data-field.aspx

 

Cast and IsNumeric

 

2 thoughts on “Transact SQL – Queries exhausting most resources during Sort Operations

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