Monday, May 6, 2013

SharePoint 2013: List View Threshold

After migrating content from MOSS 2007 to SharePoint 2013, I attempted to navigate to a list on a particular site that contained thousands of items. I was not able to view the contents as I received a message:

(Click image to see larger view)
The number of items in this list exceeds the list view threshold, which is 5000 items.


The default threshold for general users is 5000 items. This is to limit any strain or excessive use of resources. While it makes sense and we do want SharePoint to be at optimal performance, in certain cases, we need to see more than 5000 items.

To increase this threshold, you need to modify the Resource Throttling properties of the web application. In Central Administration, select Manage Web Applications and then select the web application you wish to modify. From the General Settings button menu on the top ribbon, select Resource Throttling:




On the Resource Throttling dialog, increase the List View Threshold value:

 
Click OK.
 
You should be able to navigate to the original list which contained more than 5000 items.
 

The best practice would be, however, to create various views which limit the amount of items returned, but as with anything, sometimes you can't follow all of the rules.



NOW AVAILABLE!!!

 

20 comments:

  1. Thanks for posting this. This is the first time I've seen a screenshot of what happens when you exceed the 5000.

    Ryan

    ReplyDelete
  2. I work in an organization where IT does not allow anyone outside IT to have access to Central Admin, and IT will not change any of the default settings in SharePoint. Microsoft provides for a Daily Time Window for Large Queries, which would let users run large queries during a restricted window, or index columns, turn on "enforce unique values", or other actions that are blocked by the list view threshold. However, the default setting is no time window, so we're stuck with having to find our own workarounds or put in a ticket to ask IT to do mundane routine work on large lists.

    Believe it or not, we have abandoned a SP2010 project due to those issues, and are seriously considering how to engineer our future projects to use SharePoint 2007 rather than SharePoint 2010. We won't have SharePoint 2013 for several more years I'm sure, as we only got SharePoint 2010 less than a year ago, but I'm already dreading the day when they turn off the SharePoint 2007 farm.

    ReplyDelete
    Replies
    1. You could create custom solutions that read the list items and such which could be a workaround to the threshold. There may also be third-party tools or webparts that may assist as well. I had to go back to 2007 when I switched companies but now we are full steam ahead on migrating to 2013.

      I would approach it from a business requirement perspective and work with IT to craft an appropriate solution. What is the real need? Can you leverage Search to perform the actions? A user doesn't need to see more than 5000 list items - they just need the ones that they are interested in.

      The thresholds are there to promote performance and reduce strain on the systems. You'll have be creative to overcome the limits. Things I thought were impossible to implement, I ended up figuring out only because I was pushed and driven to provide a solution.

      Hang in there!

      -=Steve

      Delete
  3. This threshold limit has totally compromised a project I am working.

    ReplyDelete
  4. You guys need to read more before abandoning projects.
    1. You can create views with filters
    2. You have to create indexed columns on the filter fields and sort fields
    3. Once you do this, and reindex, you wont have the listview threshold on the new views
    4. Of course its not a good practice to use an All Items view, that just makes no sense.
    5. If you want to read all items you can use reporting services integrated mode to create a tabular report based on a list.
    6. You can use also search, create a display template that looks like the listview webpart, and then use the search functionalities to show all items.

    ReplyDelete
  5. How do you do this for Office 365?

    ReplyDelete
    Replies
    1. You can't because you are not supposed to change it. You need to create views that have a subset of items under the threshold.

      Delete
  6. In my case, I have a library with 16000 documents in four folders. When I go to the home page of the library I receive the error. I have set the view to filter to a subset of 60 items and set it as the default view. However when I open the library it still gives the error. It appears that if the library contains more than the threshold, any view will exceed the limit (unless I have show in folders turned on - in which case no files are found, because they live in the 4 folders). Is the threshold determined by number of items in the library to be queried, or by the number of docs to be returned by the query?

    ReplyDelete
    Replies
    1. It should be based on the number of docs to be returned. How are you filtering the view?

      Delete
    2. The number of records queried is definitely a factor not just the number of records returned. We have experience this many times.

      Delete
  7. How many maximum views can be created on a sharepoint list?

    ReplyDelete
    Replies
    1. I can't find any limits documented anywhere. It is probably safe to say that you can have more than what you need or is reasonable.

      Delete
  8. Excellent post and Q&A. Thanks.

    ReplyDelete
  9. Here are some suggestions and explanations on how to manage the list view threshold in SharePoint Online. Just a note, the OneDrive for Business list library list view threshold is still 5,000, but you can synchronize up to 20,000 files with the OneDrive for Business client. All other document libraries on the personal site, or on SPO site collections will only allow 5,000, thus passing the 5,000 item mark without the necessary adjustments can introduce a variety or problems like permissions, the ability to view files, delete files, rename files, etc, until you get the list view threshold under control. Sometimes it is tricky, and you have to try a couple of different methods. Managed metadata navigation is one of the most complicated to set up if you are not familiar, but it seems to work the best.

    Manage lists and libraries with many items
    https://support.office.com/en-US/Article/Manage-lists-and-libraries-with-many-items-b8588dae-9387-48c2-9248-c24122f07c59?ui=en-US&rs=en-US&ad=US

    James

    ReplyDelete
    Replies
    1. Thanks for the info! I would suggest next time breaking this up into smaller chunks. People don't read so much. Sorry I am all about user experience nowadays.

      Delete
  10. When you are working with large sets of data - whether it be list items or document repositories, like Land leases (if you're familiar with O&G) - you should use a combination of search-based navigation, search, and connected list view web parts. Open in Windows Explorer should be disabled, logical storage units like folders should be avoided and you should never ever override the list view threshold. That is the single most effective way to ensure poor performance across your whole farm.

    ReplyDelete
  11. What will actually happen when you have listview with 15k items in all items and you have 200 users actively using the page with listview(listview brings 15k records with 30 items per page) in the morning. Will it crash SQL server(heavy memory/CPU utilization

    )? or will it be too slow for like 150 users and may work for 50 users?

    I just want to know what will user see or what is the impact?

    ReplyDelete
    Replies
    1. 30 items per page shouldn't impact too much. Having hundreds, especially in the grid view, slows down the page for sure. Overall impact to your environment depends on your environment.

      Delete
    2. 30 items per page shouldn't impact too much. Having hundreds, especially in the grid view, slows down the page for sure. Overall impact to your environment depends on your environment.

      Delete
  12. thank you so much, really usefull information. First time I see this kind of information with all the research I have done.

    ReplyDelete