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.
Dina Ayoub
Program Manager on SharePoint, MSFT
Comments
Anonymous
February 17, 2011
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.Anonymous
March 07, 2011
The comment has been removedAnonymous
June 15, 2011
Hi Dina, 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 BertholdAnonymous
June 22, 2011
Hi Dina, 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.Anonymous
June 23, 2011
The comment has been removedAnonymous
June 27, 2011
Hi Sudhansu, 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[1].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);Anonymous
August 11, 2011
Is it possible to change the List View Threshold on only 1 list, site or Site Collection?Anonymous
August 11, 2011
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.Anonymous
August 17, 2011
What would be the ideal value for List View Lookup Threshold, we have 8 and can we increase and Any impact on performance?Anonymous
September 02, 2011
Nice oneAnonymous
September 02, 2011
Amit, I would recommend keeping the lookup threshold to 6 or 8. Performance testing shows that things get exponentially bad at 10 or more.Anonymous
April 27, 2012
Your post helped me to change Change the LVT . Thank YouAnonymous
May 02, 2012
You can disable the list view threshold on a specific list: sharepoint.microsoft.com/.../BlogPost.aspxAnonymous
May 02, 2012
Yes, Wes.. I wrote that blog post too :)Anonymous
June 13, 2012
Thanks for sharing this. Regards PAnonymous
May 28, 2013
The comment has been removedAnonymous
June 26, 2013
Hi Dina nice post in detailed descriptionAnonymous
September 11, 2013
very nice articleAnonymous
October 17, 2013
Will this throttle limit create a problem when we upload bunch of items which exceeds throttle limit? We have a .NET application which uploads pictures in sharepoint. Let's say the limit is 2000 and we upload 5000 pictures, will this give an error? THanks,Anonymous
October 18, 2013
Hi Smaug, Adding items should not trigger this threshold, so you should be fine.Anonymous
October 18, 2013
Hi Smaug, Adding items should not trigger this threshold, so you should be fine.Anonymous
December 01, 2013
How I can read a list with more than 10k records without changing the threshold value? I also have 20 lookup fields in a list so how can I read such list without altering the lookup limit from Central Admin?Anonymous
December 02, 2013
Hi Subhajit, Please take a look at this help article: office2010.microsoft.com/.../manage-lists-and-libraries-with-many-items-HA010378155.aspx Another option is here: technet.microsoft.com/.../cc262813(v=office.14).aspxAnonymous
March 03, 2014
Hi Dina, I am the corporate document control manger for a mega size project handling almost half a million document between shop drawings, correspondence and design. I incremented the treshold to 50000 records where some record for one area exceeded this number. I tried to resolve this issue by created folder which I hate to do that . Lately I am not able even to upload documents in the newely open folder. I appreciate you opinion on this issue. Regards Charlie A. Ayash http://www.mdocsystem.com/Anonymous
March 05, 2014
Hi Charlie, I would recommend reading this article on TechNet that explains some more features in SharePoint that you can use to help manage the large list: technet.microsoft.com/.../cc262813(v=office.14).aspx Thanks, DinaAnonymous
June 17, 2014
thanks for sharing this valuable info. greetz.Anonymous
April 01, 2015
Nice blog