473,221 Members | 1,833 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 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 9727
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: dave yan | last post by:
hi, i have some forms which use javascript for data validation, e.g., checking to make sure all required fields are completed, checking that data falls within valid ranges for certain fields,...
4
by: Ashish | last post by:
Hi Friends, I am looking for an anti-popup blocker solution, which can open popup window, irrespective whether any popup blocker is on or off. Well, I have succeeded in creating the same...
26
by: Raffi | last post by:
Hi, We have a database application that runs in a popup Internet Explorer application window. The reason for this is to isolate the casual user from the address bar and the typical IE navigation...
2
by: domtam | last post by:
Hi all, Here is what I want: when a user clicks a <asp:button>, its event handler will do some server-side processing and then pop up a windows of another page. As far as I know, if no pop-up...
7
by: rob c | last post by:
Hi Does anyone know what triggers a "popup blocker"? I'm going to be opening some sub-windows from my main page and don't want to get caught in a blocker. Thanks Rob
3
by: Yisehaq | last post by:
Hi guys I am trying to prepare few html pages which contain OLAP cubes on a CD. The problem I have is that the popup blocker blocks the OLAP modules from being shown. Therefore, I wanted to write...
9
by: =?Utf-8?B?Tkg=?= | last post by:
Hi, I have an invoicing system built in asp.net 2.0. Part of it automatically opens up a new IE window using a bit of javascript. This is stopped by the pop up blockers in IE. Is there a way...
2
by: andrewwan1980 | last post by:
I have a problem with a child window using the parent's FORM to do a submission/post to open a new window. This is being blocked by IE7. This is happening when accessing an external website that is...
1
mageswar005
by: mageswar005 | last post by:
hi, how to detect yahoo popup blocker, in my site i open a popup window but some local pc yahoo popup blocker can control my popup window. how can i detect the popup blocker....
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.