select top 10 ... and select top 30 follows different execution plan

Question

During query optimization I encounted a strange behaviour of sql server (Sql Server 2008 R2 Enterprise). I created several indexes on tables, as well as some indexed views. I have two queries, for example:

select top 10 N0."Oid",N1."ObjectType",N1."OptimisticLockField" from ((("dbo"."Issue" N0
 inner join "dbo"."Article" N1 on (N0."Oid" = N1."Oid"))
 inner join "dbo"."ProductLink" N2 on (N1."ProductLink" = N2."Oid"))
 inner join "dbo"."Technology" N3 on (N2."Technology" = N3."Oid"))
where (N1."GCRecord" is null and (N0."IsPrivate" = 0) and ((N0."HasMarkedAnswers" = 0) or N0."HasMarkedAnswers" is null) and (N3."Name" = N'Discussions'))
order by N1."ModifiedOn" desc

and

select top 30 N0."Oid",N1."ObjectType",N1."OptimisticLockField" from ((("dbo"."Issue" N0
 inner join "dbo"."Article" N1 on (N0."Oid" = N1."Oid"))
 inner join "dbo"."ProductLink" N2 on (N1."ProductLink" = N2."Oid"))
 inner join "dbo"."Technology" N3 on (N2."Technology" = N3."Oid"))
where (N1."GCRecord" is null and (N0."IsPrivate" = 0) and ((N0."HasMarkedAnswers" = 0) or N0."HasMarkedAnswers" is null) and (N3."Name" = N'Discussions'))
order by N1."ModifiedOn" desc

both queries are the same, except first starts with select top 10 and second with select top 30. Both queries returns the same result set - 6 rows. But the second query is 5 times faster then the first one! I looked at the actual execution plans for both queries, and of course, they differs. Second query uses indexed view, and performs great, and the first query denies to use it, using indexes on tables instead. I repeat myself - both queries are the same, to the same table, at the same server, they differs only by number in "top" part. I tried to force optimizer to use indexed view in the first query by updating statistics, destroing indexes it used and so on. No matter how I try actual execution do not use indexed view for the first query and always use it for the second one.

I am really intrested in the reasons causing such behavior. Any suggestions?

Update I am not sure that it can help without decribing corresponding indexes and view, but this is actual execution plan diagramms: for select top 19:

for select top 18:

another confusing fact is that for the select top 19 query sometimes indexed view is used, sometimes not

Answer 1

The only thing I can think of is perhaps the optimizer in the first query concluded that the specifying criteria is not selective enough for the "better" execution plan to be used.

If you are still investigating this see if TOP 60, 90, 100, ... produces the second execution plan and performs well. You could also tinker with it to see what the threshold is for the optimizer to select the second plan in this case.

Also try the queries without the order by statement to see if that is affecting the selection of the query plan (check the index on that field, etc)

Beyond that, you said you can't use index hints so perhaps a re-write where you select top X from your Article table (N1) with a bunch of exists statements in your where clause would provide better performance for you.