How to determine sql process responsible for system load

Hi
I administer sql databases at a local hospital and would occasionally like
to determine which sql server process/task is responsible for the load on
the system (cpu, memory etc) at a particular time.
The server hosts a largish number of databases for various 3rd party systems
and some in house systems primarily accessed in an interactive
(non-transactional) manner. I monitor system performance regularly and have
been noticing sustained periods (~5 mins) of high cpu usage (>50%) and disk
activity, which is not consistent with the normal pattern. I would like to
work out which database/application is responsible. At the same time SQL
compilations are low and the Buffer Manager looks healthy (ie Page life
expectancy, Cache Hits and Lizy Writes all good). Task Manager reports that
SQL Server is the application using the cpu time and the server does not run
any other applications.
The Current Activity in Enterprise Manager shows me all active connections
and processes but not their relative system load. The Profiler will show me
which processes are running and what they are doing, which is very useful,
however it does not show me what is running at the time I start the profile
log, which would typically be _after_ I have discovered that the system is
excessively loaded.
Is there any other way to get a snapshot of all active sql server processes
and their relative load/queryplan cost/size of returned recordset etc that
could give me a clue?
InterBase, for instance, has a Performance Monitor which gives a real-time
display of memory use, records fetched and much more per database and per
running query/procedure.
Thanks & Regards
Bas
========================================
==
Bas Groeneveld
Benchmark Design System and Software Engineering
PO Box 165N, Ballarat North, VIC 3350
Phone: +61 3 5333 5441 Mob: 0409 954 501Forgot to mention that we are running SQL Server 2000 SP3
Bas
"Bas Groeneveld" <nospam@.nospam.com.au> wrote in message
news:1aReg.252$ap3.32@.news-server.bigpond.net.au...
> Hi
> I administer sql databases at a local hospital and would occasionally like
> to determine which sql server process/task is responsible for the load on
> the system (cpu, memory etc) at a particular time.
> The server hosts a largish number of databases for various 3rd party
systems
> and some in house systems primarily accessed in an interactive
> (non-transactional) manner. I monitor system performance regularly and
have
> been noticing sustained periods (~5 mins) of high cpu usage (>50%) and
disk
> activity, which is not consistent with the normal pattern. I would like to
> work out which database/application is responsible. At the same time SQL
> compilations are low and the Buffer Manager looks healthy (ie Page life
> expectancy, Cache Hits and Lizy Writes all good). Task Manager reports
that
> SQL Server is the application using the cpu time and the server does not
run
> any other applications.
> The Current Activity in Enterprise Manager shows me all active connections
> and processes but not their relative system load. The Profiler will show
me
> which processes are running and what they are doing, which is very useful,
> however it does not show me what is running at the time I start the
profile
> log, which would typically be _after_ I have discovered that the system is
> excessively loaded.
> Is there any other way to get a snapshot of all active sql server
processes
> and their relative load/queryplan cost/size of returned recordset etc that
> could give me a clue?
> InterBase, for instance, has a Performance Monitor which gives a real-time
> display of memory use, records fetched and much more per database and per
> running query/procedure.
> Thanks & Regards
> Bas
> --
> ========================================
==
> Bas Groeneveld
> Benchmark Design System and Software Engineering
> PO Box 165N, Ballarat North, VIC 3350
> Phone: +61 3 5333 5441 Mob: 0409 954 501
>
>|||Hi Bas,
You best bet is probably running profiler. You should be able to get a
pretty good overview of what's going on within SQL Server during your busy
times. You will be able to identify any long running queries and there's eve
n
a column for CPU usage per event.
Ray
"Bas Groeneveld" wrote:

> Forgot to mention that we are running SQL Server 2000 SP3
> Bas
> "Bas Groeneveld" <nospam@.nospam.com.au> wrote in message
> news:1aReg.252$ap3.32@.news-server.bigpond.net.au...
> systems
> have
> disk
> that
> run
> me
> profile
> processes
>
>|||Hi Bas,
You best bet is probably running profiler. You should be able to get a
pretty good overview of what's going on within SQL Server during your busy
times. You will be able to identify any long running queries and there's eve
n
a column for CPU usage per event.
Ray
"Bas Groeneveld" wrote:

