Until a few days ago we didn't even know SPID 1 existed. These are different guys, using old version of Crystal passing crappy queries.
Here's the scenario
SPID 1 is running (inefficient select)
SPID 2 is waiting
30 minutes pass and SPID 2 is still waiting PAGEIOLATCH_SH
SPID 3 fires off
Now SPID 2 is blocking SPID 3, but SPID 2 is still waiting for SPID 1
Finally when SPID 1 finishes, everything catches up. But it appears that there is a period of at least 45 minutes when all SPID's are just sleeping.
1. If SPID 1 is just running an inefficient select query, why is it holding up SPID 2 (Which reads and inserts). SPID 1 should have a shared lock on table, this would prevent SPID 2 from insert/update?
2. Is there any steps I can take to give SPID 2 precendence? or is it first come first serve?
Since SPID 1 has a very inefficient query, it is probably doing a table scan. This will incur a shared lock on the entire table. With a shared lock on the table, SPID2 must wait to do any insert/update/delete. SPID 2 can read all day long, but any write activity must wait until his intent lock is upgraded to an exclusive lock. SPID 3 is waiting, because it will need to get its intent shared lock upgraded to a shared lock, and the intent exclusive lock (from SPID2) is preventing that upgrade.
Now, if SPID 3 wanted to cash in enough frequent flier miles, they could bring a companion along, but only in Economy class, unless they brought no luggage, in which case they could travel Business class. Alternatively, they could upgrade to first class, but only on Yak Shaving day.
Ok Here's what I've got. I did a search on the 2:1:2 and didn't find anything in the output file.
Here's a small excerpt from the proc....
There's actually about 15 more of the Spid 70s.
What causes multiple instances of Spid 70 to show up? At this point in time it looks like nothing is going on. Just a bunch of waiting.
63 sleeping 70 1 TAB: 7:1177771253 
70 sleeping 0 0 7:1:2233567
70 sleeping 0 0 7:1:1699288
70 sleeping 0 0 7:1:3235145
70 sleeping 0 0 7:1:2014717
I think that only one person at a time can have a latch, so if one person is sitting on that latch, but stuck waiting for a mass update, then you could see this behaviour. I will have to go look that up in Kalen's book.
As for SPID 70, the most common cause is Parallelism. See if the waittype is CX_PACKET, or some such. The waittype should be 0x208.