Who's there?

If your server is sluggish, a job is taking longer than expected, or someone just says ‘the database is slow’ — the first thing I want to know is who’s connected and what they’re running. This is the script I use first

It shows you current active sessions, including who’s logged in, the application they’re using, the statement and batch, how long it’s been running and if they are being blocked.

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    r.start_time,
    DATEDIFF(SECOND, r.start_time, GETDATE()) AS duration_seconds,
    r.cpu_time,
    r.reads,
    r.writes,
    r.blocking_session_id,
    SUBSTRING(
        t.text,
        (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(t.text)
            ELSE r.statement_end_offset
          END - r.statement_start_offset) / 2) + 1
    ) AS current_statement,
    t.text AS full_batch,
    r.plan_handle
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r
    ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1
ORDER BY r.start_time ASCSQL

The image below gives an idea of what you’ll see in the results.

The columns worth paying attention to are:

If you want to include idle sessions with open transactions then just update the WHERE clause to this:

WHERE s.is_user_process = 1
  AND (r.session_id IS NOT NULL OR s.open_transaction_count > 0)SQL

This should help you decide where to look next.