CHecking SQL Acitivity

Often, I need to check the activity on our SQL servers to see what is running, what might be bogging down the system, or even what might be blocking a process. SQL has two built in commands to check activity, sp_who and sp_who2, but neither really solves the problem I have. You can see the output of both below:

exec sp_who

As you can see, this doesn't tell me if a process is blocking and it only tells me the very basic part of the command they are running.

exec sp_who2

This is slightly better in that it includes the BlkBy column as well as the Program Name. The Program Name is useful to know those users who might be using Access since Access is notorious for pulling entire tables and no using WITH(NOLOCK) to prevent blocking of tables.

The query below I adapted from What SQL Statements Are Currently Executing?. I am working to adapt this to run as a SQL Job to alert us when someone is blocking a query and to send us what they are running. I'll add that code next week when I have it working.

-- Do not lock anything, and do not get held up by any locks.
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
    -- What SQL Statements Are Currently Running?
    SELECT [SPID] = session_Id
      , blocking_session_id AS BLKBY
      , [Database] = DB_NAME(sp.dbid)
      , nt_domain + '\' + nt_username AS NetworkID
      , Hostname
      , [Status] = er.status
      , [Wait] = wait_type
      , [Individual Query] = SUBSTRING (qt.text,
             er.statement_start_offset/2,
      (CASE WHEN er.statement_end_offset = -1
             THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
            ELSE er.statement_end_offset END -
                                er.statement_start_offset)/2)

    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE --session_Id > 50 AND    -- Ignore system spids.
    session_Id NOT IN (@@SPID)     -- Ignore this current statement.
    ORDER BY 1, 2