In today's post, I will show you how to return active SQL Server sessions, showing CPU usage, number of reads and writes, which user is performing the query, what is being executed by this session, what software is used, what is the hostname and various other information about the session and the query. For those who already know sp_WhoIsActive (If you don't know it, find out more accessing this post), you will notice that they have a very similar return, and that was the intention of the query.
On several occasions, I have had slow problems when running sp_WhoIsActive in environments with high processing and disk contention and/or TempDB, causing the return of the SP to take several seconds, even a few minutes, since this SP has a lot of use of TempDB to return the results in the way it currently returns.
With the aim of providing a similar solution, but one that was lighter and did not use TempDB, allowing it to be executed quickly even in scenarios like the one mentioned above, me and Tiago Neves We created this “leaner” version, returning the main information and without using the various parameters that the original SP provides us.
What is the difference to sp_WhoIsActive?
Does not use TempDB
Faster execution
Simpler code to understand
It can easily be used as a view, table-valued function or scalar function, allowing you to use order by, select into, where, etc.
In addition to showing the running query, it also shows the Outer Command (sp_WhoIsActive also shows if the parameter @get_outer_command = 1 is used)
If the session is a job, it shows the name of the job in the program_name column
Returns the execution plan XML (sp_WhoIsActive also shows if the @get_plans = 1 parameter is used)
Source code for “sp_Tiny_WhoIsActive”
With the source code below, you can return information from running sessions with just 1 SELECT, and you can even create a view to facilitate future queries.
View source code (Full version - SQL Server 2012+)
SELECT
RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 86400 AS VARCHAR), 2) + ' ' +
RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' +
RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3)
AS Duration,
A.session_id AS session_id,
B.command,
TRY_CAST('<?query --' + CHAR(10) + (
SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE
WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)
ELSE B.statement_end_offset
END
) - B.statement_start_offset
) / 2 + 1
)
) + CHAR(10) + '--?>' AS XML) AS sql_text,
TRY_CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,
A.login_name,
'(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE
WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGE%LATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999)
WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'
ELSE ''
END), '') AS wait_info,
FORMAT(COALESCE(B.cpu_time, 0), '###,###,###,###,###,###,###,##0') AS CPU,
FORMAT(COALESCE(F.tempdb_allocations, 0), '###,###,###,###,###,###,###,##0') AS tempdb_allocations,
FORMAT(COALESCE((CASE WHEN F.tempdb_allocations > F.tempdb_current THEN F.tempdb_allocations - F.tempdb_current ELSE 0 END), 0), '###,###,###,###,###,###,###,##0') AS tempdb_current,
FORMAT(COALESCE(B.logical_reads, 0), '###,###,###,###,###,###,###,##0') AS reads,
FORMAT(COALESCE(B.writes, 0), '###,###,###,###,###,###,###,##0') AS writes,
FORMAT(COALESCE(B.reads, 0), '###,###,###,###,###,###,###,##0') AS physical_reads,
FORMAT(COALESCE(B.granted_query_memory, 0), '###,###,###,###,###,###,###,##0') AS used_memory,
NULLIF(B.blocking_session_id, 0) AS blocking_session_id,
COALESCE(G.blocked_session_count, 0) AS blocked_session_count,
'KILL ' + CAST(A.session_id AS VARCHAR(10)) AS kill_command,
(CASE
WHEN B.[deadlock_priority] <= -5 THEN 'Low'
WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal'
WHEN B.[deadlock_priority] >= 5 THEN 'High'
END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority],
B.row_count,
COALESCE(A.open_transaction_count, 0) AS open_tran_count,
(CASE B.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END) AS transaction_isolation_level,
A.[status],
NULLIF(B.percent_complete, 0) AS percent_complete,
A.[host_name],
COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name],
(CASE WHEN D.name IS NOT NULL THEN 'SQLAgent - TSQL Job (' + D.[name] + ' - ' + SUBSTRING(A.[program_name], 67, LEN(A.[program_name]) - 67) + ')' ELSE A.[program_name] END) AS [program_name],
H.[name] AS resource_governor_group,
COALESCE(B.start_time, A.last_request_end_time) AS start_time,
A.login_time,
COALESCE(B.request_id, 0) AS request_id,
W.query_plan
FROM
sys.dm_exec_sessions AS A WITH (NOLOCK)
LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id
JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id
LEFT JOIN msdb.dbo.sysjobs AS D ON RIGHT(D.job_id, 10) = RIGHT(SUBSTRING(A.[program_name], 30, 34), 10)
LEFT JOIN (
SELECT
session_id,
wait_type,
wait_duration_ms,
resource_description,
ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGE%LATCH%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking
FROM
sys.dm_os_waiting_tasks
) E ON A.session_id = E.session_id AND E.Ranking = 1
LEFT JOIN (
SELECT
session_id,
request_id,
SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations,
SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current
FROM
sys.dm_db_task_space_usage
GROUP BY
session_id,
request_id
) F ON B.session_id = F.session_id AND B.request_id = F.request_id
LEFT JOIN (
SELECT
blocking_session_id,
COUNT(*) AS blocked_session_count
FROM
sys.dm_exec_requests
WHERE
blocking_session_id != 0
GROUP BY
blocking_session_id
) G ON A.session_id = G.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X
OUTER APPLY sys.dm_exec_query_plan(B.plan_handle) AS W
LEFT JOIN sys.dm_resource_governor_workload_groups H ON A.group_id = H.group_id
WHERE
A.session_id > 50
AND A.session_id <> @@SPID
AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND A.open_transaction_count > 0))
If you want an even lighter and faster version, I removed some features (such as displaying the execution plan XML, counting locked sessions, etc.) and made the code available below:
View source code (Faster version with some fewer features)
SELECT
RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 86400 AS VARCHAR), 2) + ' ' +
RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' +
RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3)
AS Duration,
A.session_id AS session_id,
B.command,
TRY_CAST('<?query --' + CHAR(10) + (
SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE
WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)
ELSE B.statement_end_offset
END
) - B.statement_start_offset
) / 2 + 1
)
) + CHAR(10) + '--?>' AS XML) AS sql_text,
TRY_CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,
A.login_name,
'(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE
WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGEIOLATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999)
WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'
ELSE ''
END), '') AS wait_info,
FORMAT(COALESCE(B.cpu_time, 0), '###,###,###,###,###,###,###,##0') AS CPU,
FORMAT(COALESCE(F.tempdb_allocations, 0), '###,###,###,###,###,###,###,##0') AS tempdb_allocations,
FORMAT(COALESCE((CASE WHEN F.tempdb_allocations > F.tempdb_current THEN F.tempdb_allocations - F.tempdb_current ELSE 0 END), 0), '###,###,###,###,###,###,###,##0') AS tempdb_current,
FORMAT(COALESCE(B.logical_reads, 0), '###,###,###,###,###,###,###,##0') AS reads,
FORMAT(COALESCE(B.writes, 0), '###,###,###,###,###,###,###,##0') AS writes,
FORMAT(COALESCE(B.reads, 0), '###,###,###,###,###,###,###,##0') AS physical_reads,
FORMAT(COALESCE(B.granted_query_memory, 0), '###,###,###,###,###,###,###,##0') AS used_memory,
NULLIF(B.blocking_session_id, 0) AS blocking_session_id,
'KILL ' + CAST(A.session_id AS VARCHAR(10)) AS kill_command,
(CASE
WHEN B.[deadlock_priority] <= -5 THEN 'Low'
WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal'
WHEN B.[deadlock_priority] >= 5 THEN 'High'
END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority],
B.row_count,
COALESCE(A.open_transaction_count, 0) AS open_tran_count,
(CASE B.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END) AS transaction_isolation_level,
A.[status],
NULLIF(B.percent_complete, 0) AS percent_complete,
A.[host_name],
COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name],
A.[program_name],
COALESCE(B.start_time, A.last_request_end_time) AS start_time,
A.login_time,
COALESCE(B.request_id, 0) AS request_id
FROM
sys.dm_exec_sessions AS A WITH (NOLOCK)
LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id
JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id
LEFT JOIN (
SELECT
session_id,
wait_type,
wait_duration_ms,
resource_description,
ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGEIO%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking
FROM
sys.dm_os_waiting_tasks
) E ON A.session_id = E.session_id AND E.Ranking = 1
LEFT JOIN (
SELECT
session_id,
request_id,
SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations,
SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current
FROM
sys.dm_db_task_space_usage
GROUP BY
session_id,
request_id
) F ON B.session_id = F.session_id AND B.request_id = F.request_id
OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X
WHERE
A.session_id > 50
AND A.session_id <> @@SPID
AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND A.open_transaction_count > 0))
As you may have noticed, I use the FORMAT function, available from SQL Server 2012. If you are on an earlier version, executing the code below will generate an error for you. Just remove this function to be able to use the query normally (The numbers just won't have the thousands separators, but this is even good for sorting), or use the version below:
View source code (SQL Server 2008 and earlier)
SELECT
RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 86400 AS VARCHAR), 2) + ' ' +
RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' +
RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3)
AS Duration,
A.session_id AS session_id,
B.command,
CAST('<?query --' + CHAR(10) + (
SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE
WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)
ELSE B.statement_end_offset
END
) - B.statement_start_offset
) / 2 + 1
)
) + CHAR(10) + '--?>' AS XML) AS sql_text,
CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,
A.login_name,
'(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE
WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGEIOLATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999)
WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'
ELSE ''
END), '') AS wait_info,
COALESCE(B.cpu_time, 0) AS CPU,
COALESCE(F.tempdb_allocations, 0) AS tempdb_allocations,
COALESCE((CASE WHEN F.tempdb_allocations > F.tempdb_current THEN F.tempdb_allocations - F.tempdb_current ELSE 0 END), 0) AS tempdb_current,
COALESCE(B.logical_reads, 0) AS reads,
COALESCE(B.writes, 0) AS writes,
COALESCE(B.reads, 0) AS physical_reads,
COALESCE(B.granted_query_memory, 0) AS used_memory,
NULLIF(B.blocking_session_id, 0) AS blocking_session_id,
COALESCE(G.blocked_session_count, 0) AS blocked_session_count,
'KILL ' + CAST(A.session_id AS VARCHAR(10)) AS kill_command,
(CASE
WHEN B.[deadlock_priority] <= -5 THEN 'Low'
WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal'
WHEN B.[deadlock_priority] >= 5 THEN 'High'
END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority],
B.row_count,
B.open_transaction_count,
(CASE B.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END) AS transaction_isolation_level,
A.[status],
NULLIF(B.percent_complete, 0) AS percent_complete,
A.[host_name],
COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name],
A.[program_name],
H.[name] AS resource_governor_group,
COALESCE(B.start_time, A.last_request_end_time) AS start_time,
A.login_time,
COALESCE(B.request_id, 0) AS request_id,
W.query_plan
FROM
sys.dm_exec_sessions AS A WITH (NOLOCK)
LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id
JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id
LEFT JOIN (
SELECT
session_id,
wait_type,
wait_duration_ms,
resource_description,
ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGEIO%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking
FROM
sys.dm_os_waiting_tasks
) E ON A.session_id = E.session_id AND E.Ranking = 1
LEFT JOIN (
SELECT
session_id,
request_id,
SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations,
SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current
FROM
sys.dm_db_task_space_usage
GROUP BY
session_id,
request_id
) F ON B.session_id = F.session_id AND B.request_id = F.request_id
LEFT JOIN (
SELECT
blocking_session_id,
COUNT(*) AS blocked_session_count
FROM
sys.dm_exec_requests
WHERE
blocking_session_id != 0
GROUP BY
blocking_session_id
) G ON A.session_id = G.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X
OUTER APPLY sys.dm_exec_query_plan(B.[plan_handle]) AS W
LEFT JOIN sys.dm_resource_governor_workload_groups H ON A.group_id = H.group_id
WHERE
A.session_id > 50
AND A.session_id <> @@SPID
AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND B.open_transaction_count > 0))
Query execution result
That's it, folks!
If you have any suggestions or improvements, feel free to let me know in the comments or submit your changes via the link https://github.com/dirceuresende/tiny-sp_whoisactive.
I hope you liked it, hugs and see you next time!
sql server lightweight tiny sp_whoisactive identify query session running lighter without consuming tempdb low consumption
sql server lightweight tiny sp_whoisactive identify query session running lighter without consuming tempdb low consumption
Dirceu Resende
Database & BI Architect · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…