Query pauses after returning a fixed number of rows

I have a view which runs quickly (a few seconds) for up to 41 records (e.g., TOP 41) but takes several minutes for 44 or more records, with intermediate results if run with TOP 42 or TOP 43. Specifically, it will return the first 39 records in a few seconds, then halt for almost three minutes before returning the remaining records. This pattern is the same when querying TOP 44 or TOP 100.

This view originally derived from a base view, adding to the base just one filter, the last one in the code below. There seems to be no difference if I chain the child view from the base or if I write the child view with the code from the base in-lined. The base view returns 100 records in just a few seconds. I'd like to think that I can get the child view to run about as quickly as the base, not 50 times slower. Has anyone seen this kind of behavior? Any guesses as to cause or resolution?

This behavior has been consistent for the last few hours as I've tested the queries involved, though the number of rows returned before things start to slow down has bumped up and down slightly. This is not new; I'm looking at it now because the total run time had been acceptable (<2 minutes), but I've seen this pause in related log files for months, at least.
Blocking

I've never seen the query blocked, and the problem exists even when there's no other activity on the database (as validated by sp_WhoIsActive). The base view includes NOLOCK throughout, for what that's worth.
Queries

Here's a cut-down version of the child view, with the base view in-lined for simplicity. It still exhibits the jump in run time at about 40 records.

```
SELECT TOP 100 PERCENT
Map.SalesforceAccountID AS Id,
CAST(C.CustomerID AS NVARCHAR(255)) AS Name,
CASE WHEN C.StreetAddress = 'Unknown' THEN '' ELSE C.StreetAddress END AS BillingStreet,
CASE WHEN C.City = 'Unknown' THEN '' ELSE SUBSTRING(C.City, 1, 40) END AS BillingCity,

Some things to try:

-
Check your indexes

-
Are all the JOIN key fields indexed? If you use this view a lot, I would go so far as to add a filtered index for the criteria in the view. For instance...

-
CREATE INDEX ix_CustomerId ON WarehouseCustomers(CustomerId, EmailAddress)
WHERE
DateMadeObsolete IS NULL
AND AccessKey IN ('C', 'R')
AND CustomerID NOT IN (243566)

-
Update statistics

• There could be issues with out-of-date stats. If you can swing it, I would do a FULLSCAN. If there are a large number of rows, it's possible that data has changed significantly without triggering an automatic recalc.

-
Clean up the query

-
Make the Map JOIN a NOT EXISTS - You don't need any data from that table, since you only want non-matching records

-
Remove the ORDER BY. I know the comments say that it doesn't matter but I find that very hard to believe. It may not have mattered for your smaller result sets since the data pages are already cached.

StackExchange Database Administrators Q#18799, answer score: 4