August 7, 2013 at 5:36 am
Hi
I've been trying to determine the simplest way to determine which sessions are using high CPU.
I've used the DMV 'sys.dm_exec_sessions', which in the output, gives 'CPU_TIME' and 'MEMORY_USAGE' for each session.
Could someone please help elaborate what each metric actually shows? Could I use the above to help determine which active session is currently using the highest amount of CPU?
August 7, 2013 at 12:29 pm
Recommand using SQL Server Profiler + Performance Monitor to catch the sessions.
August 7, 2013 at 3:50 pm
Yes, you could use these values, but you would need to sample and aggregate them. There may be a session with high CPU values, but which has been idle for a fortnight.
Someone said Profiler: only use Profiler for quick peeks. For any longer tracing, use server-side traces.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 8, 2013 at 1:51 pm
The following query is also helpful to see what user sessions are killing CPU, as user sessions that are not currently running will not clutter things up. It's served me pretty well when I've just wanted a quick glance at what is chewing through CPU:
SELECT session_id,
COUNT(*) AS Threads,
SUM(context_switches_count) AS ContextSwitches
FROM sys.dm_os_tasks
WHERE session_id>50
GROUP BY session_id
ORDER BY Threads desc
I sometimes order by one or the other of the aggregated columns (or both). It's often not necessary to obsess about the ordering, though.
Cheers!
August 12, 2013 at 2:49 am
Thank you, will check this out.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply