Posted by: R Manimaran | December 17, 2014

SharePoint exceeds the List view Threshold limit to 5000 items. Why 5000?

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.

Threshold

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.

http://aboutsqlserver.com/2012/01/11/locking-in-microsoft-sql-server-part-12-lock-escalation/

In the above situation we need to create indexed columns on the list and perform the list view operation.

Advertisements

Responses

  1. Superb information. Thank u so much. This is going to help me a lot. Looking forward to more such info’s from u…Thank u for sharing.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: