Ever wondered why sql will not use the index and still perform very slow. Here are few points where the queries will ignore index.

CONDITIONAL FILTER

Consider following stored procedure

CREATE STORED PROCEDURE [UpdateAllItems]
     @days int = 0
BEGIN
     declare @date date;
     set @date = DATEADD(day, @day,  GETUTCDATE());
     UPDATE Projects 
     SET
          ...
          ...
     WHERE
          @days = 0 OR DateUpdated > @date
END

The above query looks like we want to either update all projects or only the projects which were modified in before given days. So we expect this query to run very long if @days is 0, but we expect it to run fast when @days is 1. Unfortunately, query engine will not use index when @days is.

Because query engine will use index only if query references column without OR, Irrespective of which path it will take at runtime. Query analyzer cannot change query based on the input parameters.

So above query should be rewritten as following.

CREATE STORED PROCEDURE [UpdateAllItems]
     @days int = 0
BEGIN
     declare @date date;
     set @date = DATEADD(day, @day,  GETUTCDATE());
     IF @days = 0 
        UPDATE Projects 
        SET
             ...
             ...;
     ELSE
        UPDATE Projects 
        SET
             ...
             ...;
        WHERE DateUpdated > @date    
END

In above example, server knows that it needs to use index in the ELSE path.

DATEPART FILTER

I have seen many people write following query to compute something for given year, for example lets say we want count of all projects updated in given year and we have index of DateUdated on the Projects table.

SELECT count(*) from Projects WHERE DATEPART(YEAR, DateUpdated) = 2020;

Above query will cause INDEX SCAN which is slower than INDEX SEEK. To speed up above query, we can write following query.

SELECT count(*) from Projects WHERE DateUpdated > '2020-01-01' AND DateUpdated < '2021-01-01';

Above query will do INDEX SEEK and will perform faster than the previous one. If tables are very small, the difference will not be visible, but when we have millions of rows, the SCAN will be much slower than the SEEK.