DBA Scripts: Get Query Performance Deep-Dive

When Top CPU Doesn’t Explain “Why Is This Slow”

The query usually runs fine and sometimes takes forever, or the server is slow and Top CPU Queries names nothing that explains it. A CPU ranking tells you what was busy, not why something was slow. These four scripts go after the usual reasons it misses: one stored procedure buried in ad-hoc noise, a plan that suits some parameter values and not others, a plan cache full of single-use plans, and queries spilling to tempdb. Match your symptom to a script.

Why This Deep-Dive Matters

• A stored procedure ranking separates application logic performance from ad-hoc query noise, useful when the workload is mostly proc-based and a general top-CPU list gets cluttered with framework-generated queries
• High variance (max time far exceeding min time) on the same cached plan is the clearest signal of parameter sniffing, a plan compiled for one set of parameter values performing badly for another
• A plan cache dominated by single-use ad-hoc plans wastes memory that could otherwise cache reusable plans, and often means the application isn’t parameterizing its queries
• Memory grant spills are genuinely invisible in standard wait statistics, they show up as TempDB I/O pressure or RESOURCE_SEMAPHORE waits with no obvious query-level attribution unless you look at total_spills directly

When to Run These Scripts

• Get-StoredProcedurePerformance, when the workload is proc-heavy and a general top-CPU ranking doesn’t isolate what matters
• Get-QueryVariance, investigating a query that “usually runs fine but sometimes takes forever,” the classic parameter-sniffing symptom
• Get-PlanCacheHealth, routine health check (part of the standard suite), or when investigating unexplained plan cache memory pressure
• Get-MemoryGrantSpills, investigating TempDB I/O pressure or RESOURCE_SEMAPHORE waits that don’t have an obvious cause from wait stats alone

The Scripts

1. Get-StoredProcedurePerformance: Stored Procedures Ranked by Total Elapsed Time

/*
Script Name : Get-StoredProcedurePerformance
Category    : performance
Purpose     : Stored procedures from the plan cache ranked by total elapsed time — shows execution count, average and max duration, CPU, and logical reads. Resets on SQL Server restart or plan eviction.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-query-performance-deep-dive/)
Requires    : VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE is enough on SQL Server 2022 and later),
              plus VIEW ANY DEFINITION, or VIEW DEFINITION in each database, to resolve procedure names
Notes       : Without metadata visibility OBJECT_NAME returns NULL. The row is kept and proc_name
              shows the object_id instead, so a restricted login sees unnamed rows, not an empty result.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

SELECT TOP 50
    DB_NAME(ps.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
    COALESCE(OBJECT_NAME(ps.object_id, ps.database_id),
             '(object_id ' + CONVERT(varchar(12), ps.object_id) + ', name not visible)') AS proc_name,
    ps.execution_count,
    ps.total_elapsed_time / 1000 / ps.execution_count AS avg_ms,
    ps.max_elapsed_time / 1000 AS max_ms,
    ps.min_elapsed_time / 1000 AS min_ms,
    ps.total_worker_time / 1000 / ps.execution_count AS avg_cpu_ms,
    ps.total_logical_reads / ps.execution_count AS avg_logical_reads,
    ps.total_logical_writes / ps.execution_count AS avg_logical_writes,
    ps.total_physical_reads / ps.execution_count AS avg_physical_reads,
    /* Raw totals for sorting */
    ps.total_elapsed_time / 1000 AS total_elapsed_ms,
    ps.total_worker_time / 1000 AS total_cpu_ms,
    CONVERT(VARCHAR(16), ps.last_execution_time, 120) AS last_execution_at,
    CONVERT(VARCHAR(16), ps.cached_time, 120) AS plan_cached_at
FROM sys.dm_exec_procedure_stats ps
WHERE ps.database_id > 4 /* user databases only */
ORDER BY ps.total_elapsed_time DESC;

2. Get-QueryVariance: Queries With Wildly Inconsistent Execution Time

