PAGEIOLATCH is a lead blocker
in [Using MS SQL Server]
|
From: kmounkhaty@yahoo.com on 29 Nov 2005 17:06 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,
From: Erland Sommarskog on 30 Nov 2005 07:13 kmounkhaty(a)yahoo.com (smounkhaty(a)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, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
Pages: 1 Prev: SCOPE_INDENTITY() Not Returning Next: Joining tables in different databases |