In SharePoint 2010 and 2013 when working with large list (which contains large amount of items more than 5000) we may experience the Threshold issue on List View with the below error.
Here why 5000?
This limit is not related with SharePoint. It’s related to Microsoft SQL server. SharePoint uses SQL server as the database tables to store your list values.
- In SQL server, mostly uses the row-level locking to avoid database contention and to ensure accurate update without impacting the other users who are accessing the other rows.
- When we did a Select/Update operation and as a result of this consider 5000 rows are affected which causes the SQL server to lock all the 5000 rows at once.
- But instead of doing so, for SQL server it is efficient to escalate the lock to the top level object (i…e to the Table) and lock the whole table at once, until that operation gets completed.
This actual number will not be always 5000. It depends on the amount of database operation you are performing, your site and its configuration.
When this escalation happens, other users will not able to access the whole table. Hence this threshold is maintained in the SharePoint to avoid the performance degradation.
Here is an article which will help you understand the SQL SERVER Lock escalation with example.
In the above situation we need to create indexed columns on the list and perform the list view operation.