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

  • 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
    

Related