Monday, June 18, 2012

Monitor Current SQL Server Processes Without Activity Monitor

As a database admin or one of the main guys in SQL Server admin group, we always want to see what's going on the sever level. Example: What are the session Id's that are active , Is there any head blocks occurring due to which the server is having high load or is there anyone running an expensive query that is sucking all the server resources. As a part of SQL Server tools , SQL Server management studio provides a utility called Activity Monitor that gives a very good picture of whats going on. We are here not to discuss the Activity Monitor  but another way by which you can figure out what going on in the server. The reason i am presenting this because many times when the load on the server increases , there are times Activity monitor can't even come up and you are stuck. 

This situation could  happen when the server is running on low memory,  high CPU usage or may be a process that is sucking all the server resources. I will present a query that will help monitor current SQL Server processes even when you can't bring up  Activity Monitor and  figure out what might be  the cause of down of the problem. There have been a number of times that I couldn't bring up the activity monitor and use this query to figure out the SessionId(SPID) that is causing the problems to the SQL Server. 

The following TSQL code queries the sys database and figure out the current processes on the SQL Server and also provides the total number of pages allocated to the process. You should sort it by total number of pages allocated (descending) to get the most expensive process running. 

 SELECT s.session_id    AS 'SessionId',
       s.login_name    AS 'Login',
       COALESCE(s.host_name, c.client_net_address) AS 'Host',
       s.program_name  AS 'Application',
       t.task_state    AS 'TaskState',
       r.start_time    AS 'TaskStartTime',
       r.[status] AS 'TaskStatus',
       r.wait_type     AS 'TaskWaitType',
       TSQL.[text] AS '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
       )               AS 'TotalPagesAllocated'
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  tsu.session_id = s.session_id
       LEFT JOIN sys.dm_os_tasks t
            ON  t.session_id = tsu.session_id
            AND t.request_id = tsu.request_id
       LEFT JOIN sys.dm_exec_requests r
            ON  r.session_id = tsu.session_id
            AND r.request_id = tsu.request_id
       OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) 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
       ) > 0;

I would suggest you wrap this query in a view and query the view using order by TotalPagesAllocated DESC. You can then KILL the SPID that is causing an issue or figure out where the problem is coming from. I will briefly discuss the columns returned by this query : 

1)SessionID: The unique number assigned to a process connected to SQL Server. This is also called a SPID.

2) Login: The login to which the task is tied.

3) Host: The host from where the task is originated.

4) Application: The application that invokes this task.

5)  TaskState: The current state of the task. 

6) TaskStartTime: The time at which the task began. 

7) TaskStatus: The current status of the task. 

8) TaskWaitType: Indicates the event the task is  waiting on.

9) TSQL: Actual SQL that is being run. 

10) TotalPagesAllocated: This is sum of total pages allocated to the process. The higher number indicates the possibility of the process sucking the resources of the server. 

You can also set up an automatic job that can look at this view and perform some logic based on process running time or TotalPagesAllocated.That would be a good way of automate monitoring of server.   

I hope you have enjoyed reading this article. Please leave your comments . I will keep on improving my posts and will try to share good material.  


  1. Hi, I would appreciate this for SQL 2000. Thank you.

  2. Hi Alfonso: Thanks for your comment. I am trying to find out the equivalent of this query on SQL 2000 but i haven't worked on it. Give me some time so that i can find out. Please share if you find out something.

  3. Ok, no problem. Take your time. I'll share with you if I find something. Regards.

  4. Hi, nice work, how get the Login and DBName columns?

  5. I had this issue few day ago and couldn't fix it. Thats when I started googling and found a team of friendly people called called ServerBuddies.
    They provide quality remote server management, including troubleshooting, Server Management, Plesk Support, Server Maintenance, Server Monitoring, Server Troubleshooting and support at a affordable rates. Here are option for Server Management, Server Maintenance, Server Monitoring, Server Troubleshooting, Server Optimization, Plesk Support, Linux Support, cPanel Support and Plesk Support.
    They are great, I highly recommend to use these guys!

  6. This is an interesting query, thanks! The only question I have open on it is if the page allocations being shown with this query are memory page allocations or disk page allocations...

  7. Thanks for sharing this Information, Got to learn new things from your Blog on Android.The freeware version of Web app that comes with monitoring status of server and worldwide locations.