Analyze CPU Time per SQL Session

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paolo Taverna - 07 Mar 2008 08:05 GMT

Hello
Installation is SQL Server 2005 Enterprise Edition with 8 Processor. Is it
possible to analyze which SQL Server Session is consuming how much Processor
Ressource in Time or Percent? With the DMV Views i see that there are 11000
open SQL server Session. The 8 CPU are constant by 80 Percent (perfmon and
Taskmanager). This server is dedicated for SQL Server.
With the View sys.dm_exec_sessions i see how many Sessions are open, but i
must now, how many CPU Consumption per Session, so i can identify the
Sessions who are responsable for the high CPU Workload

Somebody knows how i can use the DMV Views or are there tools to retrieve
the information?

regards
Paolo

Reply to this Message

Roy Harvey (SQL Server MVP) - 07 Mar 2008 13:11 GMT

You have already found sys.dm_exec_sessions. Have you looked at the
column cpu_time it returns?

Roy Harvey
Beacon Falls, CT

>Hello
>Installation is SQL Server 2005 Enterprise Edition with 8 Processor. Is it
[quoted text clipped - 11 lines]
>regards
>Paolo

Reply to this Message

Paolo Taverna - 07 Mar 2008 14:21 GMT

Hi Roy

yes i had a look at the column cpu_time. So far i understand is the column
cpu_time the sum of the total cpu_time of the request since this Session is
alive. So if the column last_request_end_time is a lot behind the time when
the CPU's have high workload, the column total_cpu_time is not helpful.

Regards Paolo

> You have already found sys.dm_exec_sessions. Have you looked at the
> column cpu_time it returns?
[quoted text clipped - 19 lines]
>>regards
>>Paolo

Reply to this Message

Linchi Shea - 08 Mar 2008 02:24 GMT

It's not an ideal approach, but you could always take snapshots of the DMV
and find the cpu time differences between snapshots, and that should give you
some rough idea.

Linchi

> Hi Roy
>
[quoted text clipped - 28 lines]
> >>regards
> >>Paolo

Reply to this Message