Decreased SharePoint performance while increasing unique Security Scopes on large lists

Hello!

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

So, we quite often come across situations where 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 ensure that all applications are stored in one place, on the other hand, only the user who submitted the application and the group or groups of users who participate in its processing have rights to these elements.
')
First, some terminology:

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

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

Microsoft recommendations

Microsoft recommends following fairly simple rules: avoid the number of simultaneous Security Scopes of more than 5,000 per document library or per list.

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

1. Makes a call to the database content and reads:
   a. List metadata, i.e. definition of field lists, etc.
   b. Computed fields, event handler definitions, and more.
   c. All scope of the list (more precisely the presentation).
2. On the side, the front-end determines: the user falls into scope.
3. Pulls already directly data.

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

In practice, it can be seen that problems start already with 1000 elements with unique rights.

Using these SQL queries, you can calculate how much 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 proposes to solve the problem:

1. Use folders to encapsulate permissions on groups of elements 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 to the sheet.

What causes the violation of these recommendations:

1. 20% decrease in performance for every 1,000 unique rights on a sheet.
2. Software errors when attempting to sever rights on an element and issue unique ones.

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

For the developer there is no transaction support. And a set of operations of the form (create an element, break rights, issue rights to it in MS SharePoint):

  var item = list.AddItem(...); // ... item.Update(); item.BreakRoleInheritance(false); item.RoleAssignments.Add(new SPRoleAssignment(...)); 

will be interrupted in unexpected places, simply because the farm does not withstand the load (ie, not because of logical errors).

It is clear that recommendations 2 and 3 are of a more intimate nature and do not solve the problem of storing elements together and unique rights, so we’ll look at the first as the most effective way - using folders.

Our example

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



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



Inside the list 721 folder ...:



... for which rights are granted as follows:



According to Google Analytics, the average page load time is less than a second - at the level of visits to this particular list for this period, about 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 do not differ in permissions to read visibility, but in the possibility of change, you can use an 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 engage in SharePoint for 5 years, eat a lot of dogs and cacti. It has its own nuances, but it works absolutely exactly - it has already been tested on two portals.

As always, we were glad to share our experience!