September 23, 2015 at 8:02 am
Hi All,
Can any one help me to get the script to find the current queries and process that are causing to utilize high CPU on the SQL server 2008/2008 R2.?
Thanks In advance.
September 24, 2015 at 1:04 am
bala2 (9/23/2015)
Hi All,Can any one help me to get the script to find the current queries and process that are causing to utilize high CPU on the SQL server 2008/2008 R2.?
Thanks In advance.
Something like this?
😎
SELECT TOP (10)
*
FROM sys.dm_exec_requests DER
OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) DEST
OUTER APPLY sys.dm_exec_sessions DES
WHERE DER.status = N'running'
AND DER.session_id = DES.session_id
AND DER.session_id <> @@SPID
ORDER BY DER.cpu_time DESC;
October 6, 2015 at 2:52 am
I'd go instead with https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
Eirikur's query will show individual queries with high CPU usage, but by using just that you may miss the queries which are running very, very frequently.
Which is worse for overall CPU usage, the query that uses 30 seconds of CPU and runs twice an hour, or the query which uses 600ms of CPU and runs 5 times a second? If you look only at individual queries, you'll identify the first, probably not the second.
Oh, and a minor correction
SELECT TOP (10)
*
FROM sys.dm_exec_requests DER
INNER JOIN sys.dm_exec_sessions DES
OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) DEST
WHERE DER.status = N'running'
AND DER.session_id = DES.session_id
AND DER.session_id <> @@SPID
ORDER BY DER.cpu_time
It shouldn't be an apply between exec_requests and exec_sessions. Apply is used to run a function or subquery once per row of the outer query, it's not a replacement for a join and, if used as one, generally runs really slow. Not a problem here, these DMVs shouldn't have lots of rows in, but can be a problem when used with large tables. I've seen a query go from 13 hours to 20 minutes by replacing an unrequired APPLY with a JOIN.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply