10

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 19:

for select top 18: for select top 18:

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

objectbox
  • 1,281
  • 1
  • 11
  • 13
  • Is the indexed view one of the "tables" mentioned in the `FROM` clause? If so, does adding the `WITH (NOEXPAND)` hint change anything? – Damien_The_Unbeliever Nov 18 '11 at 07:35
  • The view is not directly referenced in the query, only underlying tables. All works fine for the second query, but not for the first. In fact, I open SSMS query window, paste second query - it uses indexed view, then I replace "top 30" with "top 10" in that query and execution plan changes - indexed view is not used at all! – objectbox Nov 18 '11 at 08:18
  • 1
    Did you issue a `DBCC FREEPROCCACHE; DBCC FREESYSTEMCACHE('ALL')` between queries to guarantee a fair test? – MicSim Nov 18 '11 at 15:25
  • 1
    Just a left field thought but have you tried using a variable for the top (n) statement? e.g. select top (@toprows) from ... – AnthonyJ Nov 18 '11 at 21:54
  • to @MicSim - I tried DBCC FREEPROCCACHE; DBCC FREESYSTEMCACHE('ALL') - nothing changes - same execution plan for both cases. – objectbox Nov 19 '11 at 09:09
  • to @AnthonyJ - it works fine with variables. But, I have no control over the queries. Thanx for suggestion, however – objectbox Nov 19 '11 at 09:14
  • You could force it to use a specific index by adding an index or table hint in the SQL. As to WHY it's occurring in the first place... execution plans would help. Otherwise all I can think is that in one case it's getting the top 30 records from article 1st. the other case, it's getting the results of the join first then getting the top 10 records; because the database sees a performance gain.. have statistics on the tables been refreshed? are there lots of deletes/inserts into the tables? are the tables sized to allow for growth or are they having to readjust on each insert/update? – xQbert Nov 21 '11 at 02:05
  • I wrote that already - I saw execution plans, and they, of course, differs. That's obvious. I wonder why sql produces such an uneffective plan for the first query. As for the hints - I can't use them - I have no control over the queries. Anyway thanks for suggestions! – objectbox Nov 21 '11 at 07:10
  • Can you post the execution plans? That would help us a lot in diagnosing this. – mwigdahl Nov 21 '11 at 16:43
  • I am not sure that it would help, but I posted execution plan diagramms. – objectbox Nov 22 '11 at 12:34

1 Answers1

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.

Chris Townsend
  • 3,042
  • 27
  • 31
  • 1
    I can't modify the query. It is produced by ORM - Xpo. I have already experimented with different values in "top" - for values greater then 25 it indexed view is always picked and execution plan is nice. for values lesser then 17 it always do not use indexed view. Thanks for suggestions – objectbox Nov 21 '11 at 17:07
  • 1
    and besides, that query performance is not a big problem - there are a lot of other ways to solve this issue. I am more intrested in the answer to the question WHY? – objectbox Nov 21 '11 at 17:24