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

Failed to execute the package or element. Build errors were encountered

Restore of database 'DataBase_Name' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

On-premises data gateway December release