Site icon Joanne C Klein

SharePoint Online List View Threshold

Joanne Klein
Reading Time: 12 minutes

Blog post: 8 minute read
Date last verified: August 2017

[Update November 2017] Microsoft announced Predictive Indexing which will change the way large lists and libraries handle throttling. Column indexes will be added automatically behind-the-scenes as you need them based on views you’ve created, columns you’re filtering on, etc. The net effect of this is the limit has been increased from 5000 up to 20000 (if they are added prior to reaching this limit)! You will see the benefits of this if you are viewing a list/library in the modern experience. Below is the excerpt from Ignite:

Does the remainder of this blog post still apply if the limit in your environment is now 20000? Yes, albeit the risk has been greatly reduced by about 15000 items! You still need to be aware of the limit, plan your lists/libraries accordingly and know if your users will be in the Classic or Modern view while viewing it. I recommend you still add column indexes manually ahead of time if you know you will be filtering a list/library by that column in case you exceed 20000 items and the auto-index hasn’t happened. Once you’re over that limit, you will no longer be able to add a column index.

Note: this blog post is referring to the end-user experience. The 5000 limit still applies for REST calls. Please refer to a great post by Marc Anderson where he goes into more detail – Predictive Indexing comes to Office 365 Lists and Libraries.

I know what you’re thinking… hasn’t this topic been covered enough already Joanne? Please hear me out…I’ve been around the SharePoint space long enough to have read at least 30-40 different blog posts talking about why the list view threshold limit exists in the first place, correct ways to provision libraries so you don’t run into it and how you can set yourself up for success if you exceed it. Heck, I’ve even written one of them! 😉

Here is the official link from Microsoft on managing them (Manage large lists and libraries in SharePoint) and an important excerpt from it explaining where the number 5000 comes from:

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.

I understand the technical reason behind the threshold limit but I still wasn’t satisfied with how well I understood the end-user experience. I suspect there are others out there who, like me, weren’t exactly sure what an end-user would see once the threshold limit was exceeded. Yes, I knew it would throw an error but I wanted to test out Microsoft’s recommendations in the link above to see if I could get around the limit if I designed a library using them. I not only wanted to document my observations but since my Office 365 tenants seem to be straddling both classic and modern worlds right now, I also wanted to capture some Modern library screenshots and document what the differences, if any, were between it and a Classic one.

Why this and why now?

I’m working with a team on a large migration effort to migrate shared network drive content into SharePoint Online. We decided not to take the “lift and shift” approach (right decision by the way), but to take this golden opportunity to clean-up what was there and look at ways to introduce some Information Architecture prior to migrating it into SharePoint.

Like most organizations, even after an aggressive clean-up there’s going to be some BIG network drive folders to migrate and like it or not, no matter how much we are trying not to, some of them are going to end up in the same document library. Due to this, our team really needs to understand the list view threshold, how we can mitigate the risk of running into it and what we will see if/when we hit that wall.

To that end, this past week I set out to test the List View threshold – I wanted to see it in action myself.  No matter how many other blog posts I’ve read on this subject, none of them demonstrated to me clear enough what the end-user experience would be like. Some of the questions I had:

This test is in a SharePoint Online environment which means, unlike an on-premises environment:

Disclaimer: this post is based on my observations during a non-scientific test on a large SharePoint Online library. If your experiences have been different than what I observed, please let me know. There are many variables to take into account and I want to ensure I’ve documented the correct findings.

Following are the steps I did for this test and the results I observed.

Set-up prior to migration

  1. I created one document library called Big Library with 4 root folders in it called East, West, North, and South.
  2. I created 2 choice columns on the library, both required:
    • Region
    • Vendor Rating
  3. I created 2 separate column indexes for the above choice columns. (Everything I’ve read indicates you need to do this BEFORE reaching the threshold. This makes sense since the creation of the index can itself exceed the threshold if you wait to do it AFTER the item count has exceeded 5000)
  4. I left the “All Documents” view with the default settings which is to display within folders in batches of 30.
  5. I created 2 Grouped by views. One to group by Region; the other to group by Vendor Rating. I changed both of these views to display “without folders in all folders” since this is typically how I see many Grouped by views being configured in real-world scenarios in libraries with folders. I left the default setting to display in batches of 30:

