Missing Index

[bhugot]

Hello, we are using this provider since a few month with orchestration having more than 500k activities using Azure Sql and some index have been given by Azure Sql query performance:

One on NewEvents that is specialy used in our 500k activities orchestration

CREATE NONCLUSTERED INDEX [IX_NewEvents_TaskHub_VisibleTime] ON [dt].[NewEvents]
(
    [TaskHub] ASC,
    [VisibleTime] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

And 2 for dt.Instances

CREATE NONCLUSTERED INDEX [IX_Instances_ParentInstanceID] ON [dt].[Instances]
(
    [ParentInstanceID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Instances_RuntimeStatus_Name] ON [dt].[Instances]
(
    [RuntimeStatus] ASC,
    [Name] ASC
)
INCLUDE([CreatedTime],[ExecutionID],[LastUpdatedTime]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

I think for sure the first one should be added but not sure for the 2 on dt.Instances

[cgillum]

Hi @bhugot. Can you clarify for me why we need these additional indexes? I didn't quite understand your original explanation. Are you saying that Azure SQL is making this explicit recommendation in the Azure Portal?

[bhugot]

Yes it is explicit recommendation from the Azure Portal