November 29, 2005 at 3:21 pm
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 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,
December 1, 2005 at 11:19 am
I just had similiar problems with pageiolatch_sh. A query took 30 minutes to run. I found an index problem, dropped it, rebuilt it and bam, query runs sub 60 ms now. This was on a 10 gig data wharehouse all on single RAID 5 (Pentium 3 700 mhz, 2 gig RAM).
Try profiler and look for long running transactions and go into query analyzer to analyze them.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply