none
Slow Query Duration: Slow Query StackTrace-Managed: at Microsoft.SharePoint.Utilities.SqlSession.OnPostExecuteCommand on list item setting permission RRS feed

  • Question

  • Slow Query Duration: 10192.45750165624 Slow Query StackTrace-Managed:
    at Microsoft.SharePoint.Utilities.SqlSession.OnPostExecuteCommand(SqlCommand command, SqlQueryData monitoringData)
    at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)
    at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock)
    at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadlock)
    at Microsoft.SharePoint.Library.SPRequestInternalClass.UpdateRoleAssignment(String bstrUrl, UInt32 dwObjectType, String bstrObjId, Guid& pguidScopeId, Int32 lPrincipalID, Boolean bAddToCurrentScopeOnly, Boolean bAllowAddToLimitedAccess, Object& pvarArrIdRolesToAdd, Object& pvarArrIdRolesToRemove)
    at Microsoft.SharePoint.Library.SPRequestInternalClass.UpdateRoleAssignment(String bstrUrl, UInt32 dwObjectType, String bstrObjId, Guid& pguidScopeId, Int32 lPrincipalID, Boolean bAddToCurrentScopeOnly, Boolean bAllowAddToLimitedAccess, Object& pvarArrIdRolesToAdd, Object& pvarArrIdRolesToRemove)
    at Microsoft.SharePoint.Library.SPRequest.UpdateRoleAssignment(String bstrUrl, UInt32 dwObjectType, String bstrObjId, Guid& pguidScopeId, Int32 lPrincipalID, Boolean bAddToCurrentScopeOnly, Boolean bAllowAddToLimitedAccess, Object& pvarArrIdRolesToAdd, Object& pvarArrIdRolesToRemove)
    at Microsoft.SharePoint.SPRoleAssignmentCollection.UpdateAssignment(Int32 principalId, SPRoleDefinitionBindingCollection bindings, List`1 originalRoleBindingIds, Boolean addOnly, Boolean addToCurrentScopeOnly, Boolean allowAddToLimitedAccess)
    at Microsoft.SharePoint.SPRoleAssignment.Update()
    at ADEHS.DAL.UserActionLogs.<>c__DisplayClassd.b__c()
    at Microsoft.SharePoint.SPSecurity.<>c__DisplayClass5.b__3()
    at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode)
    at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(WaitCallback secureCode, Object param)
    at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(CodeToRunElevated secureCode)
    at ADEHS.DAL.UserActionLogs.SendNotificationToEntity(String entityId, String entityUserGroupName, String title, String title_ar, String comments, String comments_ar, SPUser created_by, SPSite site)
    at ADEHS.Webparts.Module4.PerformanceReporting.FormE.FormE.FormE.sendNotification(FormEEntity formE)
    at ADEHS.Webparts.Module4.PerformanceReporting.FormE.FormE.FormE.SaveFormE(Boolean isDraft)

    1) What code is doing is adding one list item and setting permission. List have just one text column apart from "Title". "UserActionLogs" custom List has around 260 items only and item added with RWEP and list is inheriting permission from parent site.

    2) Its variation used site collection for English and Arabic version

    3) NICs supports Gbps bandwidth and are on AUTO mode

    4) Indexes were created on CONTENTTYPE but no help

    slow query

    Sunday, January 29, 2017 9:31 AM

Answers

  • Dear Victoria,

    The issue not yet resolved but there is a progress. After a long email chain with Microsoft expert, they suggested below :

    Please check your content database and verify that you have following Stored Procedure (“proc_SecRepairOrphanRoleAssignments”

    )  is present under it.

    storedProc responsible for cleaning roleassignmentTable

    This feature has been added and this kind of issues has fixed on version 15.0.4605 or higher ,

    If you have missing this stored procedure that means you have using earlier version of SharePoint and not updated latest or near to latest cumulative updates . 

    In that condition , 

    • First check that you have using version 15.0.4569.1000 this is a “NOT SUPPORTED” version . It is old SP1 and has been retracted and released as 15.0.4571.1502 (which has supported one)

    You can use following PowerShell script to see your base line.

    https://blogs.technet.microsoft.com/stefan_gossner/2015/04/20/powershell-script-to-display-version-info-for-installed-sharepoint-product-and-language-packs/

    • If you have this condition please first upgrade your system with rereleased SP1 first.
    • Then Upgrade your SharePoint with Latest CU which is currently ​“January 2017 CU (15.0.4893.1001)”

     After upgrade operation completes that SPContentDatabase.Repair($true)  function should resolve the issue. 

    Once issue is closed, I will update thread. After upgrade too, it did not remove or reduced row count in RoleAssignment, we are in touch.

    • Marked as answer by ShahidAliK Wednesday, March 8, 2017 5:01 AM
    Sunday, February 26, 2017 7:55 AM

All replies

  • Hi Shahid,

    Did you try to set unique permission on the list items?

    If yes, please check the RoleAssignment table in the content database to see if there are too many records in it.

    When we stop permission inheritance for an item, all the role assignments for the list item will be added to the table.

    Best Regards,

    Victoria

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, January 30, 2017 8:33 AM
    Moderator
  • Dear Victoria,

    I think you hit the nail, this looks root cause of exception above. Table "RoleAssignment" table has 17,549,723 (17 million!) records. I find this abnormal.

    A brieft history about "UserActionLogs" custom list is, it had around 13K records in English site and Arabic site had 10K items all with unique permissions. We deleted all those list items (items deleted using ECMA script, because I did not want to slow down production at any time). Usage is, these are on screen, user specific notifications which are obsolete after a month or so. This design worked for a year, but now failing.

    Now both lists have 800 and 500 items respectively in them, but still getting issue mentioned above.

    Could these many records is root cause of this issue? then how to remove only records which belongs to the items i deleted from "UserActionLogs" ?

    public static void SendNotificationToEntity(string entityId, string entityUserGroupName, string title, string title_ar, string comments, string comments_ar, SPUser created_by, SPSite site = null)
            {
    
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    if (site == null)
                        site = new SPSite(SPContext.Current.Site.Url);
                    using (site)
                    {
                        try
                        {
    
                            SPWeb root_web = site.OpenWeb("/");
                            SPWeb en_web = site.OpenWeb("/en/");
                            SPWeb ar_web = site.OpenWeb("/ar/");
    
    
                            if (en_web != null)
                            {
                                en_web.AllowUnsafeUpdates = true;
                                ar_web.AllowUnsafeUpdates = true;
    
                                SPList list = en_web.GetList(SPUrlUtility.CombineUrl(site.RootWeb.ServerRelativeUrl, "/en/Lists/" + ListTitles.UserActionLogs));
                                SPList list_ar = ar_web.GetList(SPUrlUtility.CombineUrl(site.RootWeb.ServerRelativeUrl, "/ar/Lists/" + ListTitles.UserActionLogs));
    
                                if (list != null)
                                {
                                    SPListItem item = list.AddItem();
                                    SPListItem item_ar = list_ar.AddItem();
    
                                    item[SiteColumns.Title] = title;
                                    item_ar[SiteColumns.Title] = title_ar;
    
                                    if (created_by != null)
                                    {
                                        item[SiteColumns.Author] = created_by;
                                        item_ar[SiteColumns.Author] = created_by;
                                    }
                                    if (!string.IsNullOrEmpty(comments))
                                    {
                                        item[SiteColumns.Comments] = comments;
                                        item_ar[SiteColumns.Comments] = comments_ar;
                                    }
    
                                    item.Update();
                                    item_ar.Update();
    
                                    SPList entityList = root_web.GetList(SPUrlUtility.CombineUrl(root_web.ServerRelativeUrl, "/Lists/" + ListTitles.Entity));
                                    SPListItem entity = entityList.GetItemById(int.Parse(entityId));
                                    List<SPPrincipal> groups = new List<SPPrincipal>();
    
                                    if (entity != null)
                                    {
                                        object reportingSRA = entity[EntityList.ReportingSRAID];
                                        if (reportingSRA != null)
                                        {
                                            SPFieldLookupValue approvalStatus = new SPFieldLookupValue(reportingSRA.ToString());
                                            string sraId = approvalStatus.LookupValue;
                                            SPList sraList = root_web.GetList(SPUrlUtility.CombineUrl(root_web.ServerRelativeUrl, "/Lists/" + ListTitles.SRA));
                                            SPListItem sra = sraList.GetItemById(int.Parse(sraId));
                                            object sectorName = sra["Sector_x003a_Title"];
                                            if (sectorName != null)
                                            {
                                                approvalStatus = new SPFieldLookupValue(sectorName.ToString());
                                                string sector = approvalStatus.LookupValue;
                                                SPGroup entityAdminGroup = getCorrespondingSharepointGroup(sector + " Entity Administrators", site);
                                                groups.Add(entityAdminGroup);
    
                                                switch (entityUserGroupName)
                                                {
                                                    case API.SharePoint.Common.Constants.GroupEntityMemberTitle:
                                                        SPGroup entityMemberGroup = getCorrespondingSharepointGroup(sector + " " + API.SharePoint.Common.Constants.GroupEntityMemberTitle, site);
                                                        groups.Add(entityMemberGroup);
                                                        break;
                                                    default:
                                                        SPListItemCollection items = UserMapDB.GetUserMapEntryById(entityId);
                                                        foreach (SPListItem temp in items)
                                                        {
                                                            object emailAddress = temp["EmailAddress"];
                                                            if (emailAddress != null)
                                                            {
                                                                SPUser user = site.RootWeb.EnsureUser("i:0#.f|ldapmember|" + emailAddress);
                                                                bool isExistInGroup = UserDB.InGroup(user, sector + " " + entityUserGroupName);
                                                                if (isExistInGroup)
                                                                    groups.Add(user);
                                                            }
                                                        }
                                                        break;
                                                }
                                            }
                                        }
                                    }
    
                                    if (groups.Count > 0)
                                    {
                                        en_web.AllowUnsafeUpdates = true;
                                        ar_web.AllowUnsafeUpdates = true;
                                        item.BreakRoleInheritance(true);
    
                                        SPRoleAssignmentCollection SPRoleAssColn = item.RoleAssignments;
                                        for (int i = SPRoleAssColn.Count - 1; i >= 0; i--)
                                        {
    
                                            SPRoleAssColn[i].RoleDefinitionBindings.RemoveAll();
                                            SPRoleAssColn[i].Update();
    
                                        }
    
                                        SPRoleDefinition rDefination = site.RootWeb.RoleDefinitions.GetByType(SPRoleType.Reader);
                                        foreach (SPPrincipal group in groups)
                                        {
                                            if (group != null)
                                            {
                                                SPRoleAssignment roleassignment_group = new SPRoleAssignment(group);
                                                roleassignment_group.RoleDefinitionBindings.Add(rDefination);
    
                                                item.RoleAssignments.Add(roleassignment_group);
                                            }
                                        }
                                    }
    
                                    item.Update();
    
                                    if (groups.Count > 0)
                                    {
                                        en_web.AllowUnsafeUpdates = true;
                                        ar_web.AllowUnsafeUpdates = true;
                                        item_ar.BreakRoleInheritance(true);
    
                                        SPRoleAssignmentCollection SPRoleAssColn = item_ar.RoleAssignments;
                                        for (int i = SPRoleAssColn.Count - 1; i >= 0; i--)
                                        {
    
                                            SPRoleAssColn[i].RoleDefinitionBindings.RemoveAll();
                                            SPRoleAssColn[i].Update();
    
                                        }
    
                                        SPRoleDefinition rDefination = site.RootWeb.RoleDefinitions.GetByType(SPRoleType.Reader);
                                        foreach (SPPrincipal group in groups)
                                        {
                                            if (group != null)
                                            {
                                                SPRoleAssignment roleassignment_group = new SPRoleAssignment(group);
                                                roleassignment_group.RoleDefinitionBindings.Add(rDefination);
                                                item_ar.RoleAssignments.Add(roleassignment_group);
                                            }
                                        }
                                    }
    
                                    item_ar.Update();
    
                                }
                                else
                                    LoggingService.LogErrorInULS("Error : List '" + ListTitles.UserActionLogs + "' not found.");
    
                                en_web.AllowUnsafeUpdates = false;
                                ar_web.AllowUnsafeUpdates = false;
                                en_web.Dispose();
                                ar_web.Dispose();
                                root_web.Dispose();
                            }
                        }
                        catch (Exception ex)
                        {
                            LoggingService.LogErrorInULS("EHS: Error while adding item in logs : " + ex.Message + "StackTrace: " + ex.StackTrace);
                            //throw ex;
                        }
                    }
    
                });
    
            }
    


    • Edited by ShahidAliK Wednesday, February 1, 2017 5:26 AM code and grammer update
    Wednesday, February 1, 2017 5:10 AM
  • Hi Shahid,

    After you delete these items, did you check the records in the RoleAssignment table?

    If not, please check it first to see how many records are left in the table.

    Best Regards,

    Victoria


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, February 2, 2017 2:40 AM
    Moderator
  • Hi Victoria,

    Yes, As I mentioned that I already deleted unwanted list items after deletion DB table "RoleAssignment" still has 17+ million records.

    Yesterday, we ran Powershell command ContentDB.repair($true) on content DB, it removed only two orphaned records. Now how to check or find why updating permission this list items are throwing exception?


    Thursday, February 2, 2017 3:52 AM
  • Hi Shahid,

    Per my knowledge, if delete the list item, then the related role assignments will also be deleted in RoleAssignment table.

    You can check the RoleAssignment and AllDocs table together to see if there are any records in RoleAssignment table which are not associated with any items in the AllDocs table by using the ScopeId as a filter.

    Best Regards,

    Victoria


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, February 3, 2017 6:06 AM
    Moderator
  • Hi Victoria,

    Yes, there are unrelated records which are in large number.

    1) So can I safely run below query? 

    delete from RoleAssignment where ScopeId not in (select ScopeId from alldocs)

    I have only this "UserActionLogs" which has unique item permissions.

    2) Why is this happening? How can I avoid recurrence of this situation?

    3) Shall I re-create this list with "stop inheritance" and remove all groups (442 groups in this case) access and code will add new list items with unique permission?

    Your help is appreciated.


    • Edited by ShahidAliK Friday, February 3, 2017 7:54 PM updated query
    • Marked as answer by ShahidAliK Friday, February 3, 2017 7:54 PM
    • Unmarked as answer by ShahidAliK Friday, February 3, 2017 7:54 PM
    Friday, February 3, 2017 7:50 PM
  • Hi Shahid,

    Not only when list items have unique permissions, the records will be added to RoleAssignment table, all the other items which do not inherited from the site even the site collection, the records will be added to the RoleAssignment table.

    It is not recommended to modify the database directly.

    I recommend to remove all the unique permissions for all the lists and items in the site and then check happens.

    When adding a new list item without setting its permission, the list item will be inherited from its parent's permission by default.

    Best Regards,

    Victoria


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, February 6, 2017 5:53 AM
    Moderator
  • Dear Victoria, 

    I double checked, only two these two custom lists "UserActionsLogs" have broken inhertance only. Rest of complete site artifacts inheriting permissions from parent.

    I found 15+ million records as a result of following query;

    select count(*) "NOT PRESENT" from RoleAssignment where ScopeId not in (select ScopeId from alldocs)

    Now we will find out different approach to meet business requirement but if you can help in removing/reducing rows in "RoleAssignment" would be helpful.

    I also tried below script but it reduced only approx. 3000 records from "RoleAssignment" table. Now my problem is how to reduce the large number of records :(

    $web = Get-SPWeb "http://info-dxb-sp01/en/";
    $spGroups = $web.sitegroups
    $list = $web.Lists["UserActionLogs"];
    
    $list.BreakRoleInheritance($true);
    $roleAssignments = $list.RoleAssignments; 
    
    ForEach($group in $spGroups) {
    write-host "Removing group named: " $group.name 
    $roleAssignments.Remove($group); 
    }
    $web.Dispose();


    • Edited by ShahidAliK Monday, February 6, 2017 6:26 AM update in info.
    Monday, February 6, 2017 6:24 AM
  • Hi Shahid,

    I recommend to use SQL Profile Trace tool to monitor the process when setting a list item permission to make sure that the issue is related with the table RoleAssignment table.

    If that is the case, you can backup the content database first and then re-move the unrelated records to see what happens.

    Best Regards,

    Victoria 


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Tuesday, February 7, 2017 2:46 AM
    Moderator
  • Dear Victoria,

    Thank you I will try then update this thread. Your replies appreciated.

    Regards,

    Shahid Ahmed.


    • Edited by ShahidAliK Tuesday, February 7, 2017 6:38 AM
    Tuesday, February 7, 2017 6:36 AM
  • Hi Shahid,

    I am checking to how is everything going.

    Was your issue resolved?

    Best Regards,

    Victoria


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, February 13, 2017 2:48 AM
    Moderator
  • Hi Victoria,

    It was not possible to try on production as all the time users are alive. I have opened a premium (enterprise) ticket with Microsoft support. Am waiting on it.

    If we find any solution, I will update this thread. Your replies and follow-up appreciated.

    Tuesday, February 14, 2017 5:14 AM
  • Dear Victoria,

    The issue not yet resolved but there is a progress. After a long email chain with Microsoft expert, they suggested below :

    Please check your content database and verify that you have following Stored Procedure (“proc_SecRepairOrphanRoleAssignments”

    )  is present under it.

    storedProc responsible for cleaning roleassignmentTable

    This feature has been added and this kind of issues has fixed on version 15.0.4605 or higher ,

    If you have missing this stored procedure that means you have using earlier version of SharePoint and not updated latest or near to latest cumulative updates . 

    In that condition , 

    • First check that you have using version 15.0.4569.1000 this is a “NOT SUPPORTED” version . It is old SP1 and has been retracted and released as 15.0.4571.1502 (which has supported one)

    You can use following PowerShell script to see your base line.

    https://blogs.technet.microsoft.com/stefan_gossner/2015/04/20/powershell-script-to-display-version-info-for-installed-sharepoint-product-and-language-packs/

    • If you have this condition please first upgrade your system with rereleased SP1 first.
    • Then Upgrade your SharePoint with Latest CU which is currently ​“January 2017 CU (15.0.4893.1001)”

     After upgrade operation completes that SPContentDatabase.Repair($true)  function should resolve the issue. 

    Once issue is closed, I will update thread. After upgrade too, it did not remove or reduced row count in RoleAssignment, we are in touch.

    • Marked as answer by ShahidAliK Wednesday, March 8, 2017 5:01 AM
    Sunday, February 26, 2017 7:55 AM
  • Yes, issue is resolved, please find details below;

    This stored procedure responsible was missing over the content database due to the farm was not updated with the required CU which had this stored procedure. Following was done to resolve it.

    • Update the farm with rereleased SP1 for 2013
    • Update the farm to the latest CU “Jan 2017” and install latest Arabic language pack
    • Run db.repair($true)
    The stored procedure started deleting the orphaned records.
    Wednesday, March 8, 2017 5:01 AM