Thursday, February 27, 2014

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
  CREATE VIEW dbo.vwCurrentSQLProcess
   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

        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]
        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
        (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
        [#PagesAllocated] DESC;

These are the results... from my pretty idle, inactive laptop:

SessionID Login Host App. Process State StartTime Status WaitType tSQL #PagesAllocated
5 sa NULL NULL SIGNAL HANDLER SUSPENDED 2014-02-26 08:10:39.807 background KSOURCE_WAKEUP NULL 4

Total number of active/open SQL Server connections, per database

If you've managed SQL Server, I'm sure you've experienced problems with client connections.  There are countless client libraries for SQL Server, and sometimes there can be just as many problems with each of them.  :-(
The database connection is done in several steps;  Establish connection, the handshake, parse the connection string, and then SQL has to authenticate.  Then, checks are done at the database and the transaction level, and more.
Unfortunately, when clients disconnect, the connection is not always closed properly.  PHP, for example, is known for problems releasing the connection, which creates 'orphaned' connections.  Typically caused by problems at the app, or even network-related problems, but whatever it is, these orphans can very easily consume valuable resources from your server!
In this post I've just given you a method to return the active/open connections, per database. Both sp_who or sp_who2 are great, but they output a LOT of detail that you need to scroll through, and translate.  This is just a fast way to get in there, and count your connections, per database.
   /* Return Active/Open connections per database. */ 
      DB_NAME(dbid) [Database],
      hostname [Host],
      d.client_net_address [IPaddress],
      loginame [Login],
      COUNT(dbid) [NoOfConnections]
      sys.sysprocesses s INNER JOIN sys.dm_exec_connections d
        ON s.spid = d.session_id
      d.session_id >= 51 -- << user sessions are >= 51

These are my results.  If I was on a larger network, we'd see other IPs and host machines:

Database Host IPaddress Login NoOfConnections
master MYPCNAME <local machine> MYPCNAME\MyName 6

Please see this for more detail on sys.dm_exec_connections:

Also see this for more detail on sys.sysprocesses.  But, don't miss the backward compatibility note!  This one has been replaced by sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests, and it will be gone in a future release: