SharePoint 2010/2013: List view Lookup threshold uncovered


SharePoint with Large lists is common scenario in any Sharepoint deployment. While there are Several blogs / Guidance TechNet articles which exist working /Managing Large lists, My blog is particularly about the "List view lookup Threshold"

Here is a table summarizes information about resource throttles and limits that you need to be aware of. These throttles and limits are set on the Resource Throttling page in Central Administration


or Limit




List View Lookup Threshold








Specifies the maximum number of join operations, such as those based on lookup, Person/Group, or workflow status columns.

If the query uses more than eight columns, the operation is blocked. However, it is possible to programmatically select which columns to use by using maximal view, which can be set through the object model


This is for SharePoint 2013 Post June 2013 CU update . Refer to Note at end of this Article for Details .

From <>

This feature limits the number of joins that a query can perform. 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. By number of joins, I mean the number of Lookup, Person/Group, or Workflow Status fields that are included in the query. So for example, if you have a view that displays 6 lookup columns, and filters on another 3 distinct lookup columns then by default that view won't work, since the List View Lookup Threshold is 8, and the view is attempting to use 9 lookups.

Here are few observations which affect this threshold , which may cause either the request to hit the threshold limit & hence be throttled or may lead to the views showing data beyond the threshold limit configured in central Admin

1. What classifies as Lookup columns: Standard lookup columns, single-value managed metadata, multiple-value managed metadata, single-value people and group columns, and multiple-value people and group columns , Workflow Status , Created by , Modified by ( people ) are obviously counted as lookup columns .

2. Additionally following columns shows on list view also work as lookup columns , Name ( linked to Document) , Link (Edit to edit item) , Name ( linked to Document with edit menu), type ( icon linked to document)



3. We allow overriding the query throttling in following circumstances

a. User is browsing when the system is in an Unthrottled Time window ( Web application throttling settings)


b. The current user is a box administrator (part of local admin group on the machine where the request is served from) . Local admin privileges can also be obtained by being part of an AD group which in turn is a part of local or Domain Admins groups . you can use the following command prompt to dump the group membership of a logged on user on a particular Machine /workstation .

C:> WhoAmI /Groups

c. The user is browsing using farm admin/Application Pool account   for the Web-application .

We highly recommend that you do not increase this number beyond the Default Threshold Limit , because through thorough testing we've observed that there's a serious non-gradual performance degradation that shows up above 8/12  joins. Not only does the throughput that the server can handle drop significantly at that point, but the query ends up using a disproportionately large amount of the SQL Server's resources, which negatively affects everybody else using that same database. Here is an article which talks about more on the Performance impact


Working with Large Lists in SharePoint 2010 - List Throttling

SharePoint 2010: How to Change the List View Threshold and Other Resource Throttling Settings

Manage lists and libraries with many items



** Note : The List view Lookup Threshold has been revised to Default of 12 in SharePoint 2013 , after applying  the June 2013 CU update .

Any new Web-Application created will have List View Lookup Threshold set to  Default =12 , While the values on Existing Web-apps needs to be modified Manually . 



Comments (15)
  1. Stephen says:

    Rather than MS architecting a viable SP solution, MS forces their customers to redesign most of their functioning libraries/lists. Who doesn't have business critical lists with more than 8 lookups? Are you kidding me? That's loyalty for you, not!

  2. Matthew Chestnut says:

    This is a list *view* threshold, not a *list( threshold.  Keep in mind that *lists* can have more the 8 lookup columns.  However, the *view* is limited to 8 lookup columns.

  3. mandar jogalekar says:

    we have sharepoint server machines in cloud having oct 2013 updates.

    still the lookup threshold is 8. how?

  4. SharePoint 2013 Developer Training Online says:

    Thanks for this helpful information I agree with all points you have given to us. I will follow all of them.

    <a href="…/"> SharePoint 2013 Developer Training Online</a>

  5. Rajan Kapoor [MSFT] says:

    Thanks  for the comments . Mandar Threshold=12 would show up if you create a new Web-app at a build higher then June 2013 CU , Existing Web-apps will not have the values updated by Default.

  6. azu says:

    Lookup Plus  for SharePoint 2013, It is more than Sharepoint Lookup. Cascaded Lookup, Filtered lookup, Cross-site Lookup /drop down/ and

    some controls are free.  ("Create new item" link) Visit, site: www. azu . mn

  7. BlueSky2010 says:

    Thank you – very helpful!

  8. Yuko says:

    Thank you for your information! Even it is possible to modify the setting to have more lookup columuns, I am wondering if some issues on performance?

  9. Nice Blog.. thanks..

  10. Ha Ha Ha Ha Ha says:

    Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha Ha

  11. Fatima says:

    Lists are accessed through views.  I have a list with 30 lookup columns.  It became necessary as I have 30 persons with 30 different roles on each team.  Each team member is responsible for up to 11 different types of documentation.  What is suppose to be the resolution?  Simply telling me I cannot do it in a very expensive product doesn't seem to be the answer.

    Once my list went over the threshold of 12 (SP 2013), then it disappeared.  I was unable to open it in SharePoint.  I had to use the designer.  I can neither submit data, nor read data.

  12. Anders says:

    Thanks for this Rajan!

    Please note that this limitation also applies to creating templates with content. If you have more lookup coulmns than the limit in your library (not list views), then you will not be able to create templates with content from that library. templates without content works fine though.
    I’ve spent the last day troubleshooting this and your post is the final piece I needed.

  13. AshDDN says:

    Rajan – Thanks for the informative post. What do you mean by “maximal view” (“However, it is possible to programmatically select which columns to use by using maximal view, which can be set through the object model”).

    My problem is that I can control the number of lookup columns in my view queries by providing viewfields attribute to my COM CAML Queries. However, in case of list item inserts or updates done though JS COM, the item gets inserted but then the COM returns an error. I think the error is because after insert operation, SP reads the list item back and at that time I have no way to influence it to only read certain number of columns and not run into lookup column limitation. Any thoughts?

Comments are closed.

Skip to main content