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.

Software Boundary Guidance for SharePoint 2013 Gotcha’s (Don't do this)

Recently published on Microsoft TechNet (30 October) were the Software boundaries and limits for SharePoint 2013. Some noticeable adds are in this guidance so please take the time and familiarize yourself with the changes. Some of the guidance however did not change and a couple in particular I want to reference due to the fact on pretty much every engagement I go on with a customer these areas are not adhered to and in some cases blown completely out of the water.

List view lookup threshold	8 join operations per query	Threshold	Specifies the maximum number of joins allowed per query, such as those based on lookup, person/group, or workflow status columns. If the query uses more than eight joins, the operation is blocked. This does not apply to single item operations. When using the maximal view via the object model (by not specifying any view fields), SharePoint will return up to the first eight lookups.
List view threshold	5,000	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.

To give an example of what I see customers do:

Notice where I highlighted.

These two setting changes will provide you with the opportunity one day to bring your farm to its knees and in most cases will also cause you to pick up the phone and contact Microsoft to open a case. This is a case that can easily be avoided.. How? By reading the Software Boundaries and Limits for SharePoint 2013. We put this guidance out for a reason and the teams aren’t just randomly selecting numbers
out of the air and filling in the charts. We do extensive testing on our products with many different hardware configurations that we expect our customers to run under. These boundary numbers are areas where we
will see performance degradation occur. Also notice that some of these are soft boundaries and some are hard. The Hard boundaries are supportable stop points and should not be exceeded. The Soft boundaries fall in line with the example above. This is a throttling limit we set. You can adjust it if you like but you will face issues down the line. Depending on your hardware you may not run into it for some time past
the limit. But I can guarantee you that in some time you will run into an issue here.

So let’s go through an example of what this customer faced. The background here is that I was there for a week performing a Microsoft RAP (Risk Assessment Program) and this is one of the test cases that we
run.