SQL Server See What's Running

Below are a collection of SQL query that I use to diagnose and trace what is running on the server.

This is my goto snippet to quickly identify what is running and the current state of the server. It will provide you cpu and disk read time as well as the query plans which can be used to identify problem children.

1
DROP TABLE #PERFSNAP
2

3
SELECT
4
    r.cpu_time,
5
    r.logical_reads,
6
    r.session_id
7
INTO #TEMP
8
FROM sys.dm_exec_sessions s
9
JOIN sys.dm_exec_requests r
10
ON s.session_id = r.session_id
11
--AND s.last_request_start_time = r.start_time
12
WHERE is_user_process = 1
13

14
WAITFOR DELAY '00:00:01'
15

16
SELECT
17
    [text] = SUBSTRING(
18
        h.text,
19
        (r.statement_start_offset / 2) + 1,
20
        ((  CASE r.statement_end_offset
21
            WHEN -1
22
            THEN DATALENGTH(h.text)
23
            ELSE r.statement_end_offset
24
            END - r.statement_start_offset) / 2) + 1
25
    ),
26
    [cpu_diff] = r.cpu_time - t.cpu_time,
27
    [read_diff] = r.logical_reads - t.logical_reads,
28
    p.query_plan,
29
    r.wait_type,
30
    r.wait_time,
31
    r.last_wait_type,
32
    r.wait_resource,
33
    r.command,
34
    [database] = DB_NAME(r.database_id),
35
    r.blocking_session_id,
36
    r.granted_query_memory,
37
    r.session_id,
38
    r.reads,
39
    r.writes,
40
    r.row_count,
41
    s.[host_name],
42
    s.program_name,
43
    s.login_name
44
INTO #PERFSNAP
45
FROM sys.dm_exec_sessions s
46
JOIN sys.dm_exec_requests r
47
ON s.session_id = r.session_id
48
AND s.last_request_start_time = r.start_time
49
FULL JOIN #TEMP t
50
ON t.session_id = s.session_id
51
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
52
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p
53
ORDER BY 3 desc
54

55
DROP TABLE #TEMP
56

57
SELECT * FROM #PERFSNAP

This is another variation of the above code snippet just without the cpu and read timing, and query plans.

1
BEGIN
2
    -- Do not lock anything, and do not get held up by any locks.
3
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
4

5
    -- What SQL Statements Are Currently Running?
6
    SELECT
7
        [spid] = session_Id,
8
        ecid,
9
        [database] = DB_NAME(sp.dbid),
10
        [user] = nt_username,
11
        [status] = er.status,
12
        [wait] = wait_type,
13
        [individual_query] = SUBSTRING(
14
            qt.text,
15
            er.statement_start_offset / 2,
16
            (   CASE
17
                WHEN er.statement_end_offset = -1
18
                THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
19
                ELSE er.statement_end_offset
20
                END - er.statement_start_offset
21
            ) / 2
22
        ),
23
        [parent_query] = qt.text,
24
        program_name,
25
        hostname,
26
        nt_domain,
27
        start_time
28
    FROM sys.dm_exec_requests er
29
    JOIN sys.sysprocesses sp
30
    ON er.session_id = sp.spid
31
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) qt
32
    WHERE session_Id > 50 -- ignore system spids
33
    AND session_Id NOT IN (@@SPID) -- ignore this current statement
34
    --AND session_Id IN ( 126 )
35
    --AND DB_NAME(sp.dbid) = ''
36
    ORDER BY 1, 2
37
END
38

39
--EXECUTE sp_who2 85

These snips will help track down different types of wait resources and their reasons.

1
--KEY: 8:281474980315136 (4102a73ebc59)
2
--( Key Wait Resource )--
3
SELECT
4
    [TableName] = o.name,
5
    [IndexName] = i.name,
6
    [SchemaName] = SCHEMA_NAME(o.schema_id)
7
FROM sys.partitions p
8
JOIN sys.objects o
9
ON p.OBJECT_ID = o.OBJECT_ID
10
JOIN sys.indexes i
11
ON p.OBJECT_ID = i.OBJECT_ID
12
AND p.index_id = i.index_id
13
WHERE p.hobt_id = 281474980315136
14

15
--( Page Wait Resource )--
16
SELECT DB_NAME(68)
17

18
-- select the database based on the output above statement then execute the below statement
19
DBCC traceon (3604)
20
GO
21
-- Database_id, file_id, page_id
22
DBCC page (68, 1, 492478)
23

24
--( Object Wait Resource )--
25
--OBJECT: 8:1954106002:0
26
SELECT OBJECT_NAME(1954106002)

~ SK