how to Identify a rogue SQL process on a server

Hi,
Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
which is hammering the server.
Every now and then, our DB server get hammered (4* CPUs running at over 80%
usage), and I can see a list of process in management\process info. but it
shows the total cpu counter rather then the process that hammering the
server now.
Is there a way to identify which SQL process is hammering the server?
Windows 2000 Sp4 / SQL 2000 SP3a
Kind regards,
Jeremy Byrski
www.CentralR.com
EXEC sp_who2
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server?
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>
|||you can use sp_who2 to see what activity is going on, or use enterprise
manager, under "Management" > "Current Activity"
Simon Worth
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server?
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>
|||You can also use PerfMon and sysprocesses to find the spid which is using up
a lot of CPU time. Find the highest Thread<sqlservr#<instance>)\%Processor
Time counter in Perfmon and match it to Thread(sqlservr#<instance>)\ID
Thread, which corresponds to the kpid column in sysprocesses. Use that
information to run dbcc inputbuffer(<spid>). More information is available
at http://support.microsoft.com/default...;en-us;117559.
Adrian
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
> 80% usage), and I can see a list of process in management\process info.
> but it shows the total cpu counter rather then the process that hammering
> the server now.
>
> Is there a way to identify which SQL process is hammering the server?
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>
|||Hi Simon, and Aeron
I did try that, but it seems the display the Total CPU time since maybe the SQL service has started.
I'm looking to identify a SQL Process ID that it taking a lot of CPU usage now... in realtime, so that we can identify the query that is causing the high CPU usage on the system in a snapshot of time.
Kind regards,
Jeremy Byrski
www.CentralR.com
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message news:Oa0LGBBJFHA.4060@.TK2MSFTNGP14.phx.gbl...
> you can use sp_who2 to see what activity is going on, or use enterprise
> manager, under "Management" > "Current Activity"
> --
> Simon Worth
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> 80%
> it
>
|||Hi Aidan.
I cant spot which performance object which sas the sql threads listed...
could you point me in the right direction...?
Thanks a million,
Kind regards,
Jeremy Byrski
www.CentralR.com
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
> up a lot of CPU time. Find the highest
> Thread<sqlservr#<instance>)\%Processor Time counter in Perfmon and match
> it to Thread(sqlservr#<instance>)\ID Thread, which corresponds to the kpid
> column in sysprocesses. Use that information to run dbcc
> inputbuffer(<spid>). More information is available at
> http://support.microsoft.com/default...;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
>
|||Wow Adrian - that' s pretty cool. Got any more tips like that one?
Steve.
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
up[vbcol=seagreen]
> a lot of CPU time. Find the highest Thread<sqlservr#<instance>)\%Processor
> Time counter in Perfmon and match it to Thread(sqlservr#<instance>)\ID
> Thread, which corresponds to the kpid column in sysprocesses. Use that
> information to run dbcc inputbuffer(<spid>). More information is available
> at http://support.microsoft.com/default...;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
hammering
>