Decreased SharePoint Performance with Unique Security Scopes on Large Lists

    Hello!

    In this article, we decided to share our life observations on the performance of large lists in SharePoint.

    So, we often encounter situations when there is a list on the SharePoint portal in which, for example, user requests are stored and processed. In addition, there is always a desire to make sure that all applications are stored in one place, on the other hand, that only the user who submitted the application has the rights to these elements and the group or groups of users who participate in its processing.

    First, a little terminology:

    ACL (Access Control List) - an ordered list of principals that determine the rights to an element or group of elements.

    Security scopes - a set of pairs of groups or user + ACL.

    Microsoft recommendations

    Microsoft recommends that you follow fairly simple rules: avoid more than 5,000 simultaneous Security Scopes per document library or list.

    Why is that? Where did the restriction come from? First, let's answer the question of how MS SharePoint processes the list request.

    1. Makes a call to the content database and reads:
      a. List metadata, i.e. defining field lists, etc.
      b. Computed fields, event handler definitions and more.
      c. All scope of the list (or rather presentation).
    2. On the front-end side, it determines: the user enters the scope.
    3. It pulls out the data directly.

    There are explanations for this approach, and they are associated with supporting the model of external providers of membership and roles in the first place (AD, i.e. AD is the most widespread provider of membership and roles).

    In practice, you can see that problems begin already with 1000 elements with unique rights.

    Using these SQL queries, you can calculate how many Security Scopes you currently have:

    SELECT [SiteId], [ScopeId], [RoleDefWebId], [WebId], [ScopeUrl], [Acl]   
    FROM [your Content DB].[dbo].[Perms]   order by scopeurl  
    


    An even better query exposes the web URL

    SELECT [fullurl],perms.[SiteId] , perms.[ScopeId], [RoleDefWebId], [WebId], [ScopeUrl], [Acl]   
    FROM [dbo].[Perms],[dbo].allwebs   
    where perms.WebId = allwebs.id
    order by scopeurl
    


    As an aside, you can see the number of security principals per scope, using this SQL:

    select COUNT(ra.PrincipalId) as [Count],p.ScopeUrl from RoleAssignment ra with(nolock) 
    join Perms p with(nolock) 
    on p.SiteId = ra.SiteId and p.ScopeId = ra.ScopeId 
    group by p.ScopeUrl 
    order by p.ScopeUrl desc
    


    How Microsoft suggests solving the problem:

    1. Use folders to encapsulate permissions on groups of items with similar rights.
    2. Split into different lists or document libraries and use inherited rights.
    3. Never go beyond the limit of 5,000 unique rights on a sheet.

    What the violation of these recommendations leads to:

    1. A 20% reduction in performance for every 1,000 unique permissions per sheet.
    2. Software errors when trying to break the rights on an element and issue unique ones.

    For MS SharePoint-based solutions, it is critical that the farm holds the load, and here's why.

    There is no transaction support for the developer. And sets of operations of the form (create an element, break rights, grant rights to it in MS SharePoint):
     var item = list.AddItem(...);
                        // ...
                        item.Update();
                        item.BreakRoleInheritance(false);
                        item.RoleAssignments.Add(new SPRoleAssignment(...));
    

    will interrupt in unexpected places, simply because the farm cannot withstand the load (i.e. not due to logical errors).

    It is clear that recommendations 2 and 3 are more chamber in nature and do not solve the problem of storing elements together and unique rights, so we will focus on the first as the most effective way - using folders.

    Our example

    For example, let's take a list from a really working system, in which there are currently 132,073 elements:



    There are 3,590 Security Scopes in it - it looks something like this:



    Inside the list there are 721 folders ...:



    ... for which rights are granted as follows:



    According to Google Analytics , the average page loading time is less than a second - at the level of visits of this particular list for this period of the order of 968 per day, during working hours.



    Our approach

    In accordance with our approach, rights are distributed like this:

      public static void ProcessItem(SPListItem item, SPFolder folder)
            {
                var file = item.Web.GetFile(item.UniqueId);
                // Внимание не используйте item.File т.к. он во многих случаях будет null
                // http://msdn.microsoft.com/ru-ru/library/microsoft.sharepoint.splistitem.file.aspx            
                file.MoveTo(string.Format("{0}/{1}", folder.Url, item[SPBuiltInFieldId.LinkFilename]));            
            }  
    

    I would also like to add that if the sets of rights differ not in visibility permissions, but in the possibility of change, you can use the Event Handler, which will prohibit changes.

     class PermissionEventHandler : SPItemEventReceiver
        {
            public override void  ItemUpdating(SPItemEventProperties properties)
            {
                var updatingAllowed = CheckUserHasPermission(properties.Web.CurrentUser, properties.ListItem);
                // определить может ли пользователь изменять элемент
                if (!updatingAllowed)
                {
                    properties.Cancel = true;
                    properties.ErrorMessage = "Access denied";
                }               
            }
            public override void ItemAdding(SPItemEventProperties properties)
            {
                //...
            }      
            //...
        }
    

    Before the invention of this approach, we had to work closely with SharePoint for 5 years, eat a lot of dogs and cacti. It has its own nuances, but it definitely works - it has already been tested on two portals.

    As always, we were happy to share the experience!

    Also popular now: