Monitor current SQL Server processes
How many times does somebody come to you and say 'Is something running on the server right now?' Or, 'Why is it so slow?' Time and time again, you're in there running sp_who2, trying to figure out where the problem is -- what is sucking your server resources?
Activity Monitor is pretty good. It's like SQL Server's version of Perf Mon. But, like most GUI-based tools, it's a memory pig all by itself! Here is a quick piece that I use to go behind the Activity Monitor GUI, and still get the goods. I've wrapped it into a view, which I target in a job that I use for monitoring, and collecting stats from the server. You could just use the SELECT statement, or even put it into a procedure.
Let me know what you think.
USE master
GO
CREATE VIEW dbo.vwCurrentSQLProcess
AS
/*
View utilized to help automate the monitoring of SQL Server processes.
It is available at all times on demand, but also used by the scheduled job which
collects the data and dumps into dbo.CurrentSQLProcess. Useful to automate
monitoring, alerts, and server administration.
SELECT * FROM dbo.vwCurrentSQLProcess
Auth: ME
Date: 2/27/2014
*/
SELECT
TOP 100 PERCENT
s.session_id [SessionID],
s.login_name [Login],
COALESCE(s.host_name, c.client_net_address) [Host],
s.program_name [Application],
r.command [Process],
t.task_state [State],
r.start_time [StartTime],
r.[status] [Status],
r.wait_type [WaitType],
TSQL.[text] [tSQL],
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) +
(tsu.internal_objects_alloc_page_count -
tsu.internal_objects_dealloc_page_count ) [#PagesAllocated]
FROM
sys.dm_exec_sessions s LEFT JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id LEFT JOIN sys.dm_db_task_space_usage tsu
ON s.session_id = tsu.session_id LEFT JOIN sys.dm_os_tasks t
ON tsu.session_id = t.session_id
AND tsu.request_id = t.request_id LEFT JOIN sys.dm_exec_requests r
ON tsu.session_id = r.session_id
AND tsu.request_id = r.request_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL
WHERE
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) +
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) > 0
ORDER BY
[#PagesAllocated] DESC;
These are the results... from my pretty idle, inactive laptop:
Activity Monitor is pretty good. It's like SQL Server's version of Perf Mon. But, like most GUI-based tools, it's a memory pig all by itself! Here is a quick piece that I use to go behind the Activity Monitor GUI, and still get the goods. I've wrapped it into a view, which I target in a job that I use for monitoring, and collecting stats from the server. You could just use the SELECT statement, or even put it into a procedure.
Let me know what you think.
USE master
GO
CREATE VIEW dbo.vwCurrentSQLProcess
AS
/*
View utilized to help automate the monitoring of SQL Server processes.
It is available at all times on demand, but also used by the scheduled job which
collects the data and dumps into dbo.CurrentSQLProcess. Useful to automate
monitoring, alerts, and server administration.
SELECT * FROM dbo.vwCurrentSQLProcess
Auth: ME
Date: 2/27/2014
*/
SELECT
TOP 100 PERCENT
s.session_id [SessionID],
s.login_name [Login],
COALESCE(s.host_name, c.client_net_address) [Host],
s.program_name [Application],
r.command [Process],
t.task_state [State],
r.start_time [StartTime],
r.[status] [Status],
r.wait_type [WaitType],
TSQL.[text] [tSQL],
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) +
(tsu.internal_objects_alloc_page_count -
tsu.internal_objects_dealloc_page_count ) [#PagesAllocated]
FROM
sys.dm_exec_sessions s LEFT JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id LEFT JOIN sys.dm_db_task_space_usage tsu
ON s.session_id = tsu.session_id LEFT JOIN sys.dm_os_tasks t
ON tsu.session_id = t.session_id
AND tsu.request_id = t.request_id LEFT JOIN sys.dm_exec_requests r
ON tsu.session_id = r.session_id
AND tsu.request_id = r.request_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL
WHERE
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) +
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) > 0
ORDER BY
[#PagesAllocated] DESC;
These are the results... from my pretty idle, inactive laptop:
|