While working with SharePoint Lists and Libraries, everyone must have come across the below error:
The number of items in this list exceeds the list view threshold, which is 5000 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited.
In this blog, we will be exploring the meaning of List View Threshold (LVT) and ways we can overcome it.
Let’s start with making sure we understand the difference between a boundary and threshold.
Boundaries are absolute limits that cannot be breached. This is a design choice by Microsoft.
Thresholds are restrictions within which the default value can’t be exceeded unless that value is explicitly modified.
Boundaries and Thresholds in SharePoint List and Library
- File Size: 2GB per item.
- Document Count: A flat count of 30,000,000 items per library (if broken up by folders and views).
- Item Count: This is also limited to 30,000,000 items per list.
- Bulk Operations: Users are restricted to 100 items per bulk upload.
- List View Threshold: 5,000 items per view.
- Admin View Threshold: 20,000 items per view.
- Co-author limits: 10 concurrent editors per document (although the maximum supported is 99).
What is List View Threshold (LVT)?
Most importantly, don’t confuse list view threshold with the limit of items allowed in the list (it is 3 million). SharePoint list view threshold is the end-user limit of 5,000 as a SQL throttling limit. To minimize database contention SQL Server (the back-end database for SharePoint) often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at once, then it’s more efficient for SQL Server to temporarily lock the entire table until the database operation is completed.
The actual number is not always 5000, and can vary depending on your site, the amount of activity in the database, and your site’s configuration.
If you are an administrator and trying to configure views, keep in mind that for admins the LVT is 20,000 and you might not have any problem loading the views returning more than 5000 item, but users with fewer privileges might not be able to load the view.
What if the List View Threshold is reached?
End-User: Instead of seeing the content they requested, will see a friendly error message stating that their content cannot be returned.
Administrator: Add/Remove/Update list columns, managing a column index, deleting a list, deleting a site, save list as a template with data, showing totals in list views, enable/disable attachments operations can be blocked because when executed they affect every item in the list, which could trigger the threshold limits.
Ways to overcome List View Threshold.
Use Index and Filter efficiently – Limiting item being displayed in a view.
Indexes help in improving the list and library performance as it helps in retrieving items quickly. In SharePoint 20 indexes can be created for a list or library, but as indexes add some overhead to every database operation to maintain them, it’s best to only add indexes for most common columns.
Using indexed columns to filtered view helps in overcoming the LVT, but make sure the first indexed column in the filter expression does not exceed the List View Threshold. SharePoint selects the first indexed column in a query. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes, even if the result of the filtered view returns less than the List View Threshold.
If you move items into the Recycle bin, those items will still be counted when determining whether the filter expression exceeds the LVT. If you clear the recycle bin they are no longer counted.
A quick and easy win is to fiddle with the pagination within your views. This setting effectively returns results in batches relating to whatever limit you decide to set.
Folders Folders and more Folders – Organizing items into folders
Organizing items in a folder structure improves the efficiency of data access. A newly created folder also creates a new internal index within the underlying database. This means that whenever a folder is accessed, its index is used to retrieve the relevant data.
If you choose the Show all items without folders option in the Folders section when you create or modify a view in this list or library, you must then use a filter that is based on a simple index to ensure you don’t reach the List View Threshold.
Bypass List View Threshold
Turn off the list view threshold (Not recommended by Microsoft)
Turning off the list view threshold can help overcome the LVT issue for lists and libraries but it is not a recommended solution by Microsoft. Thresholds and limits are in place to help keep your environment performing well.
Change the list view threshold limits (Not recommended by Microsoft)
We can change the list view threshold as well. That is also not recommended by Microsoft, but it is a better option than turning off the threshold limits altogether.
Setup daily time window for large queries
This could be a great option if you can limit large queries to only be executed during certain times of the day. This could be useful if your users need to pull large amounts of data or run custom queries during a specified time-period during the day.
The LVT cannot be changed in SharePoint online and there is no ability to create a Daily time window as well.