Lock requests/sec very high....

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."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|||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...
> "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. I
n
> SQL 2000 you need to run profiler to capture the workload and then analyze
> it.
> David
>
>|||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 w 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.
"Enric" <vtam13@.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:
>|||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?
"David Browne" <davidbaxterbrowne no potted meat@.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...
> "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
>|||"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

Lock Requests/Sec question

In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
Requests/Sec is around 2000 and the times for the execution of the INSERTS
and SELECTS are around 16MS which is perfomance for the client requests.
Suddenly, the Locks Request will drop to around 125 at the same time as the
INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
Is one causing the other?
Thanks,
Don
i've included the past thread below in case there's info that would help.
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are going
to 1 table while the majority are going to another table in the same DB. This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
Tom Moreau 2/13/2006 4:49 PM PST
Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
JXStern 2/13/2006 7:06 PM PST
On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT and
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.It could be lots of things but the two most common are blocking (not the
same as deadlocks) and checkpoints. You can see blocking by running sp_who2
and look for rows with a number (spid) in the Blkd column. But checkpoints
are a prime suspect as well. You can see when they occur by monitoring the
perfmon counters for CheckPoint Pages / Sec under the SQL Server counters.
When this happens the disks will usually max out for the duration of the
checkpoint and will stall any other requests to or from the disk. You stated
you have the data and log files separated onto separate disks but are they
just logical or really physical disks. Having the files on two logical
drives (partitions or LUNs) that are on the same physical drive array (or
worse single drive) does nothing for performance. It actually gives a false
sense of security. You can also have a look at these links to see how to
narrow down the culprit.
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:02F1ED1B-AC28-4DA5-8CA1-E3735C6254B3@.microsoft.com...
> In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
> Requests/Sec is around 2000 and the times for the execution of the INSERTS
> and SELECTS are around 16MS which is perfomance for the client requests.
> Suddenly, the Locks Request will drop to around 125 at the same time as
> the
> INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
> Is one causing the other?
> Thanks,
> Don
> i've included the past thread below in case there's info that would help.
>
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> Tom Moreau 2/13/2006 4:49 PM PST
> Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
>
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> JXStern 2/13/2006 7:06 PM PST
> On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
> <donsql22222@.discussions.microsoft.com> wrote:
>>It's almost looks like theres some sort of contention between the INSERT
>>and
>>the SELECT, but since it's in separate tables...i have no idea what to
>>look
>>for.
> Of course there's contention, for LRU pages in memory, the moreso
> because they are different tables!
> Run the queries during the slow period from QA with SET STATISTICS IO
> ON, and look at the physical reads numbers going from 0 to whatever.
> J.sql

Lock Requests/Sec question

In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
Requests/Sec is around 2000 and the times for the execution of the INSERTS
and SELECTS are around 16MS which is perfomance for the client requests.
Suddenly, the Locks Request will drop to around 125 at the same time as the
INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
Is one causing the other?
Thanks,
Don
i've included the past thread below in case there's info that would help.
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist a
t
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are goin
g
to 1 table while the majority are going to another table in the same DB. Thi
s
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
Tom Moreau 2/13/2006 4:49 PM PST
Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
JXStern 2/13/2006 7:06 PM PST
On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT an
d
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.It could be lots of things but the two most common are blocking (not the
same as deadlocks) and checkpoints. You can see blocking by running sp_who2
and look for rows with a number (spid) in the Blkd column. But checkpoints
are a prime suspect as well. You can see when they occur by monitoring the
perfmon counters for CheckPoint Pages / Sec under the SQL Server counters.
When this happens the disks will usually max out for the duration of the
checkpoint and will stall any other requests to or from the disk. You stated
you have the data and log files separated onto separate disks but are they
just logical or really physical disks. Having the files on two logical
drives (partitions or LUNs) that are on the same physical drive array (or
worse single drive) does nothing for performance. It actually gives a false
sense of security. You can also have a look at these links to see how to
narrow down the culprit.
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:02F1ED1B-AC28-4DA5-8CA1-E3735C6254B3@.microsoft.com...
> In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
> Requests/Sec is around 2000 and the times for the execution of the INSERTS
> and SELECTS are around 16MS which is perfomance for the client requests.
> Suddenly, the Locks Request will drop to around 125 at the same time as
> the
> INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
> Is one causing the other?
> Thanks,
> Don
> i've included the past thread below in case there's info that would help.
>
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> Tom Moreau 2/13/2006 4:49 PM PST
> Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
>
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> JXStern 2/13/2006 7:06 PM PST
> On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
> <donsql22222@.discussions.microsoft.com> wrote:
> Of course there's contention, for LRU pages in memory, the moreso
> because they are different tables!
> Run the queries during the slow period from QA with SET STATISTICS IO
> ON, and look at the physical reads numbers going from 0 to whatever.
> J.

