How to identify which CPU(s) is using for a query

If a query uses parallelism when executing, it may be interesting to see actually which CPU(s) are getting involved.

The below query gives you that information;

SELECT     t.session_id
          ,t.request_id

          ,t.scheduler_id

          ,t.task_state

          ,s.cpu_id

          ,r.database_id

          ,r.sql_handle

          ,w.last_wait_type

FROM sys.dm_os_tasks t

INNER JOIN sys.dm_os_schedulers s

     ON t.scheduler_id=s.scheduler_id

INNER JOIN sys.dm_exec_requests r

     ON t.session_id=r.session_id

INNER JOIN sys.dm_os_workers w

     ON t.task_address=w.task_address 

WHERE t.session_id=51 -- put the SPID

Copy and Paste the query below in different query and execute it. While executing this, run the query above in another window. Remember to change the SPID accordingly.

USE adventureworks

GO

SELECT @@SPID

GO

SELECT o.SalesOrderID, COUNT(*) AS Order_Count

FROM Sales.SalesOrderDetail o

WHERE EXISTS(

                   SELECT *

                   FROM Sales.SalesOrderHeader I

                   WHERE o.SalesOrderID>I.SalesOrderID                    

                   )

GROUP BY o.SalesOrderID

GO 50

You will see an output like this.

Notice the cpu_id column and it has two CPUs, 0 and 1. Look at the wait type as well. 

You can identify the query by using the sql_handle.