SQL Server Queries Using CPU Cycles

Query shows which SQL Server queries are using the most CPU cycles:

SELECT sessions.session_id
      ,requests.wait_time / (1000.0) 'wait_time(sec)'
      ,requests.total_elapsed_time / (1000.0) 'total_elapsed_time(Sec)'
      ,Substring(sqltext.TEXT, (requests.statement_start_offset / 2) + 1, 
         CASE requests.statement_end_offset
         WHEN - 1
         THEN Datalength(sqltext.TEXT)
         ELSE requests.statement_end_offset
         END - requests.statement_start_offset
        ) / 2
       ) + 1) AS statement_text
      ,Coalesce(Quotename(Db_name(sqltext.dbid)) + N'.' + 
                Quotename(Object_schema_name(sqltext.objectid, sqltext.dbid)) + N'.' + 
                Quotename(Object_name(sqltext.objectid, sqltext.dbid)), '') AS command_text
FROM sys.dm_exec_sessions AS sessions
INNER JOIN sys.dm_exec_requests AS requests 
      ON requests.session_id = sessions.session_id
      CROSS APPLY sys.dm_exec_sql_text(requests.sql_handle) AS sqltext
WHERE requests.session_id != @@SPID
ORDER BY requests.cpu_time DESC;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s