• I don't know whether to always believe the blocked process report. At times I see lots of purported blocking like this, where one select statement is supposedly blocking another. As you can see, we're using the default isolation level, read committed. ( I altered the hostname and loginname fields, and had to replace the ">" with "]" to get the xml to appear in this post for some reason).

    If nothing else, the blocked process report does not tell me how/why one query is blocking another.

    <blocked-process-report monitorLoop="1649082"]

    <blocked-process]

    <process id="process38132e8" taskpriority="0" logused="0" waitresource="PAGE: 17:4:94458" waittime="87078" ownerId="13262486200" transactionname="SELECT" lasttranstarted="2008-12-03T13:23:16.110" XDES="0x4e10c8558" lockMode="S" schedulerid="2" kpid="5372" status="suspended" spid="98" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2008-12-03T13:23:16.110" lastbatchcompleted="2008-12-03T13:23:16.110" clientapp=".Net SqlClient Data Provider" hostname="xxx" hostpid="7216" loginname="xxx" isolationlevel="read committed (2)" xactid="13262486200" currentdb="17" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"]

    <executionStack]

    <frame line="1" sqlhandle="0x020000003c2fd4171752b341f4fb2e78c4fd25a39d3268cb"/]

    <frame line="1" sqlhandle="0x02000000272ffe37c5c986299be2ada0f87a7d86ca14ff81"/]

    </executionStack]

    <inputbuf]

    select count(*) from employees where client_id=11343 and isadmin=1 </inputbuf]

    </process]

    </blocked-process]

    <blocking-process]

    <process status="suspended" waitresource="PAGE: 17:4:94458" waittime="100500" spid="161" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2008-12-03T13:23:02.657" lastbatchcompleted="2008-12-03T13:23:02.657" clientapp=".Net SqlClient Data Provider" hostname="xxx" hostpid="7216" loginname="xxx" isolationlevel="read committed (2)" xactid="13262406360" currentdb="17" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"]

    <executionStack]

    <frame line="1" sqlhandle="0x0200000066da5526607b1b624239a36f488fec861b4ab401"/]

    <frame line="1" sqlhandle="0x02000000b4e2201f1eda6c0a562330100b742a51c5d4d9f1"/]

    </executionStack]

    <inputbuf]

    select count(*) from employees where client_id=34273 and isadmin=1 </inputbuf]

    </process]

    </blocking-process]

    </blocked-process-report]