Sql Query to Find Where Threshold Is Met

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.

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.

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.

• Separate your content into multiple lists and libraries.
• Create multiple list views. Each view can show the content in different ways allowing users to choose what they prefer to see. Some common example may include Recently Changed, Recently Added, Created By Me, Assigned to Me, Due Today.
• Add filters to your list views to limit the content that is returned.
• Use "AND" when defining your filters. Choosing "OR" will return all results first before your filter is applied.
• The first criteria in your view filters should have the greatest impact on reducing the number of items returned.
• Add an Index to any columns used for sorting or filtering your views. SharePoint automatically works some magic in SQL Server when you create an indexed column which allows it to quickly analyze and return values, bypassing thresholds.

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.

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:

• In your Web Application Settings, ensure that Object Model Override is enabled.
• The user executing the code must have Full Control or Full Read permissions.
• The SPQueryThrottleMode property of your SPQuery should be set to Override.

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.

As a Microsoft Gold Partner, we offer Microsoft 365-based solutions to help businesses become more productive and efficient through collaboration.

If you'd like to learn more about how our SharePoint solutions can transform your business, visit our SharePoint Consulting page.

Sql Query to Find Where Threshold Is Met

Source: https://www.abelsolutions.com/working-with-list-view-thresholds-in-sharepoint/

Share this post