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.

Answer 1

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.

Answer 2

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.