List Threshold Limits & Workarounds in Sharepoint

Minimum Limit is 2000 and Maximum limit is 2147483647

list view threshold by default is 5000 and 20000 for auditors and administrators.

Effect on performance :The limitation actually comes from sql server. if SQL Server executes a query on a single table that would exceed 5,000 results, SQL Server will lock the entire table while the query executes. Since SharePoint stores all list data in a single table, a single query that returns over 5,000 items will lock all of the list and library data for that entire content database! Users who are accessing content in SharePoint while the SQL Table is locked will have to wait longer for their results to be returned.

Avoiding List Threshold Issues:

By carefully configuring your list views, you can return the data that users need to see while keeping under your threshold limits for that list or library. Here are some tips to consider while planning the structure for your content.

•Separate your content into multiple lists and libraries.

•Create multiple list views. Each view can show the content in
different ways allowing users to choose what they prefer to see.
Some common example may include Recently Changed, Recently Added,
Created By Me, Assigned to Me, Due Today.

•Add filters to your list views to limit the content that is
returned.

•Use “AND” when defining your filters. Choosing “OR” will return all
results first before your filter is applied.

•The first criteria in your view filters should have the greatest
impact on reducing the number of items returned.

•Add an Index to any columns used for sorting or filtering your
views. SharePoint automatically works some magic in SQL Server when
you create an indexed column which allows it to quickly analyze and
return values, bypassing thresholds.