471,075 Members | 801 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

PAGEIOLATCH is a lead blocker

Hi Guru,

After spening quite sometimes to watch my box, I've seen PAGEIOLATCH is
a lead blocker in my SQL Server 2000 server. Below is the detailed:

SPID lastwaittype waitresource blocked status cmd
57 LCK_M_S KEY: 7:963690681:8 65 sleeping execute
65 PAGEIOLATCH_SH 7:1:217904 0 sleeping select
I thought, latching should be very short-term synchronization. From
systemprocess table, I saw the latch waited in a minute sleeping
without doing any work.

My database is about 23GB and more than 5000 tables. The RAID subsystem
is RAID1 with 1 disk mapped to C and D logically. Data files and tempdb
files are located in one location. Tranlog file and log backup files
are located in the same location with different disk spindle.

Currently, we are experiencing very slowness and IO bound. I'm ready to
rebuild the server by putting the RAID10 and 1 and distributing
multiple data files to different RAID10 and tempdb and log files to
RAID1.

Other than this, how to minimize the IO latch contention?

Thanks so much,

Silaphet,

Nov 29 '05 #1
1 9657
km********@yahoo.com (sm********@bremer.com) writes:
After spening quite sometimes to watch my box, I've seen PAGEIOLATCH is
a lead blocker in my SQL Server 2000 server. Below is the detailed:

SPID lastwaittype waitresource blocked status cmd
57 LCK_M_S KEY: 7:963690681:8 65 sleeping execute
65 PAGEIOLATCH_SH 7:1:217904 0 sleeping select
I thought, latching should be very short-term synchronization. From
systemprocess table, I saw the latch waited in a minute sleeping
without doing any work.
The above only tells us that the last time spid 65 waited for something
it was a latch. Presumably, it's holding some other lock that blocks
process 57.

The way to wind this up is to look in syslockinfo, but this is a quite
tedious work. I have a utility SP which gives you a snapshot of active
processes and their locks and blocks, including last statement. You find
it on my web site: http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
My database is about 23GB and more than 5000 tables. The RAID subsystem
is RAID1 with 1 disk mapped to C and D logically. Data files and tempdb
files are located in one location. Tranlog file and log backup files
are located in the same location with different disk spindle.

Currently, we are experiencing very slowness and IO bound. I'm ready to
rebuild the server by putting the RAID10 and 1 and distributing
multiple data files to different RAID10 and tempdb and log files to
RAID1.

Other than this, how to minimize the IO latch contention?


While throwing hardware at the problem may alleviate the situation,
the true answer are probably to review the queries that are involved
in blocking situations, and either change them or add indexes to speed
them up.

If you are stuck with a 3rd-party product that you cannot change, you
may be interested in Quickshift, a product that claims to be able to
increase the performance of IO-bound applications. I have not used this
product myself though.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 30 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by dave yan | last post: by
26 posts views Thread by Raffi | last post: by
7 posts views Thread by rob c | last post: by
9 posts views Thread by =?Utf-8?B?Tkg=?= | last post: by
mageswar005
1 post views Thread by mageswar005 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.