Introduction
In SQL Server, performance issues are very common. Sometimes queries run slow, sometimes users get stuck, and sometimes the system becomes unresponsive.
Two major reasons:
Blocking (queries waiting on each other)
Poor performance (CPU, IO, memory issues)
In this blog, we will learn how to solve both problems using two powerful stored procedures:
👉 dbusp_BlockingTree → For blocking analysis
👉 dbusp_PerformanceStats → Full performance dashboard
Part 1: Understanding Blocking
Blocking happens when:
One query locks data
Another query tries to access same data
Second query waits
Result: system slowdown
Solution 1: Blocking Tree Script
We use:
👉 dbusp_BlockingTree
Full script:
IF OBJECT_ID('dbusp_BlockingTree', 'P') IS NOT NULL
DROP PROCEDURE dbusp_BlockingTree;
GO
/*
================================================================================
SP Name : dbusp_BlockingTree
Purpose : Shows full blocking chain tree using modern DMVs.
NOTE: This is a BLOCKING TREE viewer, not deadlock capture.
For actual deadlocks → use Extended Events (system_health session).
Author : DBA Team
Version : 2.0 (DBA Grade)
Parameters:
@MinWaitSec - Show only sessions waiting >= N seconds (default 0)
@DBName - Filter by database name (NULL = all databases)
@ShowAllSessions - 0 = only blocking chain | 1 = all active sessions
@KillLeadBlocker - 1 = KILL the head blocker SPID
@DBAConfirm - Safety switch: must be 1 to allow KILL
Result Sets:
#1 → Blocking Tree (visual chain with depth indentation)
#2 → Lock Resource Details (which object is being locked)
#3 → Lead Blocker Summary (quick action card for DBA)
================================================================================
*/
CREATE PROCEDURE dbusp_BlockingTree
@MinWaitSec INT = 0, -- Min wait threshold in seconds
@DBName SYSNAME = NULL, -- NULL = all DBs
@ShowAllSessions BIT = 0, -- 1 = include non-blocked active sessions too
@KillLeadBlocker BIT = 0, -- 1 = attempt KILL on lead blocker
@DBAConfirm BIT = 0 -- Safety: must be 1 to allow @KillLeadBlocker
AS
BEGIN
SET NOCOUNT ON;
-- =========================================================================
-- SECTION 0: Safety Guard for KILL
-- =========================================================================
IF @KillLeadBlocker = 1 AND @DBAConfirm = 0
BEGIN
RAISERROR(
'KILL blocked! Set @DBAConfirm = 1 along with @KillLeadBlocker = 1 to confirm.',
16, 1
);
RETURN;
END;
-- =========================================================================
-- SECTION 1: Collect session + request data from modern DMVs
-- sys.dm_exec_sessions → login, host, DB, isolation level, open txns
-- sys.dm_exec_requests → wait info, CPU, reads, blocking_session_id
-- sys.dm_exec_sql_text → batch SQL text (safe NULL handle check)
-- =========================================================================
IF OBJECT_ID('tempdb..#tmpSessions') IS NOT NULL
DROP TABLE #tmpSessions;
SELECT
s.session_id AS SPID,
ISNULL(r.blocking_session_id, 0) AS BlockedBy,
-- Wait info
ISNULL(r.wait_time, 0) / 1000.0 AS WaitTimeSec,
ISNULL(r.wait_type, N'—') AS WaitType,
ISNULL(r.wait_resource, N'—') AS WaitResource,
-- Session info
s.login_name AS LoginName,
s.host_name AS HostName,
s.program_name AS AppName,
ISNULL(DB_NAME(r.database_id),
DB_NAME(s.database_id)) AS DBName,
-- Perf counters
ISNULL(r.cpu_time, 0) AS CPUTimeMs,
ISNULL(r.logical_reads, 0) AS LogicalReads,
ISNULL(r.writes, 0) AS Writes,
ISNULL(r.row_count, 0) AS RowsReturned,
-- Transaction info
s.open_transaction_count AS OpenTxnCount,
CASE s.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'READ UNCOMMITTED'
WHEN 2 THEN 'READ COMMITTED'
WHEN 3 THEN 'REPEATABLE READ'
WHEN 4 THEN 'SERIALIZABLE'
WHEN 5 THEN 'SNAPSHOT'
ELSE 'Unknown'
END AS IsolationLevel,
-- Status
s.status AS SessionStatus,
ISNULL(r.status, N'—') AS RequestStatus,
r.command AS CommandType,
-- SQL Text: full batch
CASE
WHEN r.sql_handle IS NOT NULL
AND r.sql_handle <> 0x0000000000000000000000000000000000000000
THEN REPLACE(REPLACE(
ISNULL(bt.TEXT, N'[Could not retrieve SQL]'),
CHAR(10), ' '), CHAR(13), ' ')
ELSE N'[No active request / idle in txn]'
END AS BatchText,
-- Current statement (slice from batch using offsets)
CASE
WHEN r.sql_handle IS NOT NULL
AND r.sql_handle <> 0x0000000000000000000000000000000000000000
AND bt.TEXT IS NOT NULL
THEN REPLACE(REPLACE(
SUBSTRING(
bt.TEXT,
(r.statement_start_offset / 2) + 1,
CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(bt.TEXT)
ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
END
), CHAR(10), ' '), CHAR(13), ' ')
ELSE N'—'
END AS CurrentStatement,
-- Start time of current request
r.start_time AS RequestStartTime,
-- Used in CTE tree building
CAST(RIGHT('0000' + CAST(s.session_id AS VARCHAR(10)), 4)
AS VARCHAR(4000)) AS SortPath
INTO #tmpSessions
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
OUTER APPLY (
SELECT TEXT
FROM sys.dm_exec_sql_text(r.sql_handle)
WHERE r.sql_handle IS NOT NULL
AND r.sql_handle <> 0x0000000000000000000000000000000000000000
) bt
WHERE s.is_user_process = 1 -- Exclude system SPIDs
AND s.session_id <> @@SPID -- Exclude this SP itself
AND (
-- Only blocking chain members
(@ShowAllSessions = 0 AND (
ISNULL(r.blocking_session_id, 0) > 0
OR EXISTS (
SELECT 1 FROM sys.dm_exec_requests r2
WHERE r2.blocking_session_id = s.session_id
)
))
OR
-- All active sessions
(@ShowAllSessions = 1 AND (
s.status IN ('running', 'sleeping', 'suspended')
OR r.session_id IS NOT NULL
))
)
AND ISNULL(r.wait_time, 0) / 1000.0 >= @MinWaitSec
AND (@DBName IS NULL
OR DB_NAME(r.database_id) = @DBName
OR DB_NAME(s.database_id) = @DBName);
-- =========================================================================
-- SECTION 2: Recursive CTE — Build Blocking Tree
-- =========================================================================
WITH BlockerTree AS
(
-- Anchor: Root blockers (sessions that are blocking others but not blocked themselves)
SELECT
SPID,
BlockedBy,
WaitTimeSec,
WaitType,
WaitResource,
LoginName,
HostName,
AppName,
DBName,
CPUTimeMs,
LogicalReads,
OpenTxnCount,
IsolationLevel,
SessionStatus,
RequestStatus,
CommandType,
BatchText,
CurrentStatement,
RequestStartTime,
CAST(RIGHT('0000' + CAST(SPID AS VARCHAR(10)), 4)
AS VARCHAR(4000)) AS LEVEL
FROM #tmpSessions
WHERE (BlockedBy = 0 OR BlockedBy = SPID) -- Self-block edge case guard
AND EXISTS (
SELECT 1 FROM #tmpSessions c
WHERE c.BlockedBy = SPID
AND c.BlockedBy <> c.SPID
)
UNION ALL
-- Recursive: blocked children
SELECT
c.SPID,
c.BlockedBy,
c.WaitTimeSec,
c.WaitType,
c.WaitResource,
c.LoginName,
c.HostName,
c.AppName,
c.DBName,
c.CPUTimeMs,
c.LogicalReads,
c.OpenTxnCount,
c.IsolationLevel,
c.SessionStatus,
c.RequestStatus,
c.CommandType,
c.BatchText,
c.CurrentStatement,
c.RequestStartTime,
CAST(p.LEVEL + RIGHT('0000' + CAST(c.SPID AS VARCHAR(10)), 4)
AS VARCHAR(4000))
FROM #tmpSessions c
INNER JOIN BlockerTree p ON c.BlockedBy = p.SPID
WHERE c.BlockedBy > 0
AND c.BlockedBy <> c.SPID
)
-- =========================================================================
-- RESULT SET 1: Visual Blocking Tree
-- =========================================================================
SELECT
-- Visual tree node
REPLICATE(' ', (LEN(LEVEL) / 4) - 1)
+ CASE
WHEN (LEN(LEVEL) / 4) - 1 = 0
THEN N'👑 HEAD BLOCKER ──▶ '
ELSE N' └── '
END
+ CAST(SPID AS NVARCHAR(10))
+ N' (Blocked by: '
+ CASE WHEN BlockedBy = 0 THEN 'None' ELSE CAST(BlockedBy AS NVARCHAR(10)) END
+ N')' AS BlockingTree,
SPID,
BlockedBy,
WaitTimeSec,
WaitType,
WaitResource,
LoginName,
HostName,
AppName,
DBName,
SessionStatus,
RequestStatus,
CommandType,
IsolationLevel,
OpenTxnCount,
CPUTimeMs,
LogicalReads,
RequestStartTime,
CurrentStatement,
BatchText
FROM BlockerTree
ORDER BY LEVEL;
-- =========================================================================
-- RESULT SET 2: Lock Resource Details
-- Which object/index is being contested
-- =========================================================================
SELECT
tl.request_session_id AS SPID,
tl.resource_type AS LockResourceType, -- TABLE / PAGE / KEY / ROW
tl.resource_subtype AS LockSubType,
tl.resource_database_id AS LockDBID,
DB_NAME(tl.resource_database_id) AS LockDBName,
ISNULL(OBJECT_NAME(
tl.resource_associated_entity_id,
tl.resource_database_id), '—') AS LockedObject,
tl.resource_description AS LockDetail,
tl.request_mode AS LockMode, -- S, X, IX, SIX etc.
tl.request_status AS LockStatus, -- GRANT / WAIT / CONVERT
tl.request_type AS LockType
FROM sys.dm_tran_locks tl
INNER JOIN #tmpSessions ts ON tl.request_session_id = ts.SPID
ORDER BY tl.request_session_id, tl.resource_type;
-- =========================================================================
-- RESULT SET 3: Lead Blocker Summary (DBA Action Card)
-- =========================================================================
SELECT TOP 10
ts.SPID AS LeadBlockerSPID,
ts.LoginName,
ts.HostName,
ts.AppName,
ts.DBName,
ts.IsolationLevel,
ts.OpenTxnCount,
ts.CPUTimeMs,
ts.LogicalReads,
ts.RequestStartTime,
DATEDIFF(SECOND, ts.RequestStartTime, GETDATE())
AS HoldingTxnSec,
COUNT(victims.SPID) AS BlockedSessionCount,
N'KILL ' + CAST(ts.SPID AS NVARCHAR(10)) + N';'
AS KillCommand,
ts.CurrentStatement AS BlockerCurrentStatement
FROM #tmpSessions ts
LEFT JOIN #tmpSessions victims ON victims.BlockedBy = ts.SPID
WHERE (ts.BlockedBy = 0 OR ts.BlockedBy = ts.SPID)
AND EXISTS (
SELECT 1 FROM #tmpSessions c2
WHERE c2.BlockedBy = ts.SPID AND c2.SPID <> ts.SPID
)
GROUP BY
ts.SPID, ts.LoginName, ts.HostName, ts.AppName,
ts.DBName, ts.IsolationLevel, ts.OpenTxnCount,
ts.CPUTimeMs, ts.LogicalReads, ts.RequestStartTime,
ts.CurrentStatement
ORDER BY BlockedSessionCount DESC, HoldingTxnSec DESC;
-- =========================================================================
-- SECTION 3: KILL Lead Blocker (with double safety check)
-- =========================================================================
IF @KillLeadBlocker = 1 AND @DBAConfirm = 1
BEGIN
DECLARE @LeadSPID INT;
DECLARE @KillSQL NVARCHAR(50);
DECLARE @KillMsg NVARCHAR(200);
-- Pick the worst offender: most victims, longest running
SELECT TOP 1
@LeadSPID = ts.SPID
FROM #tmpSessions ts
WHERE (ts.BlockedBy = 0 OR ts.BlockedBy = ts.SPID)
AND EXISTS (
SELECT 1 FROM #tmpSessions c2
WHERE c2.BlockedBy = ts.SPID AND c2.SPID <> ts.SPID
)
ORDER BY
(SELECT COUNT(*) FROM #tmpSessions v WHERE v.BlockedBy = ts.SPID) DESC,
DATEDIFF(SECOND, ts.RequestStartTime, GETDATE()) DESC;
IF @LeadSPID IS NOT NULL
BEGIN
BEGIN TRY
SET @KillSQL = N'KILL ' + CAST(@LeadSPID AS NVARCHAR(10)) + N';';
EXEC sp_executesql @KillSQL;
SET @KillMsg = N'✅ KILLED SPID ' + CAST(@LeadSPID AS NVARCHAR(10))
+ N' at ' + CONVERT(NVARCHAR(30), GETDATE(), 120);
RAISERROR(@KillMsg, 10, 1) WITH NOWAIT;
END TRY
BEGIN CATCH
SET @KillMsg = N'❌ KILL failed for SPID '
+ CAST(@LeadSPID AS NVARCHAR(10))
+ N' → ' + ERROR_MESSAGE();
RAISERROR(@KillMsg, 16, 1);
END CATCH;
END
ELSE
BEGIN
RAISERROR('No lead blocker found to KILL.', 10, 1) WITH NOWAIT;
END;
END;
-- Cleanup
IF OBJECT_ID('tempdb..#tmpSessions') IS NOT NULL
DROP TABLE #tmpSessions;
END;
GO
-- 1. Default: Show blocking chain only
EXEC dbusp_BlockingTree;
-- 2. Only sessions waiting 5+ seconds
EXEC dbusp_BlockingTree @MinWaitSec = 5;
-- 3. Specific database only
EXEC dbusp_BlockingTree @DBName = N'Vipul';
-- 4. All active sessions (not just blocked chain)
EXEC dbusp_BlockingTree @ShowAllSessions = 1;
-- 5. Kill lead blocker (double confirm required)
EXEC dbusp_BlockingTree
@KillLeadBlocker = 1,
@DBAConfirm = 1;
-- 6. Full DBA investigation mode
EXEC dbusp_BlockingTree
@MinWaitSec = 3,
@DBName = N'Vipul',
@ShowAllSessions = 0;What This Script Does
It gives 3 outputs:
Blocking Tree
Shows full chain
Who is blocking whom
Lock Details
Table / row / index lock info
Lead Blocker
Main problem query
Ready KILL command
How to Use
EXEC dbusp_BlockingTree;
Filter options:
EXEC dbusp_BlockingTree @MinWaitSec = 5;
EXEC dbusp_BlockingTree @DBName = 'PayrollDB';
Kill blocker (careful ⚠️):
EXEC dbusp_BlockingTree
@KillLeadBlocker = 1,
@DBAConfirm = 1;
When to Use This Script
Users stuck
Queries not finishing
LCK_M_* wait types
High wait time
Part 2: Full SQL Server Performance Monitoring
Blocking is only one part. A real DBA needs to monitor the complete system health.
This is used here:
dbusp_PerformanceStats
Full script:
-- ============================================================
-- dbusp_PerformanceStats | ENTERPRISE DBA-Grade Dashboard v3.1
-- Categories:
-- QUERY PERFORMANCE: 2, 3, 4, 5, 6, 7, 15, 26
-- INDEX HEALTH: 8, 9, 10, 16, 25
-- DATABASE HEALTH: 1, 11, 12, 13, 14, 17, 18, 19, 20, 27, 28
-- MEMORY/RESOURCES: 13, 21, 22, 23, 24
-- INFRASTRUCTURE: 11, 29, 30
-- CONCURRENCY: 5, 6, 14, 15, 24
-- SECTIONS:
-- 1 Large Tables (Size Analysis)
-- 2 Top CPU Heavy Queries (Query Perf)
-- 3 Top Duration Queries (Query Perf)
-- 4 Top IO Heavy Queries (Query Perf)
-- 5 Currently Running + Blocking Queries (Concurrency)
-- 6 Execution Plans for Slow Queries (Query Tuning)
-- 7 Stored Procedure Performance (SP Perf)
-- 8 Missing Index Suggestions (Index Health)
-- 9 Index Fragmentation Report (Index Health)
-- 10 Unused Indexes (Index Cleanup)
-- 11 Top Wait Statistics (Bottleneck Analysis)
-- 12 TempDB Usage Per Session (Resource Usage)
-- 13 Memory Pressure Indicators (Memory Health)
-- 14 Deadlock History (Concurrency Issues)
-- 15 Lock Escalation & Contention Pattern (Locking Analysis)
-- 16 Stale Statistics Report (DB Integrity)
-- 17 Database Health & Configuration (DB Status)
-- 18 Transaction Log Status & VLF Check (Log Health)
-- 19 Page Compression Opportunities (Storage Opt)
-- 20 Plan Cache Regression / QS Fallback (Query Analytics)
-- 21 Parameter Sniffing Detection (Query Issues)
-- 22 Database IO Subsystem Analysis (IO Performance)
-- 23 Connection Pool Analysis (Concurrency)
-- 24 CPU & Scheduler Queue Analysis (CPU Health)
-- 25 Table Partitioning Opportunities (Storage Opt)
-- 26 Query Recompilation & Hints Analysis (Query Stability)
-- 27 Database Recovery Model & Backups (Data Safety)
-- 28 Object Permissions & Security Audit (Security)
-- 29 Server Configuration Issues (Config Health)
-- 30 Extended Events & Trace Configuration (Monitoring)
-- ============================================================
IF OBJECT_ID('dbo.dbusp_PerformanceStats', 'P') IS NOT NULL
DROP PROCEDURE dbo.dbusp_PerformanceStats;
GO
CREATE PROCEDURE dbo.dbusp_PerformanceStats
@ThresholdAvgSec FLOAT = 2, -- Avg elapsed time filter (seconds)
@ThresholdCPUSec FLOAT = 1, -- Avg CPU time filter (seconds)
@ThresholdReads BIGINT = 10000, -- Total logical+physical reads filter
@TableSizeInMB INT = 1024, -- Table size threshold (MB) -> 1 GB default
@RowCount BIGINT = 10000000, -- Row count threshold -> 10 Million
@TopRows INT = 10, -- Top N rows for query lists
@DbName SYSNAME = N'', -- Target DB; blank = current DB
@FragmentationThreshold FLOAT = 30.0, -- Index fragmentation % threshold
@MinIndexSizePages INT = 500, -- Skip small indexes in frag check
@StaleStatsThresholdDays INT = 7, -- Statistics older than N days = stale
@LockEscalationThreshold INT = 5000, -- Locks before escalation happens
@QueryStoreThreshold INT = 10, -- Query Store top N items
@ShowSection TINYINT = 0, -- 0 = All sections; 1-30 = specific section
@OnlyProblems BIT = 0, -- 1 = hide INFO / healthy rows
@IncludeRunbook BIT = 1 -- 1 = include DBA guidance text
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
-- ── Resolve DB name ──────────────────────────────────────────
IF @DbName = N'' OR @DbName IS NULL
SET @DbName = DB_NAME();
DECLARE @TargetDbId INT = DB_ID(@DbName);
DECLARE @SqlMajorVersion INT;
DECLARE @Edition NVARCHAR(128) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128));
DECLARE @CpuCount INT;
DECLARE @ServerStartTime DATETIME;
SELECT
@SqlMajorVersion = CONVERT(INT, PARSENAME(CONVERT(VARCHAR(32), SERVERPROPERTY('ProductVersion')), 4)),
@CpuCount = cpu_count
FROM sys.dm_os_sys_info;
SELECT @ServerStartTime = create_date
FROM sys.databases
WHERE database_id = 2;
-- ── Capture this SP own sql_handle to exclude from results ──
DECLARE @ThisSqlHandle VARBINARY(64);
SELECT @ThisSqlHandle = sql_handle
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;
-- ── Section Directory (always shown) ─────────────────────────
SELECT ID, [Type], Title
FROM (VALUES
(1, 'Table Size', 'Large Tables (>' + CAST(@TableSizeInMB AS VARCHAR) + ' MB or >' + CAST(@RowCount AS VARCHAR) + ' rows)'),
(2, 'Query Perf', 'Top CPU Heavy Queries'),
(3, 'Query Perf', 'Top Duration Queries (Longest Avg)'),
(4, 'Query Perf', 'Top IO Heavy Queries (Reads)'),
(5, 'Concurrency', 'Currently Running + Blocking Queries'),
(6, 'Query Tuning','Execution Plan XML for Top Slow Queries'),
(7, 'SP Perf', 'Stored Procedure Performance Analysis'),
(8, 'Index', 'Missing Index Suggestions'),
(9, 'Index', 'Index Fragmentation Report'),
(10, 'Index', 'Unused Indexes'),
(11, 'Wait Stats', 'Top Wait Statistics (Server-Wide)'),
(12, 'TempDB', 'TempDB Usage Per Session'),
(13, 'Memory', 'Memory Pressure Indicators'),
(14, 'Deadlock', 'Deadlock History'),
(15, 'Concurrency', 'Lock Escalation & Contention Pattern'),
(16, 'Integrity', 'Stale Statistics Report (>' + CAST(@StaleStatsThresholdDays AS VARCHAR) + ' days)'),
(17, 'DB Health', 'Database Health & Configuration Status'),
(18, 'DB Health', 'Transaction Log Status & VLF Health'),
(19, 'Storage', 'Page Compression Opportunities'),
(20, 'Query Store', 'Plan Cache Regression / Query Store Fallback'),
(21, 'Query Issues','Parameter Sniffing Detection'),
(22, 'IO Perf', 'Database IO Subsystem Analysis'),
(23, 'Concurrency', 'Connection Pool Analysis'),
(24, 'CPU Health', 'CPU & Scheduler Queue Analysis'),
(25, 'Storage', 'Table Partitioning Opportunities'),
(26, 'Query Stab', 'Query Recompilation & Query Hints Analysis'),
(27, 'Data Safety', 'Database Recovery Model & Backup Status'),
(28, 'Security', 'Object Permissions & Security Audit'),
(29, 'Config', 'Server Configuration Issues & Best Practices'),
(30, 'Monitoring', 'Extended Events & Trace Configuration')
) AS T(ID, [Type], Title);
-- ============================================================
-- SECTION 1 : Large Tables
-- ============================================================
IF @ShowSection IN (0, 1)
BEGIN
SELECT
'Large Tables' AS [Section],
t.NAME AS [Table],
SUM(CASE WHEN i.index_id < 2 THEN p.rows ELSE 0 END) AS [Row Count],
CAST(SUM(a.total_pages) * 8.0 / 1024 AS DECIMAL(10,1)) AS [Total Size MB],
CAST(SUM(a.total_pages) * 8.0 / 1024 / 1024 AS DECIMAL(10,3)) AS [Total Size GB],
CAST(SUM(a.used_pages) * 8.0 / 1024 AS DECIMAL(10,1)) AS [Used Size MB]
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE i.index_id < 2
GROUP BY t.NAME
HAVING
(SUM(a.total_pages) * 8.0 / 1024) >= @TableSizeInMB
OR SUM(CASE WHEN i.index_id < 2 THEN p.rows ELSE 0 END) >= @RowCount
ORDER BY [Total Size MB] DESC;
END
-- ============================================================
-- SECTION 2 : Top CPU Heavy Queries
-- ============================================================
IF @ShowSection IN (0, 2)
BEGIN
SELECT *
FROM
(
SELECT TOP (@TopRows)
'Top CPU Queries' AS [Section],
CASE
WHEN (QS.total_worker_time / NULLIF(QS.execution_count,0)) / 1000000.0 >= (@ThresholdCPUSec * 5) THEN 'CRITICAL'
WHEN (QS.total_worker_time / NULLIF(QS.execution_count,0)) / 1000000.0 >= (@ThresholdCPUSec * 2) THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'High CPU Query' AS IssueType,
QS.execution_count,
QS.creation_time AS FirstExecuted,
QS.last_execution_time AS LastExecuted,
CAST(QS.total_worker_time / 1000000.0 AS DECIMAL(18,2)) AS TotalCPU_Sec,
CAST((QS.total_worker_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgCPU_Sec,
CAST(QS.total_elapsed_time / 1000000.0 AS DECIMAL(18,2)) AS TotalElapsed_Sec,
CAST((QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgElapsed_Sec,
QS.total_logical_reads + QS.total_physical_reads AS TotalReads,
QS.total_logical_writes AS TotalWrites,
DB_NAME(T.dbid) AS DBName,
CASE
WHEN QS.total_logical_reads + QS.total_physical_reads > @ThresholdReads THEN 'Expensive scans, missing indexes, bad joins, or parameter sniffing can drive CPU.'
ELSE 'High CPU concentrated inside execution plan operators or repeated recompiles.'
END AS LikelyCause,
CASE
WHEN QS.total_logical_reads + QS.total_physical_reads > @ThresholdReads THEN 'Review actual plan, reduce reads, and validate selective indexes before touching MAXDOP.'
ELSE 'Review execution plan for CPU-heavy operators and validate parameter stability.'
END AS RecommendedAction,
'SET STATISTICS IO ON; SET STATISTICS TIME ON; -- Review actual plan for this statement' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Check plan cache entry, compare reads vs CPU, capture actual execution plan, and test with representative parameters in lower environment.'
ELSE NULL
END AS DBARunbook,
T.text AS FullQueryText,
ISNULL(NULLIF(LTRIM(RTRIM(
SUBSTRING(T.text,
(QS.statement_start_offset / 2) + 1,
CASE
WHEN QS.statement_end_offset = -1 THEN LEN(T.text)
ELSE (QS.statement_end_offset - QS.statement_start_offset) / 2
END
)
)), ''), T.text) AS ExactStatement
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
WHERE (QS.total_worker_time / NULLIF(QS.execution_count,0)) / 1000000.0 > @ThresholdCPUSec
AND QS.sql_handle <> ISNULL(@ThisSqlHandle, 0x0)
AND DB_NAME(T.dbid) = @DbName
AND T.text IS NOT NULL
) AS CPUQ
WHERE @OnlyProblems = 0 OR CPUQ.Severity <> 'INFO'
ORDER BY CPUQ.TotalCPU_Sec DESC;
END
-- ============================================================
-- SECTION 3 : Top Duration Queries
-- ============================================================
IF @ShowSection IN (0, 3)
BEGIN
SELECT *
FROM
(
SELECT TOP (@TopRows)
'Top Duration Queries' AS [Section],
CASE
WHEN (QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 >= (@ThresholdAvgSec * 5) THEN 'CRITICAL'
WHEN (QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 >= (@ThresholdAvgSec * 2) THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Long Running Query' AS IssueType,
QS.execution_count,
QS.creation_time AS FirstExecuted,
QS.last_execution_time AS LastExecuted,
CAST(QS.total_elapsed_time / 1000000.0 AS DECIMAL(18,2)) AS TotalElapsed_Sec,
CAST((QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgElapsed_Sec,
CAST(QS.total_worker_time / 1000000.0 AS DECIMAL(18,2)) AS TotalCPU_Sec,
QS.total_logical_reads + QS.total_physical_reads AS TotalReads,
DB_NAME(T.dbid) AS DBName,
CASE
WHEN CAST(QS.total_worker_time AS FLOAT) / NULLIF(QS.total_elapsed_time,0) < 0.30 THEN 'Elapsed time is much higher than CPU, so waits, blocking, IO, or client fetch delay are likely.'
ELSE 'Query is expensive even when running, usually due to poor plan shape, large row counts, or repeated scans.'
END AS LikelyCause,
CASE
WHEN CAST(QS.total_worker_time AS FLOAT) / NULLIF(QS.total_elapsed_time,0) < 0.30 THEN 'Correlate with wait stats and blocking section before changing indexes.'
ELSE 'Review joins, predicates, and statistics quality for this statement.'
END AS RecommendedAction,
'SET STATISTICS IO ON; SET STATISTICS TIME ON; -- Capture actual plan and compare waits during execution' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'If duration is high but CPU is low, first check waits, blockers, TempDB spills, and storage latency. If both are high, tune plan shape and row reduction.'
ELSE NULL
END AS DBARunbook,
T.text AS FullQueryText,
ISNULL(NULLIF(LTRIM(RTRIM(
SUBSTRING(T.text,
(QS.statement_start_offset / 2) + 1,
CASE
WHEN QS.statement_end_offset = -1 THEN LEN(T.text)
ELSE (QS.statement_end_offset - QS.statement_start_offset) / 2
END
)
)), ''), T.text) AS ExactStatement
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
WHERE (QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 > @ThresholdAvgSec
AND QS.sql_handle <> ISNULL(@ThisSqlHandle, 0x0)
AND DB_NAME(T.dbid) = @DbName
AND T.text IS NOT NULL
) AS DurQ
WHERE @OnlyProblems = 0 OR DurQ.Severity <> 'INFO'
ORDER BY DurQ.AvgElapsed_Sec DESC;
END
-- ============================================================
-- SECTION 4 : Top IO Heavy Queries
-- ============================================================
IF @ShowSection IN (0, 4)
BEGIN
SELECT *
FROM
(
SELECT TOP (@TopRows)
'Top IO Queries' AS [Section],
CASE
WHEN (QS.total_logical_reads + QS.total_physical_reads) >= (@ThresholdReads * 10) THEN 'CRITICAL'
WHEN (QS.total_logical_reads + QS.total_physical_reads) >= (@ThresholdReads * 3) THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'High Read Query' AS IssueType,
QS.execution_count,
QS.creation_time AS FirstExecuted,
QS.last_execution_time AS LastExecuted,
QS.total_logical_reads + QS.total_physical_reads AS TotalReads,
(QS.total_logical_reads + QS.total_physical_reads)
/ NULLIF(QS.execution_count,0) AS AvgReads,
QS.total_logical_writes AS TotalWrites,
CAST(QS.total_worker_time / 1000000.0 AS DECIMAL(18,2)) AS TotalCPU_Sec,
CAST(QS.total_elapsed_time / 1000000.0 AS DECIMAL(18,2)) AS TotalElapsed_Sec,
DB_NAME(T.dbid) AS DBName,
'Large scans, non-selective predicates, stale statistics, or missing covering indexes are likely.' AS LikelyCause,
'Check missing/duplicate indexes, verify up-to-date statistics, and reduce returned rows.' AS RecommendedAction,
'SET STATISTICS IO ON; -- Validate seekability and compare actual vs estimated row counts' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Start with estimated and actual execution plans, then validate index usage and stale stats before creating new indexes.'
ELSE NULL
END AS DBARunbook,
T.text AS FullQueryText,
ISNULL(NULLIF(LTRIM(RTRIM(
SUBSTRING(T.text,
(QS.statement_start_offset / 2) + 1,
CASE
WHEN QS.statement_end_offset = -1 THEN LEN(T.text)
ELSE (QS.statement_end_offset - QS.statement_start_offset) / 2
END
)
)), ''), T.text) AS ExactStatement
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
WHERE (QS.total_logical_reads + QS.total_physical_reads) > @ThresholdReads
AND QS.sql_handle <> ISNULL(@ThisSqlHandle, 0x0)
AND DB_NAME(T.dbid) = @DbName
AND T.text IS NOT NULL
) AS IOQ
WHERE @OnlyProblems = 0 OR IOQ.Severity <> 'INFO'
ORDER BY IOQ.TotalReads DESC;
END
-- ============================================================
-- SECTION 5 : Currently Running + Blocking Queries
-- OUTER APPLY used for blocker — safe when blocking_session_id = NULL/0
-- ============================================================
IF @ShowSection IN (0, 5)
BEGIN
SELECT *
FROM
(
SELECT
'Active + Blocking' AS [Section],
CASE
WHEN R.blocking_session_id > 0 THEN 'CRITICAL'
WHEN R.wait_type LIKE 'LCK%' OR R.open_transaction_count > 0 THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
CASE
WHEN R.blocking_session_id > 0 THEN 'Blocking'
WHEN R.wait_type LIKE 'LCK%' THEN 'Lock Wait'
ELSE 'Active Request'
END AS IssueType,
R.session_id,
R.status,
R.command,
DB_NAME(R.database_id) AS DBName,
S.login_name AS LoginName,
S.host_name AS HostName,
S.program_name AS ProgramName,
T.text AS QueryText,
R.start_time,
CAST(R.total_elapsed_time / 1000.0 AS DECIMAL(18,1)) AS ElapsedTimeSec,
CAST(R.cpu_time / 1000.0 AS DECIMAL(18,1)) AS CPUTimeSec,
R.granted_query_memory AS GrantedMemoryKB,
R.reads AS CurrentReads,
R.writes AS CurrentWrites,
R.logical_reads AS CurrentLogicalReads,
R.wait_type,
R.wait_time,
R.wait_resource,
R.blocking_session_id,
BT.text AS BlockingQueryText,
R.open_transaction_count AS OpenTransactions,
CASE
WHEN R.blocking_session_id > 0 THEN 'Blocking chain detected. Open transaction, long-running DML, or uncommitted work is likely holding locks.'
WHEN R.wait_type LIKE 'LCK%' THEN 'Session is waiting on locks even if direct blocker text is not available.'
ELSE 'Request is active; review only if elapsed time or resource usage is high.'
END AS LikelyCause,
CASE
WHEN R.blocking_session_id > 0 THEN 'Review blocker session first, validate open transaction, and kill only after business confirmation.'
WHEN R.wait_type LIKE 'LCK%' THEN 'Trace blocker, commit/rollback offending transaction, and reduce transaction scope.'
ELSE 'Monitor request and capture plan if it remains active beyond normal runtime.'
END AS RecommendedAction,
CASE
WHEN R.blocking_session_id > 0 THEN 'DBCC INPUTBUFFER(' + CAST(R.blocking_session_id AS VARCHAR(20)) + ');'
ELSE 'DBCC INPUTBUFFER(' + CAST(R.session_id AS VARCHAR(20)) + ');'
END AS FixScript,
CASE
WHEN @IncludeRunbook = 1 AND R.blocking_session_id > 0 THEN 'Inspect blocker session, host, login, open transaction count, and business impact. Do not kill blocker before confirming rollback cost.'
WHEN @IncludeRunbook = 1 THEN 'Capture session text, wait type, and elapsed time. If request persists, correlate with waits, IO, and TempDB sections.'
ELSE NULL
END AS DBARunbook
FROM sys.dm_exec_requests R
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T
LEFT JOIN sys.dm_exec_sessions S ON R.session_id = S.session_id
LEFT JOIN sys.dm_exec_requests BR ON R.blocking_session_id = BR.session_id
OUTER APPLY sys.dm_exec_sql_text(BR.sql_handle) BT
WHERE R.session_id > 50
AND R.session_id <> @@SPID
AND DB_NAME(R.database_id) = @DbName
AND T.text IS NOT NULL
) AS BlockQ
WHERE @OnlyProblems = 0 OR BlockQ.Severity <> 'INFO'
ORDER BY BlockQ.ElapsedTimeSec DESC;
END
-- ============================================================
-- SECTION 6 : Execution Plans for Slow Queries
-- ============================================================
IF @ShowSection IN (0, 6)
BEGIN
SELECT TOP (@TopRows)
'Execution Plans' AS [Section],
QS.execution_count,
CAST(QS.total_elapsed_time / 1000000.0 AS DECIMAL(18,2)) AS TotalElapsed_Sec,
CAST((QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgElapsed_Sec,
DB_NAME(T.dbid) AS DBName,
-- Full batch text
T.text AS FullQueryText,
-- Exact slow statement
ISNULL(NULLIF(LTRIM(RTRIM(
SUBSTRING(T.text,
(QS.statement_start_offset / 2) + 1,
CASE
WHEN QS.statement_end_offset = -1 THEN LEN(T.text)
ELSE (QS.statement_end_offset - QS.statement_start_offset) / 2
END
)
)), ''), T.text) AS ExactStatement,
P.query_plan AS ExecutionPlanXml
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) P
WHERE (QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 > @ThresholdAvgSec
AND QS.sql_handle <> ISNULL(@ThisSqlHandle, 0x0)
AND DB_NAME(T.dbid) = @DbName
ORDER BY AvgElapsed_Sec DESC;
END
-- ============================================================
-- SECTION 7 : Stored Procedure Performance
-- ============================================================
IF @ShowSection IN (0, 7)
BEGIN
SELECT *
FROM
(
SELECT
'SP Performance' AS [Section],
CASE
WHEN (PS.total_elapsed_time / NULLIF(PS.execution_count,0)) / 1000000.0 >= (@ThresholdAvgSec * 5) THEN 'CRITICAL'
WHEN (PS.total_elapsed_time / NULLIF(PS.execution_count,0)) / 1000000.0 >= (@ThresholdAvgSec * 2) THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Slow Stored Procedure' AS IssueType,
DB_NAME(PS.database_id) AS DBName,
OBJECT_NAME(PS.object_id, PS.database_id) AS ProcedureName,
PS.cached_time,
PS.last_execution_time,
PS.execution_count,
CAST(PS.total_elapsed_time / 1000000.0 AS DECIMAL(18,2)) AS TotalElapsedSec,
CAST((PS.total_elapsed_time / NULLIF(PS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgElapsedSec,
CAST(PS.total_worker_time / 1000000.0 AS DECIMAL(18,2)) AS TotalCPUSec,
CAST((PS.total_worker_time / NULLIF(PS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgCPUSec,
PS.total_logical_reads AS TotalLogicalReads,
PS.total_logical_reads / NULLIF(PS.execution_count,0) AS AvgLogicalReads,
PS.total_logical_writes AS TotalLogicalWrites,
PS.total_physical_reads AS TotalPhysicalReads,
PS.total_physical_reads / NULLIF(PS.execution_count,0) AS AvgPhysicalReads,
CASE
WHEN PS.total_physical_reads > 0 THEN 'Procedure is hitting storage and likely needs indexing, stats review, or row reduction.'
WHEN PS.total_logical_reads / NULLIF(PS.execution_count,0) > @ThresholdReads THEN 'Procedure is scan-heavy and may have poor parameter selectivity or stale stats.'
ELSE 'Procedure CPU/runtime is high and needs statement-level plan analysis.'
END AS LikelyCause,
'Drill into top statements inside the procedure, review plan cache, and compare runtime by parameter set.' AS RecommendedAction,
'EXEC sp_helptext ''' + OBJECT_NAME(PS.object_id, PS.database_id) + ''';' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Capture the procedure text, identify the slowest statements, and test representative parameters with actual plans before changing indexes.'
ELSE NULL
END AS DBARunbook
FROM sys.dm_exec_procedure_stats PS
WHERE DB_NAME(PS.database_id) = @DbName
AND (PS.total_elapsed_time / NULLIF(PS.execution_count,0)) / 1000000.0 > @ThresholdAvgSec
AND OBJECT_NAME(PS.object_id, PS.database_id) <> 'dbusp_PerformanceStats'
) AS SPQ
WHERE @OnlyProblems = 0 OR SPQ.Severity <> 'INFO'
ORDER BY SPQ.AvgElapsedSec DESC;
END
-- ============================================================
-- SECTION 8 : Missing Index Suggestions
-- SQL Engine internally track karta hai kaunsa index
-- queries ke liye helpful hota — ye wahi list hai
-- ImpactScore jitna bada = utna zaruri index
-- ============================================================
IF @ShowSection IN (0, 8)
BEGIN
SELECT *
FROM
(
SELECT TOP 20
'Missing Indexes' AS [Section],
CASE
WHEN CAST(MIG.avg_total_user_cost * MIG.avg_user_impact * (MIG.user_seeks + MIG.user_scans) AS DECIMAL(18,1)) >= 100000 THEN 'CRITICAL'
WHEN CAST(MIG.avg_total_user_cost * MIG.avg_user_impact * (MIG.user_seeks + MIG.user_scans) AS DECIMAL(18,1)) >= 10000 THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Missing Index Candidate' AS IssueType,
DB_NAME(MID.database_id) AS DBName,
OBJECT_NAME(MID.object_id, MID.database_id) AS [Table],
CAST(
MIG.avg_total_user_cost
* MIG.avg_user_impact
* (MIG.user_seeks + MIG.user_scans)
AS DECIMAL(18,1)) AS ImpactScore,
CAST(MIG.avg_user_impact AS DECIMAL(5,1)) AS [Avg Impact %],
MIG.user_seeks AS Seeks,
MIG.user_scans AS Scans,
MID.equality_columns AS EqualityCols,
MID.inequality_columns AS InequalityCols,
MID.included_columns AS IncludedCols,
'Optimizer repeatedly needed a better access path; existing indexes may be missing, non-covering, or stale.' AS LikelyCause,
'Validate duplicate/overlapping indexes and test this index in lower environment before production deployment.' AS RecommendedAction,
'CREATE INDEX '
+ QUOTENAME(
LEFT(
'IX_Missing_'
+ OBJECT_NAME(MID.object_id, MID.database_id)
+ '_'
+ REPLACE(REPLACE(REPLACE(
ISNULL(REPLACE(MID.equality_columns, ', ', '_'), '')
+ CASE
WHEN MID.inequality_columns IS NOT NULL
THEN '_' + REPLACE(MID.inequality_columns, ', ', '_')
ELSE ''
END
, '[',''), ']',''), ',','')
, 120) -- prevent >128 char error
)
+ ' ON '
+ QUOTENAME(DB_NAME(MID.database_id)) + '.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(MID.object_id, MID.database_id)) + '.'
+ QUOTENAME(OBJECT_NAME(MID.object_id, MID.database_id))
+ ' ('
+ ISNULL(MID.equality_columns, '')
+ CASE
WHEN MID.inequality_columns IS NOT NULL THEN
CASE WHEN MID.equality_columns IS NOT NULL THEN ', ' ELSE '' END
+ MID.inequality_columns
ELSE ''
END
+ ')'
+ CASE
WHEN MID.included_columns IS NOT NULL
THEN ' INCLUDE (' + MID.included_columns + ')'
ELSE ''
END
+ ';' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Compare with existing indexes first. Avoid creating near-duplicate indexes, and measure write overhead after implementation.'
ELSE NULL
END AS DBARunbook
FROM sys.dm_db_missing_index_details MID
INNER JOIN sys.dm_db_missing_index_groups MIG_GRP ON MID.index_handle = MIG_GRP.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats MIG ON MIG_GRP.index_group_handle = MIG.group_handle
WHERE DB_NAME(MID.database_id) = @DbName
) AS MIQ
WHERE @OnlyProblems = 0 OR MIQ.Severity <> 'INFO'
ORDER BY MIQ.ImpactScore DESC;
END
-- ============================================================
-- SECTION 9 : Index Fragmentation
-- 10-30% frag -> REORGANIZE (lightweight, online)
-- >30% frag -> REBUILD (heavier but thorough)
-- ============================================================
IF @ShowSection IN (0, 9)
BEGIN
SELECT *
FROM
(
SELECT
'Index Fragmentation' AS [Section],
CASE
WHEN IPS.avg_fragmentation_in_percent > @FragmentationThreshold THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Index Fragmentation' AS IssueType,
@DbName AS DBName,
OBJECT_NAME(IPS.object_id, @TargetDbId) AS [Table],
I.name AS [Index],
IPS.index_type_desc AS IndexType,
IPS.index_depth AS [Depth],
IPS.page_count AS Pages,
CAST(IPS.avg_fragmentation_in_percent AS DECIMAL(5,1)) AS [Frag %],
'Index has fragmented pages due to page splits and ongoing modifications.' AS LikelyCause,
CASE
WHEN IPS.avg_fragmentation_in_percent > @FragmentationThreshold THEN 'Schedule REBUILD in maintenance window; use REORGANIZE only for moderate fragmentation.'
ELSE 'Reorganize during low activity and then update statistics if needed.'
END AS RecommendedAction,
CASE
WHEN IPS.avg_fragmentation_in_percent > @FragmentationThreshold
THEN 'ALTER INDEX [' + I.name + '] ON [' + OBJECT_SCHEMA_NAME(IPS.object_id, @TargetDbId) + '].[' + OBJECT_NAME(IPS.object_id, @TargetDbId) + '] REBUILD;'
ELSE 'ALTER INDEX [' + I.name + '] ON [' + OBJECT_SCHEMA_NAME(IPS.object_id, @TargetDbId) + '].[' + OBJECT_NAME(IPS.object_id, @TargetDbId) + '] REORGANIZE;'
END AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Skip tiny indexes, avoid rebuilding during peak load, and review fill factor if the same index fragments quickly after maintenance.'
ELSE NULL
END AS DBARunbook
FROM sys.dm_db_index_physical_stats(@TargetDbId, NULL, NULL, NULL, 'LIMITED') IPS
INNER JOIN sys.indexes I ON IPS.object_id = I.object_id AND IPS.index_id = I.index_id
WHERE IPS.page_count >= @MinIndexSizePages
AND IPS.avg_fragmentation_in_percent >= 10
AND I.name IS NOT NULL
) AS FragQ
WHERE @OnlyProblems = 0 OR FragQ.Severity <> 'INFO'
ORDER BY FragQ.[Frag %] DESC;
END
-- ============================================================
-- SECTION 10 : Unused Indexes
-- Ye indexes likhe hain but queries use nahi karti
-- INSERT/UPDATE/DELETE pe ye bhi maintain hote hain = overhead
-- NOTE: Stats SQL Server restart pe reset hoti hain
-- ============================================================
IF @ShowSection IN (0, 10)
BEGIN
SELECT *
FROM
(
SELECT
'Unused Indexes' AS [Section],
CASE
WHEN ISNULL(IUS.user_updates,0) >= 1000 THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Unused Index' AS IssueType,
@DbName AS DBName,
OBJECT_NAME(I.object_id, @TargetDbId) AS [Table],
I.name AS [Index],
I.type_desc AS IndexType,
ISNULL(IUS.user_seeks, 0) AS UserSeeks,
ISNULL(IUS.user_scans, 0) AS UserScans,
ISNULL(IUS.user_lookups, 0) AS UserLookups,
ISNULL(IUS.user_updates, 0) AS UserUpdates,
IUS.last_user_seek AS LastSeek,
IUS.last_user_scan AS LastScan,
'Index is being maintained for writes but has shown no read benefit since the last server restart.' AS LikelyCause,
CASE
WHEN ISNULL(IUS.user_updates,0) >= 1000 THEN 'Validate usage history and dependency before dropping this index.'
ELSE 'Monitor across at least one business cycle before removal.'
END AS RecommendedAction,
'DROP INDEX [' + I.name + '] ON [' + OBJECT_SCHEMA_NAME(I.object_id, @TargetDbId) + '].[' + OBJECT_NAME(I.object_id, @TargetDbId) + '];' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Do not drop immediately after restart-based stats reset. Validate with query plans, job workloads, and reporting windows first.'
ELSE NULL
END AS DBARunbook
FROM sys.indexes I
LEFT JOIN sys.dm_db_index_usage_stats IUS
ON I.object_id = IUS.object_id
AND I.index_id = IUS.index_id
AND IUS.database_id = @TargetDbId
INNER JOIN sys.objects O ON I.object_id = O.object_id
WHERE O.type = 'U'
AND I.index_id > 0
AND I.is_primary_key = 0
AND I.is_unique_constraint = 0
AND (ISNULL(IUS.user_seeks,0) + ISNULL(IUS.user_scans,0) + ISNULL(IUS.user_lookups,0)) = 0
) AS UnusedQ
WHERE @OnlyProblems = 0 OR UnusedQ.Severity <> 'INFO'
ORDER BY UnusedQ.UserUpdates DESC;
END
-- ============================================================
-- SECTION 11 : Top Wait Statistics
-- Server kahan "wait" kar raha hai — root cause tool
-- CXPACKET -> Parallelism (check MAXDOP)
-- LCK_M_* -> Locking/Blocking
-- PAGEIOLATCH_* -> Disk IO slow / missing indexes
-- SOS_SCHEDULER -> CPU pressure
-- WRITELOG -> TLog IO slow
-- RESOURCE_SEMAPHORE -> Memory grant waits
-- ============================================================
IF @ShowSection IN (0, 11)
BEGIN
SELECT *
FROM
(
SELECT TOP 20
'Wait Statistics' AS [Section],
CASE
WHEN CAST(WS.wait_time_ms * 100.0 / NULLIF(SUM(WS.wait_time_ms) OVER(), 0) AS DECIMAL(5,1)) >= 25 THEN 'CRITICAL'
WHEN CAST(WS.wait_time_ms * 100.0 / NULLIF(SUM(WS.wait_time_ms) OVER(), 0) AS DECIMAL(5,1)) >= 10 THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
CASE
WHEN WS.wait_type LIKE 'LCK%' THEN 'Locking / Blocking'
WHEN WS.wait_type LIKE 'PAGEIOLATCH%' THEN 'Disk IO'
WHEN WS.wait_type IN ('CXPACKET','CXCONSUMER') THEN 'Parallelism'
WHEN WS.wait_type LIKE 'SOS_SCHEDULER%' THEN 'CPU Pressure'
WHEN WS.wait_type = 'WRITELOG' THEN 'Transaction Log IO'
WHEN WS.wait_type = 'RESOURCE_SEMAPHORE' THEN 'Memory Grant Pressure'
WHEN WS.wait_type LIKE 'ASYNC_NETWORK%' THEN 'Client Fetch Delay'
ELSE 'General Wait'
END AS IssueType,
WS.wait_type,
CAST(WS.wait_time_ms / 1000.0 AS DECIMAL(18,1)) AS TotalWaitSec,
CAST(WS.signal_wait_time_ms / 1000.0 AS DECIMAL(18,1)) AS SignalWaitSec,
CAST((WS.wait_time_ms - WS.signal_wait_time_ms) / 1000.0 AS DECIMAL(18,1)) AS ResourceWaitSec,
WS.waiting_tasks_count AS WaitingTasks,
CAST(WS.wait_time_ms * 100.0
/ NULLIF(SUM(WS.wait_time_ms) OVER(), 0) AS DECIMAL(5,1)) AS [Wait %],
CASE
WHEN WS.wait_type LIKE 'LCK%' THEN 'Blocking chains, long transactions, or hot rows/pages are dominating wait time.'
WHEN WS.wait_type LIKE 'PAGEIOLATCH%' THEN 'Storage latency, large scans, or missing indexes are forcing physical reads.'
WHEN WS.wait_type IN ('CXPACKET','CXCONSUMER') THEN 'Parallel worker imbalance or low cost threshold can create excessive parallel waits.'
WHEN WS.wait_type LIKE 'SOS_SCHEDULER%' THEN 'Runnable tasks are waiting for CPU time.'
WHEN WS.wait_type = 'WRITELOG' THEN 'Transaction log flush latency or undersized log files are slowing commits.'
WHEN WS.wait_type = 'RESOURCE_SEMAPHORE' THEN 'Large memory grants are queued behind limited workspace memory.'
WHEN WS.wait_type LIKE 'ASYNC_NETWORK%' THEN 'Application/client is consuming result sets slowly.'
ELSE 'Review this wait with workload context before taking action.'
END AS LikelyCause,
CASE
WHEN WS.wait_type LIKE 'LCK%' THEN 'Go to blocking and lock sections first; reduce transaction scope and indexing contention paths.'
WHEN WS.wait_type LIKE 'PAGEIOLATCH%' THEN 'Review high-read queries, missing indexes, and storage latency.'
WHEN WS.wait_type IN ('CXPACKET','CXCONSUMER') THEN 'Validate MAXDOP and cost threshold after confirming the queries are worth parallelism.'
WHEN WS.wait_type LIKE 'SOS_SCHEDULER%' THEN 'Review scheduler queue, expensive CPU queries, and server concurrency.'
WHEN WS.wait_type = 'WRITELOG' THEN 'Inspect log disk latency, autogrowth pattern, and transaction batch size.'
WHEN WS.wait_type = 'RESOURCE_SEMAPHORE' THEN 'Tune big memory-grant queries and validate max server memory settings.'
WHEN WS.wait_type LIKE 'ASYNC_NETWORK%' THEN 'Review application fetch pattern and avoid returning unnecessary rows.'
ELSE 'Monitor with workload change history before remediation.'
END AS RecommendedAction,
CASE
WHEN WS.wait_type LIKE 'LCK%' THEN 'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 5;'
WHEN WS.wait_type LIKE 'PAGEIOLATCH%' THEN 'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 22;'
WHEN WS.wait_type = 'WRITELOG' THEN 'DBCC SQLPERF(LOGSPACE);'
ELSE '-- Investigate related section in this procedure'
END AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Wait stats are cumulative since last restart (' + CONVERT(VARCHAR(19), @ServerStartTime, 120) + '). Clear interpretation must include workload timing and recent incidents.'
ELSE NULL
END AS DBARunbook
FROM sys.dm_os_wait_stats WS
WHERE WS.wait_type NOT IN (
'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_EVENTHANDLER',
'REQUEST_FOR_DEADLOCK_SEARCH','LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE',
'CHECKPOINT_QUEUE','DBMIRROR_EVENTS_QUEUE','SQLTRACE_BUFFER_FLUSH',
'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE','XE_DISPATCHER_WAIT',
'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',
'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',
'SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT','SP_SERVER_DIAGNOSTICS_SLEEP',
'WAITFOR','XE_TIMER_EVENT','BROKER_RECEIVE_WAITFOR',
'WAITFOR_TASKSHUTDOWN','SLEEP_BUFFERPOOL_SERIALIZATIONTEST',
'SLEEP_SYSTEMTASK','HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_WORK_QUEUE'
)
) AS WaitQ
WHERE @OnlyProblems = 0 OR WaitQ.Severity <> 'INFO'
ORDER BY WaitQ.TotalWaitSec DESC;
END
-- ============================================================
-- SECTION 12 : TempDB Usage Per Session
-- Kaunsa session TempDB pe zyada load daal raha hai
-- (#temp tables, sort spills, hash join spills)
-- ============================================================
IF @ShowSection IN (0, 12)
BEGIN
SELECT TOP (@TopRows)
'TempDB Usage' AS [Section],
TSU.session_id,
S.login_name,
S.host_name,
S.program_name,
CAST(TSU.user_objects_alloc_page_count * 8.0 / 1024 AS DECIMAL(10,1)) AS UserObjects_MB,
CAST(TSU.internal_objects_alloc_page_count * 8.0 / 1024 AS DECIMAL(10,1)) AS InternalObjects_MB,
CAST((TSU.user_objects_alloc_page_count
+ TSU.internal_objects_alloc_page_count) * 8.0 / 1024 AS DECIMAL(10,1)) AS TotalTempDB_MB,
T.text AS CurrentQuery
FROM sys.dm_db_session_space_usage TSU
INNER JOIN sys.dm_exec_sessions S ON TSU.session_id = S.session_id
LEFT JOIN sys.dm_exec_requests R ON TSU.session_id = R.session_id
OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) T
WHERE TSU.session_id > 50
AND (TSU.user_objects_alloc_page_count + TSU.internal_objects_alloc_page_count) > 0
ORDER BY TotalTempDB_MB DESC;
END
-- ============================================================
-- SECTION 13 : Memory Pressure Indicators
-- ============================================================
IF @ShowSection IN (0, 13)
BEGIN
-- 13a. Overall Memory Summary
SELECT
'Memory Summary' AS [Section],
CASE
WHEN memory_utilization_percentage >= 95 OR page_fault_count > 0 THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'SQL Memory Pressure' AS IssueType,
physical_memory_in_use_kb / 1024 AS [SQL Mem Used MB],
memory_utilization_percentage AS [Mem Utilization %],
page_fault_count AS PageFaults,
CASE
WHEN memory_utilization_percentage >= 95 THEN 'SQL Server is using almost all memory available within current target.'
WHEN page_fault_count > 0 THEN 'Process-level page faults indicate operating system memory churn.'
ELSE 'No immediate SQL memory pressure signal from process memory.'
END AS LikelyCause,
CASE
WHEN memory_utilization_percentage >= 95 THEN 'Validate max server memory against OS needs and correlate with pending memory grants.'
WHEN page_fault_count > 0 THEN 'Review OS memory pressure, antivirus exclusions, and non-SQL memory consumers.'
ELSE 'Monitor with memory grants and wait stats.'
END AS RecommendedAction,
'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 29;' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Treat this as an indicator only. Confirm with RESOURCE_SEMAPHORE waits, PLE trend, and max server memory configuration before changing memory.'
ELSE NULL
END AS DBARunbook
FROM sys.dm_os_process_memory
WHERE @OnlyProblems = 0
OR memory_utilization_percentage >= 95
OR page_fault_count > 0;
-- 13b. Buffer Pool usage by Database
SELECT
'Buffer Pool by DB' AS [Section],
CASE database_id
WHEN 32767 THEN 'ResourceDB'
ELSE DB_NAME(database_id)
END AS DBName,
COUNT(*) * 8 / 1024 AS BufferPool_MB,
SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) * 8 / 1024 AS DirtyPages_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY BufferPool_MB DESC;
-- 13c. Memory Grants (waiter_count > 0 = memory pressure)
SELECT
'Memory Grants' AS [Section],
CASE
WHEN SUM(waiter_count) > 0 THEN 'CRITICAL'
WHEN SUM(available_memory_kb) / 1024 < 512 THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Workspace Memory Grants' AS IssueType,
SUM(waiter_count) AS PendingMemoryGrants, -- > 0 is bad
SUM(grantee_count) AS GrantsOutstanding,
SUM(target_memory_kb) / 1024 AS TargetMemoryMB,
SUM(total_memory_kb) / 1024 AS TotalMemoryMB,
SUM(available_memory_kb) / 1024 AS AvailableMemoryMB,
CASE
WHEN SUM(waiter_count) > 0 THEN 'Queries are queued for memory grants, usually due to large sorts, hashes, or concurrent big reports.'
WHEN SUM(available_memory_kb) / 1024 < 512 THEN 'Available grant memory is low and may soon queue large queries.'
ELSE 'Memory grant subsystem currently looks stable.'
END AS LikelyCause,
CASE
WHEN SUM(waiter_count) > 0 THEN 'Tune largest memory grant queries, reduce spills, and validate max server memory.'
WHEN SUM(available_memory_kb) / 1024 < 512 THEN 'Review concurrent reporting workload and expensive sort/hash plans.'
ELSE 'Continue monitoring.'
END AS RecommendedAction,
'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 11;' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Capture currently running heavy queries when pending grants are non-zero; the blockers are often active reports or bad join/sort plans.'
ELSE NULL
END AS DBARunbook
FROM sys.dm_exec_query_resource_semaphores
HAVING @OnlyProblems = 0
OR SUM(waiter_count) > 0
OR SUM(available_memory_kb) / 1024 < 512;
END
-- ============================================================
-- SECTION 14 : Deadlock History
-- ============================================================
IF @ShowSection IN (0, 14)
BEGIN
SELECT
'Deadlock Guidance' AS [Section],
CASE
WHEN OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NOT NULL THEN 'INFO'
ELSE 'WARNING'
END AS Severity,
'Deadlock Capture' AS IssueType,
CASE
WHEN OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NOT NULL THEN 'Helper procedure is available for deadlock history review.'
ELSE 'No local deadlock helper procedure was found.'
END AS LikelyCause,
CASE
WHEN OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NOT NULL THEN 'Review captured deadlock graph and identify object access order mismatch.'
ELSE 'Enable deadlock capture through system_health Extended Events or trace flag based monitoring.'
END AS RecommendedAction,
CASE
WHEN OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NOT NULL THEN 'EXEC dbo.dbusp_DeadLockTree;'
ELSE 'SELECT CAST(target_data AS XML) FROM sys.dm_xe_session_targets st INNER JOIN sys.dm_xe_sessions s ON st.event_session_address = s.address WHERE s.name = ''system_health'';'
END AS FixScript,
CASE
WHEN @IncludeRunbook = 1 AND OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NOT NULL THEN 'Review victim and survivor statements, isolate index/order differences, and reduce transaction overlap.'
WHEN @IncludeRunbook = 1 THEN 'On SQL Server 2012, system_health usually captures deadlocks. Extract XML deadlock graph, then fix access order, indexing, or transaction scope.'
ELSE NULL
END AS DBARunbook
WHERE @OnlyProblems = 0
OR OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NULL;
IF OBJECT_ID('dbo.dbusp_DeadLockTree', 'P') IS NOT NULL
EXEC dbo.dbusp_DeadLockTree;
END
-- ============================================================
-- SECTION 15 : Lock Escalation & Contention Pattern
-- Shows current locks, escalation trends, and blocking patterns
-- ============================================================
IF @ShowSection IN (0, 15)
BEGIN
SELECT *
FROM
(
SELECT
'Lock Contention' AS [Section],
CASE
WHEN SUM(1) OVER (PARTITION BY TL.resource_type, TL.resource_database_id) > @LockEscalationThreshold THEN 'CRITICAL'
WHEN TL.request_mode IN ('X','IX','SIX') THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Lock Contention' AS IssueType,
R.session_id AS SessionID,
R.database_id AS DBId,
DB_NAME(R.database_id) AS DBName,
CONVERT(VARCHAR(100), R.command) AS Command,
CONVERT(VARCHAR(100), R.status) AS [Status],
TL.request_mode AS LockMode,
TL.request_type AS LockType,
TL.request_status AS LockStatus,
SUM(1) OVER (PARTITION BY TL.resource_type, TL.resource_database_id) AS LockCount,
CASE
WHEN SUM(1) OVER (PARTITION BY TL.resource_type, TL.resource_database_id) > @LockEscalationThreshold
THEN 'Large lock footprint suggests escalation risk or hot object contention.'
ELSE 'Current lock mix may still become a hotspot if transactions stay open.'
END AS LikelyCause,
CASE
WHEN SUM(1) OVER (PARTITION BY TL.resource_type, TL.resource_database_id) > @LockEscalationThreshold
THEN 'Reduce batch size, add selective indexes, and shorten transaction scope.'
ELSE 'Monitor lock footprint and correlate with blocker sessions.'
END AS RecommendedAction,
'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 5;' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Large lock counts usually come from wide updates/deletes or poor seekability. Review affected statements before considering lock hints.'
ELSE NULL
END AS DBARunbook,
T.text AS QueryText
FROM sys.dm_exec_requests R
INNER JOIN sys.dm_tran_locks TL ON R.session_id = TL.request_session_id
LEFT JOIN sys.dm_exec_sessions S ON R.session_id = S.session_id
OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) T
WHERE R.session_id > 50
AND R.database_id = @TargetDbId
AND TL.request_status <> 'WAIT'
) AS LockQ
WHERE @OnlyProblems = 0 OR LockQ.Severity <> 'INFO'
ORDER BY LockQ.LockCount DESC, LockQ.SessionID;
END
-- ============================================================
-- SECTION 16 : Stale Statistics Report
-- Shows tables where statistics haven't been updated in N days
-- Stale stats = bad cardinality estimates = bad query plans
-- ============================================================
IF @ShowSection IN (0, 16)
BEGIN
SELECT *
FROM
(
SELECT TOP 50
'Stale Statistics' AS [Section],
CASE
WHEN DATEDIFF(DAY, STATS_DATE(S.object_id, S.stats_id), GETDATE()) > (@StaleStatsThresholdDays * 4) THEN 'CRITICAL'
WHEN DATEDIFF(DAY, STATS_DATE(S.object_id, S.stats_id), GETDATE()) > @StaleStatsThresholdDays THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Stale Statistics' AS IssueType,
@DbName AS DBName,
OBJECT_NAME(S.object_id, @TargetDbId) AS [Table],
S.name AS StatName,
CAST(STATS_DATE(S.object_id, S.stats_id) AS DATE) AS LastUpdated,
DATEDIFF(DAY, STATS_DATE(S.object_id, S.stats_id), GETDATE()) AS StaleInDays,
'Cardinality estimates may drift after significant data change or prolonged time without stats refresh.' AS LikelyCause,
CASE
WHEN DATEDIFF(DAY, STATS_DATE(S.object_id, S.stats_id), GETDATE()) > @StaleStatsThresholdDays THEN 'Update statistics for this object and retest affected queries.'
ELSE 'Monitor high-change tables for auto-update effectiveness.'
END AS RecommendedAction,
'UPDATE STATISTICS [' + OBJECT_SCHEMA_NAME(S.object_id, @TargetDbId) + '].[' + OBJECT_NAME(S.object_id, @TargetDbId) + '] [' + S.name + '];' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Prioritize large or volatile tables first. If parameter-sensitive workloads improved after stats refresh, review auto-stats and maintenance cadence.'
ELSE NULL
END AS DBARunbook
FROM sys.stats S
WHERE OBJECTPROPERTY(S.object_id, 'IsUserTable') = 1
AND STATS_DATE(S.object_id, S.stats_id) IS NOT NULL
) AS StatsQ
WHERE @OnlyProblems = 0 OR StatsQ.Severity <> 'INFO'
ORDER BY StatsQ.StaleInDays DESC;
END
-- ============================================================
-- SECTION 17 : Database Health & Configuration Status
-- Recovery model, compatibility, flags, space info
-- ============================================================
IF @ShowSection IN (0, 17)
BEGIN
SELECT
'Database Health' AS [Section],
CASE
WHEN D.state_desc <> 'ONLINE' OR D.is_auto_close_on = 1 OR D.is_auto_shrink_on = 1 THEN 'CRITICAL'
WHEN D.page_verify_option_desc <> 'CHECKSUM' OR D.compatibility_level < 110 THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Database Configuration' AS IssueType,
D.name AS DBName,
D.recovery_model_desc AS RecoveryModel,
D.state_desc AS [State],
D.compatibility_level AS CompatLevel,
D.is_read_only AS IsReadOnly,
D.is_auto_close_on AS AutoClose_On,
D.is_auto_shrink_on AS AutoShrink_On,
D.page_verify_option_desc AS PageVerify,
SUM(CAST(MF.size AS BIGINT)) * 8 / 1024 / 1024 AS TotalSizeMB,
CASE
WHEN D.state_desc <> 'ONLINE' THEN 'Database is not fully online.'
WHEN D.is_auto_close_on = 1 THEN 'AUTO_CLOSE can cause repeated open/close overhead and plan cache churn.'
WHEN D.is_auto_shrink_on = 1 THEN 'AUTO_SHRINK can create fragmentation and unnecessary IO.'
WHEN D.page_verify_option_desc <> 'CHECKSUM' THEN 'Page verification is weaker than CHECKSUM.'
WHEN D.compatibility_level < 110 THEN 'Compatibility level is below SQL Server 2012 native level.'
ELSE 'Database health indicators are within expected baseline.'
END AS LikelyCause,
CASE
WHEN D.is_auto_close_on = 1 OR D.is_auto_shrink_on = 1 THEN 'Disable AUTO_CLOSE / AUTO_SHRINK unless a niche use case requires them.'
WHEN D.page_verify_option_desc <> 'CHECKSUM' THEN 'Set PAGE_VERIFY CHECKSUM.'
WHEN D.compatibility_level < 110 THEN 'Review application compatibility and raise database compatibility level when safe.'
ELSE 'Continue baseline monitoring.'
END AS RecommendedAction,
CASE
WHEN D.is_auto_close_on = 1 THEN 'ALTER DATABASE [' + D.name + '] SET AUTO_CLOSE OFF;'
WHEN D.is_auto_shrink_on = 1 THEN 'ALTER DATABASE [' + D.name + '] SET AUTO_SHRINK OFF;'
WHEN D.page_verify_option_desc <> 'CHECKSUM' THEN 'ALTER DATABASE [' + D.name + '] SET PAGE_VERIFY CHECKSUM;'
ELSE '-- No immediate fix required'
END AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Configuration fixes should be applied during controlled change windows and validated against vendor/application requirements.'
ELSE NULL
END AS DBARunbook
FROM sys.databases D
INNER JOIN sys.master_files MF ON D.database_id = MF.database_id
WHERE D.name = @DbName
AND (
@OnlyProblems = 0
OR D.state_desc <> 'ONLINE'
OR D.is_auto_close_on = 1
OR D.is_auto_shrink_on = 1
OR D.page_verify_option_desc <> 'CHECKSUM'
OR D.compatibility_level < 110
)
GROUP BY D.name, D.recovery_model_desc, D.state_desc, D.compatibility_level,
D.is_read_only, D.is_auto_close_on, D.is_auto_shrink_on, D.page_verify_option_desc, D.database_id;
END
-- ============================================================
-- SECTION 18 : Transaction Log Status & VLF Health
-- High VLF count = more log fragmentation = worse performance
-- IdeaL: <100 VLFs for healthy log
-- ============================================================
IF @ShowSection IN (0, 18)
BEGIN
CREATE TABLE #LogSpace
(
DatabaseName SYSNAME,
LogSizeMB DECIMAL(18,5),
LogSpaceUsedPct DECIMAL(18,5),
[Status] INT
);
INSERT INTO #LogSpace
EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS');
SELECT
'TLog Status' AS [Section],
CASE
WHEN LS.LogSpaceUsedPct >= 90 THEN 'CRITICAL'
WHEN LS.LogSpaceUsedPct >= 70 THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Transaction Log Health' AS IssueType,
LS.DatabaseName AS DBName,
CAST(LS.LogSizeMB AS DECIMAL(10,1)) AS LogSizeMB,
CAST(LS.LogSizeMB * LS.LogSpaceUsedPct / 100.0 AS DECIMAL(10,1)) AS LogUsedMB,
CAST(LS.LogSpaceUsedPct AS DECIMAL(10,2)) AS LogUsedPct,
'High log usage can come from large transactions, missing log backups, or undersized log/autogrowth pattern.' AS LikelyCause,
CASE
WHEN LS.LogSpaceUsedPct >= 90 THEN 'Take immediate action: check active transaction, log backup chain, and log autogrowth history.'
WHEN LS.LogSpaceUsedPct >= 70 THEN 'Review log backup frequency and growth settings before log fills further.'
ELSE 'Continue monitoring log utilization.'
END AS RecommendedAction,
'DBCC SQLPERF(LOGSPACE); DBCC LOGINFO(' + QUOTENAME(@DbName, '''') + ');' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'If recovery model is FULL, confirm recent log backups. If log reuse is blocked, inspect log_reuse_wait_desc and active long transactions.'
ELSE NULL
END AS DBARunbook
FROM #LogSpace LS
WHERE LS.DatabaseName = @DbName
AND (@OnlyProblems = 0 OR LS.LogSpaceUsedPct >= 70);
DROP TABLE #LogSpace;
END
-- ============================================================
-- SECTION 19 : Page Compression Opportunities
-- Identifies tables that could benefit from compression
-- Needs 2008+ Enterprise Edition
-- ============================================================
IF @ShowSection IN (0, 19)
BEGIN
SELECT
'Compression Opportunities' AS [Section],
OBJECT_SCHEMA_NAME(IPS.object_id) AS SchemaName,
OBJECT_NAME(IPS.object_id) AS [Table],
I.name AS [Index],
IPS.page_count AS UncompressedPages,
CAST(IPS.page_count * 8 / 1024.0 AS DECIMAL(10,1)) AS UncompressedSizeMB,
CASE
WHEN IPS.page_count > 1000
THEN 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(IPS.object_id) + '].['
+ OBJECT_NAME(IPS.object_id) + '] REBUILD WITH (DATA_COMPRESSION = PAGE);'
ELSE 'Too small for compression benefit'
END AS CompressionScript
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') IPS
INNER JOIN sys.indexes I ON IPS.object_id = I.object_id AND IPS.index_id = I.index_id
WHERE IPS.page_count > 1000
AND IPS.alloc_unit_type_desc = 'IN_ROW_DATA'
ORDER BY IPS.page_count DESC;
END
-- ============================================================
-- SECTION 20 : Plan Cache Regression / Query Store Fallback
-- SQL Server 2012 friendly fallback for regressed / unstable plans
-- ============================================================
IF @ShowSection IN (0, 20)
BEGIN
SELECT *
FROM
(
SELECT TOP (@QueryStoreThreshold)
'Plan Cache Regression' AS [Section],
CASE
WHEN QS.plan_generation_num >= 10 OR QS.max_elapsed_time >= (QS.min_elapsed_time * 20) THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Plan Regression / Instability' AS IssueType,
DB_NAME(T.dbid) AS DBName,
QS.execution_count AS ExecutionCount,
QS.plan_generation_num AS RecompilationCount,
CAST(QS.min_elapsed_time / 1000000.0 AS DECIMAL(18,4)) AS MinElapsedSec,
CAST(QS.max_elapsed_time / 1000000.0 AS DECIMAL(18,4)) AS MaxElapsedSec,
CAST((QS.max_elapsed_time - QS.min_elapsed_time) / 1000000.0 AS DECIMAL(18,4)) AS VarianceSec,
sys.fn_varbintohexstr(QS.plan_handle) AS PlanHandleHex,
SUBSTRING(T.text, 1, 200) AS QueryPreview,
'Plan cache shows high runtime variance or repeated recompiles, often due to parameter sensitivity, stats changes, or unstable memory grants.' AS LikelyCause,
'Compare good vs bad parameter sets, inspect cached plan, and consider stats refresh or targeted recompilation strategy.' AS RecommendedAction,
'DBCC FREEPROCCACHE(' + sys.fn_varbintohexstr(QS.plan_handle) + '); -- clear only this plan after validation' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'On SQL Server 2012 this section is a Query Store fallback. Validate parameter sniffing, stale statistics, and plan cache volatility before forcing plan-level changes.'
ELSE NULL
END AS DBARunbook
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
WHERE DB_NAME(T.dbid) = @DbName
AND T.text IS NOT NULL
AND QS.execution_count > 5
AND QS.sql_handle <> ISNULL(@ThisSqlHandle, 0x0)
) AS PlanQ
WHERE @OnlyProblems = 0 OR PlanQ.Severity <> 'INFO'
ORDER BY PlanQ.VarianceSec DESC, PlanQ.RecompilationCount DESC;
END
-- ============================================================
-- SECTION 21 : Parameter Sniffing Detection
-- Queries with high variance in execution time/reads across invocations
-- = potential parameter sniffing victims
-- ============================================================
IF @ShowSection IN (0, 21)
BEGIN
SELECT TOP (@TopRows)
'Parameter Sniffing Risk' AS [Section],
CAST(QS.total_elapsed_time / 1000000.0 AS DECIMAL(18,2)) AS TotalElapsedSec,
QS.execution_count,
CAST((QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgElapsedSec,
QS.total_logical_reads,
CAST((QS.total_logical_reads / NULLIF(QS.execution_count,0)) AS DECIMAL(10,0)) AS AvgReads,
DB_NAME(T.dbid) AS DBName,
SUBSTRING(T.text, 1, 100) AS QueryPreview,
'HIGH VARIANCE - Monitor for sniffing' AS Recommendation
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
WHERE DB_NAME(T.dbid) = @DbName
AND T.text IS NOT NULL
AND QS.execution_count > 10
AND QS.min_elapsed_time < QS.max_elapsed_time * 0.1
ORDER BY (QS.max_elapsed_time - QS.min_elapsed_time) DESC;
END
-- ============================================================
-- SECTION 22 : Database IO Subsystem Analysis
-- Physical reads, writes, latency metrics from DMVs
-- ============================================================
IF @ShowSection IN (0, 22)
BEGIN
SELECT *
FROM
(
SELECT
'IO Subsystem' AS [Section],
CASE
WHEN CAST(MFFP.io_stall_read_ms AS FLOAT) / NULLIF(MFFP.num_of_reads,0) >= 50
OR CAST(MFFP.io_stall_write_ms AS FLOAT) / NULLIF(MFFP.num_of_writes,0) >= 20 THEN 'CRITICAL'
WHEN CAST(MFFP.io_stall_read_ms AS FLOAT) / NULLIF(MFFP.num_of_reads,0) >= 20
OR CAST(MFFP.io_stall_write_ms AS FLOAT) / NULLIF(MFFP.num_of_writes,0) >= 10 THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Database File Latency' AS IssueType,
DB_NAME(MFFP.database_id) AS DBName,
MFFP.file_id,
MF.name AS FileName,
MF.physical_name AS PhysicalPath,
MF.type_desc AS FileType,
MFFP.num_of_reads AS PhysicalReads,
CAST(MFFP.num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(10,1)) AS DataReadMB,
CAST(MFFP.io_stall_read_ms / 1000.0 AS DECIMAL(10,1)) AS ReadLatencySec,
CAST(CAST(MFFP.io_stall_read_ms AS FLOAT) / NULLIF(MFFP.num_of_reads,0) AS DECIMAL(10,2)) AS AvgReadLatencyMs,
MFFP.num_of_writes AS PhysicalWrites,
CAST(MFFP.num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(10,1)) AS DataWrittenMB,
CAST(MFFP.io_stall_write_ms / 1000.0 AS DECIMAL(10,1)) AS WriteLatencySec,
CAST(CAST(MFFP.io_stall_write_ms AS FLOAT) / NULLIF(MFFP.num_of_writes,0) AS DECIMAL(10,2)) AS AvgWriteLatencyMs,
CAST(MFFP.io_stall / 1000.0 AS DECIMAL(10,1)) AS TotalLatencySec,
'Slow storage, overloaded LUN, large scans, or repeated autogrowth can increase file stall time.' AS LikelyCause,
'Review storage latency, file growth settings, and top read/write queries before moving files.' AS RecommendedAction,
'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 4;' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Use average latency ms per read/write as the first signal. Data files above ~20ms read or log files above ~10ms write deserve investigation.'
ELSE NULL
END AS DBARunbook
FROM sys.dm_io_virtual_file_stats(@TargetDbId, NULL) MFFP
INNER JOIN sys.master_files MF ON MFFP.database_id = MF.database_id AND MFFP.file_id = MF.file_id
) AS IOFileQ
WHERE @OnlyProblems = 0 OR IOFileQ.Severity <> 'INFO'
ORDER BY IOFileQ.TotalLatencySec DESC;
END
-- ============================================================
-- SECTION 23 : Connection Pool Analysis
-- Current connections, idle sessions, potential connection issues
-- ============================================================
IF @ShowSection IN (0, 23)
BEGIN
SELECT
'Connection Analysis' AS [Section],
COUNT(*) AS TotalConnections,
SUM(CASE WHEN S.status = 'running' THEN 1 ELSE 0 END) AS RunningConnections,
SUM(CASE WHEN S.status = 'sleeping' THEN 1 ELSE 0 END) AS SleepingConnections,
SUM(CASE WHEN S.status = 'dormant' THEN 1 ELSE 0 END) AS DormantConnections,
MAX(DATEDIFF(SECOND, S.last_request_end_time, GETDATE())) AS MaxIdleSecond
FROM sys.dm_exec_sessions S
WHERE S.session_id > 50;
-- Top connections by database
SELECT TOP 20
'Connections by DB' AS [Section],
DB_NAME(S.database_id) AS DBName,
S.login_name,
S.host_name,
S.program_name,
COUNT(*) AS ConnectionCount,
SUM(S.memory_usage) * 8 / 1024 AS MemoryUsageMB
FROM sys.dm_exec_sessions S
WHERE S.session_id > 50
GROUP BY DB_NAME(S.database_id), S.login_name, S.host_name, S.program_name
ORDER BY ConnectionCount DESC;
END
-- ============================================================
-- SECTION 24 : CPU & Scheduler Queue Analysis
-- CPU throttling, runnable tasks, scheduler health
-- ============================================================
IF @ShowSection IN (0, 24)
BEGIN
-- Scheduler stats
SELECT *
FROM
(
SELECT
'Scheduler Health' AS [Section],
CASE
WHEN runnable_tasks_count > 10 THEN 'CRITICAL'
WHEN runnable_tasks_count > 5 THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'CPU Scheduler Queue' AS IssueType,
scheduler_id,
cpu_id,
status,
runnable_tasks_count,
current_tasks_count,
CAST(work_queue_count AS DECIMAL(10,0)) AS QueuedTasks,
CASE
WHEN runnable_tasks_count > 5 THEN 'Schedulers have runnable tasks waiting for CPU, indicating CPU pressure or excessive parallelism.'
ELSE 'Scheduler queue depth is within normal range.'
END AS LikelyCause,
CASE
WHEN runnable_tasks_count > 5 THEN 'Review CPU-heavy queries, MAXDOP, and concurrent workload bursts.'
ELSE 'Continue monitoring.'
END AS RecommendedAction,
'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 2;' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Sustained runnable queue is more important than a one-time spike. Re-sample during business peak before making server-wide changes.'
ELSE NULL
END AS DBARunbook
FROM sys.dm_os_schedulers
WHERE scheduler_id < 256
) AS SchedQ
WHERE @OnlyProblems = 0 OR SchedQ.Severity <> 'INFO'
ORDER BY SchedQ.runnable_tasks_count DESC;
-- CPU utilization over time
SELECT
'CPU Utilization' AS [Section],
CASE
WHEN CAST(SUM(signal_wait_time_ms) * 100.0 / NULLIF(SUM(wait_time_ms),0) AS DECIMAL(5,1)) >= 25 THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'CPU Signal Waits' AS IssueType,
CAST(SUM(signal_wait_time_ms) * 100.0 / NULLIF(SUM(wait_time_ms),0) AS DECIMAL(5,1)) AS CPUUtilizationPercent,
COUNT(DISTINCT wait_type) AS UniqueWaitTypes,
'High signal wait ratio means workers are waiting for CPU after getting in line.' AS LikelyCause,
'If ratio remains high with runnable tasks, tune expensive CPU queries or reduce concurrency.' AS RecommendedAction,
'EXEC dbo.dbusp_PerformanceStats @DbName = ''' + @DbName + ''', @ShowSection = 11;' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'This is not exact OS CPU percent; it is SQL wait-based pressure signal. Read it together with scheduler queues and top CPU queries.'
ELSE NULL
END AS DBARunbook
FROM (
SELECT signal_wait_time_ms, wait_time_ms, wait_type
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK', 'WAITFOR', 'SOS_SCHEDULER_YIELD')
) WS
HAVING @OnlyProblems = 0
OR CAST(SUM(signal_wait_time_ms) * 100.0 / NULLIF(SUM(wait_time_ms),0) AS DECIMAL(5,1)) >= 25;
END
-- ============================================================
-- SECTION 25 : Table Partitioning Opportunities
-- Large tables that could benefit from partitioning
-- ============================================================
IF @ShowSection IN (0, 25)
BEGIN
SELECT TOP 20
'Partitioning Candidates' AS [Section],
OBJECT_SCHEMA_NAME(T.object_id) AS SchemaName,
T.name AS TableName,
SUM(CAST(P.rows AS BIGINT)) AS [RowCount],
CAST(SUM(AU.total_pages) * 8 / 1024.0 / 1024.0 AS DECIMAL(10,2)) AS SizeGB,
CASE
WHEN SUM(AU.total_pages) * 8 / 1024.0 / 1024.0 > 10
THEN 'Good candidate for partitioning (Range on DateAdded or ID)'
ELSE 'Monitor'
END AS Recommendation
FROM sys.tables T
INNER JOIN sys.indexes I ON T.object_id = I.object_id
INNER JOIN sys.partitions P ON I.object_id = P.object_id AND I.index_id = P.index_id
INNER JOIN sys.allocation_units AU ON P.partition_id = AU.container_id
WHERE I.index_id < 2
GROUP BY T.object_id, T.name, OBJECT_SCHEMA_NAME(T.object_id)
HAVING SUM(AU.total_pages) * 8 / 1024.0 / 1024.0 > 1 -- > 1 GB
ORDER BY SizeGB DESC;
END
-- ============================================================
-- SECTION 26 : Query Recompilation & Query Hints Analysis
-- Recompilation counts, forced plans, optimization hints
-- ============================================================
IF @ShowSection IN (0, 26)
BEGIN
SELECT TOP (@TopRows)
'Query Recompilation Risk' AS [Section],
QS.plan_generation_num AS RecompilationCount,
QS.execution_count,
CAST((QS.total_elapsed_time / NULLIF(QS.execution_count,0)) / 1000000.0 AS DECIMAL(18,4)) AS AvgElapsedSec,
DB_NAME(T.dbid) AS DBName,
SUBSTRING(T.text, 1, 100) AS QueryPreview,
CASE
WHEN QS.plan_generation_num > 10
THEN 'HIGH RECOMPILE COUNT - Use WITH(RECOMPILE) or add OPTION(RECOMPILE)'
ELSE 'Normal'
END AS Recommendation
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) T
WHERE DB_NAME(T.dbid) = @DbName
AND T.text IS NOT NULL
ORDER BY RecompilationCount DESC;
END
-- ============================================================
-- SECTION 27 : Database Recovery Model & Backup Status
-- Shows backup history, LSN progression, recovery readiness
-- ============================================================
IF @ShowSection IN (0, 27)
BEGIN
SELECT
'Recovery Model & Backups' AS [Section],
CASE
WHEN BS.LastFullBackup IS NULL THEN 'CRITICAL'
WHEN D.recovery_model_desc = 'FULL' AND BL.LastLogBackup IS NULL THEN 'CRITICAL'
WHEN DATEDIFF(HOUR, BS.LastFullBackup, GETDATE()) > 24 THEN 'WARNING'
ELSE 'INFO'
END AS Severity,
'Backup Compliance' AS IssueType,
DB_NAME(D.database_id) AS DBName,
D.recovery_model_desc AS RecoveryModel,
BS.LastFullBackup AS LastFullBackup,
BL.LastLogBackup AS LastLogBackup,
DATEDIFF(HOUR, BS.LastFullBackup, GETDATE()) AS HoursSinceFullBackup,
DATEDIFF(DAY, BS.LastFullBackup, GETDATE()) AS DaysSinceFullBackup,
BS.first_lsn,
BS.last_lsn,
CASE
WHEN BS.LastFullBackup IS NULL THEN 'No full backup found in msdb history.'
WHEN D.recovery_model_desc = 'FULL' AND BL.LastLogBackup IS NULL THEN 'FULL recovery model without log backup history breaks point-in-time recovery.'
WHEN DATEDIFF(HOUR, BS.LastFullBackup, GETDATE()) > 24 THEN 'Full backup is older than expected policy threshold.'
ELSE 'Backup history looks present for the current policy baseline.'
END AS LikelyCause,
CASE
WHEN BS.LastFullBackup IS NULL THEN 'Take an immediate full backup after business approval.'
WHEN D.recovery_model_desc = 'FULL' AND BL.LastLogBackup IS NULL THEN 'Start regular transaction log backups immediately.'
WHEN DATEDIFF(HOUR, BS.LastFullBackup, GETDATE()) > 24 THEN 'Review SQL Agent/job schedule and backup retention failures.'
ELSE 'Continue monitoring backup jobs and test restores.'
END AS RecommendedAction,
CASE
WHEN D.recovery_model_desc = 'FULL' AND BL.LastLogBackup IS NULL THEN 'BACKUP LOG [' + D.name + '] TO DISK = ''<path>'' WITH INIT;'
ELSE 'BACKUP DATABASE [' + D.name + '] TO DISK = ''<path>'' WITH INIT;'
END AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Backup history in msdb is necessary but not sufficient. Also validate restore tests, job alerts, and backup file availability.'
ELSE NULL
END AS DBARunbook
FROM sys.databases D
LEFT JOIN (
SELECT database_name, MAX(backup_finish_date) AS LastFullBackup,
MAX(first_lsn) as first_lsn, MAX(last_lsn) as last_lsn
FROM msdb.dbo.backupset
WHERE type = 'D' -- Full backup
GROUP BY database_name
) BS ON D.name = BS.database_name
LEFT JOIN (
SELECT database_name, MAX(backup_finish_date) AS LastLogBackup
FROM msdb.dbo.backupset
WHERE type = 'L'
GROUP BY database_name
) BL ON D.name = BL.database_name
WHERE D.name = @DbName
AND (
@OnlyProblems = 0
OR BS.LastFullBackup IS NULL
OR (D.recovery_model_desc = 'FULL' AND BL.LastLogBackup IS NULL)
OR DATEDIFF(HOUR, BS.LastFullBackup, GETDATE()) > 24
);
END
-- ============================================================
-- SECTION 28 : Object Permissions & Security Audit
-- Shows exposed permissions, excessive grants, security risks
-- ============================================================
IF @ShowSection IN (0, 28)
BEGIN
SELECT TOP 50
'Permission Audit' AS [Section],
SCHEMA_NAME(O.schema_id) AS SchemaName,
O.name AS ObjectName,
O.type_desc AS ObjectType,
DP.name AS PrincipalName,
DP.type_desc AS PrincipalType,
PR.permission_name AS Permission,
PR.state_desc AS PermState,
CASE
WHEN DP.name = 'public'
THEN 'SECURITY RISK - Excessive public permissions'
WHEN PR.permission_name IN ('SELECT', 'INSERT', 'UPDATE', 'DELETE') AND DP.type = 'U'
THEN 'Review - User has DML'
ELSE 'Normal'
END AS SecurityNote
FROM sys.database_principals DP
INNER JOIN sys.database_permissions PR ON DP.principal_id = PR.grantee_principal_id
INNER JOIN sys.objects O ON PR.major_id = O.object_id
WHERE (DP.name = 'public' OR DP.type = 'U')
AND PR.state_desc = 'GRANT'
ORDER BY DP.name, O.name;
END
-- ============================================================
-- SECTION 29 : Server Configuration Issues & Best Practices
-- Checks for bad server configs that impact DB performance
-- ============================================================
IF @ShowSection IN (0, 29)
BEGIN
SELECT *
FROM
(
SELECT
'Config Issues' AS [Section],
CASE
WHEN SCI.name = 'priority boost' AND SCI.value_in_use = 1 THEN 'CRITICAL'
WHEN SCI.name = 'max degree of parallelism' AND SCI.value_in_use > CASE WHEN @CpuCount > 8 THEN 8 ELSE @CpuCount END THEN 'WARNING'
WHEN SCI.name = 'cost threshold for parallelism' AND SCI.value_in_use < 5 THEN 'WARNING'
WHEN SCI.name = 'max server memory (MB)' AND SCI.value_in_use < 2048 THEN 'WARNING'
WHEN SCI.name = 'optimize for ad hoc workloads' AND SCI.value_in_use = 0 THEN 'INFO'
ELSE 'INFO'
END AS Severity,
'Server Configuration' AS IssueType,
SCI.name AS ConfigName,
SCI.value_in_use AS CurrentValue,
SCI.value AS ConfigValue,
SCI.description AS Description,
CASE
WHEN SCI.name = 'priority boost' AND SCI.value_in_use = 1 THEN 'Priority boost is not recommended for SQL Server and can destabilize scheduling.'
WHEN SCI.name = 'max degree of parallelism' AND SCI.value_in_use > CASE WHEN @CpuCount > 8 THEN 8 ELSE @CpuCount END THEN 'MAXDOP may be too high for current CPU topology.'
WHEN SCI.name = 'cost threshold for parallelism' AND SCI.value_in_use < 5 THEN 'Very low cost threshold can push too many queries into parallel plans.'
WHEN SCI.name = 'max server memory (MB)' AND SCI.value_in_use < 2048 THEN 'SQL Server max memory is set very low for most workloads.'
WHEN SCI.name = 'optimize for ad hoc workloads' AND SCI.value_in_use = 0 THEN 'Ad hoc plan cache bloat risk exists on chatty ad hoc workloads.'
ELSE 'Configuration looks acceptable, but validate against workload baseline.'
END AS LikelyCause,
CASE
WHEN SCI.name = 'priority boost' AND SCI.value_in_use = 1 THEN 'Disable priority boost.'
WHEN SCI.name = 'max degree of parallelism' AND SCI.value_in_use > CASE WHEN @CpuCount > 8 THEN 8 ELSE @CpuCount END THEN 'Review and lower MAXDOP after workload testing.'
WHEN SCI.name = 'cost threshold for parallelism' AND SCI.value_in_use < 5 THEN 'Raise cost threshold after confirming plan behavior.'
WHEN SCI.name = 'max server memory (MB)' AND SCI.value_in_use < 2048 THEN 'Increase max server memory while preserving OS headroom.'
WHEN SCI.name = 'optimize for ad hoc workloads' AND SCI.value_in_use = 0 THEN 'Enable optimize for ad hoc workloads if plan cache churn is confirmed.'
ELSE 'No immediate server-wide change required.'
END AS RecommendedAction,
'EXEC sp_configure ''' + SCI.name + ''', <new_value>; RECONFIGURE;' AS FixScript,
CASE
WHEN @IncludeRunbook = 1 THEN 'Treat server-level changes carefully. Validate after-hours or in staging first, because these affect all databases on the instance.'
ELSE NULL
END AS DBARunbook
FROM sys.configurations SCI
WHERE SCI.name IN (
'max server memory (MB)',
'min server memory (MB)',
'max degree of parallelism',
'cost threshold for parallelism',
'optimize for ad hoc workloads',
'default full-text language',
'network packet size (B)',
'priority boost'
)
) AS ConfigQ
WHERE @OnlyProblems = 0 OR ConfigQ.Severity <> 'INFO'
ORDER BY CASE ConfigQ.Severity WHEN 'CRITICAL' THEN 1 WHEN 'WARNING' THEN 2 ELSE 3 END, ConfigQ.ConfigName;
END
-- ============================================================
-- SECTION 30 : Extended Events & Trace Configuration
-- Shows tracing setup, extended events sessions, audit status
-- ============================================================
IF @ShowSection IN (0, 30)
BEGIN
-- Extended Events sessions (if available)
IF OBJECT_ID('sys.dm_xe_sessions', 'V') IS NOT NULL
BEGIN
SELECT
'Extended Events' AS [Section],
name AS SessionName,
'Active' AS SessionStatus
FROM sys.dm_xe_sessions;
END
ELSE
BEGIN
SELECT 'Extended Events not available on this SQL Server version' AS [Info];
END
-- Server Audits (if available)
IF OBJECT_ID('sys.server_audits', 'V') IS NOT NULL
BEGIN
SELECT
'Server Audits' AS [Section],
name,
CASE WHEN is_state_enabled = 1 THEN 'ENABLED' ELSE 'DISABLED' END
FROM sys.server_audits;
END
ELSE
BEGIN
SELECT 'Extended Events / Audits not available on this SQL Server version' AS [Info];
END
PRINT N'TIP: Enable Extended Events or SQL Trace for troubleshooting:';
PRINT N' - xe_session_health: Tracks system issues';
PRINT N' - trace_events: Deadlock, long-running queries';
END
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNum,
ERROR_SEVERITY() AS Severity,
ERROR_STATE() AS [State],
ERROR_PROCEDURE() AS [Procedure],
ERROR_LINE() AS [Line],
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
GO
-- ── Quick run examples ───────────────────────────────────────
-- Full enterprise diagnostic dashboard
-- EXEC dbo.dbusp_PerformanceStats;
-- Specific DB
-- EXEC dbo.dbusp_PerformanceStats @DbName = 'Vipul';
-- Specific diagnostic sections:
-- EXEC dbo.dbusp_PerformanceStats @ShowSection = 5; -- Blocking queries
-- EXEC dbo.dbusp_PerformanceStats @ShowSection = 15; -- Lock contention
-- EXEC dbo.dbusp_PerformanceStats @ShowSection = 16; -- Stale statistics
-- EXEC dbo.dbusp_PerformanceStats @ShowSection = 17; -- Database health
-- EXEC dbo.dbusp_PerformanceStats @ShowSection = 22; -- IO analysis
-- EXEC dbo.dbusp_PerformanceStats @ShowSection = 29; -- Config issues
-- EXEC dbo.dbusp_PerformanceStats @OnlyProblems = 1; -- Hide healthy rows
-- Custom thresholds & full diagnostics
-- EXEC dbo.dbusp_PerformanceStats @DbName = 'PayrollDB', @ThresholdAvgSec = 5,
-- @TopRows = 20, @StaleStatsThresholdDays = 14, @ShowSection = 0, @IncludeRunbook = 1;
What This Script Does (Very Powerful )
This is a complete DBA dashboard with 30 sections.
Query Performance
Top CPU queries
Slow queries
IO heavy queries
Execution plans
Index Health
Missing indexes
Fragmentation
Unused indexes
Memory & CPU
Memory pressure
CPU usage
Scheduler load
Concurrency
Blocking queries
Deadlocks
Lock contention
Database Health
Large tables
Log usage
Backup status
Security & Config
Permissions audit
Server configuration issues
How to Use Performance Script
Run full dashboard:
EXEC dbo.dbusp_PerformanceStats;Check specific section:
-- Blocking queries
EXEC dbo.dbusp_PerformanceStats @ShowSection = 5;
-- Wait stats
EXEC dbo.dbusp_PerformanceStats @ShowSection = 11;
-- Index fragmentation
EXEC dbo.dbusp_PerformanceStats @ShowSection = 9;
-- IO analysis
EXEC dbo.dbusp_PerformanceStats @ShowSection = 22;
Real-Life DBA Workflow (Very Important )
Step 1: Check Blocking
EXEC dbusp_BlockingTree;
👉 If blocking found → fix immediately
Step 2: Check Wait Stats
EXEC dbo.dbusp_PerformanceStats @ShowSection = 11;
👉 Find root cause:
LCK → Blocking
PAGEIOLATCH → IO issue
CXPACKET → Parallelism
Step 3: Check Slow Queries
EXEC dbo.dbusp_PerformanceStats @ShowSection = 3;
Step 4: Check Index Issues
EXEC dbo.dbusp_PerformanceStats @ShowSection = 8;
EXEC dbo.dbusp_PerformanceStats @ShowSection = 9;
Step 5: Check Memory & CPU
EXEC dbo.dbusp_PerformanceStats @ShowSection = 13;
EXEC dbo.dbusp_PerformanceStats @ShowSection = 24;
Best Practices (Golden Rules)
Keep transactions short
Always use proper indexes
Avoid large updates in peak time
Monitor wait stats regularly
Never kill session without checking
Conclusion
For any SQL Server DBA:
👉 dbusp_BlockingTree = Quick troubleshooting tool
👉 dbusp_PerformanceStats = Complete health dashboard
If you use both together, you can:
Detect problems fast
Fix root cause
Improve system performance