Home > SharePoint, SharePoint 2010, SharePoint 2013 > SharePoint 2010: List View Threshold Explained

SharePoint 2010: List View Threshold Explained


In this post I try to explain the List View Threshold that was introduced in SharePoint 2010, the benefits that it provides, the operations that are prevented when the threshold is exceeded, and possible ways to get them working again.

What is the List View Threshold?

SharePoint 2010 has introduced a List View Threshold which helps to keep the SharePoint servers performing well by restricting the amount of list data that can be queried and displayed. This applies to lists and libraries including document libraries.

This configuration applies to each web application independently, i.e. Portal, Workplaces, Archive, and My Sites. The default limit is set at 5,000 items.

Once a query exceeds the configured limit, the query is stopped and usually a message is displayed indicating something to the effect that it is stopped because the List View Threshold is exceeded.

Typically users will see such queries in use by List Views and List View Web Parts, so when the List View Threshold takes effect, the List View or List View Web Part will display a message instead of the expected items.

Why is the List View Threshold set at only 5,000 items?

The reason the List View Threshold is set by default to 5,000 items is because of the way that SQL Server behaves when querying large numbers of items without a suitable index to work with. When sorting and/or filtering a list SQL Server has to scan the sorted/filtered fields in all records in the database table, i.e. all items in the SharePoint list.

After approx. 5,000 items, SQL Server usually determines that it is more efficient to apply a table lock to resolve contention issues with other queries also trying to query the same items, enabling it to access all items quickly. The consequence of the table lock is to temporarily block other queries on the table until it is complete. This can impact the performance of SharePoint with lots of concurrent users as some queries must wait for others to complete. The threshold prevents this from happening.

Wasn’t this a problem on SharePoint 2007?

The List View Threshold did not exist in SharePoint 2007, and so it did not restrict queries to list data. This meant that there was no limit to be exceeded so queries on lists with over 5,000 items could cause the table lock to occur and the SharePoint performance could be impacted.

A SharePoint 2007 environment was therefore likely to have been impacted with lists and document libraries containing more than 5000 items, but this was just the way it behaved and nothing prevented this from happening. Microsoft appeared to have recognised that this is an issue for some and introduced the List View Threshold in SharePoint 2010 so that this issue cannot arise without deliberately increasing or removing the threshold first. Removing the threshold would appear to make SharePoint 2010 perform more like SharePoint 2007 did when handling large lists.

What operations may be blocked because of the List View Threshold?

The blocked operations listed below are taken from the Designing Large Lists and Maximizing List Performance document published by Microsoft for SharePoint 2010.

Blocked operations when list exceeds the List View Threshold

Add/Remove/Update a list column All columns including lookup and calculated columns, in addition to many types of updates, such as a type change or a uniqueness change. Some updates, such as a name change, are not blocked because they do not affect every item in the list.
Add/Remove/Update a List Content Type Affects every item in the list so it is blocked for any list that has more items than the list view threshold.
Create/Remove an Index Affects every item in the list so it is blocked for any list that has more items than the list view threshold.
Manage files which have no checked in version A non-indexed recursive query that fails for any list that has more items than the list view threshold.
Non-indexed recursive queries Includes filters and some sorts. This operation fails when the list size is greater than the list view threshold. Because there is no index, it does a full scan against the entire list. Also it returns all items, and it ignores folders.
Cross list query Includes queries by the content query Web Part and follows the list view threshold setting for auditors and administrators, which by default is 20,000. If the operation involves more than 20,000 items, the query fails.
Lookup columns that enforce relationship behavior You cannot create lookup columns that enforce relationship behavior when the list it references contains more items than the list view threshold.
Deleting a list Affects every item in the list so it is blocked for any list that has more items than the list view threshold.
Deleting a site If the sum of all items in a site is greater than the list view threshold, deleting the site is prevented because it affects too many items.
Save List as Template with Data Affects every item in the list so it is blocked for any list that has more items than the list view threshold.
Showing Totals in List Views Performs a query against every item in the list so it is blocked for any list that has more items than the list view threshold.
Enable/disable attachments in a list Affects every item in the list so it is blocked for any list that has more items than the list view threshold.

Blocked operations when container exceeds the List View Threshold

Delete/Copy/Rename a folder Fails when the folder contains more items than the list view threshold because it affects too many rows.
Queries that filter on non-indexed columns Fails when the container (folder or list) contains more items than the list the view threshold because it does a full scan against the entire folder because there is no index.
Set fine grained security permissions Fails whenever the list or folder on which you are trying to set fine grained permissions contains more items than the list view threshold because it affects too many rows. You can still set fine-grained permissions on child items, such as documents, in a large list, although you cannot set the permissions on the list itself or on folders that contain more items than the list view threshold.
Open with Explorer Does not show any items if a container has more items than the list view threshold (excluding items in sub folders). If a folder has 8,000 items total, but it has a sub folder that contains 4,000 items and only 4,000 items in the root, then Open with Explorer will work. If the root of a list contains more items than the list view threshold then Open with Explorer will not show anything. To use Open with Explorer the list must have items organized into folders in amounts less than the list view threshold in the root of any container.

Available features that might not work as expected

Datasheet view

The datasheet view button that is available in the Library ribbon tab of a document library is not disabled if the list grows above the list view threshold. However, if the list size exceeds the list view threshold, the view loads some items, but it displays a message that says, “You do not have permission to view the entire list because it is larger than the list view threshold enforced by the administrator.” You can disable the datasheet view option from the ribbon in the settings for the list. There is also a hard limit of 50,000 items so this view will be blocked even if the list view threshold is above 50,000.

How can some of these problems be resolved?

The List View Threshold can simply be manually increased to a high enough number that it is effectively disabled for all lists in the web application, or programmatically it can be completely disabled or disabled only on individual lists. However this will result in the potential for queries to perform poorly, undoing all of the performance benefits that the threshold was designed to resolve.

The threshold only applies to queries against lists where the sort order or the filter uses a column that does not have an index. Therefore adding an index to such columns will not only potentially improve performance of those queries as they use an index rather than performing a full table scan in SQL Server, but also this means a large list can be queried without the threshold stopping it from completing.

We can therefore prevent web parts and list views from displaying the threshold error message by adding an index to the fields that are sorted or filtered in the view.

For instance, if you have a web part or list view which displays all items checked out to ‘me’, then adding an index to the “Checked Out To” field enables the web part or view to work properly without the threshold preventing it from completing.

Adding an index to the “Checked Out To” field according to this MSDN article will also resolve the same problem on the “Manage files which have no checked in version” page that is found via the document library settings page.

It is not clear what kind of index could be added to a list, or what other action could be taken to resolve the fine grained security permissions issue as none of the fields in the list appear to be relevant when managing permissions. Therefore to ensure fine grained security permissions will work in large lists, it appears only raising the List View Threshold will achieve this, along with the potential performance degradation that this is designed to prevent.

Note, if a list already exceeds the List View Threshold then this will prevent the ability to add an index to the list as this is one of the many operations that are blocked by the List View Threshold, so this can only be performed when the threshold is lifted.

Alternative to permanently or temporarily lifting the List View Threshold, SharePoint web applications can be configured with a “Daily Time Window for Large Queries”, which is often set to enable these kinds of operations out of hours when the system is under reduced load and is therefore less likely to experience poor performance resulting from these queries. This effectively removes the List View Threshold during the configured time window so that the operations which are usually blocked can be performed out of hours without significantly affecting other users.

The List View Threshold settings are available via SharePoint Central Administration. Click on Manage web applications, select the web application to configure, then in the ribbon under General Settings, click to drop-down the menu of options, and choose Resource Throttling. All settings on this page apply to the selected web application.

Test what effect removing the List View Threshold will have on your environment

Before deciding to effectively remove the List View Threshold during business hours on a live production environment, it would be beneficial to benchmark the performance with some likely scenarios to test whether the environment will still perform sufficiently well under load. Then an informed decision could be taken on the trade-off between keeping the List View Threshold and having to live with the limitations, using an out-of-hours Daily Time Window for Large Queries, or perhaps even raising the threshold sufficiently to not have any effect altogether. Even so, in real live situations, if performance problems do occur, it would be wise to keep this in mind with the option to bring back the threshold if necessary to keep the environment performing well.

Microsoft recommend that the threshold should not be lifted, but they provide the capability to do so if needed, and the “Daily Time Window for Large Queries” seems a sensible approach to meeting the needs half-way so that these often necessary operations can be executed, while protecting the performance of the system when under heavier load.

Advertisements
  1. July 29, 2013 at 06:16

    Hi Nick, this article is good explanation. I also posted about threshold issue too and if you pleased, I would like to share your post on my blog.
    thanks.

  2. zak
    September 20, 2013 at 14:41

    How to increase throttling for perticular site collection not for whole web application ?

    • September 20, 2013 at 20:20

      Hi Zak
      It is not possible to adjust the threshold just for a site collection only the web application.
      Best regards
      Nick

  3. February 13, 2014 at 09:09

    Reblogged this on Share Point Online and commented:
    The 5000 item limits in SharePoint list view.

  4. Suhail
    February 18, 2014 at 10:12

    Useful info.. Thanks for sharing !

  1. July 29, 2013 at 06:11
  2. June 10, 2014 at 14:41
  3. July 15, 2014 at 13:40
  4. January 9, 2017 at 05:26
  5. April 16, 2017 at 10:29

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: