Performance problems

Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.When this happens, did you try running the query manually from Query
Analyzer and see if the execution plan is any different, compared to the one
when everything was okay? It could be that the statistics changed or not
up-to-date. Do you recall any data changes that correspond with the times of
bad performance?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.|||"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> This leads us to believe that we have a query, an application or some
> other unknown that prevents sql server from escalating a row level lock to
> table level.
I had this ince, by a simple DTS table export, got _millions_ of locks.
Defragmented the database in question, and it helped, operation done exactly
the same way suddenly went without any problems.
Seems like some hard-to-reproduce SQL Server problem. I monitor the lock
count closely since then, and defragmenting is my first resort in such a
case, but it never happened again since then.
Hope it helps.
Regards
Wojtek|||Hi
Locks are managed by memory. Have you checked how much memory SQL Server
consumes?
Also make sure that you don't have along running transactions. Have you
checked that your tables have propely defined indexes and moreover an
optimizer is able to use them when it creates an execution plan.
Try to identify by DBCC INPUTBUFFER (spid) what query is running by this
spid.
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi.
> We have a system with approximately 1000 users running on a dual Pentium
> with hyperthreading and 2Gb memory. The server is ultra fast and is
> performing great - most of the time. The problem is that sometimes a query
> that usually takes 2 seconds to run, suddenly will take 40 seconds or
more.
> It's like the system clutters up and everything becomes very slow.
> We have been running a profiler on the system, and found several stored
> procedures that needed optimizing and have done so - but we are still
> experiencing the same problem. We have looked at sp_lock and found that
most
> of the time there are about 200 locks but suddenly the number of locks
will
> increase to as much as 200.000 mostly on one table and for one spid.
> This leads us to believe that we have a query, an application or some
other
> unknown that prevents sql server from escalating a row level lock to table
> level.
> How do we find out if this is the case - is profiler the only way to go or
> are we missing something here? Any ideas or similar experiences?
> Your help will be greatly appreciated.
>