Microsoft – SQL Server – Transact SQL – Query Tuning – High Sorts

 

Background

Looking online to see what is available to identify Queries being impact by High Sorts.

I think the one that looks most promising for me is the work contributed by Adam Haines.

It is available :

Finding queries with high sorts
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8ab9368d-f5e9-4311-831d-9ed28bae41c6/finding-queries-with-high-sorts?forum=transactsql

 

Code with slight modifications



/*
    
    Olaf Helper - SHOWPLAN_ALL-like Query for a Cached Query Plan
    https://gallery.technet.microsoft.com/SHOWPLANALL-like-Query-for-635e1ad2

*/
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

, NbrSorts AS
(
    SELECT

          databaseName
        , objectName
        , sqlText
        , SQLStatementText
	    , queryPlanHash
        , x.queryPlan as queryPlan    
	    , executionCount as executionCount
	    , (totalRows) AS SumRows
	    , (Total_Mbs) AS Total_MBs
        , StatementSubTreeCost as StatementSubTreeCost
        , EstimatedTotalSubtreeCost as EstimatedTotalSubtreeCost
	, (IsMissingIndex) AS IsMissingIndex
    FROM
        (
	        SELECT
		          cp.query_plan_hash as queryPlanHash

                , cast(min(cast (qp.query_plan as varchar(max) )) as xml)
                    as queryPlan

                , sum(cp.execution_count) 
                    as executionCount

                , sum(cp.total_rows) as totalRows

		        , sum(CAST(x.i.value('@EstimateRows', 'FLOAT') AS decimal) ) 
                    AS EstimatedRows

		        , sum(
                        CAST(
                                  x.i.value('@AvgRowSize', 'FLOAT') 
                                * x.i.value('@EstimateRows', 'FLOAT'
                            ) AS decimal ) 
                        / (1024.*1024.) 
                    ) AS Total_MBs


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

		, avg(CAST(x.i.value(N'@EstimatedTotalSubtreeCost', 'float') AS decimal) ) 
                    AS EstimatedTotalSubtreeCost

                , avg(CAST(qp.[query_plan].exist('declare namespace sql="http://schemas.microsoft.com/sqlserver/2004/07/showplan";//sql:MissingIndexes') AS TINYINT)) 
                     AS IsMissingIndex

                , min(db_name(queryText.[dbid]) ) 
                    as databaseName

                , min(
                        object_name(
                                      queryText.objectid
                                    , queryText.[dbid]
                                   )
                     ) 
                    as objectName

                , min(queryText.[text]) 
                    as sqlText

                , SUBSTRING(
                                   min(queryText.text)
                                , (min(cp.statement_start_offset)/2) + 1
                                ,
                                    (
                                        (
                                            CASE min(statement_end_offset )
                                                WHEN -1 THEN DATALENGTH(min(queryText.text))
                                                ELSE min(cp.statement_end_offset) 
                                            END 
                                        - min(cp.statement_start_offset)
                                        )
                                            /2
                                    ) + 1

                            ) 
                    AS SQLStatementText

          --FROM sys.dm_exec_cached_plans cp (NOLOCK)

          FROM sys.dm_exec_query_stats cp (NOLOCK)

                  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

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

                  OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) queryText


	      WHERE

                -- Filter out query plan searches
                (
                   
                    (queryText.[text] not like '%XMLNAMESPACES%')

                )

                -- look for Sort Operations
                AND
                (
                    (

                           (x.i.value('@PhysicalOp', 'NVARCHAR(200)') IN (N'Sort') )

                    )
                )

            group by
                cp.query_plan_hash

    ) AS x


)
SELECT
      databaseName
    , objectName
    , sqlText
    , SQLStatementText
    , queryPlanHash
    , queryPlan
	, ExecutionCount
	, SumRows AS SumRows
	, Total_MBs as totalMBs
    , StatementSubTreeCost
    , EstimatedTotalSubtreeCost
    , IsMissingIndex

FROM NbrSorts srt

ORDER BY Total_MBs DESC


 

 

Conclusion

BTW, as I wrapped up this post found out that Mr. Haines is a Microsoft’s SQL Server MVP.

And, so are Olap Helper and Brad Schulz. Each MVP has useful sample codes referenced in the “Sample Query Plans Queries” Reference Section at the bottom of this post.

 

References

Query Plans – High Sorts

 

Query Plans – Distinct

 

Sample Query Plan Queries

 

SQL Server MVP

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