worst performance query in sql server


SELECT

       MIN(query_text.statement_text) AS [Query Text],

    SUM(query_text.total_worker_time) / SUM(query_text.execution_count) AS [Avg CPU Time],

       SUM(query_text.total_elapsed_time)/SUM(query_text.execution_count) AS [AVG Execution Time],

       SUM(total_physical_reads) [Total Physical Reads],

       SUM(total_rows) [Total Rows Returned]

FROM

    (SELECT

              EQS.*,

              SUBSTRING(ST.text, (EQS.statement_start_offset/2) + 1,

              ((CASE statement_end_offset

                     WHEN -1 THEN DATALENGTH(ST.text)

                     ELSE EQS.statement_end_offset END

            -EQS.statement_start_offset)/2) + 1) AS statement_text

     FROM sys.dm_exec_query_stats AS EQS

     CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) as ST) as query_text

GROUP BY query_text.query_hash

ORDER BY [Avg CPU Time] DESC;

Comments

  1. I sincerely appreciate you taking the time to share these information

    ReplyDelete

Post a Comment

Popular posts from this blog

Announcing backup and restore improvements for large datasets near the size limit

Enhanced refresh with the Power BI REST API is now generally available

What’s New in SQL Server 2014 since SQL Server 2008 R2