The Hell of SharePoint 2010 Throttling

I think all the settings and OM properties of SharePoint 2010 throttling are as confusing as hell.

There are

1. Normal User throttling level.

2. Site Admin throttling level with OM override.

3. Happy hour (Daily Time Window for Large Queries)

4. Unlimited query (No throttling)

5. List view limitation (Not discussed in this article. It has its own rules). All we care here is Object Model.

The most confusing part is when the "unlimited query (no throttling)" will happen. This blog http://malikhan.wordpress.com/2009/12/22/throttling-new-features-of-list-in-sharepoint-2010-part-22/ gives some clues but I decide to dig it more myself.

This is the code behind the throttle setting page.

        SPWebApplication currentItem = this.Selector.CurrentItem;
...
        currentItem.MaxItemsPerThrottledOperation = uint.Parse(this.TxtListMaximum.Text);
        currentItem.MaxUniquePermScopesPerList = uint.Parse(this.TxtListScopesMaximum.Text);
        currentItem.MaxItemsPerThrottledOperationOverride = uint.Parse(this.TxtListMaximumOverride.Text);
        currentItem.MaxQueryLookupFields = uint.Parse(this.TxtListLookupsMaximum.Text);
        currentItem.UnthrottledPrivilegedOperationWindowEnabled = this.CbEnableUnThrottleWindow.Checked;
        if (currentItem.UnthrottledPrivilegedOperationWindowEnabled)
        {
            currentItem.SetDailyUnthrottledPrivilegedOperationWindow((uint) this.DblDailyUnThrottleStartHour.SelectedIndex, (uint) (this.DblDailyUnThrottleStartMinute.SelectedIndex * 15), (uint) this.DblDailyUnThrottleDuration.SelectedIndex);
        }
        currentItem.AllowOMCodeOverrideThrottleSettings = this.RadAllowOMOverrideOn.Checked;

You can easily map the SPWebApplication's property to the settings in UI. These settings controls how the SQL query will executed on DB by default for list query.

For individual list, if you are "Farm Administrator" you can enable/disable EnableThrottling on SPList to ignore the SPWebApplication properties. However if your are running in the context of "Farm Administrator", the throttle doesn't apply at all. If you are not "Farm Admin", you cannot set this property. I don't understand the purpose of this property. (This could make sense if you want to use Powershell to manually change the list property in the context of a Farm Admin. This would be adminitration nightmare if you manage your list like this).

SPList

public bool EnableThrottling
{
    get
    {
        this.EnsurePropsFresh();
        bool flag = (bool) this.m_arrListProps[0x26, this.m_iRow];
        return !flag;
    }
    set
    {
        bool nothrottlevalue = !value;
        this.SetListNoThrottle(nothrottlevalue);
    }
}

internal void SetListNoThrottle(bool nothrottlevalue)
{
    SPSite site = this.ParentWeb.Site;
    if (!site.WebApplication.Farm.CurrentUserIsAdministrator(true))
    {
        SPGlobal.HandleUnauthorizedAccessException(new UnauthorizedAccessException());
    }
    else
    {
        SqlSession sqlSession = site.SqlSession;
        using (SPSqlCommand command = new SPSqlCommand())
        {
            command.SetStoredProcedure("proc_MakeExceptionForThrottle");
            command.Parameters.AddWithValue("@WebId", this.ParentWeb.ID);
            command.Parameters.AddWithValue("@ListId", this.ID);
            command.Parameters.AddWithValue("@NoThrottle", nothrottlevalue);
            sqlSession.ExecuteNonQuery((SqlCommand) command);
        }
        this.DirtyProps();
    }
}

I couldn't see any use of this property of SPList either. Why do I care I am a Farm Admin or a normal user (can't do anything).

public bool IsThrottled
{
    get
    {
        return (this.EnableThrottling && (this.ItemCount > this.ParentWeb.Site.WebApplication.MaxItemsPerThrottledOperation));
    }
}

This shows how SPQuery will affect the query.

Microsoft.SharePoint.SPListItemCollection

protected void EnsureListItemsData()
{

               if (this.m_Query.QueryThrottleMode != SPQueryThrottleOption.Default)
                {
                    request.SetVar(web.Url, "ThrottleMode", ((uint) this.m_Query.QueryThrottleMode).ToString(CultureInfo.InvariantCulture));
                }
                else
                {
                    request.SetVar(web.Url, "ThrottleMode", null);
                }

This shows when SPQueryThrottledException will be thrown.

Microsoft.SharePoint.SPSecurity

internal static void ValidateSecurityOnOperation(SPOperationCode code, SPSecurableObject obj)

    if (entry.m_bSiteAdmin)
    {
        if (!parentWeb.CurrentUser.IsSiteAdmin)
        {
            SPGlobal.HandleUnauthorizedAccessException(new UnauthorizedAccessException());
        }
    }
    else if (entry.m_bSlowOprations && isThrottled)
    {
        if (!parentWeb.Site.WebApplication.CurrentUserIgnoreThrottle())
        {
            throw new SPQueryThrottledException();
        }
    }

Based on the anaylsis of the code, here are the conculsions:

1. Don't be confused by "Site Collection Administrator" and "Farm Administrator". The throttle settings only applies to "Site Collection Administrator".

2. Throttling doesn't apply to Farm Administrator.

3. Throttling doesn't apply to "Happy Hour" (Daily Time Window for Large Queries)

4. If your code (web part, web page code behind) is running in the context of current logged in user, the throttle will apply depending on if your end user is normal user or site admin.

To turn it off, the only way is to set SPQuery.QueryThrottleMode to "Override" which mean unlimited.

5. If your code (exe, service) is running in the context of Farm Admin, then throttle doesn't apply at all. You don't need to set SPQuery "EnableThrottling".

Code Run in Context of Normal List User or Site Collection Admin (Web page, web party ...)

Code Run in Context of SharePoint Farm Administrator (exe, windows service ...)

List Items Count < “List View Threshold “ (5000),

i.e. <5,000

Unlimited items returned

List Item Count > “Warning Level” (2000)

i.e. >2000 but < 5000

Unlimited items returned

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

SPQueryThrottleException for normal list user.

Unlimited items returned

for site collection admin.

Unlimited items returned

List Items Count > “List View Threshold for Auditors and Administrators”(20,000),

i.e. >20,000

SPQueryThrottleException

Unlimited items returned

During Time of Daily Time Window for Large Queries.

Unlimited items returned

SPQuery.QueryThrottleMode = Override

Unlimited items returned

SPQuery.EnableThrottling = False

this property cannot be overridden in the context of normal user

Unlimited items returned

If we assume all list will eventually grow to a size that over any limit, the only pattern we need to worry is to

1. Make sure throttle settings "Object Model Override" to "Yes".

2. Set SPQuery.QueryThrottleMode = Override

3. Call the list.GetItems() with query.

Ethan Deng's SharePoint Blog