List View Threshold

Sometimes you might face a situation where you get this error:

9239 items (list view threshold is 5000)

The number of items in list exceeds the list view threshold, which is 5000 items.  Tasks that cause excessive server load (such as those involving all list items) are currently prohibited.

Even though it is recommended to use prescribed limits in your queries, but
you can change this limit from central admin:

Go to manage web applications > Select your web application > Under General Settings Select resource Throttling.

Under List View Threshold, give the minimum you can survive with.

List view threshold: Specifies the maximum number of list or library items that a database operation, such as a query, can process at the same time outside the daily time window set by the administrator during which queries are unrestricted.

List view threshold for auditors and administrators: Specifies the maximum number of list or library items that a database operation, such as a query, can process at the same time when they are performed by an auditor or administrator with appropriate permissions. This setting works with Allow Object Model Override.



When the number of unique security scopes for a list exceeds the value of the list view threshold (set by default at 5,000 list items), additional SQL Server round trips take place when the list is viewed, and this can adversely affect list view performance.

Manage Resource throttling by PowerShell :

$webApp = Get-SPWebApplication "http://xyz.com"
$myWebApplication.MaxItemsPerThrottledOperation = 5000  # change to 9300 for above

#Gets or sets a number representing the count of items at which list operations begin                       #to be throttled.
$myWebApplication.MaxItemsPerThrottledOperationOverride = $true

#Gets or sets the maximum items that are not throttled per operation when the current        

    # user is an administrator or auditor by security proxy
$myWebApplication.MaxItemsPerThrottledOperationWarningLevel = 2000

#If a list exceeds the number of items specified in this threshold then a warning is                             #displayed on the list settings page.
$myWebApplication.MaxListItemRowStorage = 8

    # Specifies the maximum number of table rows internal to the database that can be used     

    # for a list or library item. To accommodate wide lists with many columns, each item may               #be wrapped over several internal table rows, up to six rows by default. This is                                #configurable by farm administrators through the object model only.The                                              #ListItemRowStorage setting is the multiplier that SharePoint uses depending on the                       #field type.Gets or sets the maximum row ordinal that a list item may have.
$myWebApplication.MaxQueryLookupFields = 0

  #Gets or sets the maximum number of lookup fields that may be included in a list item                     #query.
$myWebApplication.DailyStartUnthrottledPrivilegedOperationsHour = 17

#Gets the hour of the start time for an unthrottled operation permissions on the local                     #time zone.
$myWebApplication.DailyStartUnthrottledPrivilegedOperationsMinute = 0

#Gets or sets the minute of the start time for an unthrottled privilege operation                                   #according to the local time zone.
$myWebApplication.DailyUnthrottledPrivilegedOperationsDuration = 1

  #Gets or sets the duration in hours for an unthrottled privilege operation.
$myWebApplication.IsBackwardsCompatible = [Microsoft.SharePoint.TriState]::false

#Gets or sets a value that indicates whether the current web application is backward                    #compatible. In terms of backwards compatibility, SharePoint 2013 provides great                             #support allowing you not only to run SharePoint 2010 migrated sites without switching        

    #to a SharePoint 2013 mode, but also to create site collections in two possible user                          #experiences: SharePoint 2010 and SharePoint 2013.
$myWebApplication.ChangeLogExpirationEnabled = $false

#Gets or sets a value specifying whether change logs are deleted after the time span                         #defined in the ChangeLogRetentionPeriod property.
$myWebApplication.Update()

Disable throttle for particular SPlist:

$mywebsite = Get-SPWeb "http://xyz.com/subsite"

$mySPList = $mywebsite.Lists["Eureka"]

$mySPList.EnableThrottling = $false
$mySPList.Update()

Verify if changed :  $mySPList.IsThrottled

Enable back :

$mySPList.EnableThrottling = $true
$mySPList.Update()

Best Practices:

• Try to identify minimum number columns in the list that should be indexed based on business scenario.


• Keep recycle bin clean in case of large deletion, deleted items are marked not to return in queries, but they are there in DB to decrease performance.


• Define folders inside your source list / library. Querying specific folders act as an internal index.


• Try using alternatives like Search APIs to query list. They have their own separate indexes

·    Create a simple or compound index.

Suggested Read:
Monitor traffic b/w SQL server and front end