Preskočiť na obsah
Skript

Top 20 dotazov podľa CPU

Najdrahšie dotazy z plan cache podľa celkového CPU času.

#cpu #plan-cache #tuning
top-20-dotazov-podla-cpu.sql 14 riadkov · T-SQL
Parametre skriptu doplň a kopíruj hotový skript
SELECT TOP (20)
        qs.total_worker_time / 1000              AS total_cpu_ms,
        qs.execution_count,
        qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
        qs.total_logical_reads / qs.execution_count AS avg_reads,
        DB_NAME(st.dbid)                         AS database_name,
        SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
            ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
        qp.query_plan
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;