Recommended value for List view threshold

Question

As per Microsoft, the recommended value for "List View Threshold" should be 5000 but in my production environment this value has been set to 10,00000.

Will there be any performance impact or any other impact due to this?

Please advise.

Answer 1

This will have an adverse impact on the performance of the SharePoint server if the number of items returned by a view is such a large number.

To minimize database contention, SQL Server uses row-level locking as a strategy to ensure accurate updates without adversely affecting users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at the same time, then it is more efficient for SQL Server to escalate the lock to the entire table until the database operation is completed. When this lock escalation occurs, it prevents other users from accessing the table. The throughput also decreases as the list view threshold increases.

See this for more information: https://technet.microsoft.com/en-us/library/cc262813.aspx?f=255&MSPPError=-2147217396#Throttling

Answer 2

I think 1 million as a LVTH is really dangerous for your farm. If you have tons of items in the list then one can easily bring down your farm / sql server.

We highly recommend that you do not increase this number beyond the Default Threshold Limit , because through thorough testing we've observed that there's a serious non-gradual performance degradation that shows up above 8/12 joins. Not only does the throughput that the server can handle drop significantly at that point, but the query ends up using a disproportionately large amount of the SQL Server's resources, which negatively affects everybody else using that same database

SharePoint 2010/2013: List view Lookup threshold uncovered

Here is a basic example:

The most basic example of this would be if you had a list with thousands of items, and someone created a view that would return all of the items in the list in a single page. List throttling ensures that such a request would not be allowed to execute. The hit on the server is alleviated, and the user gets a nice little message that says sorry, we can’t retrieve all of the data you requested because it exceeds the throttle limit for this list.

Working with Large Lists in SharePoint 2010 – List Throttling