Auto Indexing List View in SharePoint 2016

To provide the business solution, we use SharePoint list and they are never been designed to replace database tables. However, SharePoint solution keeps on growing and we hit the 5000 threshold limit. This 5000 limit was to mitigate the lock escalation within the SQL Server.
To avoid this, sometimes we increase the limit to some extent (say 10k or any value). But this have an adverse effect on performance of the server. Rather than doing this, we should create indexes on columns in list and provide that column as filter in the view of the list.
A major improvement has been done by Microsoft in this area by introducing a new timer job – “Large List column index management”. This timer job examines the views in the list which exceed the 2500 items and then determines the appropriate columns to create the index on. The choice of index column depends on filters of the view & other list view definitions as well.
A new setting in the list has been introduced “Automatic Index Management” which must be set to “Yes” for the timer job to include the list for auto indexing the columns. This settings can be accessed by navigating to List Settings --> Advanced Settings --> Automatic Index Management. Also, by default this setting is enabled for all the lists.




This timer job runs per web application like that of List View Threshold. We can check whether this timer job is running for the web application by navigating to Central Admin --> Monitoring -->Timer Job Definitions --> Review Job Definitions
 


We can also check through PowerShell whether the timer job is running on the web application or not as mentioned below:
$webApp = Get-WebApplication –Name “Web App Name”
Get-SPTimerJob –WebApplication $webApp –Identity “job-list-automatic-index-management”
PowerShell command to start the timer job is mentioned below:
Start-SPTimerJob –Identity “job-list-automatic-index-management”
Document Library Views
In document library, default view is to sort the folders before files. Hence, an improvement is done in out of the box Document Library views to address the throttling related to sort ordering. Consider a scenario of large lists where it is necessary for SQL to scan the entire list to find all of the folders to meet the sort criteria. In SharePoint 2016 IT Preview, the folder first sort criteria is omitted