Any way to kill zombie locks by commandline?

hi
we have some troubles with a object that kills our SQL2000SP4. CPU spikes to
100% and stay there. We are searching for a solution, but currently don't
have one! So it comes to situation where a object named
"company.dbo.fn_example" have more then one ProcessID. If this occour -
"all" other processes are zombies and i only know how to kill them by hand.
this makes me *stressed*, while i must kill 10 every 20 minutes.
is there any way to kill the object for e.g. with all processes inside - by
command line?
Regards
MarcSounds like a very poorly written function that is monopolizing the CPU's.
Try setting MAXDOP to 1 and see if it stays contained to just one CPU. But
I would look at other ways to optimize the statements that use this
function.
--
Andrew J. Kelly SQL MVP
"Marc Bauer" <marc.bau@.gmx.net> wrote in message
news:%23IxhtuvLGHA.2300@.TK2MSFTNGP15.phx.gbl...
> hi
> we have some troubles with a object that kills our SQL2000SP4. CPU spikes
> to 100% and stay there. We are searching for a solution, but currently
> don't have one! So it comes to situation where a object named
> "company.dbo.fn_example" have more then one ProcessID. If this occour -
> "all" other processes are zombies and i only know how to kill them by
> hand. this makes me *stressed*, while i must kill 10 every 20 minutes.
> is there any way to kill the object for e.g. with all processes inside -
> by command line?
>
> Regards
> Marc
>|||hi
> Sounds like a very poorly written function that is monopolizing the CPU's.
> Try setting MAXDOP to 1 and see if it stays contained to just one CPU.
> But I would look at other ways to optimize the statements that use this
> function.
our database specialist, reviewed the function and said - that this one is
correct and should work very fast. but sometimes - if this function gets
executed very very often it comes to something like a deadlock situation.
then all tables are locked... i have had a look to the function today, and
there is everywhere in the selects a with(nolock) used. so it cannot be a
read deadlock.
so - today - we don't know what's wrong. maybe a bug in SQL and we need to
find a workaround... this should be partly a con job with a kill - until we
found a solution.
Regards
Marc|||Can you post the function?
--
Andrew J. Kelly SQL MVP
"Marc Bauer" <marc.bau@.gmx.net> wrote in message
news:unyGffyLGHA.420@.tk2msftngp13.phx.gbl...
> hi
>> Sounds like a very poorly written function that is monopolizing the
>> CPU's. Try setting MAXDOP to 1 and see if it stays contained to just one
>> CPU. But I would look at other ways to optimize the statements that use
>> this function.
> our database specialist, reviewed the function and said - that this one is
> correct and should work very fast. but sometimes - if this function gets
> executed very very often it comes to something like a deadlock situation.
> then all tables are locked... i have had a look to the function today, and
> there is everywhere in the selects a with(nolock) used. so it cannot be a
> read deadlock.
> so - today - we don't know what's wrong. maybe a bug in SQL and we need to
> find a workaround... this should be partly a con job with a kill - until
> we found a solution.
>
> Regards
> Marc
>|||On Sat, 11 Feb 2006 17:26:09 +0100, "Marc Bauer" <marc.bau@.gmx.net>
wrote:
> but sometimes - if this function gets
>executed very very often it comes to something like a deadlock situation.
>then all tables are locked...
What do you mean, "very very often"?
Does the calling code happen to use #temp tables?
If you post source code, please also post what you can of the
invocation.
J.|||we found a endless loop in the function... :-(
Marc

any link to capacity management for SQL Servers ?

This is not just tied to disk space, but also CPU load, IO, n/w
throughput,etc.. Thank you.Hi Hassan
"Hassan" wrote:
> This is not just tied to disk space, but also CPU load, IO, n/w
> throughput,etc.. Thank you.
>
Something like MOM http://www.microsoft.com/mom/default.mspx
and
http://searchwincomputing.techtarget.com/productsOfTheYearWinner/0,296407,sid68_gci1157325_tax302584_ayr2005,00.html?
Others would be IBM Tivoli, HP Openview, CA Unicenter etc...
John|||Thank you John,
But I was looking for some white paper doc that would tell me what to look
for when one is doing capacity management planning
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:71637728-BAC0-4D39-A819-9FB338983617@.microsoft.com...
> Hi Hassan
> "Hassan" wrote:
>> This is not just tied to disk space, but also CPU load, IO, n/w
>> throughput,etc.. Thank you.
> Something like MOM http://www.microsoft.com/mom/default.mspx
> and
> http://searchwincomputing.techtarget.com/productsOfTheYearWinner/0,296407,sid68_gci1157325_tax302584_ayr2005,00.html?
> Others would be IBM Tivoli, HP Openview, CA Unicenter etc...
> John

any link to capacity management for SQL Servers ?

This is not just tied to disk space, but also CPU load, IO, n/w
throughput,etc.. Thank you.
Hi Hassan
"Hassan" wrote:

> This is not just tied to disk space, but also CPU load, IO, n/w
> throughput,etc.. Thank you.
>
Something like MOM http://www.microsoft.com/mom/default.mspx
and
[url]http://searchwincomputing.techtarget.com/productsOfTheYearWinner/0,296407,sid68_gci1157325_tax302584_ayr2005,00.htm l?[/url]
Others would be IBM Tivoli, HP Openview, CA Unicenter etc...
John
|||Thank you John,
But I was looking for some white paper doc that would tell me what to look
for when one is doing capacity management planning
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:71637728-BAC0-4D39-A819-9FB338983617@.microsoft.com...
> Hi Hassan
> "Hassan" wrote:
> Something like MOM http://www.microsoft.com/mom/default.mspx
> and
> [url]http://searchwincomputing.techtarget.com/productsOfTheYearWinner/0,296407,sid68_gci1157325_tax302584_ayr2005,00.htm l?[/url]
> Others would be IBM Tivoli, HP Openview, CA Unicenter etc...
> John

any link to capacity management for SQL Servers ?

This is not just tied to disk space, but also CPU load, IO, n/w
throughput,etc.. Thank you.Hi Hassan
"Hassan" wrote:

> This is not just tied to disk space, but also CPU load, IO, n/w
> throughput,etc.. Thank you.
>
Something like MOM http://www.microsoft.com/mom/default.mspx
and
http://searchwincomputing.techtarge...yr2005,00.html?
Others would be IBM Tivoli, HP Openview, CA Unicenter etc...
John|||Thank you John,
But I was looking for some white paper doc that would tell me what to look
for when one is doing capacity management planning
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:71637728-BAC0-4D39-A819-9FB338983617@.microsoft.com...
> Hi Hassan
> "Hassan" wrote:
>
> Something like MOM http://www.microsoft.com/mom/default.mspx
> and
> http://searchwincomputing.techtarge...yr2005,00.html?
> Others would be IBM Tivoli, HP Openview, CA Unicenter etc...
> John

Any good link on tracking memory issues

I have always found it hard to figure out if SQL has memory issues using
tools like perfmon,etc..
Its easy for CPU and IO i.e. high CPU or disk queue, but could never figure
out how to go about memory.
Yes some may say look at DBCC memorystatus,etc.. but that things greek ;)
Can someone help or maybe theres some documentation that talks about
tracking memory usage,etc. for SQL Server..Hi Hassan,
Two excellent sources I know are:
Paper "Troubleshooting Performance Problems in SQL Server 2005"
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Chapter 1, book "Inside SQL Server 2005: Query Tuning and Optimization"
by Kalen Delaney, et al.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> I have always found it hard to figure out if SQL has memory issues using
> tools like perfmon,etc..
> Its easy for CPU and IO i.e. high CPU or disk queue, but could never figure
> out how to go about memory.
> Yes some may say look at DBCC memorystatus,etc.. but that things greek ;)
> Can someone help or maybe theres some documentation that talks about
> tracking memory usage,etc. for SQL Server..
>
>