CODEWITHSUNDEEP
sql-serverperformanceindexingdatabase-performance
Leonardo
Leonardo

Reputation: 11397

Sql Server - Index not being used

After running the following query:

SELECT [hour], count(*) as hits, avg(elapsed)
FROM myTable
WHERE [url] IS NOT NULL and floordate >= '2017-05-01'
group by [hour]

the execution plan is basically a clustered Index Scan on the PK (int, auto-increment, 97% of the work)

The thing is: URL has a index on it (regular index because i'm always searching for a exact match), floordate also has an index...

Why are they not being used? How can i speed up this query?

PS: table is 70M items long and this query takes about 9 min to run

Edit 1
If i don't use (select or filter) a column on my index, will it still be used? Usually i also filter-for/group-by clientId (approx 300 unique across the db) and hour (24 unique)...

Upvotes: 2

Views: 1732

Answers (1)

Wes H
Wes H

Reputation: 4439

In this scenario, two things affect how SQL Server will choose an index.

  1. How selective is the index. A higher selectivity is better. NULL/NOT NULL filters generally have a very low selectivity.
  2. Are all of the columns in the index, also known as a covering index.

In your example, if the index cannot cover the query, SQL will have to look up the other column values against the base table. If your URL/Floordate combination is not selective enough, SQL may determine it is cheaper to scan the base table rather than do an expensive lookup from the non-clustered index to the base table for a large number of rows.

Without knowing anything else about your schema, I'd recommend an index with the following columns:

floordate, url, hour; include elapsed

Date ranges scans are generally more selective than a NULL/NOT NULL test. Moving Floordate to the front may make this index more desirable for this query. If SQL determines the query is good for Floordate and URL, the Hour column can be used for the Group By action. Since Elapsed is included, this index can cover the query completely.

You can include ClientID after hour to see if that helps your other query as well.

As long as an index contains all of the columns to resolve the query, it is a candidate for use, even if there is no filtering needed. Generally speaking, a non-clustered index is skinnier than the base table, requiring less IO than scanning the full width base table.

Upvotes: 2

Related Questions