“Specifies the maximum number of list or library items that a database operation, such as a query, can process at the same time outside the daily time window set by the administrator during which queries are unrestricted.”

In my opinion, many don’t quite understand what this really is and how to manage it properly. Many project stakeholders other than SharePoint SMEs understand this to be a limitation of how many items can be queried from the list. Rather, it is about the number of items or rows the SQL database has to ‘scan’ implicated by the list view’s query.

For example, let’s say we had a list of 30,000,000 items. Out of these items, we have 4,999 that have Country column value of Canada. List view threshold is set at 5,000.
There is a custom list view where a filter condition is Country = ‘Canada’.

Although it seems that this list view is doing a query for only 4,999 items, what is really happening at the SQL database table level is that all 30,000,000 items are being scanned.

A recommended solution is to index the column found in the list settings.

Note that the indexing of columns is not a SQL based index such as a non-clustered index, but rather indexing through the NameValuePair_Latin1_General_CI_AS table in the respective content database.

The new Automatic Index Management setting

Now, in SharePoint 2016 Preview, there is a new list setting to automatically index found in List Settings > Advanced Settings. The default is set as ‘Yes’.