One of the major reasons that this List View Threshold (LVT) feature was created is to protect the server from unintentional load that may either bring it down, or at least cause other users higher latency or failures. Changing this limit (default 5000) is quite simple, but I wouldn't recommend it unless you are positive that it will not negatively affect your system. One valid example of when you might want to do this is if you are using your farm to serve heavily cached content, that only gets updated once a day, and do not want the limit to apply for that. Even in that case, I'd recommend that you test this thoroughly before changing it. There's an awesome white paper out there that describes in full details what effects this has on the server, with a lot of pretty graphs and such to depict the performance implications. Here it is: Designing Large Lists and Maximizing List Performance (http://technet.microsoft.com/en-us/library/ff608068(office.14).aspx). Also here's a link to the help topic that explains the basic limits and what they mean: http://office2010.microsoft.com/en-us/sharepoint-server-help/manage-lists-and-libraries-with-many-items-HA010378155.aspx?redir=0
If you've got your mind set on changing the LVT or another resource throttling setting, here's how to do it:
1- Login to Central Admin
2- Go to Application Management -> Manage Web Applications
3- Pick the Web application for which you want to change the LVT (If you only have 1 web app plus the central admin one, the one you want to pick is the 1 web app; changing this for the central admin does you no good)
4- In the ribbon above, click General Settings. That will bring down a menu, from which you should pick Resource Throttling
5- Change the LVT (first item in this list) to another value and press OK, but please try to keep it to a reasonable number!
Following those steps will take you to the page where you can also edit a bunch of other settings. Here's a list of them, and a brief description of what they do and best practices or recommendations on how to set them:
- List View Threshold for Auditors and Administrators: This is by7 default a "higher limit". Queries that are run by an auditor or administrator that specifically (programmatically) request to override the LVT will be subject to this limit instead. It's 20,000 by default as opposed to the 5,000 for the LVT. I wouldn't raise this past 20,000 for the same reasons of not raising the LVT. If you'd like to read more about how to use this, take a look at this post.
- Object Model Override: If you commonly use custom code on your deployment, and have a need for overriding the LVT to a higher limit, then it may be a good idea to allow the object model override, and give auditor or administrator permissions to the application that will perform the queries. This setting is on by default, but you may disable it if you do not need it. A good example of when you might want to use this is if you've implemented some code that will perform caching of a larger set of results that are accessed often for, say, several minutes. If you are not planning on caching the content, and are planning on running these queries often, then I wouldn't recommend using this method to get around the LVT as it will adversely affect your server's performance. In short: "tread lightly". If you'd like to read more about how to use this, take a look at this post.
- List View Lookup Threshold: This feature limits the number of joins that a query can perform. 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. I would recommend that you do not increase this number beyond 8, because through thorough testing we've observed that there's a serious non-gradual performance degradation that shows up above 8 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. If you'd like to read more about this, take a look at the "Lookup columns and list views" section of this white paper: http://technet.microsoft.com/en-us/library/ff608068(office.14).aspx
- Daily Time Window for Large Queries: This feature allows you to set a time every day where users can 'go wild'. Some people call it "happy hour", but I really think it would be a very unhappy hour for the server so I avoid that terminology :-). There are a few things that you should carefully consider before deciding what time to set this to:
- List Unique Permissions Threshold: This is the number of unique permissions allowed per list. If you have a folder that you break inheritance on for permissions, and set some permissions for it (and all the items inside it), then that counts as 1 against your List Unique Permissions Threshold. Unlike the LVT and other settings, this threshold is not triggered by viewing the content or performing some other operation on it, but explicitly when changing permissions. If you can afford to, then I would recommend reducing this number. It defaults to 50,000 and that is a lot of unique permissions! Your list is very likely to encounter problems with permissions before it reaches this number, so preemptively tweaking it to what might work in your environment is a good idea.
Thanks for reading.
Program Manager on SharePoint, MSFT
This setting is just ridiculous. What you are saying is, well you can have a document library but you cannot have more than 5000 files.
Here's an example. I have a view that is a flat view (i.e. it lists all files in all folders below the current folder). So, I am in a sub-folder which has maybe 1 file and this sub-folder has another sub-folder with 9 files in it, i.e. 10 files. When my entire library has a 'massive' 5000 files I cannot use this flat view to view my 10 files from this folder level.
The setting doesn't mean that you can't have a document library with more than 5000 files - it just means that for such document libraries you need to architect it in a way that does not kill the server's performance. You can use views and filtering to achieve the results you want or using the metadata navigation feature. For the example you gave, the view would need a filter to work properly, or to not sort folders first.
Of course, some operations will remain blocked despite smart architecture and filtering, and it isn't fun for end users if they can't do anything about it (e.g. can't delete the entire list because it's too large), but if you look at it from the server or the server admin's perspective, it makes sense to put a restriction to ensure that one user does not block thousands of others behind their actions if they decide to delete, index, or retrieve all 5 million of their documents, for example. I hope that makes sense. Let me know if you have a question on how to fix a specific view, and i'd be happy to help with that.
I would glady reduce the List Unique Permissions Threshold, but I didn't find a way to do this. I searched all the web and 5.000 is common sense although no one explained how to tweak this value.
Could you please advise.
best regards Berthold
I gave settings for throttling in the central admin but yet throttling does not work. When a user just having reader access to site collection opens a view with more than 5000 rows of data, it opens without giving any error message. What could be the possible errors?? Please help.
Do you have meta data navigation enabled? Are you sure the view has more than 5,000 items? Is that user by any chance a box admin on the front end he/she is hitting? Is there a filter on an indexed field? Are you noticing poor performance caused by this, or what is the reason for needing to make this work? Have you tried setting the limit much lower to double check that it's not working all the time?
Am using Web Services. SharePoint won't return more than 2000 records per call.
So I've changed my code to use pagination and retrieve list items as Batches(2000 each call).
Add below code where ur getting results. This string has next page pointer.
listNextPagePointer = listItem.ChildNodes.Attributes["ListItemCollectionPositionNext"].Value;
In WebService call add below code.
XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");
nextPagePointer = "<Paging ListItemCollectionPositionNext='" + listNextPagePointer + "' />";
nextPagePointer = nextPagePointer.Replace("&", "&");
ndQueryOptions.InnerXml = nextPagePointer;
listViewNode = ls.GetListItems(listName, null, ndQuery, ndViewFields, ListRowLimit.ToString(), ndQueryOptions, null);
Is it possible to change the List View Threshold on only 1 list, site or Site Collection?
It is only possible to change the List View Threshold at the web app level, so short of moving a list, site or site collection to its own web app, it is not currently possible to change the setting for just one list, site or site collection.
What would be the ideal value for List View Lookup Threshold, we have 8 and can we increase and Any impact on performance?
Amit, I would recommend keeping the lookup threshold to 6 or 8. Performance testing shows that things get exponentially bad at 10 or more.
Your post helped me to change Change the LVT . Thank You
You can disable the list view threshold on a specific list:
Yes, Wes.. I wrote that blog post too :)
Thanks for sharing this.