Between last week and this week I had probably one of the weirdest errors that I have faced.
After Installing Sharepoint 2013 SP 1, some users were complaining that it was taking a long time to load the pages on this site collection, specially the list views. However Administrators verified and we were not able to reproduce it, it was fast for us. So, what did we do?
- Sit together with the user, 4 seconds render time for us, over 1 minute for the user.
- We thought, windows updates, ie updates. We checked a VM with windows 8, Still the same, with our user its fast, with hers its slow
- We checked fidler, but this didnt help a lot, the .aspx render time was fast for us, slow for them.
- Then we had to enable the developer dashboard in production, and we noticed the following.
- For the adminstrators, the page load (on the dev dashboard), had only 4 SQL requests, 2 of them taking lets say 1000 milliseconds.
- For the users, the same page, had lots of sql requests, and 2 of them taking about 25 seconds each.
- This lead us to think, maybe its resources? but why fast for administrators? and not for users. We checked CPU and RAM on SQL Servers and Sharepoint Servers, the cpu was never at 100%, barely at 40%, even with those heavy queries.
- Then we made a experiment, we were on the web application user policy, we removed ourselves from there, and voila, it became slow to us again.
At this point we already knew that it was something related to security, we checked the logs to see, we saw some errors with distributed cache too slow, we disabled it because in some blog posts some users were claiming that after disabling it, performance would be a lot better.
We disabled it, result. No change.!
- Next step, review the queries in the developer dashboard. We noticed that the requests of an admin, the sql query was small, but for normal users, the sql query was huge!, I really meant ! huge, over 8000 chars.
- This immediately lead me to check security on the data itself, on the list items and documents.
- Well, we noticed that there is a workflow on the list items(document sets), that when it the workflow finishes, it breaks the security of the item, so we had 20,000 document sets, but 15,000 of them had broken security.! that means 15,000 security scopes in a simple library.
- Basically, the same problem this guy had, its what we noticed in our environment:
- Its not surprise to anyone, that whoever designed storage this way, had no future growth in mind, we all know that Microsoft recommends a maximum of 5000 security scopes per container
Check here:
http://technet.microsoft.com/en-us/library/cc262787(v=office.15).aspx
The maximum number of unique security scopes set for a list cannot exceed 50,000.
For most farms, we recommend that you consider lowering this limit to 5,000 unique scopes. For large lists, consider using a design that uses as few unique permissions as possible.
When the number of unique security scopes for a list exceeds the value of the list view threshold (set by default at 5,000 list items), additional SQL Server round trips take place when the list is viewed, which can adversely affect list view performance.
A scope is the security boundary for a securable object and any of its children that do not have a separate security boundary defined. A scope contains an Access Control List (ACL), but unlike NTFS ACLs, a scope can include security principals that are specific to SharePoint Server 2013. The members of an ACL for a scope can include Windows users, user accounts other than Windows users (such as forms-based accounts), Active Directory groups, or SharePoint groups.
SOLUTION
Its not really a solution, but just a temporary solution to the performance problem, we created a script that resets the permissions on those 15,000 items back to inherit from the library, and voila, performance is nice again with render times below 5 seconds.
I know this is not a solution, because we are changing the security, but my message with this blog post is actually, to consider your future growth when you need to break permissions in your workflows, in our case this library growths so fast that Sharepoint Couldnt handle so many unique security scopes.
SCRIPT TO RESET PERMISSIONS
param(
$vars = @{
"BillingSiteUrl" = "http://ourdomain.com/sites/subsite1"
}
)
function Reset-RoleInheritance {
param(
[Microsoft.SharePoint.SPListItem]$listItem
)
if(-not $listItem.HasUniqueRoleAssignments) {
Write-Host "No unique permissions on" $listItem.Name
return
}
foreach($roleAssignment in $listItem.RoleAssignments) {
$roleDefinitionBindings = $roleAssignment.RoleDefinitionBindings | % { $_.Name }
$roleDefinitionBindingsString = [String]::Join("|",$roleDefinitionBindings)
$logMessage = ("{0};{1};{2}" -f $listItem.Name, $roleAssignment.Member.ToString(), $roleDefinitionBindingsString)
Add-Content -Path "roleAssignments.log" -Value $logMessage
}
$listItem.ResetRoleInheritance()
Write-Host "Unique permissions removed on" $listItem.Name
}
# Variables
$siteUrl = $vars["SiteUrl"]
$web = Get-SPWeb $siteUrl
$list = "ListName"
$statusFieldName = "StatusColumn"
$completedStatusValue = "Completed"
$listref = $web.Lists[$list]
### Check in all the files
#Check-InAllFiles $billCyclesList
### Get All Completed Document Sets Query
$query = New-Object Microsoft.SharePoint.SPQuery;
$query.Query =
(" <Where>
<And>
<Eq>
<FieldRef Name='{0}' />
<Value Type='Text'>{1}</Value>
</Eq>
<Eq>
<FieldRef Name='Is_x0020_Confidential_x003F_' />
<Value Type='Boolean'>0</Value>
</Eq>
</And>
</Where>" -f $statusFieldName, $completedStatusValue
);
$query.RowLimit = 100
$batch = 0
do {
$items = $listref.GetItems($query);
foreach($docSet in $items) {
Reset-RoleInheritance $docSet
}
$query.ListItemCollectionPosition = $items.ListItemCollectionPosition
$batch += 1
Write-Host "Processed batch" $batch
if($items.ListItemCollectionPosition -ne $null) {
Write-Host $items.ListItemCollectionPosition.PagingInfo
}
} while($query.ListItemCollectionPosition -ne $null)
Original post here: http://www.luisevalencia.com/2014/06/18/troubleshooting-slow-sharepoint-2013-list-views/