The list view threshold (LVT) has been a pain point in some SharePoint sites that I have seen. The default setting in SharePoint 2016 Preview is still 5,000 as it is in 2013.

In cases where lists contain >5,000 items, users will eventually encounter the following message and the list is not displayed.

According to Software boundaries and limits for SharePoint 2013 article the definition of the List view threshold (LVT) is:

“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.”

To manage this constraint in SharePoint 2010 and 2013, read the article Manage lists and libraries with many items

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’.

The automatic indexing is supported by the ‘Large list automatic column index management job’.
Go to Central Administration > Monitoring > Review Job Definitions

Large List Demo

Configuration

• Central Administration > Select Web Application > General – Resource Throttling
  • List View Threshold for end users to be at 10,000
  • Auditors and administrators as 20,000
    Note: I doubled the default values just for general testing.

• Test User: Added ‘Roy Kim’ user account with only contribute permissions so that I can simulate the list view threshold without the special exceptions that a site collection administrator would have.
• Custom list
  • Named ‘Large List’
  • Added site columns: Status, Gender, City, Province/State, Country,
  • Added 25,146 items with custom columns including Status. (via a PowerShell script)
  • Created View ‘By Not Started’ where Status equal to ‘Not Started’

• Large list automatic column index management job
  • Allow the timer job to run or manually run the job immediately.
• Indexed Columns
  • Status column has become automatically indexed.