SharePoint List View Lookup Threshold | TahoeSmart

As SharePoint admins, we typically deal with end-users who have gone column crazy on their SharePoint list, causing the “This view cannot be displayed because the number of lookup and workflow status columns it contains exceeds the threshold (8) enforced by the administrator” error. Beginning with SharePoint 2010, resource throttling was introduced at the web application level to limit the number of items a list view (database query) can process in a single request, as well as to limit the number of lookup columns a single view can utilize at one time.

In this post, we will take a look at the List View Lookup Threshold:


 Central Admin > Application Management > Manage Web Applications > General Settings > Resource Throttling

First, let’s look at what Microsoft has to say about this particular threshold:

Each lookup column in a list view causes a join with another table. Each additional lookup column in a view increases the complexity of metadata navigation and list view queries. In addition to standard lookup columns, single-value managed metadata, multiple-value managed metadata, single-value people and group columns, and multiple-value people and group columns count as lookup columns.

So why should we be concerned about the number of lookup columns in a list view? (A hint… PERFORMANCE)

Adding lookup columns to a view does not cause a gradual or linear decrease in performance. The graph below shows the change in throughput as the number of lookup columns in a view increases. As you can see, the change in performance from zero to eight is rather stable, but at 10 lookup columns throughput greatly decreases. This test was performed with the list by using only one row. If a list is row wrapping, then performance degrades faster.

 

The following column types count against SharePoint’s list view lookup threshold:

• Standard lookup columns
• Single-value managed metadata columns
• Multiple-value managed metadata columns
• Single-value people and group columns
• Multiple-value people and group columns

To get a better look at this, we can modify the URL of the list in question to return the list schema in XML format:

http://{YOUR_SITE_URL}/_vti_bin/owssvr.dll?Cmd=Display&List={YOUR_LIST_GUID}&XMLDATA=TRUE

Quick tip: To retrieve the GUID of a SharePoint list, navigate to the list settings page and copy the encoded GUID from the browser address bar. To quickly decode the GUID, visit this free tool.

Looking at the sample list schema below, we can see that the columns mentioned above are designated as lookup columns:

 

One thing to note is that the “Created By” and “Modified By” columns, both of which are commonly included in list views, count as lookup columns. In addition to these columns, we have found that “Workflow Status” columns also count towards the list view lookup threshold.

Therefore, the complete list of columns that count towards the threshold includes:

• Standard lookup columns
• Single-value managed metadata columns
• Multiple-value managed metadata columns
• Single-value people and group columns
• Multiple-value people and group columns
• OOTB Created By & Modified By columns
• Workflow Status Columns

Next time you find yourself troubleshooting a list view lookup threshold issue, start with the basics! Verify your threshold setting in Central Admin and understand which columns in your list view are counting towards the threshold.