Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

How can I findout which statements are using the CPU?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

I use this script. You will need to modify as you see fit to filter apps, last batch time, etc...

SELECT 
top 50
 D.text SQLStatement,
 A.program_name,
 A.Session_ID SPID,
 ISNULL(B.status,A.status) Status,
 A.login_name Login,
 A.host_name HostName,
 C.BlkBy, 
 DB_NAME(B.Database_ID) DBName,
 B.command,
 ISNULL(B.cpu_time, A.cpu_time) CPUTime,
 ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO, 
 A.last_request_start_time LastBatch
FROM
    sys.dm_exec_sessions A 
   LEFT JOIN
    sys.dm_exec_requests B
    ON A.session_id = B.session_id
   LEFT JOIN
       (
        SELECT 
                A.request_session_id SPID,
                B.blocking_session_id BlkBy
           FROM sys.dm_tran_locks as A
             INNER JOIN sys.dm_os_waiting_tasks as B
            ON A.lock_owner_address = B.resource_address
        ) C
    ON A.Session_ID = C.SPID
   OUTER APPLY sys.dm_exec_sql_text(sql_handle) D
where  D.text is not null
order by cputime desc
share|improve this answer
    
Thanks this was very usefull –  Shiraz Bhaiji Mar 17 '10 at 8:41

Management Studio > Current Activity

or

Execute sp_who2 in a query window

If it's an ongoing issue you could also run a trace to get more detailed info over time.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.