This article describes SharePoint Online view threshold limits that apply to SharePoint Online.
Having already encountered the notorious 5000 items threshold limit in SharePoint. I would like to further detail the following:-
The following article provides tips and recommendations for addressing this issue in case you have or plan to have a really large document library.
A SharePoint List is a container with rows and columns in which data can be stored in SharePoint, similar to an Excel spreadsheet. These pieces of content are called “list items”.
Let’s define what we mean by the boundaries and limits, so as to, establish the terms that will be used to ensure we’re on the same page.
Boundaries are absolute limits that cannot be breached. This is a design choice by Microsoft.
Limits are suggested guidelines that have been settled on, and are affected by system performance and testing by the product team. These can go beyond the suggested Microsoft limits but there could be performance issues at a later stage.
Thresholds are restrictions within which the default value can’t be exceeded, unless that design, or structure is modified.
Important SharePoint List Thresholds & Boundaries
There are many more important restrictions, related to SharePoint. However, let us just focus on the LVT, to keep in mind which are:
File Size: Less than 10 GB per file. Files attached to list items can be up to 250 MB in size.
Document Count: A flat count of 30,000,000 items per library (if applying metadata, folders and views etc).
Item Count: This is also limited to 30,000,000 items per list.
List View Threshold: 5,000 items per view.
Admin View Threshold: 20,000 items per view.
Sync – For optimum performance, we recommend storing no more than 100,000 files in a single OneDrive or team site library. If you use the previous OneDrive for Business sync client (Groove.exe), the sync limit per library is 5,000 items.
For the most part, these limits will be applicable to both SharePoint Online on Office 365 & SharePoint On-Premises, but with one key difference; the list thresholds in place cannot be changed for SharePoint Online. Also, the restrictions in place are in effect for everyone within the tenant, and any change could impact all customers within that shared environment.
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.
Note: The actual number is not always 5,000, and can vary depending on your site, the amount of activity in the database, and your site’s configuration.
When the whole table is locked, it prevents other users from accessing the table. If this happens too often, then users will experience a degradation of system performance. Therefore, thresholds and limits are essential to help minimize the impact of resource-intensive database operations and balance the needs of all users.
The following diagram summarizes the key points about what happens behind the scenes when you access many items in a list or library.
There are many ways you can work with or query a SharePoint list or library without receiving a List View Threshold warning. You can store up to 30 million items or documents in a SharePoint list or library. Using the following ideas, you can get the information you need and stay within the 5000 item List View Threshold. Here are just a few:-
To prevent running into the threshold limit is to have SharePoint automatically create the index for you. You can do this by enabling Metadata Navigation and Filtering as a site feature, and applying it to the lists and libraries you think will eventually reach the view threshold.
In the list/library list settings, an option exists to create a maximum of 20 indexed columns.
When you index a column, you’re asking SharePoint to make that column more accessible and serviceable in a query than other columns.
You should be aware that this prioritization comes at a cost, which is an increased overhead on the database level. Therefore, you only want to index columns that are going to be searched and filtered on often.
Filtered views, which can be used to cut out irrelevant items within a list. To make a filtered view more effective, it’s necessary for the first column within the list to be indexed to keep the returned results below the view threshold.
When you create a folder, behind the scenes you are creating an internal index. This internal index is also created for the root folder, or top-level of a list or library. When you access items in a folder, you are effectively using this internal index to access the data.
Important: If a folder contains subfolders, each subfolder and any subfolders, and their files and folders, are also counted as an item and contribute to the LVT.
It is important to consider the following when you use folders to organize a large list or library:
Note: If you move items into the SharePoint Recycle Bin, those items will still be counted when determining whether the filter expression exceeds the List View Threshold. If you clear the recycle bin they are no longer counted.
There are usually two search boxes on a SharePoint page, the site search at the top of the page, and the specific list or library search box. When you use the list or library’s Search box, you can progressively expand the scope of the search operation:
You can use a Document Center site when you want to create, manage, and store large numbers of documents. A Document Center is based on a site template and is designed to serve as a centralized repository for managing many documents. Features, such as metadata and tree view navigation, content types, and web parts, help you organize and retrieve documents in an efficient and meaningful way for your users.
Once you cross the view threshold limit you may and eventually will experience “database locking”. Users and administrators of this document library will start to lose from trivial operations like adding columns, making certain adjustments to views, setting permissions, sharing and even managing column indexes.
Eventually, further loss of functional you will experience will be unable to delete content from the libraries, delete large lists/libraries and eventually complete loss of access.
Microsoft Flows or PowerApps may run into the 5000 item limit, depending on what you are doing and even making REST calls.
That means that you won’t be able to index columns until you are you will need to bring down the number of files in your repository to go lower than the threshold limit
If you already have a library and ran into the threshold issue. It is still possible to fix, but this has now become a much more difficult to resolve. Effectively, a phase two – a transition.
First, you will need to bring down the number of items in your SharePoint list / library you to lower than the threshold limit. This can be accomplished by splitting the data into multiple document libraries.
Once this has been accomplished you can, start applying the methods outlined above.