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
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
SELECT o.SalesOrderID, COUNT(*) AS Order_Count
FROM Sales.SalesOrderDetail o
                   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

How to fix cardinality estimation anomalies [Video]

Use the link mentioned below to watch the presentation that I delivered for PASS DBA Virtual Chapter about Filtered Statistics.  http:...