Managing Large Lists in SharePoint for Users and Site Admins

Posted on August 3, 2011 by Joel Oleson

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

Add/Remove/Update a list column
Add/Remove/Update a List Content Type
Create/Remove an Index
Manage files which have no checked in version
Non-indexed recursive queries
Cross list query – Note: Includes queries by the content query Web Part and follows the list view threshold setting for auditors and administrators, which by default is 20,000. If the operation involves more than 20,000 items, the query fails.
Lookup columns that enforce relationship behavior
Deleting a list
Deleting a site
Save List as Template with Data
Showing Totals in List Views
Enable/disable attachments in a list
Explorer view (see more detail below)
List and Folder Permissions

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.

Here’s a list of walk through videos and from the help and how to videos and a handy quick reference

SharePoint lists: Techniques for managing large lists

Creating and Using an Index for Organizing a Large list – An index will allow you to perform not only faster results in a large list, but also allow you to support filtered views up to the maximum number allowed. Often it will only display the newest without an index on the column you are filtering on.

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.

Using Folders to Organize a Large List – While I see this as an old school way of attempting to address this, but please pay attention to this warning. Warning: "Open with Explorer" does not show any items if a container has more items than the list view threshold (excluding items in sub folders). If a folder has 8,000 items total, but it has a sub folder that contains 4,000 items and only 4,000 items in the root, then Open with Explorer will work. If the root of a list contains more items than the list view threshold then Open with Explorer will not show anything. To use Open with Explorer the list must have items organized into folders in amounts less than the list view threshold in the root of any container.
Datasheet View – If you’re using Access Services datasheet view (Access Web Datasheet) gets bumped up to 50,100 items. Requires Office 2003 32bit or later and Internet Explorer 32bit to support the ActiveX control. Note: The 64bit version of IE or Office will prevent it from working properly. You may find the list won’t display in the default datasheet view if the list is larger than the default list view size.

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.

Daily Time Window for Large Queries – If you have a large list and you want to add an index or a column or a number of other changes like key filters, you’ll need the administrator to make those changes, or if there’s a time window established then you can make changes that otherwise would be restricted during that window. Note: This setting is performed by a SharePoint Server Administrator, not a Site Administrator.
Using Key Filters for rapid filtering – There are certain columns and data types that you can setup as key filters. This feature is a great one for quickly being able to look up specific ranges of data from a large list.

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.

Using SharePoint Workspace for working with your large list – You can sync to SharePoint Workspace up to a maximum of 30,000 items. When you go past that even if you were already syncing prior the updates will fail. Note: You can’t sync calendars and some other special list types. The more common ones are able to be synced. After the initial sync, the data transferred is only on the items changed. So it’s very fast.
Using Excel to work with your large list – (Starts around 5min) you can sync up to a maximum 52,000 items. You will be warned if it’s larger than that. All the work you do in filtering is client side, so you won’t see the same server restrictions. Note you can’t add items via Excel to be added. The sync only goes one way.
Using Access to work with your large list – (Starts around 11min) the great thing about Access is the ability to update from the client and create custom queries and views.

http://office.microsoft.com/serverhelp/helppreview14.aspx?AssetId=HA010378155&lcid=1033&products=WSSENDUSER&respos=0&CTT=1&queryid=b98749789184406e9837c125756290a4

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.

Related Errors:

Error in SharePoint Foundation where it won’t even try to display the list.
Other related errors to large lists:
"Displaying only the newest results below. To view all results, narrow your query by adding a filter."
"Cannot show the value of the filter. The field may not be filterable, or the number of items returned exceeds the list view threshold enforced by the administrator."
"This view cannot be displayed because it exceeds the list view threshold (5,000 items) enforced by the administrator."
"The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."
Good resources for Large lists:

Designing Large Lists and Maximizing List Performance

Capacity planning for a large scale document repository

Developers

Programmatically Using the Object Model Override for the List View Threshold

Handling Large lists

An oldie but goodie whitepaper: Steve Peschka’s white paper: Working with Large Lists in Office SharePoint Server 2007.

Admin

Changing the List View Threshold and Other Resource Throttling Settings

Another reference: Managing lists with thousands or millions of items