SQL Server SP4 - Block by its own SPID

Question

SQL Server SP4 - Block by its own SPID

petmani

Old Hand

Points: 315
More actions
May 29, 2005 at 12:25 am

#109416

I have installed SQL Server SP4 in one of my Production Box. After we deployed SP4, there are SPIDs blocking on its own and it vanishes right away. Even simple select statement also sometimes getting blocked by its own. I went in more detail and found out a wait type of PAGEIOLATCH_SH sysprocesses system table for this specific SPID. I know its because of shorterm synchronization objects. Why this is shown in sp_who 'active' as a block by its own instead of just a waittime? We are seeing this issue right after we installed SP4. While we upgraded SP4 we also upgraded our server to 10 GB. Is that matter if we have more memory and if we request more data from a specific drive will there be PAGEIOLATCH_SH lock?
Thanks in advance.
Mani

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

maertean esaeasner Ten Centuries Points: 1162 More actions · Answer 1

Hi Mani,

I recieved an error of similar nature not too long ago... It could be that the subsystem cannot keep with SQL Server, thus with the extra memory, SQLServer is operating too fast for your harddisk and it this is causing the wait latch...

Check out

http://www.sqldev.net/articles/WaitTypes.htm

http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_04222005.asp

Martin

Adrian Sims-164328 SSC Journeyman Points: 94 More actions · Answer 2

We are also hitting this problem, and AWE is not enabled.

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 3

The MSDN link has changed.

http://msdn2.microsoft.com/en-us/library/aa175396(SQL.80).aspx

Michelle