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
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 .
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:> 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 http://technet.microsoft.com/en-us/library/ff608068(office.14).aspx
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 . http://support.microsoft.com/kb/2817414
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 .
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!
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.
we have sharepoint server machines in cloud having oct 2013 updates.
still the lookup threshold is 8. how?
Thanks for this helpful information I agree with all points you have given to us. I will follow all of them.
<a href="staygreenacademy.com/.../"> SharePoint 2013 Developer Training Online</a>
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.