Manage large lists and libraries in SharePoint

There are many ways you can work with or query a SharePoint list or library without receiving a List View Threshold warning. You can store up to 30 million items or documents in a SharePoint list or library. Using the following ideas, you can get the information you need and stay within the 5000 item List View Threshold. For more info on the List View Threshold itself, see Overview of lists and libraries with many items.

Working with the List View Threshold limit

SharePoint has resource throttles and limits that govern the amount of data and throughput that can be managed. The List View Threshold is by default, approximately 5000 items, and is set to allow users to work with large lists, but keep good performance. There are three main ways to work with the List View Threshold:

• For all versions of SharePoint, manage the number of items returned using indexing, filtering, folders, and offline data.

• For Server versions of SharePoint, use an administrator scheduled Daily Time Window where limits are raised.

• For Server versions of SharePoint, a network administrator can raise the limit of the List View Threshold.

For SharePoint Online, this limit can't be changed, and is in place 24 x 7 to allow users on shared tenants to always have good performance on queries. To work around the limit, we've outlined some actions you can do to keep your queries within the limit.

Note: You may occasionally see a higher number of items returned in system generated views.

• Index and filter    Planning and, creating indexes and using them in filters can keep the number of items to under the List View Threshold. For more info, see Add and index to a SharePoint column or Use filtering to modify a SharePoint view.

• Using folders to organize    You can use folders to organize data effectively, though you should be careful not to have a query on the folder return more than the List View Threshold. For more info, see Create a folder in a list or Create a folder in a document library.

• Using Document center site    A Document center is a template that can be used to create a site with features geared for document search, storage, and manipulation. With the right permissions, you can create a site or subsite with this template. For more info, see Use a Document Center site.

• Synced and offline data    Taking data offline allows you to use Excel or Access to query your list data without limits. When you sync a document folder, you can work locally on your computer, and changes are updated to the server automatically. For more info, see Set up your computer to sync SharePoint Server or Sync SharePoint files with the new OneDrive sync client.

With SharePoint Server servers (SharePoint 2016, 2013, and 2010), the 5000 item List View Threshold is the default as well. However, because there's more control by network administrators, a Daily Time Window can be set where the limits are effectively removed, allowing large queries as well as other data intensive operations to be done. This time is usually in the evening when most users are not on the system. The administrator can also choose to raise the limit if appropriate.

The last choice, also with Server versions of SharePoint, is to change the limit. This is risky, since a larger limit increases the possibility of affecting the performance for some or all users.

To check your version of SharePoint, see Which version of SharePoint am I using?

Using indexes and filtered views to control data and improve performance

If you're blocked by the List View Threshold, but your total number of items is less than 20,000, you can add indexes to columns. On all versions of SharePoint, you can create a filtered view with a column index to help reduce the number of results when working with large lists and libraries. Creating a filtered view with an indexed column is a two-step process: create an index for a column and then create a view that uses the indexed column to filter the view.

For more info on indexing columns, see Add an index to a SharePoint column.

For more info on filtering columns, see Use filtering to modify a SharePoint view.

Organizing items into folders

Although folders are not required to use large lists and libraries, you can still use them to help organize your data and improve the efficiency of your data access. Folder creation is enabled by default in document libraries, but not in lists. For more info see Create a folder in a list or Create a folder in a document library.

About using folders

Note: With SharePoint online, if you use the Move to command or drag and drop, existing metadata is moved as well.

When you create a folder, behind the scenes you are creating an internal index. This internal index is also created for the root folder, or top-level of a list or library. When you access items in a folder, you are effectively using this internal index to access the data. Bear in mind that if a folder contains subfolders, each subfolder is counted as an item (but not any items in that subfolder).

Even when the total number of items in a list or library is very large, a view of a single folder is at least as fast as a view that filters the total number of items by using an indexed column. In some scenarios, it may be possible to distribute all the items in a list or library into multiple folders such that no folder has more than five thousand items.

It is important to consider the following when you use folders to organize a large list or library:

