Missing indexes in LcsCDR database

I investigated some users complaining over Skype for Business (Lync) Monitoring Reports for Response Groups that timed out and didn't return data.


The query in the report Response Group Usage Data uses the stored procedure CdrRGSUsageTrend in the monitoring database LcsCDR.


I went through the estimated execution plan in SQL for this stored proc

The execution plan showed a number of missing indexes, which I created (listed below)


CREATE NONCLUSTERED INDEX [IX_SessionDetails_Missing1] ON [dbo].[SessionDetails]
([ReplacesDialogIdTime] ASC,[SessionIdTime] ASC,[ReplacesDialogIdSeq] ASC,[CallFlag] ASC,[MediaTypes] ASC,[User1ClientVerId] ASC,[User2ClientVerId] ASC,[SessionIdSeq] ASC,[SessionStartedById] ASC,[User1Id] ASC,[User2Id] ASC,[CorrelationId] ASC,[ReferredById] ASC)
INCLUDE ([TargetUserId],[ResponseTime],[ResponseCode],[SessionEndTime]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IX_SessionDetails_Missing2] ON [dbo].[SessionDetails]
([CorrelationId] ASC,[SessionIdTime] ASC,[ReplacesDialogIdTime] ASC,[ReplacesDialogIdSeq] ASC,[CallFlag] ASC,[MediaTypes] ASC,[User1ClientVerId] ASC,[User2ClientVerId] ASC,[SessionIdSeq] ASC,[SessionStartedById] ASC,[User1Id] ASC,[User2Id] ASC,[ReferredById] ASC)
INCLUDE ([TargetUserId],[ResponseTime],[ResponseCode],[SessionEndTime]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IX_SessionDetails_Missing3] ON [dbo].[SessionDetails]
([ReplacesDialogIdTime] ASC,[ReplacesDialogIdSeq] ASC,[MediaTypes] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IX_SessionDetails_Missing4] ON [dbo].[SessionDetails]
([CorrelationId] ASC,[MediaTypes] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IX_SessionDetails_Missing5] ON [dbo].[SessionDetails]
([ReplacesDialogIdTime] ASC,[ReplacesDialogIdSeq] ASC,[SessionIdTime] ASC,[MediaTypes] ASC)
INCLUDE ([SessionIdSeq],[CorrelationId],[User1Id],[User2Id],[SessionStartedById],[User1ClientVerId],[User2ClientVerId],[CallFlag]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IX_SessionDetails_Missing6] ON [dbo].[SessionDetails]
([ReplacesDialogIdTime] ASC,[ReplacesDialogIdSeq] ASC,[SessionIdTime] ASC,[MediaTypes] ASC)
INCLUDE ([SessionIdSeq],[User1Id],[User2Id],[SessionStartedById],[ReferredById],[User1ClientVerId],[User2ClientVerId],[ResponseCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IX_SessionDetails_Missing7] ON [dbo].[SessionDetails]
([ReplacesDialogIdTime] ASC,[ReplacesDialogIdSeq] ASC,[SessionIdTime] ASC,[MediaTypes] ASC)
INCLUDE ([SessionIdSeq],[CorrelationId],[User1Id],[User2Id],[SessionStartedById],[User1ClientVerId],[User2ClientVerId],[ResponseCode],[CallFlag]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


After creating these indexes, the report was succesfully generated.