SharePoint 2010: How to Change the List View Threshold and Other Resource Throttling Settings
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 (https://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: https://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: https://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:
- It should be an off-peak hour, or at least a time during which you expect the least load, so as to affect the least number of individuals. If you pick the time to be in the middle of the work day for the majority of your users, then even those who are not using the large list may be affected negatively.
- Try to keep it to a reasonable timeframe such that people can actually use it to fix their lists, rather than bug the farm admin (possibly you!) about it. If, for example, you set it to be "2-3 am", then it's unlikely that the users will be very happy about that. They won't want to wake up at 2 am just to delete this large list they no longer need, so they're more tempted to ask the farm admin to handle it for them.
- Remember that operations started during the window won't just abort once the window ends.. So if your window lasts till 9am, and at 9 you need the server to be crisp and clear because you get a huge load spike, people who started their list delete at 8:59 may negatively affect that experience.
- Consider different time zones. This is especially important if your organization or customers (if you're hosting SharePoint for others) are heavily geographically distributed. Setting it to 6pm may seem like a good idea for your own location, but would not be great in say, Sydney, Australia.
- 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