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.