Share via

Why your SharePoint list is so slow when it has more than 5,000 items with separate permissions.

  • Article

It has been several years, since the release of SP2013, that many people complain the "5,000 item issue".

If you have a list with more than 5,000 items which break permission inheritance, it will be unbelievably slow. Many people think that is a bug/performance recession of the product. But it is not the truth.

Actually the slowness is caused by wrong setting. The setting is actually introduced as early as SP 2010, but I don't know why no one cares about it until the release of SP 2013.

Since SP 2010, when it handles a list view, it would check "List View Threshold". If the list view has less items than the threshold, it uses a SQL query that is suitable for small list; while if it has more items than the threshold, it applies a different SQL query suitable for big list.

Moreover, there are two more settings to enable end users to view big list that exceeds the threshold, and to allow the users do such big list query in certain period of a day.

The problem is, if you don't set it explicitly, you will get a warning that your list has more items than threshold, and the list view won't display! For that reason, and because of the lack of documents, many admins just simply set an unrealistic huge threshold to make the list work for all users.

That causes the poor performance because SharePoint applies the wrong type of SQL query because of the huge Threshold.

I've requested a better KB published for several time, and even write one by myself. Unfortunately it is never published on TechNet or MSDN. So I copy the resolution here and hope more people can see it.

There are three options. You can combine the three and find the fittest for your environment.
Option 1:
Enlarge the "List View Threshold".

Warning: The minimum "List View Threshold" is 2000, the maximum is unlimited. The caveat is: a very big "List View Threshold" would result in very poor performance, because SharePoint would otherwise apply special optimizations on list with more items than "List View
Threshold".
"List View Threshold" can be changed either from central administrator site or by PowerShell command.

Change "List View
Threshold" from central administrator site.

Click the link "Manage Web Applications".
Highlight the web application of interest.
Click the triangle of "General Settings" ribbon button, in dropdown options, click "Resource Throttling".
Edit "List View Threshold" and click OK to save the settings.

Change "List View Threshold" with PowerShell.

$app=Get-SPWebApplication "http(s)://<web app url>"

$app.MaxItemsPerThrottledOperation=<your target number, e.g. 6000>

$app.Update()

Option 2: Unthrottle the Threshold in the web application level with PowerShell.
Warning: Below script unthrottle the threshold
all day long(24 hours per day). You can restrict the duration of unthrottling by setting DailyStartUnthrottledPrivilegedOperationsHour, DailyStartUnthrottledPrivilegedOperationsMinute
and DailyUnthrottledPrivilegedOperationsDuration value of the SPWebApplication
object.

$app=Get-SPWebApplication "http(s)://<web app url>"

$app.DailyUnthrottledPrivilegedOperationsDuration=24

$app.UnthrottledPrivilegedOperationWindowEnabled=$true
$app.Update()

Option 3: You can even unthrottle the Threshold in a certain List with PowerShell.

$web=Get-SPWeb "http(s)://<site or sub-site url>"

$list=$web.Lists["<List Title or GUID>"]

$list.EnableThrottling=$false
$list.Update()