SQL Server ignoring index and performs table scan

Question

we have a little problem with one of our queries, which is executed inside a .Net (4.5) application via System.Data.SqlClient.SqlCommand.

The problem is, that the query is going to perform a Table-Scan which is very slow. So the execution plan shows the Table-Scan here

Screenshot:

The details:

So the text shows, that the filter to Termine.Datum and Termine.EndDatum causing the Table-Scan. But why is the SQL-Server ignoring the Indexes? There are two indexes on Termine.Datum and Termine.EndDatum. We also tryed to add a third one with Datum and EndDatum combined.

The indexes are all non-clustered indexes and both fields are DateTime.

Clearly `statistics` is outdated!!.. Update statistics try running the query again — Pரதீப், Aug 03 '17 at 14:49
Also post the query and index details to get more accurate response. Share the execution plan here https://www.brentozar.com/pastetheplan/ — Pரதீப், Aug 03 '17 at 14:52
Two separate indexes definitely won't help since you're attempting two range queries. The single combined index *could* be useful but not guaranteed to be so. — Damien_The_Unbeliever, Aug 03 '17 at 14:54
Also after you update the stats you can run DBCC FREEPROCCACHE on that particular plan before executing that again to build new, proper one — Bartosz X, Aug 03 '17 at 14:55
I will get the complete execution plan tomorow. Then i will paste it. (on our own database the query is fast, with much more rows) — slxSlashi, Aug 03 '17 at 14:58
So I finally could get the data from our customer. Execution Plan after *UPDATE STATISTICS*: https://www.brentozar.com/pastetheplan/?id=HkIquyLDZ The (auto-formated) query text: https://pastebin.com/BPpAzVrJ — slxSlashi, Aug 07 '17 at 13:22

score 0 · Answer 1 · answered Aug 03 '17 at 14:53

It decides on Table Scan based on Estimated number of rows 124844 where as your actual rows are only 831.

Optimizer thinks that to traverse 124844 it will better do scan in table instead of Index Seek.

Also need to check about other columns selected apart from Index. If you have selected other columns apart from Index it has to Do RID Lookup after doing index seek, Optimizer might think instead of RID lookup it preferred to go with Table Scan.

First fix: Update the statistics and provide enough information to optimizer to choose better plan.

score 0 · Answer 2 · edited Aug 03 '17 at 16:48

Can you provide the full query? I see that you are pulling a range of data that span a range of 3 months. If this range is a high percentage of the dataset it might be scanning due to you attempting to return such a large percentage of the data. If the index is not selective enough it won't get picked up.

Also...

You have an OR clause in the filter. From looking at the predicate in the screenshot you provided it looks like you might be missing () around the two different filters. This might also lead to the scan.

One more thing... OR clauses can sometimes lead to bad plans - an alternative is to split the query into two UNIONED queries each with the different OR in it. If you provide the query I should be able to give you a re-written version to show this.

SQL Server ignoring index and performs table scan

2 Answers2