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 30 million items or documents in a SharePoint list or library, and using the following ideas, you can get the information you need and stay within the 5000 item List View Threshold.

Updated November 15, 2016 thanks to customer feedback.

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.

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.

Top of Page

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

Large Lists and Libraries

  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 on-premises versions.

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

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

Top of 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 on-premises 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 settings are available through the UI, and only with SharePoint on-premises 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.

Top of Page

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 on-premises versions of SharePoint, use an administrator scheduled Daily Time Window where limits are raised.

  • For on-premises 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.

  • Index and filter    Planning and, creating indexes and using them in filters can keep the number of items to under the List View Threshold.

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

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

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

With SharePoint on-premises servers (SharePoint 2010, 2013, and 2016), 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 on-premises 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

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:

  • Indexes    An index retrieves items quickly and can improve list and library performance. You can create up to 20 indexes for a list or library. Unique values require an index and the ID column is automatically indexed.

    Note: The ID column does not count against the number of indexes you can create. You can use the ID column as an index, plus 20 columns of your own choice for indexes.

    Because each index adds some overhead to every database operation to maintain the index, it’s best to only add indexes for the most common or likely columns used to query the list or library.

  • Filtered views   When you create a filtered view, make sure the first indexed column in the filter expression does not exceed the List View Threshold. SharePoint selects the first indexed column in a query. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes, even if the result of the filtered view returns less than the List View Threshold.

    For example, you have a query: size = large AND color = red. In the list, size is not indexed, but color is. As long as there are fewer than 5000 "red" items in the list, the query succeeds in a large list. However, if you have a query size = large OR color = red, though database may find all the red items, it must scan the complete list to find all the large items. If there are more than 5000 items returned, the query is throttled.

    If you use two or more columns in the filter expression, the determining index or indexes should use an AND operator. For example, if you want to return Dogs from a large list of animals. You have an unindexed column called Species where you have Dog as a value. If you just query for Species = Dog, your query will be throttled. However, if you have an indexed column called Class, your query becomes Class = Mammals AND Species = Dog. You could also search for cats and dogs with the query Class = Mammals AND (Species = DOG OR Species = Cats). The second query selects all Mammals, and then filters to Dogs and Cats.

    Note: If you move items into the 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.

In general, an index on a column enables you to quickly find the rows you want based on the values in that column, even when working with millions of items. When you combine indexes with filtered views, you can quickly retrieve the items you want. Then you can use the indexed column when you Create a filtered view based on column indexes of that list or library.

It's important to consider that each additional column index you create consumes extra resources in the database and adds some overhead to every operation to maintain the index. Therefore, you should only add indexes to columns that are actively used for filtering in views.

To help choose which columns to index, look at all the views and see which columns are used most commonly used across different views for filtering. Note that any column that you define to have a unique value requires an index.

Important: Creating an index requires accessing all items in the list, so it's possible you may be blocked from creating an index for a column if the entire list exceeds the List View Threshold. If this occurs, do the operation during the Daily Time Window, or contact your administrator.

  1. Navigate to the site that contains the list or library.

  2. Click the name on the Quick Launch, or click on the Settings menu Office 365 Settings button , click View All Site Content or Site Content, and then click the name.

  3. Do one of the following:

    • In SharePoint 2010, 2013, or 2016 click List > List Settings or Library > Library Settings in the ribbon.

    • For SharePoint Online, click Settings SharePoint 2016 Settings button on title bar. , and then click List settings or Library settings.

  4. Scroll down to the Columns section, make note of the type for each column. Make sure you select a supported column in step 7.

    Supported and unsupported columns

    Supported Column Types

    • Single line of text

    • Choice (single value)

    • Number

    • Currency

    • Date and Time

    • Person or Group (single value)

    • Managed Metadata

    • Yes/No

    • Lookup

    Unsupported Column Types

    • Multiple lines of text

    • Choice (multi-valued)

    • Calculated

    • Hyperlink or Picture

    • Custom Columns

    • Person or Group (multi-valued)

    • External data

    Important:  Although you can index a lookup column to improve performance, using an indexed lookup column to prevent exceeding the List View Threshold doesn't work. Use another type of column as the primary or secondary index.

  5. Click Indexed columns.

  6. On the Indexed Columns page, click Create a new index.

  7. Do the following:

    1. To create a simple index, select a column that is not a Lookup column in the Primary Column section, under Primary column for this index.

    2. To create a compound index, select a different column that is not a Lookup column in the Secondary Column section, under Secondary column for this index.

    3. Click Create.

    For ideas on planning your libraries initially, see Plan document libraries in SharePoint

