从dm_exec_query_stats系统表查询耗时的SQL语句

语句示例：

SELECT TOP 100
        s2.dbid ,
        s1.total_worker_time / s1.execution_count AS [Avg CPU Time] ,
        ( SELECT TOP 1
                    SUBSTRING(s2.text, statement_start_offset / 2 + 1,
                              ( ( CASE WHEN statement_end_offset = -1
                                       THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text))
                                              * 2 )
                                       ELSE statement_end_offset
                                  END ) - statement_start_offset ) / 2 + 1)
        ) AS sql_statement ,
        execution_count ,
        plan_generation_num ,
        last_execution_time ,
        total_worker_time ,
        last_worker_time ,
        min_worker_time ,
        max_worker_time ,
        total_physical_reads ,
        last_physical_reads ,
        min_physical_reads ,
        max_physical_reads ,
        total_logical_writes ,
        last_logical_writes ,
        min_logical_writes ,
        max_logical_writes
FROM    sys.dm_exec_query_stats AS s1
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE   s2.objectid IS NULL
ORDER BY s1.total_worker_time DESC;

查询结果见下图：

字段说明 见下图(更多信息见：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms189741(v=sql.110))：

转载于:https://www.cnblogs.com/mlinber/p/9728687.html