MS SQL Server - Lock waits/min threshold?

Author

Lock waits/min threshold?

2006-10-24, 6:29 pm

I recently deployed a new SQL2005 cluster (Win2k3 Ent. x64, SQL Std. Ed. x64,
SP1 + build 2153, 12GB RAM, 10GB devoted to SQL via max server memory
setting) and my monitoring tool is firing alerts on the lock waits/min
metric. It seems my lock waits/min are exceeding 200 (high as 400) for the
instance. The server is extremely busy as it supports a large website. I
get the idea behind lock waits/min, but what exactly is this metric
indicative of? Does it mean I have saturated the sql server engine where it
just can't allocate/release locks fast enough? How can I solve this issue?
Hardware (CPU and disk) counters aren't indicating any pressure (CPU <25% and
disk queueing is 0). Any input is appreciated. Thanks.

LC

Andrew J. Kelly

2006-10-24, 6:29 pm

That is simply the number of times in the last minute that a thread had to
wait to take out a lock. 200 a minute (by itself) is hardly anything to be
concerned about if this is indeed a busy system. But the question is how
long are these waiting until they get the lock? If it is milliseconds for
each that is OK, if it is seconds that is not. What does DBCC
SQLPERF(WAITSTATS) say in terms of lock waits? What about the lock wait
times? In either case you can ensure you have proper indexes on the tables
being locked and that you keep transactions as short as possible. Have a
look at these.

http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking

--
Andrew J. Kelly SQL MVP

"LC" <LC@discussions.microsoft.com> wrote in message
news:FAE997E8-B50C-43E0-A101- B3400680A8D5@microso
ft.com...
>I recently deployed a new SQL2005 cluster (Win2k3 Ent. x64, SQL Std. Ed.
>x64,
> SP1 + build 2153, 12GB RAM, 10GB devoted to SQL via max server memory
> setting) and my monitoring tool is firing alerts on the lock waits/min
> metric. It seems my lock waits/min are exceeding 200 (high as 400) for
> the
> instance. The server is extremely busy as it supports a large website. I
> get the idea behind lock waits/min, but what exactly is this metric
> indicative of? Does it mean I have saturated the sql server engine where
> it
> just can't allocate/release locks fast enough? How can I solve this
> issue?
> hardware (CPU and disk) counters aren't indicating any pressure (CPU <25%
> and
> disk queueing is 0). Any input is appreciated. Thanks.
>
> LC

JXStern

2006-10-24, 6:29 pm

On Mon, 18 Sep 2006 10:35:01 -0700, LC <LC@discussions.microsoft.com>
wrote:
>I recently deployed a new SQL2005 cluster (Win2k3 Ent. x64, SQL Std. Ed. x64,
>SP1 + build 2153, 12GB RAM, 10GB devoted to SQL via max server memory
>setting) and my monitoring tool is firing alerts on the lock waits/min
>metric. It seems my lock waits/min are exceeding 200 (high as 400) for the
>instance. The server is extremely busy as it supports a large website. I
>get the idea behind lock waits/min, but what exactly is this metric
>indicative of? Does it mean I have saturated the sql server engine where it
>just can't allocate/release locks fast enough? How can I solve this issue?
>Hardware (CPU and disk) counters aren't indicating any pressure (CPU <25% and
>disk queueing is 0). Any input is appreciated. Thanks.

If there are no hardware bottlenecks, then your app seems to have some
kind of "hot spots", maybe a master record that everyone needs to
update, and that is getting updated inside of a larger transaction.

It does mean you are losing some performance, but it may require some
kind of re-architecting to fix.

Yes, 200/min seems high enough to merit some investigation, though
that is just 3/sec, and a busy site may have 200+ tps blasting
through, so it's not awful or anything.

Just my guesses!

Of course you'd like to localize the waits to a specific piece of code
and database object, only way I know to do that is write some probes
of various kinds to look for waits, get the spids and users and
inputbuffers, etc.

Maybe simpler is run sp_who2 a bunch of times to look for blocked
processes and track it down that way.

J.

Sponsored Links