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,

• If the document/items can be related to a Department or Financial Quarter or Reporting Manager or any other conditional criteria, plan to create a separate list for each parameter, so that the list items can be divided among the specific lists.
  
  
• If you cannot create multiple lists and list items are accumulating exponentially within the same list, make sure you create separate views within the list, so that when a view is opened, only a subset of the list data is fetched from the back end for display in the view. Make sure only the required columns are added to the view.
  
  
• Implement the filter conditions, using And/Or filters, so that only items that meet the condition will be shown in the view.
  
  
  
  
• Implement indexing to the filtering/sorting rows - Indexing prevents unnecessary scanning of the back end database, whenever a query is sent to it. Though a subset of data is to be retrieved, if indexing is not present, the query will scan the whole table to get the data causing performance degradation.
  
  
• You can set the daily time window for large queries from the Web Application->General Settings->Resource Throttling. Setting this outside the office working hours would provide a Window where queries that return large numbers of rows can be processed.
  
  
  
  
• Create folders within the list/library, so as to organize the list better but make sure not to create lots of them and avoid nesting them unnecessarily.
  
  
• Datasheet views are also known as Quick Edit in SharePoint 2016, that can be used for faster sorting and filtering of data present within a large list.

A step forward in SharePoint 2016

Microsoft does not intend to provide a solution to this any time soon, as this would involve SQL level design changes. However, with SharePoint 2016, a new timer job has been introduced to automate the creation of indexes within the list.



The timer job is present in the central administration. It can be viewed from Monitoring-> Timer Jobs -> review Job Definitions.



The timer job is by default set to run on a daily basis and automatic index management allows the timer job to maintain the column indices on the lists to provide optimal query performance within the views associated with the list. Though this does not solve the threshold issue completely, it improves the query performance within the view in SharePoint 2016.

Thus we have explored a mammoth issue present within SharePoint which was present since its inception and have discussed the ways to circumvent it. Hopefully Microsoft comes up with much better resolution measures in the future iterations of SharePoint.