SQL Server - Lock timeouts performance problem

8 Dec 2005 4:36 PM

Steve

Hi,
We occasionaly have performance problems on our SQL 2000 SP4 db, during
these times we see CPU use shoot up, along with lock timeouts (upto 1000's)
in perfmon.
We suspect that the lock timeouts are the main problem, which then causes
CPU to go up.
We have run profiler during these periods to find the cause of the lock
timeouts and found two things;
1/ We only see the lock timeouts when the profiler trace has the 'exclude
system ids' filter option unselected.
2/ When we include the system id's we see loads of lock timeouts across a
range of stored procs, but some of these are read only (using indexed table
variables and functions in the query) which already use the NOLOCK option
where applicable.

Does anyone know what the cause could be here, or are the lock timeouts a
red herring?

TIA, Sorry if i posted to the wrong group.

8 Dec 2005 6:22 PM

Andrew J. Kelly

Lock timeouts can be very misleading since it includes internal system level
lock timeouts that really aren't an issue. Locks by default don't timeout
so I suspect you are barking up the wrong tree. My guess is you have poorly
optimized queries or processes running at those times but with such little
info it is impossible to say for sure. I think tracing is the right
approach to see what is going on during that time.

--
Andrew J. Kelly SQL MVP

Show quote

"Steve" <St***@discussions.microsoft.com> wrote in message
news:4120FD65-E33A-43C0-83D3-D61E75FB696E@microsoft.com...
> Hi,
> We occasionaly have performance problems on our SQL 2000 SP4 db, during
> these times we see CPU use shoot up, along with lock timeouts (upto
> 1000's)
> in perfmon.
> We suspect that the lock timeouts are the main problem, which then causes
> CPU to go up.
> We have run profiler during these periods to find the cause of the lock
> timeouts and found two things;
> 1/ We only see the lock timeouts when the profiler trace has the 'exclude
> system ids' filter option unselected.
> 2/ When we include the system id's we see loads of lock timeouts across a
> range of stored procs, but some of these are read only (using indexed
> table
> variables and functions in the query) which already use the NOLOCK option
> where applicable.
>
> Does anyone know what the cause could be here, or are the lock timeouts a
> red herring?
>
> TIA, Sorry if i posted to the wrong group.
>