September 2013: Working with List View Thresholds in SharePoint

Thresholds were introduced by Microsoft in SharePoint 2010 in order to prevent large queries from occurring which have an impact on performance of the SharePoint environment. I’ll explain why thresholds were introduced, what happens when thresholds are reached, and provide some tips to fine tune your queries to avoid performance and scalability issues.

Getting Started

First, it is good to know the difference between boundaries and thresholds. Boundaries are absolute limits that cannot be exceed. This is by design and includes things like the 2GB file size limit. There is no way to configure SharePoint so that you can upload files greater than 2GB in size. Thresholds are values that cannot be exceeded unless the values are modified. For example, a list has a threshold of 5,000 items but this can be changed if necessary.

Boundaries and Thresholds apply to many things in SharePoint including Web Applications, Content Databases, Site Collections, Lists and Libraries, Columns, and Pages. If you designing your SharePoint environment, it is important to understand these Boundaries and Thresholds. To understand the details, I recommend referring to the Software Boundaries and limits for SharePoint 2013 documentation on TechNet as well as the Designing Large Lists and Maximizing List Performance document provided by Microsoft.

I am going to focus primarily on List View Thresholds since they have direct impact on users and can easily sneak up on you over time as your content grows. There are over a dozen boundaries and thresholds that apply to Lists and Libraries alone. Be sure to read the TechNet documentation for details.

What is the List View Threshold?

The List View Threshold states that you cannot have more than 5,000 items (by default) returned in your list view. This does not mean you can only have 5,000 items in the list! It means that when a user clicks into a list, the number of items returned to the user should not exceed 5,000. This is important to understand when you are configuring you list views. And by the way, auditors and administrators have a 20,000 item threshold by default. If you’re an administrator setting up a list view, keep this in mind because your limits are different than that of your users!

Why is the List View Threshold set to 5,000 items?

Before I explain different ways to work around the list view threshold, you should understand why the threshold is in place and why it is specifically set to 5,000 items. The limitation actually comes from SQL Server.

In order to support dynamically creating lists and list columns as needed, SharePoint stores all of its list data and list columns in single SQL table. This allows greater flexibility when building your content and makes sense from a scalability point of view. When you browse to a list or library, SharePoint passes a query to SQL Server that will return all of the content requested by the user. For example, if the user simply opens the “All Items” view of a list SharePoint will build a query that requests all of the items for that view and passes that query to SQL Server.  SQL Server returns the results of the query back to SharePoint and the content is shown to the user.

For performance reasons, if SQL Server executes a query on a single table that would exceed 5,000 results, SQL Server will lock the entire table while the query executes. Since SharePoint stores all list data in a single table, a single query that returns over 5,000 items will lock all of the list and library data for that entire content database! Users who are accessing content in SharePoint while the SQL Table is locked will have to wait longer for their results to be returned.

What happens when the list threshold is reached?

So what happens when the list threshold is reached in a particular list or library? End-users, instead of seeing the content they requested, will see a friendly error message stating that their content cannot be returned.

sharepoint-thresholds-demo-list

The administrator has even more problems. Many operations are blocked when the list exceeds the threshold including, Add/Remove/Update list columns, managing a column index, deleting a list, deleting a site, save list as a template with data, showing totals in list views, enable/disable attachments. Many of these operations are blocked because when executed they affect every item in the list, which could trigger the threshold limits.

sharepoint-thresholds-server-error

Avoiding List Threshold Issues

CAREFULLY CONFIGURE YOUR LIST VIEWS

By carefully configuring your list views, you can return the data that users need to see while keeping under your threshold limits for that list or library. Here are some tips to consider while planning the structure for your content.

TURN OFF THE LIST VIEW THRESHOLD

I don’t recommend this option, and neither does Microsoft. The thresholds are in place to help keep your environment performing well. Ignoring this by simply turning off the thresholds is a recipe for poor performance and unhappy users.

CHANGE THE LIST VIEW THRESHOLD LIMITS

While this can be done easily in Central Administration, and it is a better option than turning off the threshold limits all together, this is still not a great option. However, this is an easy way to postpone the issue while you figure out the long-term solution. The Threshold limits can be changed in Central Administration under your Web Application settings.

SETUP DAILY TIME WINDOW FOR LARGE QUERIES

This could be a great option if you can limit large queries to only be executed during certain times of the day. This could be useful if your users need to pull large amounts of data or run custom queries during a specified time period during the day.

sharepoint-thresholds-large-queries

OBJECT MODEL OVERRIDE OF THE LIST VIEW THRESHOLD

If you are developing a custom solution with code that queries data in lists or libraries, the same threshold limits apply, that is unless you ensure the following:

CONTENT ITERATOR

Introduced in SharePoint 2010, the Content Iterator API allows you to iterate through content in a list rather than executing a query. This could be a way for you to process each item in a list while avoiding any throttling limits. The Content Iterator is a great way for you to crawl your sites and lists to process content in bulk. While performance is not as good as a simple query, it works great when the scenario calls for it.

OTHER TIPS FOR DEALING WITH LIST VIEW THRESHOLDS IN YOUR CODE

By default, your SPQuery will return all fields in the list you are querying.  You should set the ViewFields property to contain only the fields you actually need to return in the query. This is particularly useful if your list contains more than 8 lookup columns, which includes lookup columns, person/group columns, and workflow status columns. If your list contains more than 8 lookup columns, your SPQuery will only return the first 8, leaving you wondering why you are missing some fields in your results.
The RowLimit property of your SPQuery is intended to be used for paging or simply showing a subset of the results returned after the filtering occurs. Your threshold limits do not disappear simply by setting your RowLimit to be less than the threshold value!

Conclusion

I hope this information is useful for you. Be sure to read through the resources below for more information regarding List View Thresholds and other boundaries and thresholds enforced in both SharePoint 2010 and SharePoint 2013.

Resources