Social media


            

Friday, March 11, 2011

SharePoint performance and setting the item permissions with a workflow

When you model processes in your environment you will sooner or later come upon a request to change a permissions of the item depending on some of the item's meta-data - the author, people assigned as approvers, divisions, categories etc.

The most natural way to tackle such request would be to have a event-handler or a workflow running when item is created or modified and modify current item's permissions. It very simple with Workbox - you just need to use the "set item permissions" activity - you can read more in this post.

If you deploy it on some small list with small number of users then everything will work fine. If you try to implement it on larger list (few thousand items) with larger number of users then you may run into serious performance issues. You can experience long page load times, very high load on the SQL processor, timeouts for queries and workflow crashes.


The information about  unique permission limits can be found here for 2007 and here for 2010 (look for the "Security scope") but it's worth clarifying what it exactly means.

For both SharePoint 2007 and 2010 there are limits of 1000 unique security scopes for a list. Microsoft defines a security scope as a "security boundary for a securable object and any of its children that do not have a separate security boundary defined".
In other words this is a definition of permissions a single item, folder or list. If you do not break the inheritance for any item, the total number of scopes for the list will be equal to 1. If you break inheritance for 2 items then the total number of  scopes for the list will be 3 - the scope for the list itself + the scopes for each item. But number of scope is not the full picture. The size of the scope also matters - if you set permissions on user levels, then the list will automatically get new entry in the scope for each user with the "Limited access" permission level, which then can be inherited by items that have their permission broken and before you know you end up with a large number of very large security scopes.

The problem is not related to Workbox or a workflow in general - the cause is laying in the way SharePoint tackles the permission settings.I guess that when the SharePoint team has designed this functionality they did not think that people would break the inheritance this much. Having a workflow do it automatically for you can get you very quickly past the recommended limits.

What's worth mentioning is that Microsoft states that in case of exceeding the limits the impact of the performance degradation will be the entire farm not only the list where the security scope exist! This is something I can confirm because we have found out this problem on our own while deploying a solution for our customer (This limitation was documented only in the second half of 2010) - when our farm started to run terribly slow we started looking for the cause and after analyzing all the queries and SQL tables we saw what has happened.
The reason for this behavior is that SQL loads the ACLs into each list and item query and list of ACLs can have even few megabytes. And it's being loaded with EVERY query which brings your SQL to its knees. Here is a good article explaining this issue details http://e-junkie-chronicles.blogspot.com/2011/03/sharepoint-2010-performance-with-item_23.html

There is no good workaround for this problem.
Definitely you need to read this Best Practices document from Microsoft - http://technet.microsoft.com/en-us/library/gg128955.aspx.
One of the solutions could be using folders - you can set up permissions for few folders and then put items in those folders based on their parameters. The benefit from this solution is that permission inheritance is broken only on the folder level, so even if you have few thousand items in each folder, they all inherit permission scopes from the folder, so the number of scopes does not rise with each new item. This solution has few downsides - you need to have a limited number of security combinations (like only A, only B, only A&B, only A&B&C, only B&C, etc), because if you would like to have a separate folders for all possible combination for 100 users then you will get (number of possible permissions)^(number of users) folders. For only 10 users and 3 different permissions you get 60k folders, which gets you nowhere. Second problem is that SharePoint does not allow you move an item into a folder - you need to create a new one in the folder and delete the existing one. And once you do it you loose your workflow, workflow history, change the item ID and link to it and will need to tackle it as well.
Other solution is to remove everyone's permission to such list and create your own web parts that will run with elevated privileges and will take care of the presentation of the list and it's items and restricting permissions. This means that SharePoint list would serve only as a database and you would need to code all other functionality (list views, display and edit forms, web services, etc) yourself.