Multiple Indexes and Tuning

azure-sql-databaseindex-tuning

I'm utilising Azure SQL Database 2019.

Let's say I have columns A-F and the following indexes.

Primary Key (clustered): A

Index 1: B (ASC),C (ASC)

Index 2: B (ASC)

If I wanted to run run the following query below, is Index 2 needed or could it use Index 1 and not impact on performance?

SELECT A,B,C,D,E FROM table WHERE B = 'X'

Best Answer

The single best way to answer your question would be to run the query, measure the performance, and look at the execution plan (two steps, measure performance in one, get the execution plan in a second, capturing execution plans affects performance). See which index is used and how it's used. Then, disable that index that was used, run the query again, and see what happens. That will tell you more than any answer here is going to.

However, based on the info provided, and making assumptions on relatively even distribution of the data as well as good selectivity in the index, you're likely to see this. Index 2 is used to seek the value supplied 'X' and a key lookup is used to get the columns not included with Index 2, BCDE. Index 1 is unlikely to be used because it's wider key means more pages and a possibly deeper index. The optimizer is more likely to pick Index 2 in this scenario.

Let me emphasize though, that answer is speculation and without testing, there's no way to be certain. It's also possible that because Index 1 is more selective, a compound key, it may be more attractive to the optimizer. It's then used with a key lookup. This is less likely, but testing will tell you more than speculation will.

Related Solutions

Index Tuning on Joined Query

Put a non-clustered index on b.ReportID, and 'include' all the other columns from b that your query uses.

Sql-server – How to improve this query performance by indexing properly

Since you have a foreign key from CHILDEVENTS to CHILDINFORMATION and you don't select any column from the child information table, you can skip that table. One less join will certainly not hurt:

SELECT CHE.CHILDID, COUNT(*) AS CURRENTWEEKPOTTYBREAKS        
FROM  BKA.CHILDEVENTS AS CHE           
WHERE CHE.TYPE ='POTTYBREAK'        
  AND CHE.ADDDATE BETWEEN @currentweekstart AND @currentweekend         
GROUP BY CHE.CHILDID ;

As for indexing, the existing index on (Adddate,Type) INCLUDE (ChildId, EventId) does not look very good for this query. I would try replacing it (in fact removing both those AddDate-Type indexes) with an index on:

(Type, AddDate) INCLUDE (ChildId)

Best Answer

Related Solutions

Index Tuning on Joined Query

Sql-server – How to improve this query performance by indexing properly

Related Question