Lock requests/sec very high....

3 Apr 2006 3:40 AM

Tim Greenwood

Monitoring our system during one of its busiest times (only several
"hundred" users) we noticed the lock requests/sec on our SQL server was
hitting as high as 60,000!!! Is this normal? Or is something in the
developers code gone really wrong? Even the average sustained is up in the
5-10000 range. The CPU is hitting the ceiling and we are getting timeouts.
I just don't believe that 500 users is too many for a system like this.

4 web servers hitting 2 asmx WebServices servers against a dual-Xeon SQL box
which is quite loaded. We are adding another SQL box just for fail-over but
this should be able to process this load I think.

3 Apr 2006 4:18 AM

David Browne

Show quote Hide quote

"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23JckWBtVGHA.4900@TK2MSFTNGP12.phx.gbl...
> Monitoring our system during one of its busiest times (only several
> "hundred" users) we noticed the lock requests/sec on our SQL server was
> hitting as high as 60,000!!! Is this normal? Or is something in the
> developers code gone really wrong? Even the average sustained is up in
> the 5-10000 range. The CPU is hitting the ceiling and we are getting
> timeouts. I just don't believe that 500 users is too many for a system
> like this.
>
> 4 web servers hitting 2 asmx WebServices servers against a dual-Xeon SQL
> box which is quite loaded. We are adding another SQL box just for
> fail-over but this should be able to process this load I think.
>

"Lock requests/sec" is a general measure of SQL Server load. Almost every
request requires locks, so if the system is busy, you will have a lot of
locks.

You should probably analyze the database server workload to see what's
happening, and what queries are causing the most Logical IO and using the
most CPU. In SQL 2005 you can just look at the server summary reports. In
SQL 2000 you need to run profiler to capture the workload and then analyze
it.

David

3 Apr 2006 8:08 AM

Enric

Hi Tim,
One important point is to know if non-technical user have access to lauch
queries or something like that.

--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)

Show quoteHide quote

"David Browne" wrote:

>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23JckWBtVGHA.4900@TK2MSFTNGP12.phx.gbl...
> > Monitoring our system during one of its busiest times (only several
> > "hundred" users) we noticed the lock requests/sec on our SQL server was
> > hitting as high as 60,000!!! Is this normal? Or is something in the
> > developers code gone really wrong? Even the average sustained is up in
> > the 5-10000 range. The CPU is hitting the ceiling and we are getting
> > timeouts. I just don't believe that 500 users is too many for a system
> > like this.
> >
> > 4 web servers hitting 2 asmx WebServices servers against a dual-Xeon SQL
> > box which is quite loaded. We are adding another SQL box just for
> > fail-over but this should be able to process this load I think.
> >
>
> "Lock requests/sec" is a general measure of SQL Server load. Almost every
> request requires locks, so if the system is busy, you will have a lot of
> locks.
>
> You should probably analyze the database server workload to see what's
> happening, and what queries are causing the most Logical IO and using the
> most CPU. In SQL 2005 you can just look at the server summary reports. In
> SQL 2000 you need to run profiler to capture the workload and then analyze
> it.
>
> David
>
>
>

3 Apr 2006 2:53 PM

Tim Greenwood

It would be very hard to provide any definitive code. There isn't anything
pointing at one specific stored proc or anything...I'd have to dump all our
procedures out to you! This is the busiest time of the week for us. There
is alot of orders being placed so lots of inserts/updates are happening. I
guess I just figured our server could stand up to more users.

I did also notice that there were over 1500 processes running in SQL server,
most running the same thing. That sounds like a bit much since our
connection pools collectively max at 250.

Show quoteHide quote

"Enric" <vta***@terra.es.(donotspam)> wrote in message
news:D635498D-23EB-4D93-9A51-FB62D16088FE@microsoft.com...
> Hi Tim,
> One important point is to know if non-technical user have access to lauch
> queries or something like that.
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)
>
>
> "David Browne" wrote:
>
>>
>> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
>> news:%23JckWBtVGHA.4900@TK2MSFTNGP12.phx.gbl...
>> > Monitoring our system during one of its busiest times (only several
>> > "hundred" users) we noticed the lock requests/sec on our SQL server was
>> > hitting as high as 60,000!!! Is this normal? Or is something in the
>> > developers code gone really wrong? Even the average sustained is up in
>> > the 5-10000 range. The CPU is hitting the ceiling and we are getting
>> > timeouts. I just don't believe that 500 users is too many for a system
>> > like this.
>> >
>> > 4 web servers hitting 2 asmx WebServices servers against a dual-Xeon
>> > SQL
>> > box which is quite loaded. We are adding another SQL box just for
>> > fail-over but this should be able to process this load I think.
>> >
>>
>> "Lock requests/sec" is a general measure of SQL Server load. Almost
>> every
>> request requires locks, so if the system is busy, you will have a lot of
>> locks.
>>
>> You should probably analyze the database server workload to see what's
>> happening, and what queries are causing the most Logical IO and using the
>> most CPU. In SQL 2005 you can just look at the server summary reports.
>> In
>> SQL 2000 you need to run profiler to capture the workload and then
>> analyze
>> it.
>>
>> David
>>
>>
>>

3 Apr 2006 2:55 PM

Tim Greenwood

I don't have the link handy but yesterday I was told by an MVP that select
statements do not generate locks unless they are in a transaction. Is this
not so? Looking at the results of a profiler run I'd have to believe that
is false. I see reference to certain procedures afterwhich there are MANY
locks aquired/released. Seems maybe some of these select statements should
have NOLOCK added to them. Would this even make a difference?

Show quoteHide quote

"David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in
message news:OonWDXtVGHA.5332@tk2msftngp13.phx.gbl...
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23JckWBtVGHA.4900@TK2MSFTNGP12.phx.gbl...
>> Monitoring our system during one of its busiest times (only several
>> "hundred" users) we noticed the lock requests/sec on our SQL server was
>> hitting as high as 60,000!!! Is this normal? Or is something in the
>> developers code gone really wrong? Even the average sustained is up in
>> the 5-10000 range. The CPU is hitting the ceiling and we are getting
>> timeouts. I just don't believe that 500 users is too many for a system
>> like this.
>>
>> 4 web servers hitting 2 asmx WebServices servers against a dual-Xeon SQL
>> box which is quite loaded. We are adding another SQL box just for
>> fail-over but this should be able to process this load I think.
>>
>
> "Lock requests/sec" is a general measure of SQL Server load. Almost every
> request requires locks, so if the system is busy, you will have a lot of
> locks.
>
> You should probably analyze the database server workload to see what's
> happening, and what queries are causing the most Logical IO and using the
> most CPU. In SQL 2005 you can just look at the server summary reports.
> In SQL 2000 you need to run profiler to capture the workload and then
> analyze it.
>
> David
>

3 Apr 2006 5:16 PM

David Browne

"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ul%23ve6yVGHA.4976@TK2MSFTNGP11.phx.gbl...
>I don't have the link handy but yesterday I was told by an MVP that select
>statements do not generate locks unless they are in a transaction. Is this
>not so?

Under the default isolation level READ COMMITED, all SELECT's generate
shared locks. Those locks are not held across multiple statements without a
transaction, but they are acquired and released just the same.

> Looking at the results of a profiler run I'd have to believe that is
> false. I see reference to certain procedures afterwhich there are MANY
> locks aquired/released. Seems maybe some of these select statements
> should have NOLOCK added to them. Would this even make a difference?
> , , ,

Could, but there is no reason to think that you have a problem with locks.
The number of lock requests is probably a just a sign of a large workload.

David