So, last week I was at the #SPEvo13 conference in London, and one innocuous session by Wayne Ewington sent shivers down my spine – about query throttling. So, what is query throttling again, and why did it was I worried?

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, and could be very slow. To overcome this, SharePoint rejects queries that breach it’s rules with a SPQueryThrottledException. Fine, but what are the factors in determining if it’s throttled? Well, that’s kind of complicated. You’ve got the following factors:

• Indexed Columns – An index makes it more likely that the query will succeed – ‘cos it doesn’t have to scan the whole List. You can still get an exception if your eventual results set is more than 5000 items. An Index on a Unique valued column will always pass, though.
• Query Throttle Limit – you can adjust this value from 5000 items – but Microsoft did chose that value for a reason. Beware!
• Who is querying? Ordinary users have one limit (normally 5000 items), Super users (users with Read or Full Control on the entire web app) a second limit, and Farm Admins are unlimited. Note that as developers typically develop using a Farm Admin account, this is risky, and means careful testing with other accounts and real volumes of data is necessary. Also not that App Pool accounts are still limited – Elevating privileges will not avoid query throttling. And Site Collection Admins are also still limited.
• How you’re querying – There are some ways around these limits by getting the results in blocks, using the ContentIterator control.
• Overrides – You can allow code to override your query throttle limits on a web app.
• “Happy Hour” – You can set a period (normally of low usage) where query throttle limits are turned off – for example, in the middle of the night, if you need to do reports or something.
• Authentication Method – New for SharePoint 2013, the Query Throttle limits are different if you use Classic or Claims authentication. Yup, the authentication mode matters. This came as something of a surprise to both Wayne and myself, and sent a shiver through me; I had visions of some of my applications not working…
• Query Throttling on External Lists – Not something I’d thought of before, but if your BCS connection limits the number of items it returns to a limit below the Query Throttle limit then even if your query on the external list should return more than that limit, you’ll only get the number specified by BCS, and the Query Throttle won’t be hit. In other words, the BCS limit is applied before the Query Throttle limit. Or so Wayne says – it’s not something I’ve tried.

So, not simple. I did ask if there was a document or a table of this stuff, but apparently there isn’t . This is unfortunate; I feel this whole area is unnecessarily complex, and Wayne’s session was largely a set of questions to see what the audience knew – and it turns out that an audience of largely SharePoint devs were confused by it …

I guess, therefore, test, and test again, and remember to deal with large lists…