info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
sp_microsoft_active
by BFarrell (Principal Consultant; Architecture; Engineering)
2026-03-27
Query #1: Actives
SELECT s.session_id,
r.status,
r.blocking_session_id 'Blk by',
r.wait_type,
wait_resource,
r.wait_time / (1000.0) 'Wait Sec',
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000.0) 'Elaps Sec',
db_name(r.database_id) as DBName,
'"'+Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE
WHEN r.statement_end_offset = -1
THEN Datalength(st.TEXT)
WHEN (r.statement_end_offset - r.statement_start_offset) < 0
Then Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1)+'"' AS statement_text,
'"'+Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)),
'')+'"' AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc, r.status,
r.blocking_session_id,
s.session_id
--RUNNING is actively executing on CPU
--RUNNABLE is ready to run, waiting for CPU (scheduler queue)
--SUSPENDED is waiting on a resource (I/O for reading a page, Communication on the network, Locks etc.)
---Ex: Once I/O is completed, data for a table is available in the memory, the query will move into RUNNABLE queue.
Query #2: Actives, adding transactional isolation levels, excluding CDC and internal diagnostic procedure
SELECT
r.session_id,
-- Timing
DATEDIFF(SECOND, r.start_time, SYSDATETIME()) AS elapsed_seconds,
s.login_name,
s.host_name,
s.program_name,
s.client_interface_name,
DB_NAME(r.database_id) AS database_name,
r.status AS request_status,
r.command,
-- Wait info
r.wait_type,
r.wait_time / 1000.0 AS wait_time_seconds,
r.last_wait_type,
r.wait_resource,
-- Blocking
r.blocking_session_id,
-- Isolation level
CASE r.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'RepeatableRead'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level,
-- Transaction info
s.open_transaction_count,
-- Resource usage
r.cpu_time AS cpu_time_ms,
r.total_elapsed_time AS total_elapsed_time_ms,
r.reads,
r.writes,
r.logical_reads,
r.granted_query_memory * 8 AS granted_memory_kb,
-- Statement text (current statement only)
SUBSTRING(
st.text,
(r.statement_start_offset / 2) + 1,
CASE
WHEN r.statement_end_offset = -1
THEN LEN(st.text)
ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
END
) AS current_statement_text,
-- Full batch (optional but useful)
st.text AS full_batch_text,
-- Query plan handle (for later inspection)
r.plan_handle,
qp.query_plan
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE
s.is_user_process = 1 -- exclude system sessions
AND r.session_id <> @@SPID -- exclude this session
AND
st.text not like '%sp_cdc_scan%'
AND
st.text <> 'sp_server_diagnostics'
ORDER BY
elapsed_seconds DESC;
Query #3: Actives, adding locks details, excluding CDC and internal diagnostic procedure
SELECT
r.session_id,
s.login_name,
s.host_name,
s.program_name,
DB_NAME(r.database_id) AS database_name,
r.status AS request_status,
r.command,
DATEDIFF(SECOND, r.start_time, SYSDATETIME()) AS elapsed_seconds,
-- Wait information
r.wait_type,
r.last_wait_type,
r.wait_time / 1000.0 AS wait_time_seconds,
r.wait_resource,
r.blocking_session_id,
-- Isolation level
CASE r.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'RepeatableRead'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level,
-- Transaction info
s.open_transaction_count,
-- Resource usage
r.cpu_time AS cpu_time_ms,
r.reads,
r.writes,
r.logical_reads,
-- Lock details
l.resource_type,
l.resource_subtype,
l.request_mode AS lock_mode,
l.request_status AS lock_status,
-- Object resolution (when possible)
OBJECT_NAME(
CASE
WHEN l.resource_type IN ('OBJECT', 'PAGE', 'KEY', 'RID', 'HOBT')
THEN p.object_id
END,
l.resource_database_id
) AS locked_object_name,
i.name AS locked_index_name,
-- Current statement
SUBSTRING(
st.text,
(r.statement_start_offset / 2) + 1,
CASE
WHEN r.statement_end_offset = -1
THEN LEN(st.text)
ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
END
) AS current_statement_text,
st.text AS full_batch_text,
qp.query_plan
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
LEFT JOIN sys.dm_tran_locks l
ON r.session_id = l.request_session_id
LEFT JOIN sys.partitions p
ON l.resource_associated_entity_id = p.hobt_id
LEFT JOIN sys.indexes i
ON p.object_id = i.object_id
AND p.index_id = i.index_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE
s.is_user_process = 1
AND r.session_id <> @@SPID
AND
st.text not like '%sp_cdc_scan%'
AND
st.text <> 'sp_server_diagnostics'
ORDER BY
elapsed_seconds DESC,
r.session_id;
|
|
|
|
|
|
|
|
|
|