SharePoint 2010 List Throttling

While I was searching for content for data optimization in case of large list and libraries, I found a new feature incorporated into SharePoint 2010 for putting limit on the amount of data to be retrieved from the servers. The feature is called List Throttling. 

What exactly it does is allow you to set a limit for how many rows of data can be retrieved for a list or library at any one time. As an example, you can consider a situation where user is having a list with 50,000 records and he creates a view to show all items in a single page, this could put unnecessary load on servers. List throttling ensures that such a request would not be allowed to execute. The hit on the server is alleviated, and the user gets a nice little message that says sorry, we can’t retrieve all of the data you requested because it exceeds the throttle limit for this list.  

List throttling is managed through web application. Click on Application Management --> Manage Web Applications.  Select the web application you want to configure list throttling, then in the ribbon click on the General Settings drop down and select the Resource Throttling item. Below figure shows how to enable list throttling in a web application.

Below is the list of options and their details:

List View Threshold – Specifies the maximum number of items that a database operation can involve at one time. Operations that exceed this limit are prohibited.  The default value is 5,000 and minimum value is 2,000.

Object Model Override – If you choose to allow object model override, users to whom you grant sufficient permission can override the List View Threshold programmatically for particular queries. This option needs to be enabled in order to enable super users to retrieve items through the object model, up to the amount defined in the List query size threshold for auditors and administrators.

List View Threshold for Auditors and Administrators – Specifies the maximum number of items that an object model database query can involve at one time for users to whom you grant sufficient permissions through Security Policy. This is a special limit for “super users”.  It is important to understand that this DOES NOT allow these super users to see more items in a list view.  This property is used in conjunction with the Allow object model override property described below.  That means that if Object Model Override is set to “Yes”, then these super users can retrieve up to the number of items set in this property, but only via the object model.  The way you become a “super user” is a farm admin creates a web application policy for you that grants you rights to a permission level that includes either the Site Collection Administrator and/or Site Collection Auditor rights. 

List View Lookup Threshold – Specifies the maximum number of Lookup, Person/Group, or workflow status fields that a database query can involve at one time.

Daily Time Window for Large Queries – Specifies a daily time window when large queries can be executed. Specify a time outside of working hours for this window because large queries may cause excessive server load.

List Unique Permissions Threshold - Specifies the maximum number of unique permissions that a list can have at one time.

Backward-Compatible Event Handlers - Turn on or off backward-compatible event handlers for this Web application. If this is turned off, users cannot bind document libraries to backward-compatible event handlers.   HTTP Request Monitoring and Throttling - Turn on or off the HTTP request throttling job. This job monitors front-end Web server performance, and in the event of HTTP request overload, rejects (throttles) low priority requests. You can turn request throttling off to allow services such as Search to run uninterrupted on the farm, however, in an unthrottled farm experiencing overload, front-end Web servers become less responsive, and may stop working.

In order to retrieve information using the object model in order to retrieve up to the number of items specified in the List query size threshold for auditors and administrators property, there is a property you need to set in your query object.  The property is called QueryThrottleMode and it applies to the SPQuery and SPSiteDataQuery classes.  You simply set this property to override and then use your class instance to query. 

using (SPSite objSPSite = new SPSite("http://<Your Site>")) 

{

using (SPWeb objSPWeb = objSPSite.RootWeb)

{

SPList objSPList = objSPWeb.Lists["Test List"];

SPQuery objSPQuery = new SPQuery();

objSPQuery.QueryThrottleMode = SPQueryThrottleOption.Override;

            SPListItemCollection objColl = objSPList.GetItems(objSPQuery);

            //Your custom logic here for processed data

}

}