/*
Script Name : Get-QueryVariance
Category    : performance
Purpose     : Queries from the plan cache where max execution time is at least 5x the minimum — the primary signal for parameter sniffing and plan instability. High execution count with high variance means the same query performs very differently depending on the parameter values in the cached plan.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-query-performance-deep-dive/)
Requires    : VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE is enough on SQL Server 2022 and later)
Notes       : Ad-hoc and sp_executesql statements carry no dbid in sys.dm_exec_sql_text, so
              database_name falls back to the database the plan was compiled in.
              object_name needs VIEW ANY DEFINITION (or VIEW DEFINITION in that database), else NULL.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

SELECT TOP 30
    qs.execution_count,
    qs.min_elapsed_time / 1000 AS min_ms,
    qs.max_elapsed_time / 1000 AS max_ms,
    qs.total_elapsed_time / 1000 / qs.execution_count AS avg_ms,
    qs.last_elapsed_time / 1000 AS last_ms,
    qs.max_elapsed_time / NULLIF(qs.min_elapsed_time, 0) AS max_to_min_ratio,
    (qs.max_elapsed_time - qs.min_elapsed_time) / 1000 AS variance_ms,
    /* Worker time (CPU) variance */
    qs.max_worker_time / 1000 AS max_cpu_ms,
    qs.min_worker_time / 1000 AS min_cpu_ms,
    DB_NAME(COALESCE(qt.dbid, pa.dbid)) AS database_name,
    OBJECT_NAME(qt.objectid, qt.dbid) AS object_name,
    qs.query_hash,
    LEFT(LTRIM(qt.text), 200) AS query_snippet,
    qs.creation_time AS plan_cached_at
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
OUTER APPLY (SELECT CONVERT(int, a.value) AS dbid
             FROM sys.dm_exec_plan_attributes(qs.plan_handle) a
             WHERE a.attribute = N'dbid') pa
WHERE qs.execution_count >= 5
  AND qs.min_elapsed_time > 0
  AND qs.max_elapsed_time / NULLIF(qs.min_elapsed_time, 0) >= 5
ORDER BY max_to_min_ratio DESC, variance_ms DESC;

3. Get-PlanCacheHealth: Plan Cache Composition and Ad-Hoc Bloat

/*
Script Name : Get-PlanCacheHealth
Category    : performance
Purpose     : Summarises plan cache composition by object type — highlights single-use
              plan bloat, ad-hoc SQL pressure, and total memory consumption. A high single-use
              percentage on Adhoc plans means the application is not parameterising its queries.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-query-performance-deep-dive/)
Requires    : VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE is enough on SQL Server 2022 and later)
Notes       : WARN needs both a single-use ratio over 60% and at least 100 MB of single-use Adhoc
              plans. A ratio on its own fires on a handful of plans, and once OPTIMIZE FOR AD HOC
              WORKLOADS is on, the single-use entries are small stubs, so the size gate keeps it quiet.
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

/*
  DESIGN: One result set, one row per plan type (objtype): plan counts, the single-use
  ratio, and the memory held by all plans and by single-use plans.
  Single-use plans (usecounts = 1) waste cache memory and indicate ad-hoc workloads.
  Remedies: OPTIMIZE FOR AD HOC WORKLOADS, sp_executesql, or forced parameterisation.
*/

-- By plan type
SELECT
    cp.objtype AS plan_type,
    COUNT(*) AS plan_count,
    SUM(cp.usecounts) AS total_use_count,
    SUM(CASE WHEN cp.usecounts = 1 THEN 1 ELSE 0 END) AS single_use_plan_count,
    CAST(
        100.0 * SUM(CASE WHEN cp.usecounts = 1 THEN 1 ELSE 0 END)
        / NULLIF(COUNT(*), 0)
    AS decimal(5,1)) AS single_use_pct,
    CAST(SUM(cp.size_in_bytes) / 1048576.0 AS decimal(10,1)) AS total_mb,
    CAST(SUM(CASE WHEN cp.usecounts = 1 THEN cp.size_in_bytes ELSE 0 END) / 1048576.0 AS decimal(10,1)) AS single_use_mb,
    CASE
        WHEN CAST(
                100.0 * SUM(CASE WHEN cp.usecounts = 1 THEN 1 ELSE 0 END)
                / NULLIF(COUNT(*), 0)
             AS decimal(5,1)) > 60
            AND cp.objtype = 'Adhoc'
            AND SUM(CASE WHEN cp.usecounts = 1 THEN cp.size_in_bytes ELSE 0 END) >= 104857600 /* 100 MB */
            THEN 'WARN - high ad-hoc single-use ratio; consider OPTIMIZE FOR AD HOC WORKLOADS'
        ELSE 'OK'
    END AS recommendation