Lock Requests/Sec question

In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
Requests/Sec is around 2000 and the times for the execution of the INSERTS
and SELECTS are around 16MS which is perfomance for the client requests.
Suddenly, the Locks Request will drop to around 125 at the same time as the
INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
Is one causing the other?
Thanks,
Don
i've included the past thread below in case there's info that would help.
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are going
to 1 table while the majority are going to another table in the same DB. This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
Tom Moreau 2/13/2006 4:49 PM PST
Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
...
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
Looking for suggestions about what I might look for..
During periods of time when no INSERTS are taking place, SELECT statement
durations are around 16-30ms for hundreds of requests per minute.
When there is a batch of INSERTS that are sent to the table via SP that
number in the many thousands..for a while both INSERTS and SELECTS coexist
at
around the same duration of 16-30 ms..watching this with Profiler.
Then, for some reason..some SELECT and few INSERTS will go up to the 4000 -
5000 ms range. Then things will quiet down, then go back up to 4000ms. These
don't have to be on the same table and frequently are not...INSERTS are
going
to 1 table while the majority are going to another table in the same DB.
This
happens on all the servers we have so it's not specific to one server. They
all have the same hardware and software.
I've looked for deadlocks and there are none. I've set some of the common
things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
RAM on the server and there's 1GB available.
The MDF has it's own separate drive and the LDF has it's own drive.
It's almost looks like theres some sort of contention between the INSERT and
the SELECT, but since it's in separate tables...i have no idea what to look
for.
any help appreciated.
The servers were recently upgraded from SQL 7 .. I'm not sure if this was
happening under SQL 7. I don't think so. I've updated statistics on all
servers and recreated the clustered indexes.
SQL 2000 with SP4.
thanks,
Don
Was this post helpful to you?
Reply | Print post TopTop
JXStern 2/13/2006 7:06 PM PST
On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
<donsql22222@.discussions.microsoft.com> wrote:
>It's almost looks like theres some sort of contention between the INSERT and
>the SELECT, but since it's in separate tables...i have no idea what to look
>for.
Of course there's contention, for LRU pages in memory, the moreso
because they are different tables!
Run the queries during the slow period from QA with SET STATISTICS IO
ON, and look at the physical reads numbers going from 0 to whatever.
J.
It could be lots of things but the two most common are blocking (not the
same as deadlocks) and checkpoints. You can see blocking by running sp_who2
and look for rows with a number (spid) in the Blkd column. But checkpoints
are a prime suspect as well. You can see when they occur by monitoring the
perfmon counters for CheckPoint Pages / Sec under the SQL Server counters.
When this happens the disks will usually max out for the duration of the
checkpoint and will stall any other requests to or from the disk. You stated
you have the data and log files separated onto separate disks but are they
just logical or really physical disks. Having the files on two logical
drives (partitions or LUNs) that are on the same physical drive array (or
worse single drive) does nothing for performance. It actually gives a false
sense of security. You can also have a look at these links to see how to
narrow down the culprit.
http://www.sql-server-performance.co...ce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pro...perations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:02F1ED1B-AC28-4DA5-8CA1-E3735C6254B3@.microsoft.com...
> In PerfMon during time of simultaneous INSERTS and SELECTS ... the Lock
> Requests/Sec is around 2000 and the times for the execution of the INSERTS
> and SELECTS are around 16MS which is perfomance for the client requests.
> Suddenly, the Locks Request will drop to around 125 at the same time as
> the
> INSERTS and SELECTS increase to 4000 MS...too long for client SELECTS.
> Is one causing the other?
> Thanks,
> Don
> i've included the past thread below in case there's info that would help.
>
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> Tom Moreau 2/13/2006 4:49 PM PST
> Try updating stats with FULLSCAN. Also, trace the Lock Escalation event.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:776E5217-2EDB-40FF-BFA6-E8A3E1ADF980@.microsoft.com...
> Looking for suggestions about what I might look for..
> During periods of time when no INSERTS are taking place, SELECT statement
> durations are around 16-30ms for hundreds of requests per minute.
> When there is a batch of INSERTS that are sent to the table via SP that
> number in the many thousands..for a while both INSERTS and SELECTS coexist
> at
> around the same duration of 16-30 ms..watching this with Profiler.
> Then, for some reason..some SELECT and few INSERTS will go up to the
> 4000 -
> 5000 ms range. Then things will quiet down, then go back up to 4000ms.
> These
> don't have to be on the same table and frequently are not...INSERTS are
> going
> to 1 table while the majority are going to another table in the same DB.
> This
> happens on all the servers we have so it's not specific to one server.
> They
> all have the same hardware and software.
> I've looked for deadlocks and there are none. I've set some of the common
> things in PERFMON such as CACHE BUFFER HIT RATIO. Looks ok. There's 4GB of
> RAM on the server and there's 1GB available.
> The MDF has it's own separate drive and the LDF has it's own drive.
> It's almost looks like theres some sort of contention between the INSERT
> and
> the SELECT, but since it's in separate tables...i have no idea what to
> look
> for.
> any help appreciated.
> The servers were recently upgraded from SQL 7 .. I'm not sure if this was
> happening under SQL 7. I don't think so. I've updated statistics on all
> servers and recreated the clustered indexes.
> SQL 2000 with SP4.
> thanks,
> Don
>
> Was this post helpful to you?
>
> Reply | Print post TopTop
>
>
> JXStern 2/13/2006 7:06 PM PST
> On Mon, 13 Feb 2006 16:38:26 -0800, "donsql22222"
> <donsql22222@.discussions.microsoft.com> wrote:
> Of course there's contention, for LRU pages in memory, the moreso
> because they are different tables!
> Run the queries during the slow period from QA with SET STATISTICS IO
> ON, and look at the physical reads numbers going from 0 to whatever.
> J.

Lock requests/sec

I have just played a little with the performance monitor on my SQL-server and tried to look at the lock request per second. I really don't know what an acceptable range for this is but the server is running a pretty advanced website with quite alot of transactions. With only a modest 20 users online I have an average of about 5500 locks per second, is this acceptable and what are limits I should look for? The server is a Win2k dual AMD 1,6Mhz 512MB SP4 with RAID1...I don't know if you can look at the number of locks and determine if hardware is adequate. One should look at the database design, queries and stored procedures that access and modify data. Look at the type of locks, are they shared or exclusive? Maybe 95% of the locks are shared and that is due to scanning or large datasets coming back. Maybe one mass update is aquiry exclusive locks and will escalate to a table lock.

You sholud look at the type of locks, locks/session and the queries aquiring the locks.|||I'm not really concerned with the hardware as it seemingly performs well, but I was wondering if this was a really high number of locks and if I should be concerned about it. And how do I issue exclusive locks for my inserts and updates?|||achorozy is absoolutely right, the number of locks is arbitrary, are you having deadlocks, blocking, table locks, etc are more important questions, not to repeat achorozy but these could be shared locks which could be just queries which is perfectly fine.

HTH