Contents

SharePoint performance issues caused by high amount of unique permissions

Contents

Every SharePoint admin have heard or read at least once that it is a bad practice to assign unique permissions to individual items in SharePoint. Yes, it is not good for content manageability, but why else it can be bad?

As the title says it harms performance of your SharePoint. So if you have performance issues keep reading to know if it is related to permissions.

Symptoms

  • Page loading is very slow on specific site collection(s), often leading to time-out errors
  • Workflows failing to start (farm-wide)
  • Large number of WAIT locks on SQL server

At the first glance it seemed the workflows were causing performance issues (there is Nintex Workflow installed with a lot of complex workflows implemented). I’ve noticed the workflow timer job was stuck. Another symptom was a high level of waiting processes on the SQL server.

Quick workaround

To make site accessible to users with no risk of time-out errors the ‘Workflows’ timer job could be stopped. After that the amount of awaiting processes on the SQL DB dropped down to zero and performance was restored. But of course it could not be a permanent solution, you can’t just disable workflows on the farm.

Root cause

So why workflows were causing WAIT locks and performance degradation? It looks like every time a workflow needs to access a list item, SharePoint tries to ensure that the workflow is actually allowed to read this item. Workflows do some list queries often and if a lot of items (or even all of them) have unique permissions this could cause trouble.

There is a Technet article explaining the fine-grained permissions and security scopes in SharePoint: Fine-grained permission reference for SharePoint Server 2013. They say:

There is no maximum number of scopes that can be created in a parent scope. But after you have created 1,000 scopes, a code path that requires additional Microsoft® SQL Server round trips to analyze the scopes before rendering a view is used. When there are 1,000 or fewer scopes, only one roundtrip is required.In SharePoint 2013, the limit of number of scopes returned before switching to a different algorithm is based on a query throttle limit that has a default value of 5,000. This value, even at default, can be large enough to significantly detract from performance.

There is also an older KB article (https://support.microsoft.com/kb/2420771) which describes these limitations for previous SharePoint versions.

Please also note that even if MS say in one article “there is no maximum number of scopes in a parent scope” there is actually a limit of 50000 security scopes for a single list or library (as it is said in another article: Best practices for using fine-grained permissions in SharePoint Server 2013, see “Too many scopes in a list” section)

Diagnostics

So if you have some performance issues how to calculate these security scopes (or unique permissions which are the same for the diagnostic purpose) to be sure they are the cause?

At first, you need to get info on all uniquely assigned permissions. Here is the script which does it for all web applications and stores the output into a CSV file

$reportPath = [System.String]::Format("c:\\Reports\\Permissions-{0}.csv", [System.DateTime]::Now.ToString("yyyyMMdd-HHmm"))

function Get-RoleAssignments($title, $url, $type, $roleAssignments, $customPermissionLevels) {
 foreach ($roleAssignment in $roleAssignments) {
  if ($roleAssignment.Member.GetType().Name -ne "SPUser") {
   continue
  }

  $permissions = [System.String]::Empty
  foreach ($roleDefinitionBinding in $roleAssignment.RoleDefinitionBindings) {
   if ($roleDefinitionBinding.Name -eq "Limited Access") {
    continue
   }

   if ($permissions -eq [System.String]::Empty) {
    $permissions = $roleDefinitionBinding.Name
   } else {
    $permissions = [System.String]::Format("{0}, {1}", $permissions, $roleDefinitionBinding.Name)
   }
  }

  if ($permissions -eq [System.String]::Empty) {
   continue
  }

  $member = [Microsoft.SharePoint.SPUser]($roleAssignment.Member)
  $principal = $member.LoginName
  if (![System.String]::IsNullOrEmpty($member.Email)) {
   #$principal = [System.String]::Format("{0} ({1})", $principal, $member.Email)
  }

  $principalType = "User"
  if ($member.IsDomainGroup) {
   $principalType = "Group"
  }
  "$title`t$url`t$type`t$principal`t$principalType`t$permissions`t$customPermissionLevels" `
    | Out-File $reportPath -append
 }
}

$webApplications = Get-SPWebApplication

foreach ($webApplication in $webApplications) {
 $webApplicationUrl = $webApplication.GetResponseUri([Microsoft.SharePoint.Administration.SPUrlZone]::Default)
 foreach ($siteCollection in $webApplication.Sites) {
  foreach ($web in $siteCollection.AllWebs) {
   Write-Host $web.Url

   if ($web.HasUniqueRoleAssignments) {
    $customPermissionLevels = "0"
    if ($web.HasUniqueRoleDefinitions) {
     $customPermissionLevels = "1"
    }

    Get-RoleAssignments $web.Title $web.Url "Web site" $web.RoleAssignments $customPermissionLevels
   }

   foreach ($list in $web.Lists) {
    if ($list.Hidden) {
     continue;
    }

    if ($list.HasUniqueRoleAssignments) {
     $listUrl = [System.String]::Format("{0}{1}", $webApplicationUrl.ToString().Trim('/'), $list.DefaultViewUrl)
     Get-RoleAssignments $list.Title $listUrl "List" $list.RoleAssignments "See parent web site"
    }

    foreach ($folder in $list.Folders) {
     if ($folder.HasUniqueRoleAssignments) {
      $folderUrl = [System.String]::Format("{0}{1}", $webApplicationUrl.ToString(), $folder.Url)
      Get-RoleAssignments $folder.Name $folderUrl "Folder" $folder.RoleAssignments "See parent web site"
     }
    }
   }

   $web.Dispose()
  }

  $siteCollection.Dispose()
 }
} 

I use separate scripts to gather information and process it to have a certain flexibility. So the next script counts unique permissions per URL and saves it into another CSV file

$SiteURLFilter = "http://mysite/affectedsitecollection/*"
$ReportFolder = 'C:\Reports'
$LatestReport = Get-ChildItem -Path $ReportFolder -Filter "Permissions-*" `
  | Sort-Object LastAccessTime -Descending `
  | Select-Object -First 1

$data = Import-Csv $LatestReport -Delimiter "`t" `
  | where {$_.URL -like $SiteURLFilter} `
  | Group-Object -Property URL -NoElement `
  | Sort-Object -Property Count -Descending
$data | Select -Property count,name `
  | Export-Csv (Join-Path $ReportFolder 'scopes_report.csv') -NoTypeInformation

If you open a resulting report and see a number of unique permissions larger than 5000 - this is a distinct sign you are getting in trouble with security scopes and performance issues may be caused by it.

I hope this will help to someone! Thanks for the reading and feel free to share the knowledge!