Top of Page

For a view to quickly filter through many items, the first column that you specify in the filter must be indexed. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes. You also need to make sure that the first column of the filter does not return more items than the List View Threshold, even if the result of the filtered view returns less than the List View Threshold.

If the first column of the filter returns more items than the List View Threshold, you can use a filter with two or more columns. When you define a filtered view that uses two or more columns, using an AND operator will usually limit the total number of items returned. But even in this case, you still need to specify as the first column in the filter the column that most likely returns the lesser amount of data. Note that using an OR filter almost always increases the number of items returned and won't be effective under these circumstances.

For more info on views and filters, see Create, change, or delete a view of a list or library.

Important:  Although you can index a single lookup column to improve performance, using an indexed lookup column to prevent exceeding the List View Threshold does not work. Use another type of column as the primary or secondary index.

Important:  If you're filtering a list by an indexed field, check the recycle bin for deleted items. Items in the recycle bin aren't deleted from the back-end database until they are removed from the recycle bin. If the total number of filtered items in the list and in the recycle bin is greater than the List Threshold limit, then you may get an incomplete set of results, or just none. For more info, see Empty the recycle bin or restore your files.

The following are some suggestions for typical views that would work well with indexed columns:

To filter by:

Index the:

For example:

Recently changed items

Modified column

To view only items that have changed in the past week, apply the filter Modified is greater than[Today]-7.

New items

Created column

To create a view of only those items that were added in the past week, apply the filter Created is greater than [Today]-7.

My items

Created By column

To create a view of only those items that you added, apply the filter Created By is equal to [Me].

Items due today

Due Date column (created by you in a list or library)

To create a view of only those items with a due date of today, apply the filter Due Date is equal to [Today].

Discussion board updates

Last Updated column

To create a view of only the discussions that were updated in the past month, apply the filter Last Updated is greater than [Today]-30.

Archive files in a document library

Date Modified

To create a view of documents not changed since the year 2006 (that you then want to archive), apply the filter Date Modified is less than December 31st, 2006.

Find a subset of financial data

Region, Year (as two simple indexes)

To create a view of financial data for the Northeast region in 2008, apply the filter Region equals "NE" AND Year equals 2008 because the Region column is likely to have less values than the Year column.

Note: Even when you create a filtered view based on column indexes, certain additional operations might still be blocked because they require accessing the entire list or library. These operations include the following: adding or deleting an index, creating a sort in the view definition; displaying a column total; and adding, updating, or deleting calculated fields. If this happens, try to run the operation during the Daily Time Window, when limits are lifted.

Note: If you are a developer, you can programmatically use a compound index on the first column. A compound index is based on two columns, primary and secondary, and can use either column to access the data quickly.

Top of Page

After you index a column, you can add it to a view when you create or modify a view, and then use the indexed column to filter the view. Before creating a view, you may want to add more columns to the list to enable more flexibility for sorting, grouping, and filtering.

