16 December 2014

Lync Response Group Usage Report very slow or not completing

In Lync 2013 it's possibly to generate reports in SQL reporting services. This works fine for all except the "Response Group Usage Report".

The problem is that the report never finish unless it's given a very short time window. I posted this in the Lync forum here and a very kind person who been in contact with Microsoft support posted the SQL script below.

The script creates two index that help speed up the stored procedure that fetch the data for the report.

Someone asked if this can be made more visibly so I created this blog / post. Enjoy!!
Please provide any feedback as this is my first blog / post about anything.

/*
USE [LcsCDR]
GO
DROP INDEX [IX_SessionDetails_CorrelationId_SessionIdTime] ON [dbo].[SessionDetails]
GO
*/

CREATE NONCLUSTERED INDEX [IX_SessionDetails_CorrelationId_SessionIdTime] 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 (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

/*
USE [LcsCDR]
GO
DROP INDEX [IX_SessionDetails_ReplacesDialogIdTime_SessionIdTime] ON [dbo].[SessionDetails]
GO
*/

CREATE NONCLUSTERED INDEX [IX_SessionDetails_ReplacesDialogIdTime_SessionIdTime] 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 (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

 

2 comments:

  1. Jackpotcity Casino - Goyang Tips
    Find our Jackpotcity Casino Review - 블루 벳 먹튀 100% up to 에밀리벳리카즈 $1600. Play Slots, Blackjack, Roulette, Video Poker 아시안부키 - our 해외 토토 사이트 exclusive Jackpotcity Casino welcome bonus. 라이트닝바카라

    ReplyDelete
  2. Play Free Online Game "Wild West - Gandasoft
    Play the original Wild West Wild West experience. Try your 토토카지노사이트 luck in this 예스카지노 awesome free online role-playing game! Learn the rules, rules, and rules behind

    ReplyDelete