Good day, I have the following sql server database table:
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)
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:
- Can someone explain why the newly added index is better than my intially created one.
- 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.
- 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)
DateTimeServer
field is totally irrelevant to this question. Its a field that is there for debugging. It is not used in ANY queries.