Create a view
  1. Navigate to and open the List or Library where you want to create a view.

  2. Depending on your version, do one of the following:

    • In SharePoint 2010, 2013, 2016, or Online Classic mode, click the List or Library tab, and then click Create View.

      The SharePoint Library Create view  button on ribbon.
    • In, click the down arrow on the View Options button, and then click Manage views.

      View selection menu with Manage views selected

      Scroll down to the Views section, and then click Create view.

  3. On the View Type page, click the view you want to use. If unsure, choose Standard View.

  4. Type a name under View Name.

  5. To limit the number of items displayed on each page, scroll down to Item Limit and set the Number of items to display value. When you create a view, the default number of items to be displayed is 30.

    Set number of items to display in View Settings page
  6. There are two options you can choose when setting an item limit on a page:

    • Display items in batches of the specified size which creates a page-by-page display of the data that is useful when you are browsing items in a non-interactive way.

      When there are more items than your limit setting, the display will be paged.

      Feature image

    • Limit the total number of items returned to the specified amount creates a hard limit that may or may not return the entire results of your filter operation. This can be useful when testing your view, creating a prototype, or you only want to retrieve the top values in a view.

  7. To see all the information from items in your list in a vertical pane format, use the Preview Pane style. The preview pane is one of the View Styles you can choose under Style when editing or creating a view.

    Styles choices in the View settings page

    You can more quickly navigate the data by hovering over the item title in a scrolling region on the left side of the page, to see all the column values of the current item vertically listed on the right side of the page. Less initial data is displayed, and this helps display the view faster. This style is also very useful when your list is wide or has many columns and would require horizontal scrolling to see the data.

    Feature image

  8. To see all the information from items in your list in a vertical pane format, use the Preview Pane style. The preview pane is one of the View Styles you can choose under Style when editing or creating a view.

    Styles choices in the View settings page

    You can more quickly navigate the data by hovering over the item title in a scrolling region on the left side of the page, to see all the column values of the current item vertically listed on the right side of the page. Less initial data is displayed, and this helps display the view faster. This style is also very useful when your list is wide or has many columns and would require horizontal scrolling to see the data.

    Feature image

  9. In the Filter section, click Show items only when the following is true, and then choose how you want to filter the items based on one of the columns that is indexed.

    For example, to create a view of only items that changed today, choose the Modified (Indexed) column and the is equal to condition, and then type [Today].

    Note: If you do not see any columns that say (Indexed) after the name, you do not have any indexed columns available for that list or library. You must first Create a simple or compound index.

  10. Click OK.

To Modify a view
  1. Navigate to and open the List or Library where you want to create a view.

  2. Depending on your version, do one of the following:

    • In SharePoint 2010, 2013, 2016, or Online Classic mode, click the List or Library tab, select the view under Current View, and then click Modify View.

      Library tab with Modify View highlighted
    • In SharePoint Online, click the down arrow on the View Options button, and then click Manage views.

      View selection menu with Manage views selected
  3. Scroll down to the Views section, and then click a view.

  4. You can then change settings as outlined in steps 5 through 9 on Create a view above.

  5. Click OK.

Top of Page

Metadata Navigation and Filtering is enabled by default on most SharePoint sites. Even if metadata navigation has not been configured for a specific list or library, Metadata Navigation and Filtering is still at work behind the scenes to improve the performance of views. The Metadata Navigation and Filtering feature can automatically select the best index to work every time a view is loaded. When you load new views, apply filters to views, clear filters, or apply a sort on a field, query optimization determines the best way in which to query the database.

If a user creates or loads a view that cannot use an index to query the list, then Metadata Navigation and Filtering will construct and execute a fallback query. A fallback query is a modified version of the original user query that displays a partial set of the items requested because it queries against only a portion of the list instead of the entire list. It is intended to provide you with some useful results in circumstances when the original query is blocked due to large list throttling. Up to 1,250 of the newest items are displayed based on when those list items were added to the list. Occasionally, fallback queries will return 0 results if no items in the part of the list scanned by the query contain results that match the original user query.

For more info, see Set up metadata navigation for a list or library.

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.

Note: When you move data into folders, existing metadata doesn't follow with it.

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 the same library for SharePoint Online, see Move or copy a folder, file, or link in a document library. For SharePoint 2010, 2013, or 2016, see Introduction to libraries.

Top of Page

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.

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.

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

SharePoint Export to Excel button on ribbon highlighted
Export to Excel in SharePoint 2016 or 2013

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.

SharePoint Online list with Export to Excel highlighted
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

In Access 2010, 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.

The new approach is to create an Access App, which is a SharePoint App that you can create, and 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 on-premises 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.

Top of Page

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, is offers features such as checking files in and out, workflows, filters, and views.

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.

List property "Display search box" under Miscellaneous

Top of Page

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.

Top of Page

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

Top of Page

SharePoint on-premises Administrators only

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

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.

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.

    Central admin with Manage Web Apps selected
  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.

    Central Admin Resource Throttleing selected.
  5. Change the Daily Time Window and then set the time and duration you want to use.

    The Central Admin application settings page with Daily Time window highlighted
  6. Click OK when you're done.

Top of Page

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.

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 on-premises 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 on-premises 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 on-premises versions of SharePoint.

  1. As an administrator, login to Central Admin.

  2. Go to Application Management > Manage Web Applications.

    Central admin with Manage Web Apps selected
  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.

    Central Admin Resource Throttleing selected.
  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.

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 double-check the facts, add info, and update this article.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×