Muhammad Ali Khan

MS CRM 3.0, MS CRM 4.0, Sharepoint 2007 & Sharepoint 2010

Archive for the ‘Throttling List’ Category

Throttling: New Features of List in SharePoint 2010 (Part 2/2)

Posted by Ali Khan on December 22, 2009

This is my 2nd post regarding the new features of lists in SharePoint 2010.

As we know, we could run into performance de-gradation and SharePoint Server Slow Response with List in SharePoint 2007 as the list of the size increases, because even though Microsoft recommended storing not more than 2000 list items for best performance, there was not enough performance monitoring & control from SharePoint 2007 itself. Now Performance Control & Monitoring has been applied on List in SharePoint 2010 through “Throttling”. So first let see what is throttling?

Throttling

Here is the throttling definition from Microsoft SharePoint Evaluation Guide.

“Through throttling settings, SharePoint Server 2010 provides a way for administrators to determine the level at which the server will go into throttling mode. Every five seconds, a job runs that checks server resources compared to configured levels. By default, Server CPU, Memory, Request in Queue, and Request Wait Time are monitored. After three unsuccessful checks, the server enters a throttling period and will remain in this state until a successful check is completed. Requests that were generated prior to the server’s entering throttling mode will be completed. This will, in theory, keep users from losing any current work when the server begins to throttle requests. Any new HTTP GET and Search Robot requests will generate a 503 error message and will be logged in the event viewer. While the server is in a throttling period, no new timer jobs will be started.”

Here is the link to the “SharePoint Evaluation Guide

Throttling On SharePoint 2010 Lists

 As the items in the SharePoint list continue to grow, the performance of the queries retrieving data from the list decreases. To help SharePoint users, Microsoft has provided different Performance Control checks through “Resource Throttling” on lists in SharePoint 2010.

In order to go to these settings, follow this path, Central Administration ==> Application Management ==> Manage Web Application. Once in the Web Application List, select the web application and click General Settings ==> Resource throttling. See the diagram below.

image

image

Here is a tabular representation of some of the important values and their description

Property Description Default Value
List View Threshold The number of results that can be returned in a user query. If the limit exceeds this value, an “Expensive Query Exception” is thrown in the code. Note this limit is for a normal user. Administrator limit can be configured to be much more than this. 5000
List View Threshold for Auditors and Administrators Same as above but for the administrator and power users and this value is considerably higher than the “List View Threshold” value. 20,000
Warning Level A Warning Level for the administrator that their list is close to the threshold. Note, this option is configured only through “Power Shell”

E.g. Use this power shell command to configure this value to 2500.

>> $sitecol = Get-SPSite http://mossserver:8000

>>$sitecol.WebApplication. MaxItemsPerThrottledOperationWarningLevel

= 2500

3000
List View Lookup Threshold Having a lot of lookup in a list also decreases the list performance. So this is the maximum no of lookups that a list can have. Having more lookups than this value mean the list is throttled. 6
Object Model Override This will allow the developers to override the property and ignore the throttling. E.g. SPQuery.RequestThrottleOverride & SPSiteDataQuery.RequestThrottleOverride will enable programmers to ignore the “throttling behavior” and get more items from the list than “List View Threshold”.

As administrator of SharePoint 2010, you have commands available through the Power Shell which can restrict a specific list from being queried through this model, thus users code can no longer ignore the “throttling” and the throttling Threshold value will be applied.

Yes
Daily Time Window for Large Queries This will allow the third party application (using super user or administrator credentials) to query the Large SharePoint 2010 List during off business time. Disabled

 

So with the above things in mind, how the following code (Retrieve all students from the “Student” list), will behave:-

image

Property Code Run in Context of Normal List User A (But Not SharePoint Administrator) Code Run in Context of User B (SharePoint Administrator)
List Items Count < “List View Threshold “ (5000),

i.e. <5,000

Code will run fine and return the required result. Code will run fine and return the required result.
List Item Count > “Warning Level” (2000)

i.e. >2000 but < 5000

No exception is thrown, code runs fine No Exception is thrown, code runs fine.
List Items Count > “List View Threshold”(5000) But Less than “List View Threshold for Auditors and Administrators” (20,000),

i.e. >5,000 but < 20,000

A Expensive Query Exception is thrown No Exception is thrown, and the code will return the required result.
List Items Count > “List View Threshold for Auditors and Administrators”(20,000),

i.e. >20,000

A Expensive Query Exception is thrown A Expensive Query Exception is thrown
During Time of Daily Time Window for Large Queries. Query threshold not applied, query runs fine even if the threshold limit is exceeded. Query threshold not applied, query runs fine even if the threshold limit is exceeded.
Object Model Override = true and RequestThrottleOverride is overridden Exception thrown if list is throttled, this property cannot be overridden by a normal user. No exception thrown, even if the list is throttled (i.e. Items Count >20,000)

 

Some Useful Power Shell Commands

How to? Example
Set the maximum threshold warning value for the web application  $sitecol = Get-SPSite http://mossserver:8000

$sitecol.WebApplication. MaxItemsPerThrottledOperationWarningLevel

= 2500

This command will save all the list possible commands to a file on C:\ drive. $sitecol.RootWeb.Lists | select * >> c:\listcmd.txt
Get a reference to the list $list = $sitecol.RootWeb.Lists[“Student”]
Is List Throttled $list.IsThrottled
Is Throttling on list is enabled or not. $list.EnableThrottling
Returns the maximum throttled threshold value (for administrator) $sitecol.WebApplication.MaxItemsPerThrottledOperation
Return the maximum throttled threshold value (for normal user). $sitecol.WebApplication.MaxItemsPerThrottledOperationOverride
Maximum lookup field allowed in a list. $sitecol.WebApplication.MaxQueryLookupFields

 

Briefly, Throttling allows realistic limit on the number of items that can be or should be queried from a SharePoint 2010 List. Although you can store the million of item in a SharePoint list, but the query operation will become slower as the size of the list grows. By allowing throttling, SharePoint 2010 has now help developers & especially Farm Administrators to control the large list & large list queries so that the performance of the whole SharePoint farm doesn’t decrease.

Posted in Sharepoint 2010, Sharepoint Foundation 2010, Throttling List | Tagged: | 3 Comments »