How to overcome SharePoint 5000 item limit threshold

If you are reading this blog post, you have probably already encountered or are concerned about the infamous 5000 items threshold limit in SharePoint. With this blog post, I would like to clarify what exactly the issue is, how to prevent it, and how to fix it, in case you encountered it. The following article provides tips and recommendations for addressing this issue in case you have or plan to have a really large document library.

AUGUST 2020 UPDATE: As Microsoft continues to make improvements to SharePoint libraries and performance, most of the issues described here are no longer relevant. I suggest you check out this most recent article on the current state of the 5,000 view item limit and large document libraries.

What is the SharePoint 5000 item limit threshold?

First, let me explain in non-technical terms, what the issue is all about. Many mistakenly confuse this issue and incorrectly think that the list or library cannot hold more than 5000 files or items. This is not true. According to Microsoft, the list or library can hold up to 30,000,000 documents or items. That’s a LOT of documents! So, what’s the issue then?

The issue is not necessarily with storing, but rather with displaying the information. The SharePoint 5000 item limit threshold applies to the limit of items that are displayed in a given view.

Every time you access a list or document library, there is a search query happening behind the scenes that returns all the results. If you say have 20,000 files in a document library and all of them stored in a single folder or at the root of the library, when you try to display that folder or library root – you won’t be able to see more than 5000 items on a page/view and the library will run into the 5000 item limit threshold. Item is not just a file (document). The folder also counts towards the 5000 items limit.

That is because to display those 20,000 documents, servers behind the scenes do a lot of processing and querying. Imagine, you have a really BIG BOX with 20,000 pairs of various shoes all mixed up. Now imagine I would ask you to organize the shoes by size, starting from small to large. How would you go about achieving this request? Well, it is really a 2-step process.

• Step 1. First, you have to retrieve a pair of shoes, take a peek at the label inside and note its size
• Step 2. Next, you would need to organize those shoes by size. Remember, we have 20,000 pairs!

How long would this exercise take you? A long, long, long time. Same thing with SharePoint. Just a single click on a folder to display the documents inside of it is equal to 1 shoe request above!

To elaborate on the shoe analogy… The real problem is that SharePoint puts all its shoes in the same store, which is a single SQL table. It contains all the shoes of all the libraries of all the site collections of a given content database and not just the shoes of your large library. Now, if you were to buy 1000 shoes (sounds like a dream of any woman!), then the shop owner needs to look through all of the shoes to find them. But in order to make sure some other customer is not going get one of them before he can take it, he has got to lock them up. So instead of locking up each of the shoes, whenever some customer asks for more than 5000 shoes, he closes down the shop!!! Much easier for him. Unfortunately, by doing so, he locks out all other customers for the duration of that single sale. SharePoint is aware of that, so he decides to throttle all requests for more than 5000 shoes, to make sure the other customers can still enter the shop as well. [This amazing shoe store analogy example is courtesy of SharePoint Architect, Francis Buyse]

Now that we are clear on what this SharePoint 5000 item limit threshold is all about, how do we get around it? As with the rest of things in SharePoint, there are few ways to tackle this:

Option 1: Don’t put all your eggs in one basket

If possible, move the content from a single document library and move to separate libraries and sites. Do all the documents belong to one category, department, or business function? If the answer is no, they probably belong in different libraries and sites.

Option 2: Convert to metadata and enable indexing of (metadata) columns

If you encountered the SharePoint 5000 item limit threshold, you most likely are utilizing folders with lots of files inside each folder to store documents in SharePoint, or your metadata columns are not indexed. That issue you encountered is yet another reason to convert to metadata. Why will the indexing and metadata help? Well, remember that shoe example request to organize them by size? When you convert to metadata and index those columns, you essentially complete Step 1 (looking up the shoe size step) before any search requests are initiated. To use the analogy of shoes again, if you use an indexed filter, then you actually ask the shopkeeper to look into his catalog instead of running around the shop and checking out each shoe. Makes sense?

So, in other words, with metadata and indexing, SharePoint kind of already knows it all about a file or item in a list. So when you are retrieving a view that relies on metadata/indexing, the servers don’t have to work as hard, since the background work (lookups) has already been done.

IMPORTANT: When you do configure your views, they have to be filtered/sorted on the indexed column(s).

As a side note, make sure you also configure your metadata and views correctly. The whole idea behind metadata is so you can slice and dice your files and items in a list to make it more manageable and presentable to the user. Think from a user perspective. How do you expect a user to find the right document when he or she is presented with thousands of results? You really need to use a combination of metadata columns and views (filtered, grouped, or sorted) to reduce the amount of data presented based on your query. If all of your views return thousands of results, you probably need to add additional metadata and filters.

The steps described above work best when you are just starting to design your taxonomy, metadata, configure document library, and views. But what if you already have a library and ran into the threshold issue? Well, it is still possible to follow all the steps above, but it just becomes a much harder project.

• Step 1: First, you will need to bring down the number of files in your repository to go lower than the threshold limit. Once you crossed into the above 5000 territory, it “locks” the document library from trivial operations like adding columns, making certain adjustments to views, setting permissions, sharing, and even managing column indexes. That means that you won’t be able to index columns until you are below the threshold level. So take care of this first.
• Step 2: If at all possible, consider the whole reorganization of your existing document library. Maybe it is OK if certain chunks of documents sit in a separate library or site? If you are up to the challenge, you can also create metadata properties and apply default column values to folders in your existing repository. If you do that, you can then index columns and either utilize a hybrid folder/metadata approach or hide folders altogether from a view.

I hope you found this useful. This is yet another reason to plan your taxonomy, information architecture, and the whole SharePoint roadmap before moving the documents and start doing stuff in Office 365/SharePoint.

Bonus

I created a slide deck that lists 10 unique techniques on how to mitigate, overcome, and bypass the SharePoint 5000 item limit threshold. View it on SlideShare. You can download it from there as well.