> Forgot to mention that we are running SQL Server 2000 SP3
> Bas
> "Bas Groeneveld" <nospam@.nospam.com.au> wrote in message
> news:1aReg.252$ap3.32@.news-server.bigpond.net.au...
> systems
> have
> disk
> that
> run
> me
> profile
> processes
>
>|||Thanks for the reply Ray.
My only problem is how to determine which queries are already running when I
start the profiler (as I usually start it _after_ I realise that there is an
excessive load). Is there a way to do this?
If there is no way to do that I would need to run both profiler and
performance monitor logging to files for a period of time and then work it
out from the log files. This is possible of course, I was just wondering if
there was another way.
Cheers
Bas
"rb" <rb@.discussions.microsoft.com> wrote in message
news:C94751DD-9ECE-46DA-A288-47086356B167@.microsoft.com...
> Hi Bas,
> You best bet is probably running profiler. You should be able to get a
> pretty good overview of what's going on within SQL Server during your busy
> times. You will be able to identify any long running queries and there's
even[vbcol=seagreen]
> a column for CPU usage per event.
> Ray
> "Bas Groeneveld" wrote:
>
like[vbcol=seagreen]
on[vbcol=seagreen]
like to[vbcol=seagreen]
SQL[vbcol=seagreen]
life[vbcol=seagreen]
not[vbcol=seagreen]
connections[vbcol=seagreen]
show[vbcol=seagreen]
useful,[vbcol=seagreen]
system is[vbcol=seagreen]
that[vbcol=seagreen]
real-time[vbcol=seagreen]
per[vbcol=seagreen]|||Thanks for the reply Ray.
My only problem is how to determine which queries are already running when I
start the profiler (as I usually start it _after_ I realise that there is an
excessive load). Is there a way to do this?
If there is no way to do that I would need to run both profiler and
performance monitor logging to files for a period of time and then work it
out from the log files. This is possible of course, I was just wondering if
there was another way.
Cheers
Bas
"rb" <rb@.discussions.microsoft.com> wrote in message
news:C94751DD-9ECE-46DA-A288-47086356B167@.microsoft.com...
> Hi Bas,
> You best bet is probably running profiler. You should be able to get a
> pretty good overview of what's going on within SQL Server during your busy
> times. You will be able to identify any long running queries and there's
even[vbcol=seagreen]
> a column for CPU usage per event.
> Ray
> "Bas Groeneveld" wrote:
>
like[vbcol=seagreen]
on[vbcol=seagreen]
like to[vbcol=seagreen]
SQL[vbcol=seagreen]
life[vbcol=seagreen]
not[vbcol=seagreen]
connections[vbcol=seagreen]
show[vbcol=seagreen]
useful,[vbcol=seagreen]
system is[vbcol=seagreen]
that[vbcol=seagreen]
real-time[vbcol=seagreen]
per[vbcol=seagreen]|||Hi Bas,
You could run DBCC Inputbuffer to check what query is being run (if you have
identified the spid first), you could also run sp_who2 for more info on the
queries being executed by all users. If you suspect locking/dead locks you
could enable trace flags 1204 (and 1205 I think) and get info posted to the
SQL error log.
You could run profiler and only filter out long running queries but as you
say you need to run this prior to the problem occuring to gather useful
information. You would get I/O and cpu stats from profiler so it may we wort
h
running if this problem occurs often.
Ray
"Bas Groeneveld" wrote:

> Thanks for the reply Ray.
> My only problem is how to determine which queries are already running when
I
> start the profiler (as I usually start it _after_ I realise that there is
an
> excessive load). Is there a way to do this?
> If there is no way to do that I would need to run both profiler and
> performance monitor logging to files for a period of time and then work it
> out from the log files. This is possible of course, I was just wondering i
f
> there was another way.
> Cheers
> Bas
>
> "rb" <rb@.discussions.microsoft.com> wrote in message
> news:C94751DD-9ECE-46DA-A288-47086356B167@.microsoft.com...
> even
> like
> on
> like to
> SQL
> life
> not
> connections
> show
> useful,
> system is
> that
> real-time
> per
>
>|||Hi Bas,
You could run DBCC Inputbuffer to check what query is being run (if you have
identified the spid first), you could also run sp_who2 for more info on the
queries being executed by all users. If you suspect locking/dead locks you
could enable trace flags 1204 (and 1205 I think) and get info posted to the
SQL error log.
You could run profiler and only filter out long running queries but as you
say you need to run this prior to the problem occuring to gather useful
information. You would get I/O and cpu stats from profiler so it may we wort
h
running if this problem occurs often.
Ray
"Bas Groeneveld" wrote:

> Thanks for the reply Ray.
> My only problem is how to determine which queries are already running when
I
> start the profiler (as I usually start it _after_ I realise that there is
an
> excessive load). Is there a way to do this?
> If there is no way to do that I would need to run both profiler and
> performance monitor logging to files for a period of time and then work it
> out from the log files. This is possible of course, I was just wondering i
f
> there was another way.
> Cheers
> Bas
>
> "rb" <rb@.discussions.microsoft.com> wrote in message
> news:C94751DD-9ECE-46DA-A288-47086356B167@.microsoft.com...
> even
> like
> on
> like to
> SQL
> life
> not
> connections
> show
> useful,
> system is
> that
> real-time
> per
>
>