SharePoint Query Throttling

Query throttling is designed to prevent performance degradation
In SP 2010, the default limit to access items from large lists is 5000 items for normal users and 20000 items for admin users. 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 Web Application: Set SPWebApplication.MaxItemsPerThrottledOperation property
  • For List: Set SPList.EnableThrottling property to false
  • For Specific Query Operation: Set SPQueryThrottleOption

Indexing affects throttling
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 because this is a resource-intensive operation. In this case, you could avoid the issue by indexing the Title field.

Continue ReadingSharePoint Query Throttling