Lock Monitoring

Hi,
We're trying to investigate a performance issue with our application
which uses SQL Server 2000 as the backend. The performance has become
an issue in a live customer environment and we are trying to determine
what is acquiring and holding locks for a "long" time.
We've been monitoring with SQL Profiler, looking for command completion
that takes more than 2 seconds. This has indicated some scripts, but,
some of the scripts that are reaching the 30 second execution limit are
very simple and execute in (very) sub second time under normal
circumstances.
So, something must be holding a lock that they are waiting for. Nothing
we're seeing in our trace is making this obvious.
What I'm looking to do is find a way to identify stored procedures that
in their execution time are holding locks for "long" periods of time.
Can anyone suggest a way to do this? Perfmon gives me average lock time
in ms and things like that, but not what's causing it and SQL Profiler
doesn't seem to be able to tell me what has the lock and how long it's
held the lock for (and what type of lock it is).
Any help gratefully accepted!
Cheers,
Michael
If you are experiencing deadlocks - Read up on Trace flags 1204, 1205
Use sp_who and sp_lock as well.
"Michael Jervis" <mjervis@.gmail.com> wrote in message
news:1159452500.950233.257270@.k70g2000cwa.googlegr oups.com...
> Hi,
> We're trying to investigate a performance issue with our application
> which uses SQL Server 2000 as the backend. The performance has become
> an issue in a live customer environment and we are trying to determine
> what is acquiring and holding locks for a "long" time.
> We've been monitoring with SQL Profiler, looking for command completion
> that takes more than 2 seconds. This has indicated some scripts, but,
> some of the scripts that are reaching the 30 second execution limit are
> very simple and execute in (very) sub second time under normal
> circumstances.
> So, something must be holding a lock that they are waiting for. Nothing
> we're seeing in our trace is making this obvious.
> What I'm looking to do is find a way to identify stored procedures that
> in their execution time are holding locks for "long" periods of time.
> Can anyone suggest a way to do this? Perfmon gives me average lock time
> in ms and things like that, but not what's causing it and SQL Profiler
> doesn't seem to be able to tell me what has the lock and how long it's
> held the lock for (and what type of lock it is).
> Any help gratefully accepted!
> Cheers,
> Michael
>
|||http://support.microsoft.com/kb/271509/EN-US/ has a lot of good information
on how to monitor blocking and links to other KB articles on locks and
blocking.
Tom
"Michael Jervis" <mjervis@.gmail.com> wrote in message
news:1159452500.950233.257270@.k70g2000cwa.googlegr oups.com...
> Hi,
> We're trying to investigate a performance issue with our application
> which uses SQL Server 2000 as the backend. The performance has become
> an issue in a live customer environment and we are trying to determine
> what is acquiring and holding locks for a "long" time.
> We've been monitoring with SQL Profiler, looking for command completion
> that takes more than 2 seconds. This has indicated some scripts, but,
> some of the scripts that are reaching the 30 second execution limit are
> very simple and execute in (very) sub second time under normal
> circumstances.
> So, something must be holding a lock that they are waiting for. Nothing
> we're seeing in our trace is making this obvious.
> What I'm looking to do is find a way to identify stored procedures that
> in their execution time are holding locks for "long" periods of time.
> Can anyone suggest a way to do this? Perfmon gives me average lock time
> in ms and things like that, but not what's causing it and SQL Profiler
> doesn't seem to be able to tell me what has the lock and how long it's
> held the lock for (and what type of lock it is).
> Any help gratefully accepted!
> Cheers,
> Michael
>