Manage large lists and libraries

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 365 products. To export to these, you need to have the apps installed, either Microsoft 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. After 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.

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.

Excel also lets 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.

After the data is in the Excel table, you can take advantage of the many data-analysis features of Excel, such as powerful and flexible worksheets, PivotTable reports, professional-looking charts and spark lines, conditional formatting with icons, data bars, 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.

Microsoft Outlook         From 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 task 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

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. As these web databases are upgraded you can still use and modify them, and 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 information, 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 a SQL Server  Azure database enabled with a free account and free storage. For more information about Azure, see Microsoft Azure home page.

Lastly, 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 information, see Import, link, or move data to SharePoint.

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 appear.

• 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.

• Lastly, 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.

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 information about using site templates, see Create and use site templates in SharePoint Server versions.

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 information, see What is 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.

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.

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.

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.

1. As an administrator, log in 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. On the ribbon, on General Settings to access the menu, select the down arrow, and then select Resource Throttling.

   

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

   

6. When done, select OK.

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. To see when the Daily Time Window is scheduled, contact your administrator.

The List View Threshold (LVT) is in place to provide performance across users and protect the server from unintentional overload during queries. The LVT can only be changed in Server versions of SharePoint by an administrator in the Central Admin app. We don't recommend changing it to avoid performance degradation. SharePoint doesn't allow administrators to change the LVT 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 LVT, consider using the Daily Time Window during off hours instead.

If you really want to change the LVT, follow these steps. They're standard from SharePoint 2016although the UI might differ slightly. The Central Admin app is separate from your SharePoint site and requires farm administrator rights to access. You can only change the LVT in Server versions of SharePoint.

1. As an administrator, log in to Central Admin.

2. Go to Application Management > Manage Web Applications.

   

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

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

   

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

6. Select OK.

There are additional settings on the resource throttling page that as an administrator you'll 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 LVT. Raising this limit has the same ramifications as raising the LVT. When changing this value, there are associated changes that might need to be made.

• 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 might 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 can disable it if you don't 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 aren't planning on caching the content, and are planning on running these queries often, then we don't recommend using this method to get around the LVT because 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 resources, which negatively affects everybody else using that same database. For more information, 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 workday for most of your users, then even those who are not using the large list might 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 6:00 PM might work for your own location, but would not be beneficial, 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 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.

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.

When the whole table is locked, it prevents other users from accessing the table. If this happens too often, 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.

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.

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. For specific limits and requests for your site, contact your administrator.

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 might 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's counted as 1 unique permission toward this limit. If you try to add an item that would lead to exceeding this limit, you're 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 might 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 8 columns, the operation is blocked. However, it's 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 might 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.