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.

Access denied when creating local databases

When I was installing a new Skype for Business Server 2015 frontend server pool for a customer, installation of the Skype for Business components failed with this useless error messages: 0x80004005 (Unspecified error) and failure code 1603 (which is a generic error code).

Digging into the LCSSetup_Commands.log showed this entry:

Creating database rtcdyn from scratch. Data File Path = D:\CsData\RtcDatabaseStore\rtclocal\DynDbPath, Log File Path= E:\CsData\RtcDatabaseStore\rtclocal\DynLogPath.
Exception Stack:
Type: Microsoft.SqlServer.Management.Smo.FailedOperationException, Message: Create failed for Database 'rtcdyn'.
Type: Microsoft.SqlServer.Management.Common.ExecutionFailureException, Message: An exception occurred while executing a Transact-SQL statement or batch.
Type: System.Data.SqlClient.SqlException, Message: CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'D:\CsData\RtcDatabaseStore\rtclocal\DynDbPath\rtcdyn.mdf'.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Access denied from the operating system?? Investigating the Security eventlog I found som of these entries: 

Issue is related to insufficient filesystem priviliges.

Checking the filesystem priviliges, I found that the special permissions not granted in the picture above, was missing from the root of the drive. 

How it should look like:

Adding the default user permissions the root of the drive fixed the problem and the installation of the server could continue.

Problem was caused by additional security settings made by the customer.