SharePoint 2010 lists and libraries can hold a maximum number of 30,000,000 items. In a SharePoint site the list view threshold is 5000 for users and 20,000 for auditors and administrators.

SharePoint large list and libraries operations like Delete/Update are one of the top performance killers.

What can we do if the server is overloaded when we access large lists and libraries? One solution is list view throttling.

A. List view throttling

List view throttling protects the server from unintentional load by limiting the number of list items returned per view. When the list view is greater than throttle limit, users receive a message that all the data cannot be retrieved.

Resource throttling monitors CPU %, Available Memory, ASP.NET Queue and Wait time in Queue. Every 5 seconds a timer job is checking resources. Throttling will begin after 3 unsuccessful checks. This throttling period will end after one successful check of the resources.

During a period of active throttling, HTTP GET requests and Search Robot requests will generate a 503 error and will be logged in the event viewer and no new timer jobs will begin.

Resource throttling counters can be changed via PowerShell commands. More in this article

Activate List throttling

Remark: Is recommended to configure at the Web Application level

– Go to SharePoint Central Administration

– Click on Application Management, and then click on Manage Web Applications.

– Click on a single web application to select it, then in the ribbon click on the General Settings drop down and select the Resource Throttling menu item

– A new page will open with the following fields:

– List View Threshold – this is the maximum number of items that can be retrieved in one request, all operations that exceed this limit are blocked. The default value is 5,000, the smallest is 2000.

– Object Model Override – this option needs to be enabled in order to enable auditors or administrators to retrieve items through the object model

– List View Threshold for Auditors and Administrators – this is a special limit for “super users”. Allow super users to retrieve up to the number of items set in this property via object model. Object model override needs to be activated. More in this article

– List View Lookup Threshold – This feature limits the number of Lookup, Person/Group, or Workflow Status fields that a query can perform. It should not be greater than 8

– Daily Time Window for Large Queries – this option allows you to create a block of time during the day, typically when usage is low, that you will allow queries to run and not enforce the list throttling limits.

– List Unique Permissions Threshold – This is the maximum number of unique permissions allowed per list or library

– Turn Backward-Compatible Event Handlers on or off. By default, this is off. If you have a large amount of development work in SharePoint Server 2007 that leveraged event handlers for lists or libraries, you will want to turn this on. You should check with your developers if you are upgrading from SharePoint Server 2007.

– Configure HTTP Request Monitoring And Throttling. This changes the setting in IIS for all Web servers in the farm for this Web application. You can turn request throttling off to allow services like search to run uninterrupted on the farm.

– Change Log constraints, do not to reduce this too much because it will negatively affect servers that rely on history information for sites contained in the Web application

– Click on Ok

Before applying list view throttling is recommended to test the functionality in a test environment by performing different stress tests.

B. SharePoint indexed columns

Another important step to improve list performance is indexing SharePoint columns. This option with query throttling strategies can help you to improve the performance of large lists.

You can index up to 20 columns but be aware that not all of the columns types are supported to be indexed. Here you have a complete list.

Activate Indexed columns

Remark: Before applying the following operations be sure you are doing them during the “Daily time Window” if List throttling is active, because creating an index requires accessing all the items in the list.

– Navigate to your SharePoint list and library

– In the ribbon, under List Tools or Library Tools, click the List or Library tab

– In the Settings group, click List Settings or Library Settings.

– Under the Columns section, click Indexed columns.

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

– Do one of the following:

To create a simple index:

– In the Primary Column section, under Primary column for this index, select the column.

– Click Create.

To create a compound index:

– In the Primary Column section, under Primary column for this index, select the column.

– In the Secondary Column section, under Secondary column for this index, select a different column.

– Click Create.

Thank you for reading this article.

Sources of this article:

http://blogs.technet.com/b/speschka/archive/2009/10/27/working-with-large-lists-in-sharepoint-2010-list-throttling.aspx Working with Large Lists in SharePoint 2010 – List Throttling

http://technet.microsoft.com/en-us/magazine/gg491393.aspx Understand and Configure Resource Throttling on SharePoint Server 2010

http://technet.microsoft.com/en-us/library/cc262813.aspx Designing large lists and maximizing list performance (SharePoint Server 2010)

http://office2010.microsoft.com/en-us/sharepoint-server-help/manage-lists-and-libraries-with-many-items-HA010378155.aspx?redir=0 Manage lists and libraries with many items

http://blogs.msdn.com/b/dinaayoub/archive/2010/04/27/programmatically-using-the-object-model-override-for-the-list-view-threshold.aspx Programmatically Using the Object Model Override for the List View Threshold

http://blogs.msdn.com/b/dinaayoub/archive/2010/04/22/sharepoint-2010-how-to-change-the-list-view-threshold.aspx SharePoint 2010: How to Change the List View Threshold and Other Resource Throttling Settings

http://msdn.microsoft.com/en-us/library/ff798465.aspx Query Throttling and Indexing