cs_GroupMembers_Delete SPROC taking over 20 seconds to execute

When un-joining a public-open group the sproc: cs_GroupMembers_Delete can take upwards of 20 seconds to execute.  We first thought the link was broken, and then once we waited forever noticed it eventually executed and worked fine.  I used Alex Crome's performance profiler to track it down to this sproc and duration.

We are on 7.6.10

  • Would you be able to provide the execution plan for this?  This could be obtained either by SQL Profiler, or manually running the sproc in SQL Management Studio.

  • In reply to Luke D:

    Cheers Luke.

    Can you add the following two indexes and see if these help:

     
    --cs_GroupMembers_Delete performance
    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.cs_Messaging_ActivityMessages') AND name = N'IX_cs_Messaging_ActivityMessages_Type_Inc_GroupId')
    BEGIN
        EXECUTE sp_executesql N'
        CREATE NONCLUSTERED INDEX IX_cs_Messaging_ActivityMessages_Type_Inc_GroupId
        ON dbo.cs_Messaging_ActivityMessages (Type, GroupId)'
    END
    
    IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.cs_Messaging_Messages') AND name = N'IX_cs_Messaging_Messages_DateCreated_Inc_AuthorId')
    BEGIN
        EXECUTE sp_executesql N'
        CREATE NONCLUSTERED INDEX IX_cs_Messaging_Messages_DateCreated_Inc_AuthorId
        ON dbo.cs_Messaging_Messages (DateCreated)
        INCLUDE (AuthorId)'
    END
    

  • In reply to Alex Crome:

    That appears to really speed things up.  Are these included in any sort of hotfix or should we apply them as a one-off?

  • In reply to Luke D:

    These are not in a hotfix, but have been added for v.next.  You should apply these as a one off.  When you degrade to v.next, the upgrade will be able to detect you've already got these indexes so that won't cause any issues.

Related