FROM sys.dm_exec_cached_plans cp
GROUP BY cp.objtype
ORDER BY total_mb DESC;

4. Get-MemoryGrantSpills: Queries Spilling to TempDB

/*
Script Name : Get-MemoryGrantSpills
Category    : performance
Purpose     : Top queries by memory grant spills to TempDB. Spills occur when SQL grants
              less memory than a sort or hash join operator needs, forcing intermediate
              results to disk. Invisible in wait stats — shows as TempDB I/O pressure
              or RESOURCE_SEMAPHORE waits. Needs the total_spills column, which arrived in
              SQL Server 2016 SP2 and 2017 CU3, so the script detects the column rather than
              testing a version number.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-query-performance-deep-dive/)
Requires    : VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE is enough on SQL Server 2022 and later)
Notes       : total_spills counts 8 KB pages. Ad-hoc and sp_executesql statements carry no dbid in
              sys.dm_exec_sql_text, so the database comes from the plan's compile database instead;
              filtering on the text's dbid alone dropped every ad-hoc and prepared spill.
              The diagnosis reads averages, so a query that spills on some executions only can
              still average over the CRITICAL line; max_spills_single_exec shows the worst run.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

-- total_spills/max_spills arrived in SQL Server 2016 (13.x) SP2 and 2017 (14.x) CU3, NOT in
-- 2016 RTM. A major-version test cannot gate this anyway: the whole batch is bound before any
-- IF runs, so a static reference to a missing column fails to compile and the friendly message
-- never prints. Detect the column, and keep the query in dynamic SQL so it is only bound when
-- it can succeed. Same pattern as Get-CdcAndChangeTracking.
IF COL_LENGTH('sys.dm_exec_query_stats', 'total_spills') IS NULL
BEGIN
    SELECT 'Memory grant spill tracking (total_spills) needs SQL Server 2016 SP2 / 2017 CU3 or later. '
         + 'This instance is ' + CONVERT(varchar(32), SERVERPROPERTY('ProductVersion')) + '.' AS info;
END
ELSE
BEGIN
    DECLARE @sql nvarchar(max) = N'
    SELECT TOP 30
        DB_NAME(COALESCE(qt.dbid, pa.dbid)) AS database_name,
        OBJECT_NAME(qt.objectid, qt.dbid) AS object_name,
        qs.execution_count,
        qs.total_spills AS total_spills,
        qs.total_spills / NULLIF(qs.execution_count, 0) AS avg_spills_per_exec,
        qs.max_spills AS max_spills_single_exec,
        -- Memory grant analysis (grant_kb columns: SQL 2016+)
        CAST(qs.total_grant_kb / 1024.0 / NULLIF(qs.execution_count, 0) AS DECIMAL(10,2)) AS avg_granted_mb,
        CAST(qs.total_used_grant_kb / 1024.0 / NULLIF(qs.execution_count, 0) AS DECIMAL(10,2)) AS avg_used_mb,
        CAST(qs.total_ideal_grant_kb / 1024.0 / NULLIF(qs.execution_count, 0) AS DECIMAL(10,2)) AS avg_ideal_mb,
        -- Grant efficiency: used / granted (low = wasteful over-grant; high = under-grant causing spills)
        CAST(100.0 * qs.total_used_grant_kb / NULLIF(qs.total_grant_kb, 0) AS DECIMAL(5,1)) AS grant_efficiency_pct,
        -- Grant deficit: how much more memory was needed vs what was granted
        CAST((qs.total_ideal_grant_kb - qs.total_grant_kb)
             / 1024.0 / NULLIF(qs.execution_count, 0)
             AS DECIMAL(10,2)) AS avg_grant_deficit_mb,
        CAST(qs.total_worker_time / 1000.0 / NULLIF(qs.execution_count, 0) AS DECIMAL(10,2)) AS avg_cpu_ms,
        CAST(qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS BIGINT) AS avg_logical_reads,
        qs.creation_time AS plan_cached_at,
        CASE
            WHEN qs.total_spills / NULLIF(qs.execution_count, 0) > 1000
            THEN ''CRITICAL - over 1000 pages spilled per execution on average; query needs index, stats update, or hint''
            WHEN qs.total_ideal_grant_kb > qs.total_grant_kb * 2
            THEN ''WARN - grant consistently less than half of ideal; RESOURCE_SEMAPHORE pressure likely''
            WHEN qs.total_ideal_grant_kb > qs.total_grant_kb * 1.2
            THEN ''WARN - grant undersized vs ideal; spills expected under load''
            ELSE ''WARN - spilling (lower severity)''
        END AS diagnosis,
        LEFT(qt.text, 500) AS query_text
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    OUTER APPLY (SELECT CONVERT(int, a.value) AS dbid
                 FROM sys.dm_exec_plan_attributes(qs.plan_handle) AS a
                 WHERE a.attribute = N''dbid'') AS pa
    WHERE qs.total_spills > 0
      AND COALESCE(qt.dbid, pa.dbid) > 4
    ORDER BY qs.total_spills DESC;';

    EXEC sys.sp_executesql @sql;
END;

How To Run From The Repo

Clone DBA Tools, initialize and run whichever script answers your question:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Stored procedures ranked by total elapsed time:
.\run.ps1 Get-StoredProcedurePerformance

# Queries with wildly inconsistent execution time (parameter sniffing signal):
.\run.ps1 Get-QueryVariance

# Plan cache composition and ad-hoc bloat:
.\run.ps1 Get-PlanCacheHealth

# Queries spilling to TempDB due to undersized memory grants:
.\run.ps1 Get-MemoryGrantSpills

# Any of these against a remote sql server:
.\run.ps1 Get-PlanCacheHealth -ServerInstance SQLSERVER01

These scripts live in the repo at:

• sql/performance/queries/Get-StoredProcedurePerformance.sql
• sql/performance/queries/Get-QueryVariance.sql
• sql/performance/queries/Get-PlanCacheHealth.sql
• sql/performance/queries/Get-MemoryGrantSpills.sql

Example Output

One run against the lab instance, after a stored procedure was called 6 times with a value matching 1 row and then 3 times with a value matching 190,000 of the table’s 200,000 rows. 1 row and 6 of the 14 columns are shown here.

min_ms reads 0 because the fast calls finished in under a millisecond. The ratio is worked out from the raw microsecond counters, which is why it reads 55533 rather than dividing by zero. The plan was compiled for the 1-row value: an index seek, a lookup per row and a sort sized for 1 row. The 190,000-row calls reused it, and the actual plan for one of them shows the sort estimated at 1 row, handed 512 KB of memory and spilling 42,941 pages to TempDB. That is parameter sniffing, and it is the shape this script exists to catch.

Get-MemoryGrantSpills on the same run flagged the same procedure as CRITICAL: 19,000 pages spilled over 9 executions, with max_spills_single_exec at 9,500. So 2 of the 9 calls did all of the spilling, and the 6 fast calls spilled nothing.

Understanding the Results

Best Practices

• Run Get-PlanCacheHealth as a routine check, ad-hoc plan bloat creeps up gradually and is easy to miss without a periodic look
• Treat Get-QueryVariance findings as parameter-sniffing candidates first, before assuming a generic “the query is slow” explanation
• Fix Get-MemoryGrantSpills findings at the root cause (statistics, indexing, a sniffed plan) rather than reaching for a memory grant hint as the first move. The actual execution plan names the operator that spilled
• Use Get-StoredProcedurePerformance specifically when the workload is proc-heavy, a general top-CPU ranking can bury proc performance under high-volume ad-hoc noise

MS Docs: sys.dm_exec_query_stats · sys.dm_exec_procedure_stats · sys.dm_exec_cached_plans · sys.dm_exec_sql_text · sys.dm_exec_plan_attributes

Frequently Asked Questions

Summary

“Why is this slow” splits into several distinct, answerable questions once you go past a general top-resource ranking: is it a stored procedure specifically, is it inconsistent between runs (parameter sniffing), is the plan cache itself unhealthy, and is a memory grant forcing work to disk. These four scripts each answer one of those questions directly.

Run Get-PlanCacheHealth as a routine check, and reach for the other three once a specific symptom, proc-heavy workload, inconsistent timing, or TempDB pressure, points toward the right one.

Related