‘Large’ lists – lists with over a few thousand items – can cause problems when you’re developing for SharePoint, and the whole topic is kind of complicated. So what’s the problem?

Well, fundamentally the issue is inefficient queries being run against SharePoint lists. An inefficient query against a list with a small number of items isn’t a big deal, but with larger lists (over 5000 items) SQL server has to escalate from a row-level lock to a table-level lock. This can hold up other users while the table is locked.

So, what are the limits, what options are there with throttling, and what strategies are there for mitigating the effects of query throttling?

The Limits

Even just figuring out what the limits are in SharePoint 2010 is, with typical SharePointness, complicated.

• Standard users have a default threshold of 5000. This can be configured in Central Admin. Its minimum value is 2000.
• Some ‘super users’ can have a threshold of 20000. More on these super users below.
• Server Administrators have unlimited thresholds. Note that for Developers, this means making sure you test as a normal user.

So who are ‘Super Users’? Well, I’m going to quote Jason Lee

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.

So – a Site Collection Administrator is not a ‘super user’. However, a user who has a Web Application Level Permissions that includes ‘Site Collection Administrator’ is. Hmm.

Basically, I’m going to ignore these super users. They still have a threshold, albeit a higher one, so they’ll have the same problem.

What are Throttling’s options

There are LOTS of options about how throttling is set up. Some of them are set in central admin.

You can:

• Set the thresholds for the different types of users.
• Allow Object Model Overrides. More on this below.
• A Daily Time Windows for Large Queries.

Okay, so what’s this Object Model Override? Well, programmers can write their queries to use three different type of throttling, as defined by the SPQueryThrottleOption enumeration. Those options are:

• Default (Everyone is limited to the default threshold, apart from Server Administrators)
• Strict (Everyone is limited to the default threshold)
• Override (Normal Users are limited to the default threshold, super users a limited to the higher threshold, and Server Administrators are unlimited)

Again, Jason Lee’s post is worth a look for the handy table it has at the bottom.

Side note: It seems to me that the world could’ve done without either the Override setting in Central Admin, the Override option on the SPQueryThrottleOption, or these bizarre ‘super users’. Wouldn’t one limit be enough?

In Part 2 we’ll look at what we can do about query throttling

Here are some of articles referring to from MSDN:

• Query Throttling and Indexing
• Writing Efficient Code In SharePoint Server
• Handling Large Lists and Folders
• Enforcing Uniqueness in Column Values
• Using the Content Iterator (More on that in Part 3)

Other References:

• Working with Large Lists in SharePoint 2010 – List Throttling
• Understanding List Query Throttling in SharePoint 2010