Understanding List Query Throttling Limits in SharePoint 2010

By now, most SharePoint developers will have come across the list query throttling settings in SharePoint 2010. Essentially, farm administrators can impose limits on the number of items returned by list queries, in order to protect the performance of the farm as a whole. Limits are applied on a per-Web application basis and are typically managed through the Web application settings in Central Admin.

So far, so good. The concepts of query throttling are well documented, and the rationale will be obvious to anyone who has seen a SharePoint environment grind to a halt under heavy-handed list queries. (For a good explanation of query throttling, together with information on how you can avoid hitting the limits through careful indexing, take a look at Query Throttling and Indexing by the patterns & practices team.) However, it's not always entirely clear how these settings are applied.

First of all, "administrators" is a loose term. Let's clarify who qualifies as "auditors and administrators" for the purposes of these settings. Site collection administrators do not qualify. Farm administrators do not qualify. The only people who qualify are users who have specific permission levels assigned at the Web application level. Specifically, the policy level assigned to the user must include the Site Collection Administrator or the Site Collection Auditor permissions, as shown below.



Now for the bit that took me a little longer to grasp. What does the object model override actually do? Firstly, it doesn't allow you to submit database queries that hit an unlimited number of rows in the database. Secondly, it doesn't change the list view threshold for regular users at all. All the object model override does is allow our auditors and administrators, as defined by the Web application user policy, to submit queries at the higher threshold value. In other words, if you don't use the object model override, auditors and administrators are stuck with the same standard list view threshold as everyone else.

To dig a little deeper into how these thresholds are applied, I provisioned a basic list and used a feature receiver to add 10,000 items. This puts me nicely between the lower threshold and the upper threshold. Next, I created a Web Part that attempts to retrieve all the items from the list. The core code is as follows:

SPWeb web = SPContext.Current.Web;
SPList list = web.Lists["BigList"];
SPQuery query = new SPQuery();
query.QueryThrottleMode = SPQueryThrottleOption.Override;
SPListItemCollection items = list.GetItems(query);
litMessage.Text = String.Format("This list contains {0} items", items.Count);

The important bit is the 4^th line down:

query.QueryThrottleMode = SPQueryThrottleOption.Override;

The SPQueryThrottleOption enumeration has three values: Default, Override, and Strict. If you use the default value, the standard list view threshold applies to all users except local server administrators, who are not bound by either threshold. If you set the query throttle mode to Override, users who have the required permissions in the Web application user policy can query at the higher "auditors and administrators" threshold. Local server administrators remain unbound by either threshold. Finally, if you set the query throttle mode to Strict, this closes down the local server administrator loophole and the standard list view threshold applies to all users.

The following table shows which threshold applies to which users for each of the SPQueryThrottleOption values:

Type of user	Default	Override	Strict
Site member	Standard	Standard	Standard
Site owner	Standard	Standard	Standard
Site collection admin	Standard	Standard	Standard
Web app policy: site collection admin	Standard	Higher	Standard
Web app policy: site collection auditor	Standard	Higher	Standard
Farm admin	Standard	Standard	Standard
Local server admin	Unlimited	Unlimited	Standard

Finally, I found an interesting quirk for local server admins. The list view threshold exemptions for local server administrators apply only to users who are explicit members of the Administrators group on the local server. For example, domain admins are implicit members of the local Administrators group by virtue of their membership of the Domain Admins group. However, the standard list view threshold applied to my test domain admin account.

I hope this helps to clarify things for anyone else who's confused by list view thresholds. If you want to know more, Steve Peschka's blog is the best source of information I've seen in this area.

Cross-posted from Jason Lee's Blog