• A folder can contain more items than the List View Threshold, but to avoid being blocked, you may still need to use a filtered view based on column indexes.

• If you choose the Show all items without folders option in the Folders section when you create or modify a view in this list or library, you must then use a filter that is based on a simple index to ensure you don't reach the List View Threshold.

• It is often useful to make the default view show all the available folders without any filtering so that users can choose the appropriate folder when they insert new items. Displaying all the folders also makes it less likely that items will be incorrectly added outside the folders in the list or library. Note that, unlike libraries, there is no automatic way to move items between folders in a list.

Note: If you move items into the SharePoint Recycle Bin, those items will still be counted when determining whether the filter expression exceeds the List View Threshold. If you clear the recycle bin they are no longer counted. For more info, see Empty the recycle bin or restore your files.

To move files between folders in a library, see Move or copy a folder, file, or link in a document library.

Deleting lists

Deleting large lists requires database resources that can be blocked by the List View Threshold. Try to delete large lists during the Daily Time Window to avoid blocking. Remember that if the deletion process takes longer than the allotted window, it will continue until complete, regardless of other users. Allow enough time.

For more info on how to delete a list, see Delete a list in SharePoint.

Offline sync, external data, and Access data management

Taking data offline is often more convenient and efficient when you sync with your computer. You can use Excel, Access, or Outlook, depending on the list data you're working with. You can also use external data management without limits with Enterprise Resource Planning (ERP) systems, OData, and Web services, depending on your SharePoint service.

Using offline synchronization

You can make changes on your desktop or laptop computer, and then when you bring the data back online, synchronize changes and resolve conflicts in a smooth and efficient way. Working with large lists by using offline synchronization to "scrub", analyze, or report data, helps off-load database activity and minimize use of SharePoint resources.

You can take list data offline and synchronize changes when you come back online by using several Microsoft Office products. To export to these, you need to have the apps installed, either Office 365 or Office desktop.

Microsoft Access       Access can handle more rows of data than SharePoint, and you have many powerful tools to manipulate that data. Working with your data in Access and syncing with SharePoint can let you work with larger data sets.

You can read and write most native lists from Access by linking to them, and Access works well with virtually all SharePoint data types. Linking lets you connect to data in a SharePoint list, so that you create a two-way connection to view and edit the latest data both in the SharePoint list and your Access database. Access creates a copy of (or replicates) the SharePoint list in an Access table. Once the Access table has been created, you can work with list data in Access up to the Access limit of two gigabytes (excluding any attachments which are not stored locally). Furthermore, Access caches the list data on the client, uses an efficient in-memory, write-through cache, and only transfers changed list items. All of which makes queries and updates perform much faster. A conflict resolution dialog box also helps to manage conflicting updates in a smooth way.

If the Access Services feature is activated, then you can work with considerably more data than the List View Threshold, up to 50,000 items by default. Access automatically processes list or library data in small batches and then reassemble the data, a technique that enables working with substantially more data than the List View Threshold, and without adversely impacting other users on the SharePoint site. Find more information in the Using Access Services section below.

Microsoft Excel        You can export SharePoint lists to an Excel Table, which creates a one-way data connection between the Excel table and the SharePoint list.

Export to Excel in SharePoint 2016 or 2013

Excel also will let you work with large lists without blocking. Additionally, you can sync Excel with both SharePoint and Access to take advantage of the benefits of each platform.

When you update your data from the SharePoint list and refresh the Excel table, Microsoft Excel replaces the Excel data with the latest SharePoint list data, overwriting any changes that you made to that Excel table.

Export to Excel in SharePoint Online

Once the data is in the Excel table, you can take advantage of the many data analysis features of Microsoft Excel, such as powerful and flexible worksheets, PivotTable reports, professional-looking charts and spark lines, conditional formatting with icons, data bars, and color scales, and sophisticated what-if analysis operations.

If the Access Services feature is activated, then you can work with considerably more data than the List View Threshold, up to 50,000 items by default. Find more information in the Using Access Services section below.

Microsoft Outlook        From Microsoft Outlook, you can read and write Contact, Task, Calendar and Discussion Lists, as well as synchronize Document libraries. For example, you can work with both standard task lists and project tasks lists by taking task lists offline, viewing, updating, and assigning new tasks, bringing them back online, and synchronizing them without leaving Outlook; You can also store, share, and manage SharePoint contacts more efficiently in Outlook.

Top of Page

Using Access with SharePoint Products

In Access, you create a web database based on Access Services by building tables based on linked lists, along with queries, forms, reports, and macros, that you published to a SharePoint site. These web databases are upgraded, you can still use and modify them, and you can create new ones from a template, but not from Access 2013.

You can create an Access App in SharePoint without using code. In general, a SharePoint App is a focused, point-solution that is easily distributed and made available in a marketplace. Think of an app as a convenient, alternative way to package, distribute, run, monitor, and retire a solution. For more info see Create an Access app

Unlike an Access web database application that stores data in SharePoint lists, an Access App uses a SQL Server database separate from SharePoint Products to store data, and is therefore not subject to the SharePoint List View Threshold. This significantly improves the scalability of Access solutions and makes for efficient handling of potentially millions of records. For SharePoint Server users, the SQL Server database can be configured to live behind your organization's firewall. For online users, the database is an SQL Server Azure database enabled with a free account and free storage. For more info on Azure, see Microsoft Azure home page.

Finally, you can still synchronize data between a SharePoint list and an Access table, and keep both sets of data up-to-date. This synchronization is two-way or bi-directional. Changes made in Access to the list data are uploaded to the SharePoint list, and changes made to the SharePoint list are downloaded to an Access on your computer. For more info see Import from or link data to a SharePoint list.

SharePoint search box and Document Center solutions

An alternative way to find documents or items in a large library or list is to use the list or library's Search box to enter a keyword or phrase. SharePoint Search has its own indexing mechanisms, and it is not subject to the List View Threshold or other related limits.

Another solution to manage large libraries is to create and use a Document Center site. Designed for document management, a Document Center site offers features such as checking files in and out, workflows, filters, and views.

Using the Search box to find list or library items

There are usually two search boxes on a SharePoint page, the site search at the top of the page, and the specific list or library search box. When you use the list or library's Search box, you can progressively expand the scope of the search operation:

• By default, the search scope is initially based on all the items in the current view and any subfolders. You see the results as columns that you can further filter and sort. If the List View Threshold is currently exceeded, not all results are displayed.

• If you don't find what you are looking for, you can expand your search scope to include the entire list including all subfolders, regardless of the current view or List View Threshold.

• Finally, you can expand the scope to search the entire site. In this case, you see all the results in the standard Search site page. You can further narrow the results by using the Refinement panel to filter, for example, by the author of a document or the creation date of a list item. You can even use Boolean syntax and logical operators to formulate more elaborate queries.

Notes: 

• The Search box is only available for lists and libraries that are displayed as client-side rendered views. Although the default behavior is to display the Search box, the Search Box can be hidden by the List View Web Part property, Search Box, in the Miscellaneous section of the tool pane. For more on web part properties, see Use the List and other Web Parts.

• 

Using the Document Center site

You can use a Document Center site when you want to create, manage, and store large numbers of documents. A Document Center is based on a site template and is designed to serve as a centralized repository for managing many documents. Features, such as metadata and tree view navigation, content types, and web parts, help you organize and retrieve documents in an efficient and meaningful way for your users. For more info on using site templates, see Create and use site templates.

Content Stewards can quickly configure metadata-driven navigation to perform well for most libraries without explicitly creating indexes, but also get assisted when creating additional indexes to enhance the performance over a wider range of filters and views. For more info, see Use a Document Center site

You can use a Document Center site as an authoring environment or a content archive:

• In an authoring environment, users actively check files in and out and create folder structures for those files. Versioning is enabled, and 10 or more earlier versions of each document can exist. Users check documents in and out frequently, and workflows can help automate actions on the documents.

