Query Throttling and Indexing

  • Article

A familiar challenge when you work with SharePoint lists is how to address the performance degradation that can occur when your list contains a large number of items. However, SharePoint is capable of managing extremely large lists containing millions of rows. The often-quoted limit of 2,000 items per list, actually refers to the maximum number of items that you should retrieve in a single query or view in order to avoid performance degradation. Effective indexing and query throttling strategies can help you to improve the performance of large lists.

Note

For more information about working with large lists, see List Patterns, "Designing Large Lists and Maximizing Performance" from Performance and capacity test results and recommendations on TechNet and Handling Large Folders and Lists on MSDN.

What Is Indexing?

SharePoint enables you to index columns in a list. This is conceptually similar to indexing columns in a database table; however, in the case of SharePoint lists data, the index is maintained by SharePoint instead of SQL Server.

Indexing columns in a list can substantially improve the performance of various query operations, such as queries that use the indexed column, join operations, and ordering operations such as sorting. In any list, you can either index a single column or define a composite index on two columns. Composite indexes can enable you to speed up queries across related values. However, like with database indices, list indexing does incur a performance overhead. Maintaining the index adds processing to creating, updating, or deleting items from a list, and the index itself requires storage space. A list instance supports a maximum of 20 indices. Some SharePoint features require indices and cannot be enabled on a list where there is no index slot remaining. You should choose your indexed columns carefully to maximize query performance while avoiding unnecessary overhead.

Note

Not all column data types can be indexed. For a list of column types that can be indexed, see Enforcing Uniqueness in Column Values on MSDN. Also note that you cannot include text fields in a composite index.

What Is Query Throttling?

Query throttling is a new administrative feature in SharePoint 2010. It allows farm administrators to mitigate the performance issues associated with large lists by restricting the number of items that can be accessed when you execute a query (known as the list view threshold). By default, this limit is set to 5,000 items for regular users and 20,000 items for users in an administrator role. If a query exceeds this limit, an exception is thrown and no results are returned to the calling code. Out-of-the-box, SharePoint list views manage throttled results by returning a subset of the query results, together with a warning message that some results were not retrieved. Farm administrators can use the Central Administration Web site to configure query throttling for each Web application in various ways. For example, farm administrators can do the following:

  • Change the list view threshold, both for users and for site administrators.
  • Specify whether developers can use the object model to programmatically override the list view threshold.
  • Specify a daily time window when queries that exceed the list view threshold are permitted. This enables organizations to schedule resource-intensive maintenance operations, which would typically violate the list view threshold, during off peak hours.
  • Limit the number of lookup, person, or workflow status fields that can be included in a single database query.

If the farm administrator has enabled object model overrides, you can also change list view thresholds programmatically. For example, you can do the following:

Query throttling is designed to prevent performance degradation, so you should only programmatically suspend throttling as a temporary measure and as a last resort. Ensure that you restrict the scope of any throttling overrides to a minimum. We recommend against changing the query throttling thresholds. The default limit of 5,000 items was chosen to match the default point at which SQL Server will escalate from row-level locks to a table-level lock, which has a markedly detrimental effect on overall throughput.

List-based throttling applies to other operations as well as read operations. In addition to query operations, throttling also applies to the following scenarios:

  • Deleting a list or folder that contains more than 5,000 items
  • Deleting a site that contains more than 5,000 items in total across the site
  • Creating an index on a list that contains more than 5,000 items

Note

For detailed information about the behavior and impact of indexing and query throttling, download the white paper Designing Large Lists and Maximizing Performance from Performance and capacity test results and recommendations on TechNet.

How Does Indexing Affect Throttling?

The list view threshold does not apply simply to the number of results returned by your query. Instead, it restricts the numbers of database rows that can be accessed in order to complete execution of the query at the row level in the content database. For example, suppose you are working with a list that contains 10,000 items. If you were to build a query that returns the first 100 items sorted by the ID field, the query would execute without issue because the ID column is always indexed. However, if you were to build a query that returns the first 100 items sorted by a non-indexed Title field, the query would have to scan all 10,000 rows in the content database in order to determine the sort order by title before returning the first 100 items. Because of this, the query would be throttled, and rightly so—this is a resource-intensive operation.

In this case, you could avoid the issue by indexing the Title field. This would enable SharePoint to determine the top 100 items sorted by title from the index without scanning all 10,000 list items in the database. The same concepts that apply to sort operations also apply to where clauses and join predicates in list queries. Careful use of column indexing can mitigate many large list performance issues and help you to avoid query throttling limits.