SharePoint Performance Tuning – Resource Throttling
First introduced with SharePoint 2010, Resource Throttling allows the farm administrator to control the overall performance of a SharePoint farm. The biggest performance bottleneck in SharePoint sites is usually large lists, which either have numerous items in a list or have very large items in a list. Here, the rule is very simple – the larger the SQL query the slower the performance is. If you have 100 simultaneous users in a site, and 2 of them are contributing to large lists, all 100 users will experience very slow performance. The solution for this is Resource Throttling.
Using SharePoint Resource Throttling you can set the query limits, so the farm will refuse to execute the problematic user actions at certain times. You can set the “happy hour” for those queries to any time you want and instruct your users to contribute with those large lists ONLY at specific time.
To access Resource Throttling settings for your SharePoint site/farm, go to Central Administration > Application Management and select the web application (in our example – SharePoint – 80 application) and from the ribbon select General Settings – Resource Throttling.
Resource Throttling Configuration in the Central Administration Ribbon
Now we can specify numerous maximum parameters, which are self-explanatory. In this article I will only focus on few of them.
Setting the Object Overriding and Administrative threshold is important here. Why? You can easily throttle queries and therefore improve performance, but in most cases you will still need to access, read and modify some objects from the throttled resources. It’s necessary to have a setting that will allow some selected users to contribute to the throttled resources and override the settings entered. In this way you will avoid often made requests to the throttled resources, because only a selection of users (from our example) will have rights to perform huge queries.
To set this, ensure that the Object Model Override option is set to “Yes” and that List View Threshold for Auditors and Administrators is set to the maximum number capable of showing all the content from your portal. The default value of 20000 is a sensible starting point but you will probably need to fine tune this.
Object Model Override and List View Threshold for Administrators settings
Now, set the daily time window for the rest of the users who will also need to access the throttled resources. This may be the time out of your working hours, so most users will be offline , or early morning, where the traffic is still low. To set this, look at Daily Time Window for Large Queries setting.
In my example, I’ll set the time window from 7 AM to 9 AM, so all early birds will have access to throttled queries
Daily Time Window Settings