• In a content or knowledge-base archive, by contrast, very little authoring occurs. Users only view or upload documents. Typically, content archives contain single versions of documents, and a site can scale to millions of files. In a typical scenario, such as a technical support center for a large organization, 10,000 users might access the content, primarily to read it. A subset of 3,000 to 4,000 users might upload new content to the site.

Using personal views, relational lists, and RSS feeds

To further help overall system performance and avoid reaching a resource threshold or limit, you can consider whether to use personal views, relational lists, or RSS feeds.

Personal views

Personal views    Because creating views that use a column index correctly is more complicated for large lists and libraries, you might want to remove the Manage Personal Views permission from contributors for a large list or library. By removing this permission, you can prevent users from creating a view that spans all the items and that might adversely affect the performance of the rest of the site. For more on permissions, see Edit permissions for a list or library.

Relational lists

Relational lists     When you create list relationships by using lookup columns, unique columns, and enforced relational behavior (also called referential integrity), you can reach the List View Threshold and may be blocked under the following circumstances:

• If you make a column unique in an existing list that has more items than the List View Threshold (but note that adding one item to a list that makes the list exceed the List View Threshold is an operation that is not usually blocked).

• If a list has more items than the List View Threshold, and then you turn on Cascade Delete or Restrict Delete) for a lookup field in that list.

RSS Feeds

RSS feeds     After RSS support is enabled in Central Administration and at the site collection level, you can enable and configure RSS support for the many types of lists and libraries. When users access the RSS Feed for a list or library, data is retrieved from the list. The default RSS view limits the number of items that are returned, based on the date that the item was last modified by using a filter on the Modified column. If the list or library has many items and users access the RSS Feed, it's a good idea to index the Modified column. You can also reduce the number of items retrieved by changing the number of items and number of days for which changes are included in an RSS Feed.

Find more information about managing RSS feeds see Manage RSS feeds for a site or site collection. To learn how to add an RSS feed, see Create an alert or subscribe to an RSS feed

SharePoint Server Administrators only

These operations can only be done by SharePoint or network administrators and farm administrators on SharePoint Server versions.

Using the Daily Time Window

The Daily Time Window is a way for administrators to specify a dedicated time period for all users to do unlimited operations without limits, and is usually scheduled during off-peak hours.

Although views are a primary way to retrieve items from a list or library, there are also other SharePoint commands and operations that are better done during the Daily Time Window.

Adding and deleting an index

Adding and modifying a list column

Deleting and copying folders

Changing security settings for a list or library

Saving a list with its content as a template

Deleting a website

Restoring or deleting items from the Recycle Bin

These commands and operation can fail if they exceed the site thresholds and limits when retrieving list data during normal hours. Running them without limits during the Daily Time Window can avoid failure and impacting other users.

How to setup the Daily Time Window

The Daily Time Window setup requires you to set a start time and a duration. When setting the time and duration, understand that if someone starts a long running query or operation during the window, it will continue until it completes, regardless of whether that's inside the window or not.

1. As an administrator, login to Central Admin.

2. Go to Application Management > Manage Web Applications.

   

3. Pick the application to set or change the Daily Time Window setting.

4. In the ribbon, click down arrow on General Settings to access the menu, and then click Resource Throttling.

   

6. Change the Daily Time Window and then set the time and duration you want to use.

   

7. Click OK when you're done.

If you're not a developer or don't have convenient access to developer resources to do these operations for you, you can defer these types of operations to the Daily Time Window. Check with your administrator to see when the Daily Time Window is scheduled.

How do I change the List View Threshold?

Note: SharePoint Online does not support changing the List View Threshold.

The List View Threshold is in place to provide performance across users and protect the server from unintentional overload during queries. The List View Threshold (LVT) can only be changed in Server versions of SharePoint by an administrator in the Central Admin app, and we recommend not changing it to avoid performance degradation. SharePoint Online does not allow administrators to change the List View Threshold to protect users on other tenants from performance issues when large data sets are retrieved. Use indexes, filters, and folders to manage queries instead.

