Analyze CPU Time per SQL Session

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
PaoloYou have already found sys.dm_exec_sessions. Have you looked at the
column cpu_time it returns?
Roy Harvey
Beacon Falls, CT
On Fri, 7 Mar 2008 09:05:44 +0100, "Paolo Taverna" <nomail@.nomail.nml>
wrote:
>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
>|||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
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> schrieb im Newsbeitrag
news:okf2t3demqlov8f9s7v1qca3ve11lhnl5v@.4ax.com...
> You have already found sys.dm_exec_sessions. Have you looked at the
> column cpu_time it returns?
> Roy Harvey
> Beacon Falls, CT
> On Fri, 7 Mar 2008 09:05:44 +0100, "Paolo Taverna" <nomail@.nomail.nml>
> wrote:
>>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|||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
"Paolo Taverna" wrote:
> 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
> "Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> schrieb im Newsbeitrag
> news:okf2t3demqlov8f9s7v1qca3ve11lhnl5v@.4ax.com...
> > You have already found sys.dm_exec_sessions. Have you looked at the
> > column cpu_time it returns?
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Fri, 7 Mar 2008 09:05:44 +0100, "Paolo Taverna" <nomail@.nomail.nml>
> > wrote:
> >
> >>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
> >>
>
>