Most software has limits that end-users will eventually run into. Office 365 and SharePoint are no exception to this rule, especially as far as lists and libraries are concerned.
Related: Learn how to use SharePoint Online, best practices, tips & tricks, and common issues to avoid
In this article, we’re going to explore these buffers in detail and shed some light on a few myths that have been circulating.
What is a SharePoint List?
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”.
Update: The difference between Microsoft Lists and SharePoint lists
What are Boundaries and Limits in SharePoint Lists?
Let’s begin by defining what we mean by boundaries and limits. As IT technicians, we’re used to jargon, but it’s a good idea 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, performance-wise, there could be repercussions elsewhere.
- Thresholds are restrictions within which the default value can’t be exceeded, unless that value is explicitly modified.
These sorts of numbers were only made visible from SharePoint 2010 onwards and have apparently been grafted onto SharePoint to protect users from… well, themselves.
The scenario in which this sort of protection could be deemed necessary is if, for example, one user was looking to view all 20,000 items on a list, thus slowing down everyone else using the platform.
If you’re new to SharePoint, I recommend you take a look at our Start to Learn SharePoint blog series. This will help you get started, and give you many pointers on how to leverage the platform to your advantage.
Important SharePoint List Thresholds & Boundaries
So, having boldly declared that these limits are in place, the next thing to ask is: “what are they?” There are quite a few, but some of the more important restrictions to keep in mind are:
Boundaries:
- 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.
Thresholds:
- List View Threshold: 5,000 items per view.
- Admin View Threshold: 5,000 items per view.
- Co-author limits: 10 concurrent editors per document (although the maximum supported is 99).
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.
The restrictions in place are in effect for everyone within the tenant, and any change could impact all customers within that shared environment.
The co-author limit is a brand-new introduction that’s in place specifically for SharePoint 2013.
The 5000 Items List View Threshold
So, getting back to the SharePoint lists thresholds, the end-user limit of 5,000 is in place as an SQL throttling limit. When servicing large queries, the underlying database will resort to row level locking, in order to effectively retrieve information.
However, in instances where more than 5,000 items are being frozen, there’s a greater efficiency in locking the entire table in question. This subsequent table locking can prevent other users from accessing and manipulating that data.
How can all of this be managed? There are several options available for doing so:
- Creation/utilization of Indexed Columns & filtered views
- Organizing items into folders
- Limiting the items on display
- Editing the default view
Indexed Columns & Filtered Views
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.
Which takes us to the idea of 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.
Using Folders
Within SharePoint, 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.
Limiting the items on display
The SQL query that retrieves data is only part of the experience with high density lists; once the data has been retrieved, it also needs to be rendered.
A quick and easy win here is to fiddle with the pagination within your views and is automatically set it to 30. This setting effectively returns results in batches relating to whatever limit you decide to set.
One suggestion is to apply a filter to the default Allitems.aspx view. This one view, left alone, can create all sorts of problems, but making changes to it as the default landing page for many of your users can also save future headaches.
SharePoint Lists Thresholds: Myths and Realities
Knowing what SharePoint and Office 365 Limits are and encountering them are two very different things.
We believe knowing how to deal with a dense, highly-populated list is extremely useful, because view limits can be reached quite quickly. Being able to manage them will make it a lot easier down the road.
Now that you’re aware of these limits and the impact they can have on your environment’s performance, a good thing would be to look at your farm’s lists to see where they’re at.
Our tool, Sharegate, provides a built-in report that does just that. This can make dealing with SharePoint list thresholds more manageable, to say the least!
Bypassing the List Thresholds
When performing a migration in Sharegate, you might encounter this kind of error message when trying to load a list or a library:
This view cannot be displayed because the number of items exceeds the list view threshold defined in your Central Administration.
You might need to set indexes on columns with which your view is filtered to be able to load this view.
When this error occurs, no items will load. However, there is still hope for a migration. You simply need to use the view selector, and select the “Built-In” view. This is a great view that we designed to allow you to load all your items, even if you have more than the designated threshold.
Another way to prevent running into the Threshold limit is to have SharePoint automatically create the indexes 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.
Finally, it’s impossible to cover this topic without going over this last possibility, which is to simply increase the value of the List View Threshold in the SharePoint Central Administration, in order to allow the migration of your list/library with more than 5000 items.
I would not venture into this option without first understanding that it might have an important impact on Server performance. In other words, this is not recommended, but it can still allow you to migrate your list without too much trouble.