Migration steps

  1. Using Sharegate (shameless plug for a great migration product by the way), I migrated 4996 items into the folder called East. We’ll call this the ‘migration folder’ from this point forward in the post. Why 4996 items? The threshold will start to kick in after 5000 items. Since you also have to include folders in the item count, migrating in 4996 items would put the total item count for the library at exactly 5000 items.
  2. During the migration, I updated the metadata values as follows:
    • Region was set to East for all 4996 items (column default value)
    • Vendor Rating was set randomly across all 4996 items:
      • Bronze: 2149
      • Silver: 523
      • Gold: 2324
  3. As expected, I now see the familiar warning in library settings:

The following section describes what I observed while using the Modern library experience…

Post Migration – 5000 items

I navigated into the migration folder with the default All Documents view. I immediately noticed it did not adhere to the batch limit specified on the view. It returned far more than 30 items and in fact I scrolled down and gave up before I actually hit the bottom. It did display the first couple of screens of results fairly fast. The further I went down the list, the slower it got.

I switched to the Grouped by Region view and, as expected, it rendered successfully:

I switched to the Grouped by Rating view and, as expected, it also rendered successfully:

Both of the above grouped by views rendered very fast.

Post Migration – 5001 items

I uploaded one additional document into the migration folder.

Observations:

I navigated into the migration folder and the All Documents view continued to behave as it was before, not recognizing the batch limit of 30 items.

I navigated to the Group by Region and the Grouped by Rating views. As expected, they both threw the ‘List View Exception’ error since the library is now over 5000 items and the view is displaying all items without folders in all folders:

I went back to the root of the document library and selected the same 2 Grouped by views. I was extremely surprised to see that both of the views displayed without issue when I was at the library root (not in a folder), but at this level they showed an “approximation” of the group count:

Before adding more items, I changed both Grouped by views to display “inside folders” instead of the current setting of “without folders”. Once I did this, the views returned to rendering successfully when I navigate inside a folder (I am still under 5000 in the migration folder). This is what I see for both views when in the Migration folder:

I switched back to the All Documents view to test out the column heading dynamic filter options available on the view. The Filter by options to filter on specific Vendor Rating values worked extremely well and was lightning fast. The Group by Vendor Rating option did not produce the results I was expecting:

It showed the Bronze vendor rating correctly, but nothing for the silver and gold. (Albeit this may be a current limitation of the Modern view):

I then used the metadata filters available to me on the modern library’s filter pane. To open up the filter pane, click the filter icon on the top right-hand side of the library. This worked extremely well and was fast in all tests I did. I would highly recommend teaching end-users how to use these filters in large libraries.

Post Migration – 5005 items

I uploaded an additional 4 documents into the migration folder so I was now exceeding the 5000 item count within the folder.

Observations:

The All Documents view in the folder continues to behave as before. It did not adhere to the 30 item limit but it did not fail and returned the first few screens of results to me very fast.

Within the migration folder, the Grouped by views continue to render successfully and give the exact count of each grouped by column.  This surprised me!!! Isn’t there supposed to be a threshold at 5000? Yes, but I created an indexed column on the column being grouped by so (I assume) this is allowing me to get around this threshold limit. I tested this out further by uploading an additional 8 documents (the folder now has 5009 items) and the view continued to render successfully.

My thoughts immediately went to this excerpt from Microsoft I quoted above: 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.

Could this be what’s happening? Perhaps I’m able to render the view exceeding 5000 right now, but if the site/database became busier perhaps it wouldn’t work anymore. This alone leads me to believe that it is best to stay under that magical 5000 number if at all possible.

[Update] I’ve since uploaded another 1500 documents into this folder for a total of 6500 items within one folder. The views continue to render successfully as I’ve described them above.

