Wednesday, March 9, 2011

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.


No comments:

Post a Comment

Transitioning of SQLOS to SQLPAL

​ The SQLOS​ Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on...