Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sys.dm_exec_sessions - High CPU Expand / Collapse
Author
Message
Posted Tuesday, August 27, 2013 9:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:46 AM
Points: 70, Visits: 330
Hi

I'm trying to work out which sessions are using the highest amount of CPU, this must be via script.

I'm running:

SELECT session_id, cpu_time, memory_usage FROM sys.dm_exec_sessions ORDER BY memory_usage desc

could someone help me decipher what the 'memory_usage' parameter actually depicts?

Has anyone else tried using this dynamic view to try and ascertain, quickly, which session is using the highest CPU?
Post #1488846
Posted Tuesday, August 27, 2013 10:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 40,438, Visits: 36,894
Be careful with that. The CPU_time is the total time since the connection was established, not necessarily going to help much with identifying high CPU-using queries.

Memory usage is probably the total memory grant the session has used, again since it was established.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1488859
Posted Tuesday, August 27, 2013 4:02 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 817, Visits: 743
To add what Gail says, you need to run the query once saving the data into a temp table, wait for time, and then run again to compute the delta. Alternatively, run a job that polls the table every, say, 10 minutes. Be careful that a spid can log out and new connections and get the same spid!

(And on the top of my head, I don't know what happens when a connection is reused in connection pooling.)


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1488978
Posted Wednesday, August 28, 2013 1:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:46 AM
Points: 70, Visits: 330
.
Post #1489048
Posted Wednesday, August 28, 2013 1:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:46 AM
Points: 70, Visits: 330
What would you suggest is the simplest and most easy to read method of establishing which active session is using the highest amount of CPU? I want to stay away from using Performance Monitor and would ideally want the information extracting via a SELECT statement?
Post #1489049
Posted Wednesday, August 28, 2013 2:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 40,438, Visits: 36,894
As Erland said, something that polls the table on a regular interval, stores the results somewhere and then you'd difference the latest and previous rows.

What are you trying to do here?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1489055
Posted Wednesday, August 28, 2013 3:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:46 AM
Points: 70, Visits: 330
Effectively, I need to be able to establish quickly which sessions are using a high amount of CPU.

I'd want for example, a job to run every 5 minutes, which pulls the top 5 CPU consumers and writes them to a log file. If the client were to complain about performance problems, a quick check of the log file would the first port-of-call.
Post #1489092
Posted Wednesday, August 28, 2013 4:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 225, Visits: 1,754
Use a sp_WhoIsActive with the @delta_interval option:
http://sqlblog.com/blogs/adam_machanic/archive/2011/04/26/delta-force-a-month-of-activity-monitoring-part-26-of-30.aspx

and you can also save the results in a table, but the @delta_interval option should be enough for you:
http://sqlblog.com/blogs/adam_machanic/archive/2011/04/25/capturing-the-output-a-month-of-activity-monitoring-part-25-of-30.aspx
Post #1489107
Posted Wednesday, August 28, 2013 8:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 6, 2014 5:46 AM
Points: 70, Visits: 330
I don't think it's an official Microsoft SP?

If that is the case, I can't use it

Although it looks like exactly like what I need; just a snapshot of the current CPU usage/time listed by session ID!
Post #1489244
Posted Wednesday, August 28, 2013 8:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 40,438, Visits: 36,894
wak_no1 (8/28/2013)
Although it looks like exactly like what I need; just a snapshot of the current CPU usage/time listed by session ID!


Then see Erland's suggestion from yesterday. That's how you'd achieve what you want.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1489253
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse