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.comEXEC 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.aspx?scid=kb;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
>
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0029_01C524D6.7E3E2390
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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...
>> 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
>>
>>
> >
--=_NextPart_000_0029_01C524D6.7E3E2390
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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 Byrskiwww.CentralR.com
"Simon Worth" 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" 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>>>>>>= >

---

--=_NextPart_000_0029_01C524D6.7E3E2390--|||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.aspx?scid=kb;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
>>
>>
>|||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
> 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.aspx?scid=kb;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
> >
> >
> >
> >
> >
>