SharePoint List Threshold Issue: The Traditional Problem

One of the major limitations that exists in Share Point 2010, which was carried over to SharePoint 2013 and SharePoint 2016, is the list view threshold. List threshold is crossed whenever a list view that contains 5000+ items are requested and it will issue the below error message to the end user.



Now, this doesn’t mean that SharePoint is not capable of storing more than 5000+ items in a list. As per Microsoft’s boundary limits, a list can store over 30,000,000 documents/items in a library/list. The problem occurs when an attempt is made to display more than 5000 items in a single view.

This is quite inconvenient for the end user, because from his perspective, the most used content management system can’t even process 5000 items at a time!

Let’s see what happens internally!

How is Share Point data stored and retrieved?

In order to understand why such a limitation exists, we need to know how the SharePoint list data is stored and where it is stored. SharePoint can have numerous numbers of lists and contrary to the popular belief, each SharePoint list does not have a corresponding SQL table. Instead, the entire data is listed from the multiple lists that are stored within one single SQL table.



As you can see above, all the information related to the list is stored in the AllLists table, while the content within each of these lists is stored in a single table: AllUserData. Hence, any request to a list to retrieve and show items in a view will hit AllUserData table.

What causes the threshold problem?

For performance reasons, whenever SQL Server executes a single query that returns 5000 + items, locks escalation happens within the SQL table. As a result, the entire table will be locked. Since the entire Share Point data is stored as a single table, a single list view query that exceeds 5000+ items will lock the entire Share Point data table within that content. The database and all the users will face huge performance degradation. The entire set of users using Share Point at the time of lock escalation will have to wait for a longer time to retrieve the data.

Hence, you can see that the list threshold is a limitation that is imposed upon Share Point by its backend SQL Server. This issue is generated from SQL and the reason is row lock escalation. In order to avoid this performance degradation, Share Point has imposed the limitation of 5000 items to be queried at any point of time. Any queries for 5000+ items will be dealt the threshold error message.

Isn’t there a work around for this?

If you go to central administration, we have the option to change the thresh hold to above 5000.



You can also override the list view threshold programmatically, using object model, if the setting is allowed from the central administration.



This would prevent the error message, but it will cause huge performance degradation and the view loading time will increase exponentially often, resulting in time out for all the users accessing the content DB.

Never increase the value of the default threshold above 5000.

Workarounds for the threshold issue

In order to work around this issue, we have to plan ahead of creating the list. Some rules of thumb are,