Handling Of SharePoint 2016 List View Threshold Problem Using Automatic Index Management Timer Jobs

SharePoint by design has a threshhold limit imposed on large lists, which is set to 5000. If any query or view returns more than 5000 items at any point of time, the error, in the form of a message, would be thrown, shown below: 



This is because all of the SharePoint lists are stored within a single SQL table,  and any query that returns 5000 + items will cause row lock in the SQL Server. This is in fact a design in SQL Server. As a result of the row lock escalation, the entire table will be locked and the whole Share Point data will be blocked. This will cause performance issues and the other users will suffer as the data retrieval time will rise exponentially. You can read more about this here.

However, you can resolve this by setting the index to one of the columns, which acts as the filter in the view or the query. This will prevent an entire SQL table scan in case of the data retrieval. By default, you can set 20 indexes in one particular list. Please note that maintaining an index is an additional overhead, but offers great flexibility to overcome threshhold issues.

Indexing of the columns had been done manually in Share Point 2013, but in the SharePoint Server 2016, we have huge improvements. In SharePoint Server 2016, the databases are no longer subjected to lock escalation. However, a list view threshold is enabled (configurable) on a Web Application basis. In fact, there is a timer job that periodically checks for lists that have more than 2500 items and creates an index on the most appropriate column. The name of the Timer job is “Large List column index management.” 

In the list setting, a new setting has been introduced to make the list available for indexing by the above timer job: “Automatic Index Management” must be set to “Yes” for the timer job to include the list for auto indexing the columns.

This can be accessed by navigating to List Settings -> Advanced Settings -> Automatic Index Management. By default, this setting is enabled for all the lists.



The 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 to maintain the column indices on the lists to provide an optimal query performance within the views associated with the list.