What does this look like in Classic view?

My next question is if this behaved or looked any different in the same library set to the Classic view. I will run thru the same test when the threshold limit is exceeded in Classic mode.

The All Documents view adheres to the 30 item limit consistently.

Did the Grouped by views work? It depends.

If you are within a folder and there are over 5000 items and the Grouped by view is specified as ‘Show all items without folders in all folders’, it will render successfully but it will give you a throttled result (newest) with this message on top of the view:

If you change any Grouped by views to ‘show all items inside folders in all folders’, you will see this threshold error when you navigate into a folder with >5000 items:

The Classic and Modern experiences are very different from each other in this test – the Modern view experience is a definite improvement for the end-user in my opinion.

Questions answered

Returning to my initial questions:

Question 1: If you put a column index on something you’re filtering on or grouping on, can you go over the 5000 limit?

Answer: Short answer, yes. The column index will help you if you are using metadata filtering on your document results. Also, although it appears not to help with Grouped by views in classic view (the view renders, but is throttled), it does help when in a modern view if the view is set to “view all items inside folders in all folders”.

Question 2: On indexed columns, will column header filters work as expected once you exceed 5000 items?

Answer: Yes. Not only did the column header filters work as expected, but the metadata filters in the filter pane worked as well. Both techniques filtered results very fast.

Question 3: I’ve seen many blog posts refer to the ‘All Documents’ view requesting it not be the default view if it is in a big library. If it’s only displaying items in batches (the default), why does that matter? Is this still a problem?

Answer: Although the All Documents view continued to render for me once the library reached 5000, the more I scrolled down the list the slower it got. In the modern view it did not adhere to the display batch limit. Options for configuring the All Documents view:

Question 4: What benefit do folders actually provide in the context of the threshold limit? (I’m not factoring in other reasons for folders like security)

Answer: Microsoft recommends using folders to keep you under the 5000 limit to help ensure good performance. A light-weight use of folders in addition to metadata is a great compromise to organize content at a high level if you are in a large library. Thru my testing, it appears if I use folders and Grouped by views in a Modern library, I should ensure the view setting are set to “view all items inside folders in all folders” and then the result counts appear to be correct. In fact, in the Modern view, I was able to upload > 5000 items within one folder and still group by a column in that folder with all items being accounted for. Would I recommend pushing this limit and storing more than 5000? No, I still would not!

Another reason for using folders is for setting default column values based on the folder name. The values can then be used for metadata filtering.

My 8 takeaways…

  1. Design document libraries to stay under 5000 if possible so you don’t get into this hot mess in the first place. 😉  (I have previously blogged about options for designing libraries – Multiple Libraries, Folders or Metadata?)
  2. Even though my testing proved you can in fact exceed 5000 items in a view if using indexed columns, I still wouldn’t recommend exceeding it. Err on the side of caution when dealing with performance.
  3. In a large library, favor using search and metadata filtering rather than relying on Grouped by views for end-users. In large libraries, Grouped by views aren’t an effective way to find documents anyway.
  4. If you are using a combination of folders and metadata, ensure any Grouped by views are set to ‘view all items inside folders in all folders’ if in a Modern library. If you are still in a classic library, you can set the Grouped by views to ‘view all items outside folders in all folders’ to show the newest (throttled) results.
  5. Set up column indexes ahead of time. Use these columns for filtering your list and, if required, Grouped by views.
  6. Limit the # of items in all views to 5000. This is good insurance in case you go over 5000 items.
  7. An item is an item is an item. The item count includes folders. (You can’t have more than 5000 folders at the same level of a library or else it will ALSO exceed the threshold limit. I have never seen this personally, but I know others who have, particularly in automated solutions)
  8. The behavior when the list view threshold is exceeded appears to be different depending on if you are viewing a classic or modern library. (I prefer the modern view experience simply because the results don’t appear to be throttled). It is also my assumption the modern view experience will only improve over time for end-users.

Thanks for reading and good luck with those migrations!

-JCK

Exit mobile version