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 even
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...
> > 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
> >
> >
> >
>
>|||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
> 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...
> > > 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 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 worth
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 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
> > 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...
> > > > 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
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>sql