SharePoint Stuff

In this post we look at a common issue when working with large libraries or lists in SharePoint – the list view threshold, and how to overcome it.

The problem

If you’ve ever worked with large SharePoint lists or libraries, you’ve probably tried to to open a list, or add a column you see:

“unknown render failure” or “The attempted operation is prohibited because it exceeds the list view threshold.”

This usually happens when your list goes beyond 5,000 items, triggering the List View Threshold. SharePoint stores list data in SQL tables, and when you have more than 5,000 items, or try to filter or sort on a column that isn’t indexed, SharePoint blocks the action to prevent performance degradation. This in-built limit is designed to protect performance of the list, but often can create momentary panic for list owners as it appears that the entire list is broken!

The solution

Adding an index to frequently used columns allows SharePoint to quickly locate rows without scanning the entire list, which will avoid threshold-related issues. Here’s how to add an index:

• Navigate to List Settings > Open your list or library.
• Click the Settings gear > List settings (or Library settings).
• Scroll down to the Columns section> Click Indexed columns.

Note: You can only have a maximum of 20 indices on a single list.

• Click Create a new index.
• Select the column you want to index (e.g., Status, Created Date).
• Click OK.

Note: Certain columns have the ability to connect a secondary column to the index. This will make the index a compound index, combining the two columns to create a way to query items based on both columns using an “AND” condition.

Bonus: Example scenario

I was recently working with a SharePoint list that had over 6,000 items, users began reporting that the list was not opening when selecting and displaying the “unknown render failure” message. What was the issue? A recently added column that wasn’t indexed. After the index, the default view began working correctly. A quick fix to get the list opening again for users whilst you troubleshoot is to apply an item limit on the default view. Whilst this means not all items are displayed, it at least fixes the immediate issue:

• Click the Settings gear > List settings (or Library settings).
• Select the default view (or the view that has stopped working).
• Scroll down to Item Limit > Expand this option.
• Set number of items to display to something under 5,000
• Tick the limit the total number of items returned to the specified amount checkbox
• Press OK.

Just remember to revert these settings once you have fixed your issue with indices!

---