Sql-server – Questionable duplicate index suggested by optimization

azure-sql-databaseindexindex-tuningsql server

Good day, I have the following sql server database table:
enter image description here

Please note the compound primary key. This was done for 3 reasons:

  • Prevent duplicate entries
  • Improve query performance as all queries will have all of thoes 3 keys.
  • We needed and index, and I did not want to introduce a random ID.

Please also note that this table was designed with its size in mind, This table is going to store millions and millions of rows of data.

OK now for my actual question. I am using azure sql server to host this db. and I have enabled automatic tuning. And strangely enough I see that it then went and created a new index. (see below)

enter image description here

Now In my mind this seems to be a duplicate index, as the same columns are being indexed.

So I now have two indexes on my table:

Original (My PK):

ALTER TABLE [dbo].[SensorDataRaw] ADD  CONSTRAINT [PK_SensorDataRaw] PRIMARY KEY CLUSTERED 
(
    [DateTime] ASC,
    [SensorId] ASC,
    [Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Newly Added (Auto Created by azure tuning):

CREATE NONCLUSTERED INDEX [nci_wi_SensorDataRaw_DC9789077DA75B4440AC8BFE3E2AA198] ON [dbo].[SensorDataRaw]
(
    [Key] ASC,
    [SensorId] ASC,
    [DateTime] ASC
)
INCLUDE (   [Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Observations:

  • The order of the coloumns has been reversed in the new index.
  • The new index is NOT unique
  • the new index includes the value column.

Please note my knowledge on indexes is not advanced, hence me asking this.

So my question is:

  1. Can someone explain why the newly added index is better than my intially created one.
  2. How can I remove the two indexes and just create one that covers both cases. With this being such a massive database I cannot afford the space that both these indexes will take up.
  3. Is the maybe a better design alternative?

Additional Info:

I'm assuming the type of queries becomes important here, So I have listed some examples.

All queries include DateTime, SensorId, and Key.

Simple Queries:

Select SensorId Where average value for key w is greater than x where time between (y,z)

Graphing Data:

SELECT AVG([Value]) AS 'AvgValue',    
    DATEADD( MINUTE, 
    (DATEDIFF(MINUTE, '1990-01-01T00:00:00', [dbo].[SensorDataRaw].
[DateTime]) / @IntervalInMinutes) * @IntervalInMinutes,
        '1990-01-01T00:00:00'
    )      AS 'TimeGroup'
 FROM [dbo].[SensorDataRaw]
 where 
    [dbo].[SensorDataRaw].[SensorId] = @SensorId
    and [dbo].[SensorDataRaw].[Key] = @KeyValue
    and [dbo].[SensorDataRaw].[DateTime] Between @DateFrom and @DateTo
    and [dbo].[SensorDataRaw].[Value] IS NOT NULL  
  GROUP BY (DATEDIFF(MINUTE, '1990-01-01T00:00:00', [dbo].[SensorDataRaw].
    [DateTime]) / @IntervalInMinutes)

Best Answer

The index suggested by the system is a much better fit for the query you have shown. You should aim to have columns with equality predicates as the leading columns.

Consider a phone book ordered by lastname, firstname. If your rquirement is to find all people with surnames between "Brown" and "Yates" and a first name of "John" then you need to read most of the phone book. If the phonebook was instead ordered by firstname, lastname you could easily find the "John" section and the first "Brown" in the section then all you need to do is read all the names until the lastname is after "Yates" or a new firstname is encountered.

It might not be the ideal index. Potentially you should just change the key columns in the clustered index to this order rather than creating a new one though. You need to evaluate this based on knowledge of your workload.

Related Question