What is the maximum listview threshold that can be set on a SharePoint list? What i mean to say is, threshold above the default value that is 5000. And are there any effects on the performance of a SharePoint site if the value exceeds too much if yes then what is the workaround or a solution for this? Anybody any ideas? Thank you in advance.
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.