Part of the DBA-Tools Project.
Four Angles on “Why Is This Slow” That Go Beyond Top CPU
Top CPU Queries ranks by raw resource consumption. These four scripts answer more specific questions the ranking alone can’t: Get-StoredProcedurePerformance ranks stored procedures specifically, not ad-hoc queries. Get-QueryVariance finds queries whose execution time swings wildly between runs, the primary signal for parameter sniffing. Get-PlanCacheHealth summarizes the whole plan cache’s composition, flagging ad-hoc bloat before it becomes a memory problem. Get-MemoryGrantSpills finds queries whose memory grants are so undersized they’re spilling to TempDB, invisible in wait stats but a real, fixable performance cost.
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_SEMAPHOREwaits with no obvious query-level attribution unless you look attotal_spillsdirectly
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_SEMAPHOREwaits 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
*/
-- Blog: https://sqldba.blog/dba-scripts-get-query-performance-deep-dive/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT TOP 50
DB_NAME(ps.database_id) AS database_name,
OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
OBJECT_NAME(ps.object_id, ps.database_id) 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 */
AND OBJECT_NAME(ps.object_id, ps.database_id) IS NOT NULL
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
*/
-- Blog: https://sqldba.blog/dba-scripts-get-query-performance-deep-dive/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT 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(qt.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
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. High
single-use percentages indicate parameter sniffing or missing parameterisation.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-query-performance-deep-dive/)
Requires : VIEW SERVER STATE
HealthCheck : Yes
*/
-- Blog: https://sqldba.blog/dba-scripts-get-query-performance-deep-dive/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
/*
DESIGN: Aggregates by plan type, single-use plans (usecounts = 1) waste cache memory
and indicate ad-hoc workloads. Remedies: OPTIMIZE FOR AD HOC WORKLOADS, sp_executesql,
or forced parameterisation.
*/
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'
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. Requires SQL Server 2016+ (total_spills column).
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-query-performance-deep-dive/)
Requires : VIEW SERVER STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-query-performance-deep-dive/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
IF CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) < 13
BEGIN
SELECT 'Memory grant spill tracking (total_spills) requires SQL Server 2016 or later.' AS info;
END
ELSE
BEGIN
SELECT TOP 30
DB_NAME(qt.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,
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,
CAST(100.0 * qs.total_used_grant_kb / NULLIF(qs.total_grant_kb, 0)
AS DECIMAL(5,1)) AS grant_efficiency_pct,
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 — heavy spill every execution; 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
WHERE qs.total_spills > 0
AND qt.dbid > 4
ORDER BY qs.total_spills DESC;
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.sqlsql/performance/queries/Get-QueryVariance.sqlsql/performance/queries/Get-PlanCacheHealth.sqlsql/performance/queries/Get-MemoryGrantSpills.sql
Example Output
Real output from this lab instance, not staged.
Get-PlanCacheHealth (2 rows, a genuine finding: 100% single-use ad-hoc plans):
| plan_type | plan_count | single_use_pct | total_mb | recommendation |
|---|---|---|---|---|
| View | 1 | 0.0 | 0.10 | OK |
| Adhoc | 1 | 100.0 | 0.10 | WARN, high ad-hoc single-use ratio; consider OPTIMIZE FOR AD HOC WORKLOADS |
Get-StoredProcedurePerformance, Get-QueryVariance, and Get-MemoryGrantSpills all return 0 rows here, this lab box has no stored procedures with cached execution stats, no query with a 5x-or-greater time variance, and no memory grant spills recorded, all honest, valid results for a small, low-traffic instance.
Understanding the Results
- recommendation = WARN on Adhoc plan_type — a high single-use percentage on ad-hoc plans means the application isn’t parameterizing queries; each slightly different literal value compiles and caches its own plan, wasting memory;
OPTIMIZE FOR AD HOC WORKLOADS(a server-level setting) reduces the memory cost of first-time compiles without requiring an application change - max_to_min_ratio very high in Get-QueryVariance — the same cached plan performing wildly differently depending on parameter values, the textbook parameter-sniffing pattern; consider
OPTION (RECOMPILE)on the specific statement, or query hints that stabilize the plan - diagnosis = CRITICAL in Get-MemoryGrantSpills — a query spilling to TempDB on every single execution; check for stale statistics, a missing index that would reduce the row estimate, or add an explicit
MIN_GRANT_PERCENT/MAX_GRANT_PERCENThint - avg_ideal_mb far higher than avg_granted_mb — the optimizer’s own estimate of what the query needed vs what it actually got; a wide gap explains why spilling happens even when the query “should” have enough memory available on the instance overall
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) rather than reaching for a memory grant hint as the first move
- 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
Related Scripts
You may also find these scripts useful:
Frequently Asked Questions
How is this different from Top CPU Queries?
Top CPU Queries ranks everything by raw resource use in one list. These four scripts each answer a narrower, more specific question, procedure-only ranking, execution-time consistency, plan cache composition, and memory grant efficiency, useful once the general ranking has pointed you toward a category of problem and you need the specific diagnostic for it.
Does 0 rows from Get-MemoryGrantSpills mean memory grants are never a problem here?
It means no spills have been recorded since the last restart or plan eviction on this instance, at this workload level. A larger, more memory-constrained production workload with complex sorts and hash joins is far more likely to show real findings here; this lab box’s light workload genuinely doesn’t produce any.
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.
Leave a Reply