How to Avoid List Throttling in SharePoint

If you’ve ever worked with large SharePoint lists, you’ve probably encountered the dreaded list throttling issue. Suddenly, your queries stop working, your scripts throw errors, and users start complaining. But what exactly is list throttling? Why does it happen? And most importantly, how can you avoid it?

In this article, we’ll break down everything you need to know about SharePoint list throttling, including its causes, pros and cons, and various techniques to work around it. We’ll also explore alternative implementations to ensure smooth performance while maintaining best practices.

What is List Throttling in SharePoint?

List throttling in SharePoint is a mechanism designed to protect the performance of the SharePoint environment by limiting the number of items that can be retrieved in a single query. Microsoft enforces these limits to prevent large queries from overwhelming the server.

By default, SharePoint Online has a list view threshold of 5,000 items, while SharePoint On-Premises allows administrators to configure this limit. This threshold ensures that queries do not overload the database and disrupt performance for all users.

When Does List Throttling Occur?

List throttling typically occurs when:

A user or script tries to retrieve more than 5,000 items in a single query: SharePoint automatically enforces the limit to prevent performance degradation. Even if your list contains millions of items, any single request that attempts to exceed this threshold will be blocked.
The query does not use indexed columns effectively: When filtering large datasets, indexed columns help optimize the retrieval process. If you apply filters on non-indexed columns, SharePoint has to scan the entire list, leading to throttling.
Batch operations are executed without pagination: If you attempt to retrieve or update a large number of items without breaking them into smaller batches, SharePoint will throttle the request to prevent excessive resource consumption.
Custom code, Power Automate flows, or third-party tools attempt to retrieve large datasets without optimization: Many automation processes retrieve list items in bulk without considering performance best practices, leading to throttling issues.
Filters are applied to non-indexed columns: Filtering without an index forces SharePoint to scan every item in the list, making the query highly inefficient and triggering throttling.

Why Does List Throttling Happen?

The primary reason for list throttling is performance protection. SharePoint needs to maintain an optimal user experience for all users in the tenant, and allowing unrestricted large queries could slow down the entire environment. The throttling mechanism ensures:

Preventing database lockups due to expensive queries: When a query attempts to retrieve a large dataset, the database engine must process and return all matching records. If multiple large queries run simultaneously, they can cause performance bottlenecks, affecting all users.
Maintaining fair resource allocation among multiple users: SharePoint Online is a multi-tenant environment, meaning multiple organizations share the same infrastructure. Throttling prevents any single user from consuming excessive resources, ensuring a fair distribution of system performance.
Ensuring stability in multi-tenant SharePoint Online environments: Microsoft prioritizes the stability of its cloud services. By enforcing list throttling, it prevents any single organization from negatively impacting the overall performance of the platform.

Pros and Cons of List Throttling

✅ Pros

Protects overall SharePoint performance: By limiting large queries, SharePoint ensures that the system remains responsive for all users.
Prevents single queries from monopolizing resources: Without throttling, a single poorly optimized query could slow down the entire site, affecting multiple users and workflows.
Encourages efficient data retrieval practices: Developers and administrators are encouraged to use indexing, pagination, and filtering to optimize queries, leading to better overall system performance.

❌ Cons

Limits flexibility for power users and developers: Users working with large datasets may find it challenging to retrieve the data they need without implementing workarounds.
Can break legacy applications and scripts: Older applications that were designed before throttling was enforced may stop functioning properly, requiring updates and modifications.
Forces developers to implement workarounds: Developers must use strategies like indexed queries, batch processing, and pagination to comply with throttling limits, which may require additional development effort.

Use Cases for Large List Handling

Before discussing solutions, here are some real-world scenarios where list throttling commonly becomes an issue:

Data Migration: When moving data from one SharePoint site to another, large lists need to be transferred in bulk. Without proper handling, migration scripts can easily hit throttling limits.
Custom Reporting: Business users often need to generate reports based on large datasets stored in SharePoint lists. If the queries used for reporting exceed the threshold, reports may fail to generate.
Power Automate Flows: Automated workflows frequently retrieve, process, and update SharePoint list items. If a flow attempts to retrieve too many items at once, it may get throttled, causing automation failures.
API Calls: Applications that integrate with SharePoint via REST or CSOM may need to fetch large amounts of data. Without pagination or filtering, these API calls can trigger throttling, resulting in incomplete data retrieval.

How to Avoid List Throttling: Best Practices

1️⃣ Use Indexed Columns

What It Does: Indexing speeds up queries by allowing SharePoint to quickly locate items that match filter criteria.
How to Implement: Navigate to List Settings → Indexed Columns to create an index on frequently queried columns.
Example PowerShell Command:

Set-PnPListItem -List "LargeList" -Id 1 -Values @{"IndexedColumn" = "Value"}

2️⃣ Use Views with Row Limits

What It Does: Restricts the number of items loaded at once, improving performance.
How to Implement: Create filtered views to display fewer than 5,000 items, applying indexing to filter criteria.

3️⃣ Use REST API with Pagination

What It Does: Breaks large queries into smaller chunks.
Example JavaScript Function:

function getItems(url, items = []) {
    return fetch(url, { headers: { "Accept": "application/json;odata=nometadata" } })
        .then(response => response.json())
        .then(data => {
            items.push(...data.value);
            if (data["@odata.nextLink"]) {
                return getItems(data["@odata.nextLink"], items);
            }
            return items;
        });
}

List throttling in SharePoint can be a frustrating challenge, but with the right optimizations, it’s completely manageable. By using indexed columns, filtered views, pagination, CAML queries, Power Automate optimizations, and alternative storage solutions, you can effectively work around SharePoint’s limitations while ensuring system performance remains stable.

If your project consistently hits throttling limits, consider moving high-volume data to external databases while using SharePoint as a collaboration and content management tool.

By following these best practices, you’ll not only avoid list throttling but also build scalable and efficient SharePoint applications that deliver the best performance to your users.

Got a throttling issue you need help with? Let’s talk in the comments below!