You heard that SharePoint now supports super mega sized lists and massive 4TB databases and even higher. Make sure you read this before you start creating really large lists. You need to know what you’re walking into before creating one massive document library. If you’ve already got large lists, hopefully you’re aware of the features that can help you scale and have a better experience.
After an upgrade, or after time goes on with any SharePoint 2010 environment you’re bound to run into the great features of SharePoint 2010… list thresholds. Users may not think it so great. When we upgraded we found 83 lists over the default limit of 5000 in a list view. We did some of our own testing and found that we could support 15,000 items in a list view without the delay being too great and without impacting the server too much. We had to increase our default as we found very little to help out calendars (tip: in the default list make the default view based on date range), and especially things like workflow history (which could be truncated or at least trimmed), but we found a lot of archives, and even a few doc libraries on their way to greater growth.
Out of 50 lists over 7000 items here’s the breakdown: 21 archive type lists, 7 workflow history lists, 6 somewhat transactional lists, 7 _catalogs/users lists, 4 data import lists, 3 doc libraries, 2 calendars
Required Reading for Devs and Admins, Power Users may still gain value out of reading this. It is *really* good info: Working with Large Lists in SharePoint 2010: List throttling by Steve Peschka
Below is a screenshot where you can see where the list view threshold is set. During upgrade we had a temporary window of the week following upgrade where we’ve been supporting 50,000. With so many lists over the threshold it was still 24 lists over 50,000 that we needed to work with on a case by case basis. Sounds easy to just increase this default right? Well, in the beginning you may not see much impact, but then as the list usage gets higher than the threshold may matter a lot more. The Microsoft paper "Designing Large Lists and Maximizing List Performance" strongly recommends not changing the default. You are "strongly recommended not to change this default. If poor performing queries are used on lists with more than 5,000 items, overall throughput may significantly decrease when raising this limit." It goes on to say "Operations, such as creating an index on a list that is over this limit, are prevented because the operation affects more than 5,000 items. This limit prevents the queries that have a selectivity (items that can be efficiently filtered using filter criteria) more than 5,000 items. This limit also prevents queries that filter on columns that are not indexed. This is because a query that filters (and in some cases sorts) on a column that is not indexed must perform the filter on all items in the list in order to retrieve the correct data set and will operate on more items than the list view threshold. The default value for this limit is based on farm and list performance and how SQL Server manages locks."
The document as well covers what doesn’t work when you’ve exceeded threshold:
What Fails to work well when you’re over the threshold
Below: SharePoint Administrators can change the list view threshold which is configurable per web application with a default of 5,000
Within the list the threshold is visible when you start to grow. This comparison of used vs. the max limit is similar to the storage manager feature where you can see how you compare.
Above: The list may not be displaying all items if during a filter operation it says "displaying only the newest results below." If you add an index you can display up to the maximum items allowed in the list view.
Why do I need an index? When the container (folder or list) contains more items than the list the view threshold, it does a full scan against the entire folder/list because there is no index and will fail to return the items.
How to create an index
1. Go to the list that you want to set an index for.
2. On the List tab, click List Settings.
3. Under Columns, click Indexed columns.
4. Click Create a new index.
5. Under Primary column for this index, select a column that is frequently filtered with.
6. Click Create.
Note: If you get an error preventing you from creating the index, you may need your administrator to create the index for you. Or, you may need to create the index during the Daily Time Window for Large Queries.
If you’re having issues with Datasheet view not working I suggest this great thread on the forums which explains some of the compatibility issues with clients.
How to configure key filters
1. Go to the list that you want to set a key filter for.
2. On the List tab, click List Settings.
3. Click Metadata navigation settings.
4. Under Available Key Filter Fields, select the column you want to filter, and then click Add.
You may need to create the key filter during the Daily Time Window for Large Queries or have your administrator set it up if you get an error not allowing you to set it up due to the list view threshold size.
5. Click OK.
As well you may get errors related to setting fine grained permissions. When a list contains more items than the list view threshold and you try to setup granular permissions on folders it will fail because it affects too many rows. You can still set fine-grained permissions on child items, such as documents, in a large list, although you cannot set the permissions on the list itself or on folders that contain more items than the list view threshold.
Error in SharePoint Foundation where it won’t even try to display the list.
Good resources for Large lists:
An oldie but goodie whitepaper: Steve Peschka’s white paper: Working with Large Lists in Office SharePoint Server 2007.
Another reference: Managing lists with thousands or millions of items