Find the right Index for big datatable-CodePudding

I have a huge datatable with a lot of iot-data. The table is created with ef-core codefirst:

public class IoTData : Auditierbar<IoTData, Guid>
    {
        public Guid IoTDeviceId { get; set; }
        public Guid IoTDeviceInternalId { get; set; }
        public TimeSpan Zeit { get; set; }

        public string EventMessage { get; set; }
        public DateTime Timestamp { get; set; }
        public int Input1 { get; set; }
        public int Input2 { get; set; }
        public DateTime? SendData { get; set; }
        public string IId { get; set; }
        public string DeviceId { get; set; }
        public bool TestData { get; set; }

        public virtual IoTDevice IoTDevice { get; set; }
        public virtual ICollection<IoTData_Rueckmeldung> IoTData_Feedbacks { get; set; }
        public bool InputStatus1 { get; set; }
        public bool InputStatus2 { get; set; }
    }

    public class IoTDataConfiguration : AuditierbarConfiguration<IoTData, Guid>
    {
        public override void Configure(EntityTypeBuilder<IoTData> builder)
        {
            base.Configure(builder);

            builder.ToTable("MDE_IoTData");
            builder.HasIndex(r => new { r.IoTDeviceId });
            builder.HasIndex(r => new { r.UnternehmenId, r.Datum });
            // builder.HasIndex(r => new { r.UnternehmenId, r.Datum, r.Zeit });
            builder.HasIndex(r => new { r.UnternehmenId, r.Datum, r.IoTDeviceId });
            //builder.HasIndex(r => new { r.UnternehmenId, r.Datum, r.Zeit, r.IoTDeviceId });
            builder.HasIndex(r => new { r.UnternehmenId, r.IoTDeviceInternalId }).IsUnique();
            builder.Property(r => r.Zeit).IsRequired().HasColumnType("Time(0)").HasDefaultValue(default);

            builder.Property(r => r.EventMessage).IsRequired();
            builder.Property(r => r.Datum).IsRequired().HasColumnType("Date").HasDefaultValueSql("GetUtcDate()");

        }

You can see, there are already few indexes created. My query you can see here:

declare @__companyId_0 as uniqueidentifier ='03d7fdd2-....-....-......'
declare @__starttime_Date_1 as date= '2021-9-30'  
declare @__endtime_Date_2 as date ='2021-10-01'
--declare @__starttime_TimeOfDay_3 as time ='22:00:00' 
--declare @__endtime_TimeOfDay_4 as time ='22:00:00' 
    
SELECT [m].[IoTDeviceId], [m].[Datum], [m].[Zeit], [m].[Input1], [m].[Input2], [m].[Timestamp]
FROM [MDE_IoTData] AS [m]
WHERE ((([m].[UnternehmenId] = @__companyId_0) AND 
([m].[Datum] >= @__starttime_Date_1)) AND 
([m].[Datum] <= @__endtime_Date_2)) AND 

[m].[IoTDeviceId] in(
'E87B3284-....-....-......',
'A363FE26-....-....-......',
'97924216-....-....-......',
'C8C50B8C-....-....-......',
'93F80183-....-....-......',
'F3572F35-....-....-......',
'6B19A81B-....-....-......',
'A17CBB46-....-....-......')

I thought, this index is fine for this query

builder.HasIndex(r => new { r.UnternehmenId, r.Datum, r.IoTDeviceId });

But I got sometimes sql-timeout-exceptions. Could it be, that my indexes are not right? Each IoTDeviceId can have up to 1440 rows. So in that query (two days of Data) I expected max. 11520 rows and it takes 5 sec. If the date range is bigger eg. 14 days, the query takes more than one minute.

Any ideas?

UPDATE

Queryplan: https://www.brentozar.com/pastetheplan/?id=B1CHVrOVF

Table-Schema

CREATE TABLE [dbo].[MDE_IoTData](
    [Id] [uniqueidentifier] NOT NULL,
    [Erstellt] [datetime2](7) NOT NULL,
    [Datum] [date] NOT NULL,
    [UnternehmenId] [uniqueidentifier] NOT NULL,
    [IoTDeviceId] [uniqueidentifier] NOT NULL,
    [Zeit] [time](0) NOT NULL,
    [EventMessage] [nvarchar](max) NOT NULL,
    [Input1] [int] NOT NULL,
    [Input2] [int] NOT NULL,
    [IoTDeviceInternalId] [uniqueidentifier] NOT NULL,
    [SendData] [datetime2](7) NULL,
    [Timestamp] [datetime2](7) NOT NULL,
    [InputStatus1] [bit] NOT NULL,
    [InputStatus2] [bit] NOT NULL,
 CONSTRAINT [PK_MDE_IoTData] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[MDE_IoTData] ADD  DEFAULT (getutcdate()) FOR [Erstellt]
GO

ALTER TABLE [dbo].[MDE_IoTData] ADD  DEFAULT (getutcdate()) FOR [Datum]
GO

ALTER TABLE [dbo].[MDE_IoTData] ADD  DEFAULT (N'') FOR [EventMessage]
GO

ALTER TABLE [dbo].[MDE_IoTData] ADD  DEFAULT ((0)) FOR [Input1]
GO

ALTER TABLE [dbo].[MDE_IoTData] ADD  DEFAULT ((0)) FOR [Input2]
GO

ALTER TABLE [dbo].[MDE_IoTData] ADD  DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [IoTDeviceInternalId]
GO

ALTER TABLE [dbo].[MDE_IoTData] ADD  DEFAULT ('0001-01-01T00:00:00.0000000') FOR [Timestamp]
GO

ALTER TABLE [dbo].[MDE_IoTData] ADD  DEFAULT (CONVERT([bit],(0))) FOR [InputStatus1]
GO

ALTER TABLE [dbo].[MDE_IoTData] ADD  DEFAULT (CONVERT([bit],(0))) FOR [InputStatus2]
GO

ALTER TABLE [dbo].[MDE_IoTData]  WITH CHECK ADD  CONSTRAINT [FK_MDE_IoTData_Global_Unternehmen_UnternehmenId] FOREIGN KEY([UnternehmenId])
REFERENCES [dbo].[Global_Unternehmen] ([Id])
GO

ALTER TABLE [dbo].[MDE_IoTData] CHECK CONSTRAINT [FK_MDE_IoTData_Global_Unternehmen_UnternehmenId]
GO

ALTER TABLE [dbo].[MDE_IoTData]  WITH CHECK ADD  CONSTRAINT [FK_MDE_IoTData_MDE_IoTDevice_IoTDeviceId] FOREIGN KEY([IoTDeviceId])
REFERENCES [dbo].[MDE_IoTDevice] ([Id])
GO

ALTER TABLE [dbo].[MDE_IoTData] CHECK CONSTRAINT [FK_MDE_IoTData_MDE_IoTDevice_IoTDeviceId]
GO

CodePudding user response：

The best index for this query is most likely

(UnternehmenId, Datum) INCLUDE (IoTDeviceId, IoTDeviceId, Zeit, Input1, Input2, [Timestamp])

This is called a covering index, because it covers all the columns in the query. It's best to keep those columns in the INCLUDE, however you may need to do this with a manual SQL statement, rather than with HasIndex()