If you only have a few queries that require raising the List View Threshold, consider using the Daily Time Window during off hours instead.

Warning: Changing the List View Threshold (LVT) is not recommended if you have other users, as larger values will degrade performance. Creating too large a value can cause high latency or failures for users, or potentially bring down a server.

Note: Central Admin is only available with Server installations of SharePoint.

If you really want to change the List View Threshold, follow these steps. They are standard from SharePoint 2010 through SharePoint 2016, though the UI may differ slightly. The Central Admin app is separate from your SharePoint site and requires farm administrator rights to access. You can only change the List View Threshold in Server versions of SharePoint.

1. As an administrator, login to Central Admin.

2. Go to Application Management > Manage Web Applications.

   

3. Pick the application to change the List View Threshold.

4. In the ribbon, click down arrow on General Settings to access the menu, and then click Resource Throttling.

   

5. Change the List View Threshold value (first on the list) to a reasonable value.

6. Click OK.

There are additional settings on the resource throttling page that as an administrator you will want to understand.

• List View Threshold for Auditors and Administrators:    This is by 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. By default, it's 20,000 rather than 5,000 for the List View Threshold. Raising this limit has the same ramifications as raising the List View Threshold. When changing this value, there are associated changes that may need to be made. For more info, see Programmatically Using the Object Model Override for the List View Threshold

• Object Model Override:    If you commonly use custom code on your deployment, and have a need for overriding the List View Threshold 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 List View Threshold as it will adversely affect your server's performance. In short: "tread lightly".

• List View Lookup Threshold:    This feature limits the number of joins that a query can perform. This equates to the number of Lookup, Person/Group, or Workflow Status fields that are included in the query. For example, a view that displays 6 lookup columns, and filters on another 3 distinct lookup columns uses 9 lookups. If the List View Lookup Threshold is set to 8, this will cause an error. Increasing the number to more than 8 is not recommended.

  Thorough testing by Microsoft has shown that there's a serious non-gradual performance degradation that shows up above 8 joins. Not only does server throughput 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. For more info see the Lookup columns and list views section of Performance and capacity test results and recommendations

• Daily Time Window for Large Queries:    This feature allows you to set a time every day where users can use larger queries without hitting limits. There are a few things that you should carefully consider before deciding what time to set this to:

  • To affect the fewest number of users, this should be set to an off-peak hour, or a time during which you expect the least load. If you pick a time in the middle of the work day for most of your users, then even those who are not using the large list may be affected negatively.

  • Try to keep it to a reasonable time frame such that people can use it to fix their lists, rather than contacting the administrator.

  • Operations started during the time window won't abort once the window ends. Consider the time needed in case a user attempts to delete a large list just before the cut off time.

  • Consider different time zones. This is especially important if your organization or customers are widely geographically distributed and share a centrally hosted SharePoint server. Setting it to 6pm may work for your own location, but would not be good 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 List View Threshold 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 reducing this number is recommended. It defaults to 50,000 which 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.

Overview of lists and libraries with many items

The List View Threshold (LVT) is in place to help get consistent performance across all users with queries to the database back-end. Here's some information about the limits, how it all works, and how to change the List View Threshold value.

Important: The List View Threshold cannot be changed in SharePoint Online. There also is no ability to create a Daily Time Window on SharePoint Online. Those features are only available on SharePoint 2016, SharePoint 2013, and SharePoint 2010.

Why is 5,000 such a magic number?

To minimize database contention SQL Server, the back-end database for SharePoint, often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at once, then it's more efficient for SQL Server to temporarily lock the entire table until the database operation is completed.

Note: The actual number is not always 5,000, and can vary depending on your site, the amount of activity in the database, and your site's configuration.

When the whole table is locked, it prevents other users from accessing the table. If this happens too often, then users will experience a degradation of system performance. Therefore, thresholds and limits are essential to help minimize the impact of resource-intensive database operations and balance the needs of all users.

