2005191520@SQL占用CPU和执行时间信息 | LOFT.CLUEE.TECH

SQL占用CPU最高信息

SELECT TOP 50
       execution_count,
       total_logical_reads / execution_count AS [Avg Logical Reads],
       total_elapsed_time / execution_count AS [Avg Elapsed Time],
       DB_NAME(st.dbid) AS [database name],
       OBJECT_NAME(st.dbid) AS [object name],
       OBJECT_NAME(st.objectid) AS [object name 1],
       SUBSTRING(   st.text,
                    (qs.statement_start_offset / 2) + 1,
                    ((CASE statement_end_offset
                          WHEN -1 THEN
                              DATALENGTH(st.text)
                          ELSE
                              qs.statement_end_offset
                      END - qs.statement_start_offset
                     ) / 2
                    ) + 1
                ) AS statement_text
FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE execution_count > 100
ORDER BY 1 DESC;

SQL执行时间最长

SELECT TOP 50
       COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value AS INT)) + '*', 'Resource') AS DBNAME,
       SUBSTRING(   text,

                    -- starting value for substring

                    CASE
                        WHEN statement_start_offset = 0
                             OR statement_start_offset IS NULL THEN
                            1
                        ELSE
                            statement_start_offset / 2 + 1
                    END,

                    -- ending value for substring

                    CASE
                        WHEN statement_end_offset = 0
                             OR statement_end_offset = -1
                             OR statement_end_offset IS NULL THEN
                            LEN(text)
                        ELSE
                            statement_end_offset / 2
                    END - CASE
                              WHEN statement_start_offset = 0
                                   OR statement_start_offset IS NULL THEN
                                  1
                              ELSE
                                  statement_start_offset / 2
                          END + 1
                ) AS TSQL,
       total_logical_reads / execution_count AS AVG_LOGICAL_READS
FROM sys.dm_exec_query_stats
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
WHERE attribute = 'dbid'
ORDER BY AVG_LOGICAL_READS DESC;