5.1 Tools You Need

For this example, we’ll use the following:

• Process Explorer – to check which thread in the sqlservr.exe process is using the most CPU.

You can download Process Explorer from Microsoft: Process Explorer – Sysinternals | Microsoft Learn

On the SQL Server side, you also need permissions to query DMVs such as sys.dm_os_threads, sys.dm_os_workers, and sys.dm_exec_sessions (typically VIEW SERVER STATE).

5.2 Generate a High-CPU Test Query

First, we generate a CPU-intensive query from SSMS so that we have something easy to find. In this example, the session ID is 85.

SELECT TOP (10000000)
    SUM(
        CAST(ABS(CHECKSUM(a.object_id)) AS BIGINT) +
        CAST(ABS(CHECKSUM(b.object_id)) AS BIGINT) +
        CAST(ABS(CHECKSUM(c.object_id)) AS BIGINT) +
        CAST(ABS(CHECKSUM(d.object_id)) AS BIGINT) +
        CAST(ABS(CHECKSUM(a.name)) AS BIGINT) +
        CAST(ABS(CHECKSUM(b.name)) AS BIGINT) +
        CAST(ABS(CHECKSUM(c.name)) AS BIGINT) +
        CAST(ABS(CHECKSUM(d.name)) AS BIGINT)
    ) OVER () AS calc
FROM sys.objects a
CROSS JOIN sys.objects b
CROSS JOIN sys.objects c
CROSS JOIN sys.objects d
OPTION (MAXDOP 4);

This query performs a lot of CPU work by cross joining sys.objects and applying various checksum calculations. It is not meant for production, only as a convenient way to create CPU load for testing.

5.3 Find the Hottest Thread in Process Explorer

Next, open Process Explorer and locate the sqlservr.exe process. Open its properties and switch to the Threads tab.

Sort the threads by CPU usage in descending order. You should see one thread consuming the most CPU. In this example, the top thread has thread ID 4996.

5.4 Map OS Thread ID to SQL Server Session Using DMVs

Now we use SQL Server DMVs to map the OS thread ID to a SQL Server session. The following query joins threads, workers, tasks, and sessions:

SELECT
    t.os_thread_id,
    s.session_id,
    s.program_name
FROM sys.dm_os_threads AS t
JOIN sys.dm_os_workers AS w
    ON t.worker_address = w.worker_address
JOIN sys.dm_os_tasks AS k
    ON w.task_address = k.task_address
JOIN sys.dm_exec_sessions AS s
    ON k.session_id = s.session_id
WHERE t.os_thread_id = 4996;  -- Replace with your thread ID

Replace 4996 with the OS thread ID you found in Process Explorer. The result will show:

• os_thread_id – the OS thread ID (should match what you saw in Process Explorer).
• session_id – the SQL Server session associated with this worker/thread.
• program_name – the client program (for example, “Microsoft SQL Server Management Studio”).

5.5 Inspect the Session and Its Query

Once you know the session_id, you can use classic tools to inspect what the session is doing. For example:

-- Replace xxx with the session_id returned by the DMV query
EXEC sp_who2 xxx;
DBCC INPUTBUFFER(xxx);

sp_who2 gives you an overview of the session (login, database, CPU time, status, etc.), and DBCC INPUTBUFFER shows the last statement sent by the client on that session. Together, they allow you to confirm:

• Which session is tied to the high-CPU thread.
• What query that session is executing.