Skip to content

Instantly share code, notes, and snippets.

@JosiahSiegel

JosiahSiegel/sql_server_quick_analysis.sql

Last active November 5, 2025 23:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save JosiahSiegel/9169a724981f06024d19f7f0f621b49f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save JosiahSiegel/9169a724981f06024d19f7f0f621b49f to your computer and use it in GitHub Desktop.
Download ZIP
SQL Server Quick Analysis
Raw
sql_server_quick_analysis.sql
-- Optimized Session Monitor with Query Plans (Works on both Azure SQL DB and SQL Server VM)
-- Using READ UNCOMMITTED for minimal blocking during monitoring
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
;WITH SessionMetrics AS (
SELECT
er.session_id AS sid,
er.blocking_session_id AS blocked_by,
-- Accurate issue detection based on Microsoft documentation
STUFF(
CASE WHEN er.blocking_session_id > 0 AND er.wait_time > 5000 THEN ',BLOCKED' ELSE '' END +
CASE WHEN EXISTS (SELECT 1 FROM sys.dm_exec_requests r2 WITH (NOLOCK) WHERE r2.blocking_session_id = er.session_id
AND r2.wait_time > 5000) THEN ',BLOCKER' ELSE '' END +
CASE WHEN er.cpu_time > 60000 AND er.status = 'running' THEN ',CPU_HOG' ELSE '' END +
CASE WHEN er.logical_reads > 2560000 THEN ',IO_HOG' ELSE '' END +
CASE WHEN er.granted_query_memory * 8 > 2048000 THEN ',MEM_HOG' ELSE '' END +
CASE WHEN er.total_elapsed_time > 600000 AND er.command IN ('SELECT','INSERT','UPDATE','DELETE') THEN ',LONG_RUN' ELSE '' END +
CASE WHEN es.open_transaction_count > 0
AND er.total_elapsed_time > 300000
AND er.command NOT IN ('AWAITING COMMAND') THEN ',OPEN_TRAN' ELSE '' END +
CASE WHEN er.wait_type = 'ASYNC_NETWORK_IO' AND er.wait_time > 60000 THEN ',CLIENT_SLOW' ELSE '' END +
CASE WHEN er.wait_type LIKE 'PAGEIO%' AND er.wait_time > 30000 THEN ',DISK_WAIT' ELSE '' END +
CASE WHEN er.wait_type LIKE 'LCK%' AND er.wait_time > 30000 THEN ',LOCK_WAIT' ELSE '' END +
CASE WHEN er.wait_type = 'RESOURCE_SEMAPHORE' AND er.wait_time > 10000 THEN ',MEM_WAIT' ELSE '' END +
CASE WHEN er.wait_type IN ('CXPACKET', 'CXCONSUMER')
AND er.wait_time > 120000
AND er.cpu_time < er.wait_time/4 THEN ',PARALLEL_WAIT' ELSE '' END
, 1, 1, '') AS issues,
er.wait_type,
er.wait_time / 1000 AS wait_s,
er.cpu_time / 1000 AS cpu_s,
er.total_elapsed_time / 60000 AS elapsed_m,
er.logical_reads * 8 / 1024 AS reads_mb,
er.granted_query_memory * 8 / 1024 AS mem_mb,
er.[status],
er.command,
er.percent_complete,
CASE
WHEN er.estimated_completion_time > 0
THEN DATEADD(ms, er.estimated_completion_time, GETDATE())
END AS est_end_time,
es.open_transaction_count AS tran_cnt,
-- Transaction age
CASE
WHEN tat.transaction_begin_time IS NOT NULL
THEN DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE())
END AS tran_age_s,
ISNULL(dt.database_transaction_log_bytes_used / 1024, 0) AS tran_log_kb,
DB_NAME(er.database_id) AS db,
es.program_name AS program,
es.host_name AS host,
es.login_name AS login,
-- Resource governor workload groups (only if available)
NULL AS resource_group, -- Removed subquery for performance
-- Impact scoring
(CASE
WHEN er.blocking_session_id > 0 AND er.wait_time > 30000 THEN 40
WHEN er.blocking_session_id > 0 AND er.wait_time > 10000 THEN 20
WHEN er.blocking_session_id > 0 THEN 5
ELSE 0
END +
CASE
WHEN EXISTS (SELECT 1 FROM sys.dm_exec_requests r2 WITH (NOLOCK) WHERE r2.blocking_session_id = er.session_id
AND r2.wait_time > 10000) THEN 30
WHEN EXISTS (SELECT 1 FROM sys.dm_exec_requests r2 WITH (NOLOCK) WHERE r2.blocking_session_id = er.session_id) THEN 10
ELSE 0
END +
CASE
WHEN er.cpu_time > 300000 THEN 25
WHEN er.cpu_time > 60000 THEN 10
WHEN er.cpu_time > 30000 THEN 5
ELSE 0
END +
CASE
WHEN er.logical_reads > 3200000 THEN 25
WHEN er.logical_reads > 320000 THEN 10
WHEN er.logical_reads > 32000 THEN 5
ELSE 0
END +
CASE
WHEN er.total_elapsed_time > 1800000 THEN 20
WHEN er.total_elapsed_time > 600000 THEN 10
WHEN er.total_elapsed_time > 300000 THEN 5
ELSE 0
END
) AS impact,
er.query_hash,
er.plan_handle,
-- Query text extraction
CASE
WHEN er.statement_start_offset IS NOT NULL AND txt.text IS NOT NULL
THEN SUBSTRING(txt.text, (er.statement_start_offset/2) + 1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(txt.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)
ELSE txt.text
END AS query,
-- Query plan (will be joined later for efficiency)
qp.query_plan,
-- Query profile progress
qprog.pct_complete AS profile_pct
FROM sys.dm_exec_requests er WITH (NOLOCK)
INNER JOIN sys.dm_exec_sessions es WITH (NOLOCK) ON er.session_id = es.session_id
-- Transaction joins - simplified for performance
LEFT JOIN sys.dm_tran_session_transactions tst WITH (NOLOCK)
ON tst.session_id = er.session_id
LEFT JOIN (
SELECT
transaction_id,
MIN(database_transaction_begin_time) AS transaction_begin_time,
SUM(database_transaction_log_bytes_used) AS database_transaction_log_bytes_used
FROM sys.dm_tran_database_transactions WITH (NOLOCK)
GROUP BY transaction_id
) dt ON tst.transaction_id = dt.transaction_id
LEFT JOIN (
SELECT
transaction_id,
MIN(transaction_begin_time) AS transaction_begin_time
FROM sys.dm_tran_active_transactions WITH (NOLOCK)
GROUP BY transaction_id
) tat ON tst.transaction_id = tat.transaction_id
-- Query profile progress (real-time execution progress)
LEFT JOIN (
SELECT session_id,
CAST(SUM(row_count) * 100.0 / NULLIF(SUM(estimate_row_count), 0) AS INT) AS pct_complete
FROM sys.dm_exec_query_profiles WITH (NOLOCK)
GROUP BY session_id
) qprog ON qprog.session_id = er.session_id
-- Query text and plan
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) txt
-- Get live query plan with runtime statistics and progress
OUTER APPLY sys.dm_exec_query_statistics_xml(er.session_id) qp
WHERE er.session_id <> @@SPID
AND er.session_id > 50
AND es.program_name NOT IN ('TdService', 'SQL ATP and VA for Queries Extension')
-- Minimal filtering for performance
AND er.command NOT LIKE 'UCS%'
AND er.command NOT LIKE 'BRKR%'
AND er.command NOT LIKE 'HADR%'
AND er.command NOT LIKE 'LOG%'
AND er.command NOT IN (
'CHECKPOINT', 'TASK MANAGER', 'RECEIVE',
'GHOST CLEANUP', 'LOCK MONITOR', 'SIGNAL HANDLER',
'VDI_CLIENT_WORKER', 'SYSTEM_HEALTH_MONITOR',
'XE DISPATCHER', 'PARALLEL REDO TASK', 'DB MIRROR'
)
)
SELECT
sid,
blocked_by,
ISNULL(issues, '') AS issues,
wait_type,
wait_s,
cpu_s,
elapsed_m,
reads_mb,
mem_mb,
[status],
command,
percent_complete,
est_end_time,
tran_cnt,
tran_age_s,
tran_log_kb,
db,
program,
host,
login,
resource_group,
impact,
query_hash,
LEFT(query, 500) AS query,
profile_pct,
query_plan
FROM SessionMetrics
ORDER BY
impact DESC,
CASE WHEN blocked_by > 0 THEN 0 ELSE 1 END,
wait_s DESC,
cpu_s DESC
OPTION (MAXDOP 1, RECOMPILE);
GO
-- Database Info and Platform with proper read-only detection
SELECT
'DB_INFO' AS info_type,
DB_NAME() AS db_name,
CASE
WHEN SERVERPROPERTY('EngineEdition') = 5 THEN 'Azure SQL Database'
ELSE CONCAT('SQL Server ', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)))
END AS platform,
-- Simple and reliable read-only detection
CASE
-- First check the is_read_only flag from sys.databases
WHEN (SELECT is_read_only FROM sys.databases WHERE name = DB_NAME()) = 1 THEN 'READ-ONLY'
-- Then check DATABASEPROPERTYEX
WHEN DATABASEPROPERTYEX(DB_NAME(), 'Updateability') = 'READ_ONLY' THEN 'READ-ONLY'
-- Otherwise it's read-write
ELSE 'READ-WRITE'
END AS db_status,
CAST(@@MAX_CONNECTIONS AS INT) AS max_connections,
(SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE session_id > 50) AS current_connections,
CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size * 8.0 / 1024 ELSE 0 END) AS DECIMAL(10,2)) AS data_size_mb,
CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size * 8.0 / 1024 ELSE 0 END) AS DECIMAL(10,2)) AS log_size_mb,
CAST(SUM(CASE WHEN type_desc = 'ROWS'
THEN (size * 8.0 / 1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024)
ELSE 0 END) AS DECIMAL(10,2)) AS data_free_mb
FROM sys.database_files WITH (NOLOCK)
WHERE type_desc IN ('ROWS', 'LOG')
GROUP BY type_desc WITH ROLLUP
HAVING GROUPING(type_desc) = 1
OPTION (MAXDOP 1);
GO
-- Detailed Database Files Info (including TempDB aggregate)
SELECT
'DB_FILES' AS info_type,
name AS file_name,
type_desc AS file_type,
CAST(size AS BIGINT) * 8.0 / 1024 AS size_mb,
(CAST(size AS BIGINT) * 8.0 / 1024) - (CAST(ISNULL(FILEPROPERTY(name, 'SpaceUsed'), 0) AS BIGINT) * 8.0 / 1024) AS free_mb,
CASE
WHEN size > 0 THEN (CAST(ISNULL(FILEPROPERTY(name, 'SpaceUsed'), 0) AS FLOAT) / CAST(size AS FLOAT)) * 100.0
ELSE 0.0
END AS used_pct,
physical_name
FROM sys.database_files WITH (NOLOCK)
WHERE type_desc IN ('ROWS', 'LOG')
UNION ALL
SELECT
'DB_FILES' AS info_type,
'tempdb (aggregate)' AS file_name,
'TEMPDB' AS file_type,
total_size_mb AS size_mb,
free_mb AS free_mb,
used_pct AS used_pct,
'' AS physical_name
FROM (
SELECT
SUM(CAST(tf.size AS FLOAT)) * 8.0 / 1024 AS total_size_mb,
(SUM(CAST(tf.size AS FLOAT)) - CAST(ISNULL(MAX(sau.user_object_reserved_page_count), 0) AS FLOAT) - CAST(ISNULL(MAX(sau.internal_object_reserved_page_count), 0) AS FLOAT) - CAST(ISNULL(MAX(sau.version_store_reserved_page_count), 0) AS FLOAT) - CAST(ISNULL(MAX(sau.mixed_extent_page_count), 0) AS FLOAT)) * 8.0 / 1024 AS free_mb,
CASE
WHEN SUM(CAST(tf.size AS FLOAT)) > 0 THEN (CAST(ISNULL(MAX(sau.user_object_reserved_page_count), 0) AS FLOAT) + CAST(ISNULL(MAX(sau.internal_object_reserved_page_count), 0) AS FLOAT) + CAST(ISNULL(MAX(sau.version_store_reserved_page_count), 0) AS FLOAT) + CAST(ISNULL(MAX(sau.mixed_extent_page_count), 0) AS FLOAT)) / SUM(CAST(tf.size AS FLOAT)) * 100.0
ELSE 0.0
END AS used_pct
FROM tempdb.sys.database_files tf WITH (NOLOCK)
CROSS APPLY (
SELECT
SUM(user_object_reserved_page_count) AS user_object_reserved_page_count,
SUM(internal_object_reserved_page_count) AS internal_object_reserved_page_count,
SUM(version_store_reserved_page_count) AS version_store_reserved_page_count,
SUM(mixed_extent_page_count) AS mixed_extent_page_count
FROM tempdb.sys.dm_db_file_space_usage WITH (NOLOCK)
) sau
WHERE tf.type_desc = 'ROWS'
) tempdb_stats
ORDER BY type_desc, file_name
OPTION (MAXDOP 1);
GO
-- Recent Expensive Queries (HIGHLY OPTIMIZED VERSION)
-- Pre-filter in subquery to minimize OUTER APPLY operations
;WITH FilteredStats AS (
SELECT TOP 1000 -- Limit rows before expensive operations
s.sql_handle,
s.plan_handle,
s.last_execution_time,
s.last_elapsed_time,
s.execution_count,
s.last_logical_reads,
s.total_worker_time,
s.query_hash
FROM sys.dm_exec_query_stats s WITH (NOLOCK)
WHERE s.execution_count > 0
AND s.last_execution_time >= DATEADD(MINUTE, -60, GETDATE())
ORDER BY s.last_execution_time DESC
)
SELECT TOP 5
DATEDIFF(MINUTE, fs.last_execution_time, GETDATE()) AS mins_ago,
fs.last_elapsed_time / 1000 AS last_ms,
fs.execution_count AS execs,
fs.last_logical_reads * 8 / 1024 AS reads_mb,
CASE WHEN fs.execution_count > 0
THEN fs.total_worker_time / 1000 / fs.execution_count
ELSE 0 END AS avg_cpu_ms,
LEFT(t.text, 100) AS query_start,
fs.query_hash,
p.query_plan
FROM FilteredStats fs
OUTER APPLY sys.dm_exec_sql_text(fs.sql_handle) t
OUTER APPLY sys.dm_exec_query_plan(fs.plan_handle) p
WHERE t.text IS NOT NULL
-- Single combined exclusion check using PATINDEX (much faster than multiple LIKEs)
AND PATINDEX('%SessionMetrics%', t.text) = 0
AND PATINDEX('%dm[_]exec[_]%', t.text) = 0
AND PATINDEX('%DB[_]INFO%', t.text) = 0
AND PATINDEX('%DB[_]FILES%', t.text) = 0
AND PATINDEX('%database[_]files%', t.text) = 0
AND PATINDEX('%READ UNCOMMITTED%', t.text) = 0
ORDER BY fs.last_execution_time DESC
OPTION (MAXDOP 1);
GO
-- Reset isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment