You cannot create lookup columns that enforce relationship behavior when the list it references contains more items than the list view threshold. Lookup columns that enforce relationship behavior If the operation involves more than 20,000 items, the query fails. 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. Also it returns all items, and it ignores folders.
Because there is no index, it does a full scan against the entire list. This operation fails when the list size is greater than the list view threshold. Manage files which have no checked in versionĪ non-indexed recursive query that fails for any list that has more items than the list view threshold. Some updates, such as a name change, are not blocked because they do not affect every item in the list.Īffects every item in the list so it is blocked for any list that has more items than the list view threshold. Blocked operations when list exceeds the List View ThresholdĪll columns including lookup and calculated columns, in addition to many types of updates, such as a type change or a uniqueness change. The blocked operations listed below are taken from the Designing Large Lists and Maximizing List Performance document published by Microsoft for SharePoint 2010. What operations may be blocked because of the List View Threshold? Removing the threshold would appear to make SharePoint 2010 perform more like SharePoint 2007 did when handling large lists. 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. 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.Ī 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. The List View Threshold did not exist in SharePoint 2007, and so it did not restrict queries to list data. Wasn’t this a problem on SharePoint 2007?
The threshold prevents this from happening. This can impact the performance of SharePoint with lots of concurrent users as some queries must wait for others to complete. The consequence of the table lock is to temporarily block other queries on the table until it is complete. 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.
all items in the SharePoint list.Īfter approx. 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. 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. Why is the List View Threshold set at only 5,000 items? 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. 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. Portal, Workplaces, Archive, and My Sites. This configuration applies to each web application independently, i.e. This applies to lists and libraries including document libraries. 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.
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.