How to deal with list thresholds?

Question

Our client has a news functionality in our intranet based on the standard blog template in SharePoint 2013. The Posts list contains about 4,600 blog posts and the Comments list contains nearly 6,300 comments.

Client likes to know the best way to configure/design their news functionality for the long term future. So we'd like to know the facts about the 5,000 list (view) threshold and how to best deal with this situation. Is it true that the maintenance on the list and/or views beyond 5,000 items becomes very difficult/slow?

What is the best way(s) to go forward? Client considers several options such as:

• Creating a new, extra blog site (not ideal)

• Using PowerShell to see if you can add a folder to the list (normally this is possible on a list, but not on the lists in the blog site template) to save items in the folder and avoid the 5,000 threshold. (modifying standard SharePoint is actually a no-go for this client)

• Remove items (not preferred either)

Neither of these sounds good.

Would be great to hear your experience and configuration/building recommendations!

Answer 1

A few comments:

1. The views are limited to 5000 (by default), not really the list. This means a design including a folder structure is less subject to the limitation.
2. The performance impact is more determined by the usage of the list than its volume. Number of read or write operations, and ratio between writes and reads are very important.
3. The performance problem, if any, could impact the whole SQL DB, thus the whole Web app (or site collection), not only the list being accessed.
4. The performance impact also highly depends on your hardware capabilities (mainly SQL).
5. You can increase the 5000 limit from the Central Administration, per Web app.
6. If your view is always sorted on the same column, you can set an index on it. This will greatly improve performances.
7. You can completely remove the limitation on a per-list basis from PowerShell (use [SPList.EnableThrottling], https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splist.enablethrottling.aspx)
8. In SP2016, the problem could be mitigated: http://blogs.technet.com/b/wbaer/archive/2015/08/27/navigating-list-view-thresholds-in-sharepoint-server-2016-it-preview.aspx
9. In practice, I've seen lists with 20000+ items working perfectly fine, if they are not used too often (a few users a few times per day). And if you only have a few of these lists.

Answer 2

Bad Functional design from the start

Explain them even a digital board is like a physical board, you have to clean up at some stage.

Realistically nobody reads old Blog posts. You can even use the logfiles to prove nobody has read older blogpost.

In high volume environments we start a new Blog site each month...
Prevention is always better than clean-up.

Sure you can tackle the problem technically with increased LVTH and Indexes.. but you are

A. doing more work

and

B. only postponing your problems

Answer 3

Lists can contain millions of items, however, it is the views which should not return more than 5000 items; which is the out of the box threshold value for regular users and it is highly recommended not to increase this threshold. Otherwise you will see performance issues. You should consider creating views which return lesser number of items. This can be done by creating indexes and making sure that the first fields used in the query are indexed fields so that whole list is not scanned.

Suppose, you create a view and apply a filter on non indexed column and the view returns 4000 items and total number of items in a list is 7000. The normal users will get throttling exception. This is because the view will try to scan all items (7000) in the list and then apply the filter. However, if the field is indexed, it will only scan 4000 items and users won't see throttling exception.