SQLServer_Database_QueryPlanAnalysis/Database_QueryPlanAnalysis.sql at main · amcanna1ly/SQLServer_Database_QueryPlanAnalysis · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
/*=======================================================================================
    Name: Database_QueryPlanAnalysis.sql
    Author: Alex McAnnally
    Last Edited: 12/9/2025

    Purpose:
        Analyze cached query plans for performance issues:
            - High CPU queries (top N)
            - Missing index warnings
            - Hash / sort spills
            - Key lookups
            - Implicit conversions
=======================================================================================*/

USE master;
GO

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @TopN int = 50; -- Number of top queries to analyze by total CPU

;WITH TopQueries AS
(
    SELECT TOP (@TopN)
        qs.query_hash,
        qs.query_plan_hash,
        qs.plan_handle,
        qs.sql_handle,
        qs.statement_start_offset,
        qs.statement_end_offset,
        qs.total_worker_time,
        qs.total_elapsed_time,
        qs.total_logical_reads,
        qs.total_logical_writes,
        qs.execution_count,
        qs.last_execution_time,
        qs.total_worker_time / NULLIF(qs.execution_count, 0) AS avg_worker_time,
        qs.total_elapsed_time / NULLIF(qs.execution_count, 0) AS avg_elapsed_time,
        qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS avg_logical_reads,
        qs.total_logical_writes / NULLIF(qs.execution_count, 0) AS avg_logical_writes
    FROM sys.dm_exec_query_stats AS qs
    ORDER BY qs.total_worker_time DESC
)
SELECT
    DB_NAME(st.dbid)                            AS DatabaseName,
    tq.last_execution_time,
    tq.execution_count,
    tq.total_worker_time                        AS total_cpu_time,
    tq.avg_worker_time                          AS avg_cpu_time,
    tq.total_elapsed_time                       AS total_elapsed_time,
    tq.avg_elapsed_time                         AS avg_elapsed_time,
    tq.total_logical_reads                      AS total_logical_reads,
    tq.avg_logical_reads                        AS avg_logical_reads,
    tq.total_logical_writes                     AS total_logical_writes,
    tq.avg_logical_writes                       AS avg_logical_writes,
    tq.query_hash,
    tq.query_plan_hash,

    -- Specific statement text
    SUBSTRING(
        st.text,
        (tq.statement_start_offset / 2) + 1,
        CASE
            WHEN tq.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(max), st.text)) - (tq.statement_start_offset / 2)
            ELSE (tq.statement_end_offset - tq.statement_start_offset) / 2 + 1
        END
    )                                           AS StatementText,

    -- Entire batch text
    st.text                                     AS BatchText,

    -- XML Plan
    pl.query_plan                               AS QueryPlanXml,

    -- Flags for plan warnings
    CASE WHEN pl.query_plan.exist('//MissingIndexes') = 1 THEN 1 ELSE 0 END AS HasMissingIndexWarning,
    CASE WHEN pl.query_plan.exist('//RelOp[@SpillToTempDb = 1]') = 1 THEN 1 ELSE 0 END AS HasSpillToTempDb,
    CASE WHEN pl.query_plan.exist('//RelOp[@LogicalOp = "Key Lookup"]') = 1 THEN 1 ELSE 0 END AS HasKeyLookup,
    CASE WHEN CAST(pl.query_plan AS nvarchar(max)) LIKE '%CONVERT_IMPLICIT%' THEN 1 ELSE 0 END AS HasImplicitConversion

FROM TopQueries AS tq
CROSS APPLY sys.dm_exec_query_plan(tq.plan_handle) AS pl
CROSS APPLY sys.dm_exec_sql_text(tq.sql_handle)    AS st
ORDER BY tq.total_worker_time DESC;
GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;