Why does SQL Server stall after some of the rows have been returned during a select statement?

Question

I have a simple procedure that selects 1000 rows from a table. For the sake of clarity, here's what the stored procedure looks like:

alter procedure dbo.GetDomainForIndexing
  @Amount int=1,
  @LastID bigint,
  @LastFetchDate datetime
as
begin

   select top (@Amount) *
     from DomainName with(readuncommitted)
    where LastUpdated > @LastFetchDate 
      and ID > @LastID 
      and ContainsAdultWords is not null
 order by ID

end

I've been having issues where it would run this particular procedure fine a bunch of times, the only difference being that a different @LastID value was being passed in each time. As soon as I get to a specific ID though, the procedure will return the first 880 rows almost instantly (this is happening in management studio) and then sit there and literally stall for the next 6 minutes before returning the remaining 120 rows.

What on earth could cause behaviour like this? There are no transactions associated with the connection and there are no connection pool issues. The (readuncommitted) bit does not affect the issue. The issue occurs both from within my application and when I copy the command text into SQL Management Studio for testing; indeed it is there that I discovered this weird stalling behaviour. Initially I was just trying to work out why this procedure would work fine a bunch of times and then suddenly start stalling for no apparent reason.

Any ideas?

UPDATE

The issue also occurs (stalling after 880 rows have been returned) when asking for 883 rows, but not when asking for 882 rows.

UPDATE 2

Selecting from sys.sysprocesses and sys.dm_exec_requests indicates a lastwaittype of PAGEIOLATCH_SH. What should I do?

Answer 1

Have you tried rebuilding the indexes on DomainName?

Answer 2

Quite likely "parameter sniffin"g and an suboptimal cached plan for the offending @LastID value Try this:

DECLARE @ILastID bigint
SET @ILastID = @LastID

select top (@Amount) *
from DomainName with(readuncommitted)
where LastUpdated > @LastFetchDate and ID > @ILastID and ContainsAdultWords is not null
order by ID

Another option:

What does sysprocesses say as LastWaittype? ASYNCH_NETWORK_IO? If so, then the client can't deal with the SQL Server output