100,000 lock requests/sec

[Phil]

I'm monitoring one of our servers, and on the whole it is performing
well. However, I'm puzzled by the number of LockRequests/sec that
Perfmon is recording. We frequently see values exceeding 50,000 and
the current peak is 533,616 (the average, as I type this, is 35,102).
There are only 40 users on the system.

sp_lock shows nothing like this number of locks; it shows something
of the order of 30 locks maximum for each execution, which seems far
more reasonable.

SQL Profiler seems to back up the Perfmon values; it records hundreds
of "Lock Acquired" events per second during these peak periods.

For example:

NO. OF
LOCKS OBJECT STARTTIME
===== ====== ======================
678 User_T 2005-04-15 09:03:22.863
931 User_T 2005-04-15 09:03:22.877
924 User_T 2005-04-15 09:03:22.893
16 EnquiryUser_T 2005-04-15 09:03:22.893
961 User_T 2005-04-15 09:03:22.910
820 User_T 2005-04-15 09:03:22.923
4 NULL 2005-04-15 09:03:22.923
828 User_T 2005-04-15 09:03:22.940
4 NULL 2005-04-15 09:03:22.940
734 User_T 2005-04-15 09:03:22.957

Can anyone think why such a comparatively small system should generate
these numbers of locks? Why does sp_lock NOT show the same level of
locking?

[Mike Epprecht \(SQL MVP\)]

sp_lock is a point in time capture of the locks so it may not match the per
second count.

If you have a high number of lock requests, with low request rate (i.e.
small system), it usually is another indicator that SQL Server has to do
table scans when retrieving data.

Look at http://www.sql-server-performance.com on how to find the queries
that are causing it and how to remedy it.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Phil" <ph*********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I'm monitoring one of our servers, and on the whole it is performing
well. However, I'm puzzled by the number of LockRequests/sec that
Perfmon is recording. We frequently see values exceeding 50,000 and
the current peak is 533,616 (the average, as I type this, is 35,102).
There are only 40 users on the system.

sp_lock shows nothing like this number of locks; it shows something
of the order of 30 locks maximum for each execution, which seems far
more reasonable.

SQL Profiler seems to back up the Perfmon values; it records hundreds
of "Lock Acquired" events per second during these peak periods.

For example:

NO. OF
LOCKS OBJECT STARTTIME
===== ====== ======================
678 User_T 2005-04-15 09:03:22.863
931 User_T 2005-04-15 09:03:22.877
924 User_T 2005-04-15 09:03:22.893
16 EnquiryUser_T 2005-04-15 09:03:22.893
961 User_T 2005-04-15 09:03:22.910
820 User_T 2005-04-15 09:03:22.923
4 NULL 2005-04-15 09:03:22.923
828 User_T 2005-04-15 09:03:22.940
4 NULL 2005-04-15 09:03:22.940
734 User_T 2005-04-15 09:03:22.957

Can anyone think why such a comparatively small system should generate
these numbers of locks? Why does sp_lock NOT show the same level of
locking?

[John Bell]

Hi

You may want to look monitor lock escalation and deadlocks in Profiler as
well, this should indicate where they are being taken out.

John

"Phil" <ph*********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I'm monitoring one of our servers, and on the whole it is performing
well. However, I'm puzzled by the number of LockRequests/sec that
Perfmon is recording. We frequently see values exceeding 50,000 and
the current peak is 533,616 (the average, as I type this, is 35,102).
There are only 40 users on the system.

sp_lock shows nothing like this number of locks; it shows something
of the order of 30 locks maximum for each execution, which seems far
more reasonable.

SQL Profiler seems to back up the Perfmon values; it records hundreds
of "Lock Acquired" events per second during these peak periods.

For example:

NO. OF
LOCKS OBJECT STARTTIME
===== ====== ======================
678 User_T 2005-04-15 09:03:22.863
931 User_T 2005-04-15 09:03:22.877
924 User_T 2005-04-15 09:03:22.893
16 EnquiryUser_T 2005-04-15 09:03:22.893
961 User_T 2005-04-15 09:03:22.910
820 User_T 2005-04-15 09:03:22.923
4 NULL 2005-04-15 09:03:22.923
828 User_T 2005-04-15 09:03:22.940
4 NULL 2005-04-15 09:03:22.940
734 User_T 2005-04-15 09:03:22.957

Can anyone think why such a comparatively small system should generate
these numbers of locks? Why does sp_lock NOT show the same level of
locking?

[Phil]

As mentioned in the original post, I've already monitored this in
Profiler (this is where the figures displayed in that post came from),
and it returns the same level of locking as indicated by Perfmon.

Whilst I agree that sp_lock is 'point-in-time' rather than a whole
second, the sp_lock counts are so much lower than Perfmon that I can't
see them reaching these maximum figures if they could be accumulated
over a whole second. I just don't believe that a 4Gb database on a
system with 45 users can regularly generate >500,000 lock requests per
second, especially as the value for LockWaits/sec are very low : <
0.004 and the TableLockEscalations/Sec are virtually zero . If we
were tablescanning massively, I would expect both these measurements to
be much higher than they are.

I'm still puzzled.

[John Bell]

Hi

If you have table cans you could see them in profiler, but I would expect
you to see Lock Escallation as well if that was happening.

What you don't say is what type of locks these are, if they are related to
user processes or system processes. If this is a user process then what is
the user doing at that time?

John

"Phil" <ph*********@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

As mentioned in the original post, I've already monitored this in
Profiler (this is where the figures displayed in that post came from),
and it returns the same level of locking as indicated by Perfmon.

Whilst I agree that sp_lock is 'point-in-time' rather than a whole
second, the sp_lock counts are so much lower than Perfmon that I can't
see them reaching these maximum figures if they could be accumulated
over a whole second. I just don't believe that a 4Gb database on a
system with 45 users can regularly generate >500,000 lock requests per
second, especially as the value for LockWaits/sec are very low : <
0.004 and the TableLockEscalations/Sec are virtually zero . If we
were tablescanning massively, I would expect both these measurements to
be much higher than they are.

I'm still puzzled.