if we increase the web application List View threshold value what will be the impact ? what is the best practise to do this settings.
A new feature in SharePoint Server 2010, resource throttling provides options for monitoring and throttling server resources and large lists for Web applications. This enables you to control resource utilization during peak usage and prevent user activity from negatively affecting server performance.
Resource throttling can monitor such resources as CPU, Memory, and Wait Time, checking resources every 5 seconds. Throttling will begin after 3 unsuccessful checks. This throttling period will end after a successful check of the resources.
Depending on your hardware, you can try to set some value for threshold and monitor server performance. In my case, i have configured List View Threshold = 10000, but i haven't tested case 30000 items
It is not recommended to change this value. It may consume more server resources and it increases the risk of getting deadlocks in your environment when users are accessing lists and libraries etc.
To minimize database contention, SQL Server often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other 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 once, then it's more efficient for SQL Server to temporarily escalate the lock to the entire table until the database operation is completed. Note that the actual number is not always 5,000, and can vary depending on your site, the amount of activity in the database, and your site's configuration.
When this lock escalation occurs, it prevents other users from accessing the table. If this happens too often, then users will experience a degradation of system performance. Therefore, thresholds and limits are essential to help minimize the impact of resource-intensive database operations and balance the needs of all users.
That's how SQL is designed. Instead of changing the value, i would look at the topic Ways to manage lists and libraries with many items in the same article below.
If you really need to let users access large unfiltered lists to do some tasks, i would recommend to set up a daily window where users can perform their tasks.
The daily time window is a way for farm administrators to specify a dedicated time period for all users to do unlimited operations during off-peak hours, such as 6:00 PM to 10:00 PM.
Although views are a primary way to retrieve items from a list or library, other SharePoint commands and operations also require accessing the items in a list or library, such as: adding and deleting an index, adding and modifying a list column, deleting and copying folders, changing security settings for a list or library, saving a list with its content as a template, deleting a web or site, and restoring or deleting items from the Recycle Bin. These commands and operation could also fail if they exceed the site thresholds and limits when retrieving list data.
In addition to the other answer
Here are some problems I've experienced:
- The server is under more pressure
- Actions and View's are getting slow
- When the List is used as a Lookup and a certain amount of information is reached, there is an ASP boundary reached resulting in an exception
- Your farm is not supported by Microsoft
Why do you need to increase the threshold? There are some ways to work around it without changing it.
Dealing with a large amount of items takes time and resources.
Fortunately SharePoint provides methods to help you perform common operations efficiently such as using indexes to filter and metadata navigation fallback to return a smaller set of results when a request is too large.
But some operations such as poor performing queries (ones that try and get everything from a list or filter without indexes) or operations that affect every list item such as adding a column take time and resources.
On a small list this doesn’t matter because there are so few items that the operation is fast. As the list size increases these operations take longer and use more resources. Rather than let these operations run unbounded, the list view threshold prevents them. You can think of the list view threshold as a check engine light letting you know that you should change the query and how data is accessed or perform the operation when farm usage is low.
Below given chart will explain it to you.
Increasing the LVTH is not recommended as it will decrease the performance of your SharePoint farm. If SQL Server the busy then you can imagine how your SharePoint will perform. as others already explained what happens when you increase the LVTH.
In Simple Words:If LVTH is more than 5k, then 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. If you have mutliple site collections in your content DB then all sites will suffer.
But I know a couple of my customer they increased the LVTH more than 5K but they have the really good hardware.
If you have only one site collection in the content DB then only that site will get the low performance. Other workarounds are use the Index Column, Create Multiple Views etc.