What happens behind the scenes when you access many items in a list or library?

The following diagram summarizes the key points about what happens behind the scenes when you access many items in a list or library.

1. List or library data in a site collection is stored in a SQL Server database table, which uses queries, indexes and locks to maintain overall performance, sharing, and accuracy.

2. Filtered views with column indexes (and other operations) create database queries that identify a subset of columns and rows and return this subset to your computer.

3. Thresholds and limits help throttle operations and balance resources for many simultaneous users.

4. Privileged developers can use object model overrides to temporarily increase thresholds and limits for custom applications with SharePoint Server versions.

5. Administrators can specify dedicated time windows for all users to do unlimited operations during off-peak hours with SharePoint Server versions.

6. Information workers can use appropriate views, styles, and page limits to speed up the display of data on the page.

Resource throttles and limits

Lists and libraries have specific maximum limits, as shown in this table.

Feature	Maximum Limit
Number of items in a list or library	30 million
Size of an individual file item or item attachment	10 Gigabytes

The following table summarizes information about resource throttles and limits that you need to be aware of. These throttles and limits are set on the Resource Throttling page in Central Administration with SharePoint Server versions. Contact your administrator for specific limits and requests for your site.

Note: To assist with management, the administrator is not subject to the following resource throttles and limits.

Note: Not all of these settings are available through the UI, and only with SharePoint Server versions.

Threshold or Limit	Default value	Description
List View Threshold	5,000	Specifies the maximum number of list or library items that a database operation, such as a query, can process at one time. Operations that exceed this limit are blocked. To give you time to make alternative plans, you may be warned on the List Settings page when your list has exceeded 3,000 items. The warning contains a help link to this topic.
Unique permissions limit	50,000	Specifies the maximum number of unique permissions allowed for a list or library. Every time you break the inheritance of permissions for an item or folder, it is counted as 1 unique permission toward this limit. If you try to add an item that would lead to exceeding this limit, you are prevented from doing so.
Row size limit	6	Specifies the maximum number of table rows internal to the database used for a list or library item. To accommodate wide lists with many columns, each item is wrapped over several internal table rows, up to 6 rows and up to a total of 8,000 bytes (excluding attachments). For example, if you have a list with many small columns, one that contains hundreds of Yes/No columns, then you could exceed this limit, in which case you would not be able to add more Yes/No columns to the list, but you still may be allowed to add columns of a different type. Administrators can only set this limit by using the object model, not through the user interface.
List View Lookup Threshold	12	Specifies the maximum number of join operations, such as those based on lookup, Person/Group, or workflow status columns. If the query uses more than eight columns, the operation is blocked. However, it is possible to programmatically select which columns to use by using maximal view, which can be set through the object model.
List View Threshold size for auditors and administrators	20,000	Specifies the maximum number of list or library items that a database operation, such as a query, can process at one time when performed by an auditor or administrator with appropriate permissions. This setting works in conjunction with Allow Object Model Override.
Allow Object Model Override	Y	Specifies whether developers can perform database operations, such as queries, that request an override of the List View Threshold to the higher limit specified by the List View Threshold for auditors and administrators. An administrator must enable an object model override, and then developers with appropriate permission may then programmatically request that their query use the higher List View Threshold to take advantage of it.
Daily time window	None	Specifies a time period during which resource thresholds and limits are ignored. An administrator can configure a time window during "off-peak" hours in 15 minute increments and up to 24 hours, such as, 6:00 PM to 10:00 PM or 1:30 AM to 5:15 AM. A database operation or query started within the daily time window continues until completion (or failure) even if it doesn't finish within the specified time window.

Leave us a comment

Was this article helpful? If so, please let us know at the bottom of this page. If it wasn't helpful, let us know what was confusing or missing. We'll use your feedback to check the facts, add info, and update this article. Please include your SharePoint, Operating System, and browser versions.

Updated July, 2017.