I'm seeing pageiolatch_sh values in the master..sysprocesses table's lastwaittype column. Many sysprocesses rows that have a pageiolatch lastwaittype also have nonzero values for waittime, so I know that the server process IDs (SPIDs) are waiting. What do the pageiolatch values mean?
Searching SQL Server Books Online (BOL) turns up five hits for latches, and none of the hits provides much detail. The most detailed entry says that "latches are very lightweight, short-term synchronization objects protecting actions that need not be locked for the life of a transaction. They are primarily used to protect a row when read for a connection." We're all familiar with SQL Server locks and how they can affect concurrency if they start to block other processes. What most people don't understand is that SPIDs can wait a long time for different types of latches. As you said, latch wait times show up for a SPID in master..sysprocesses, but they don't cause a SPID to appear as blocked in the output of sp_who or similar investigative tools. Let me be clear about the distinction between a latch that's blocked and a latch that's waiting. A blocked connection generally means that another connection is holding a lock that the first connection needs. When processes wait on each other for locks to be released, it's called blocking. A process that's waiting doesn't involve a lock. I'll explain the pageiolatch_sh waittype so you can see what I mean.
Pageiolatch_sh is a shared latch of the generic class pageiolatch. As the BOL entry notes, these shared latches are used as synchronization objects during disk-to-memory buffer transfers. A pageiolatch_sh value identifies a connection that's waiting on SQL Server to read a particular page from disk into memory that's available to the SQL Server buffer pool. If you have many SPIDs with pageiolatch_sh waits, especially with nonzero waittimes, make sure you don't have a disk bottleneck. (Note that memory pressure can cause disk bottlenecks.) You can use the DBCC PAGE command to see what object SQL Server is reading in from disk, which might help you track down and eliminate some of your bottlenecks.
The waitresource column in master..sys-processes will help you decode the object that's involved in the latch wait. You'll see a pageiolatch value in the waitresource column that looks something like '2:1:24.' The first number identifies the database ID, the second number identifies the file ID, and the third number identifies the page number on the file ID. You can then use the DBCC PAGE command to figure out which object owns a particular page. First, run DBCC TRACEON(3604) to direct command output to the Query Analyzer window, then run the following DBCC PAGE command to return the output that Figure 1 shows:
DBCC PAGE (2,1,24,0)
You can find plenty of information about the DBCC PAGE command's output by searching Microsoft's Web site. For now, you simply need to worry about the value listed after m_objId =, which in Figure 1 is 36. That number identifies the object that owns the page you're looking at and that the pageiolatch is waiting on. In the master database, run the following command to find out which table owns the page in question:
USE master SELECT object_name(36)
ObjectId 36 in the master database is associated with the sysmessages table. So you can deduce that a SPID was waiting while SQL Server read a page in master..sysmessages from disk into a SQL Server buffer-pool page. You'll often see pageiolatch_sh associated with tables in various user databases that have large volumes of I/O performed against them.For more in-depth information about latching, see Tom Davidson's article "Opening Microsoft's Performance-Tuning Toolbox," January 2004, InstantDoc ID 40925.
