With SharePoint 2010, a number of enhancements has been introduced to help the system stability. Some of these enhancements need to be known by the developer, otherwise they might fall into errors they cannot resolve or even explain (depending on the error code they receive).
This is the main reason why in SharePoint 2010, Developers and Administrators will need to work together more closely than ever before.
SharePoint 2010 introduces lockdown and throttling mechanisms to ensure stability and smooth operation. These save the platform from having to deal with bad queries or large amounts of data entering the system. The developer will need to be aware that they might hit certain limits when coding against SP.
1. Absolute Maximums and List Throttling
SharePoint 2010 defines the maximum items that can be stored inside a single list as 50.000.000 (that’s fifty million). A list cannot store more than fifty million items (that’s quite a huge number). However, there is no way a system would be able to process queries or operations that involve so many items! That is why SharePoint 2010 implements List Throttling; to ensure that the servers will not drop to their knees as a result of excessive large data manipulation. By default, SharePoint 2010 will throttle queries and operations on lists to just 5.000 items! That means that if a user attempts to create a view that returns more items than 5.000 or, if a developer creates a process/operation that attempts to access more than 5.000 items, the request will fail in both of these cases. Of course, this limit is configurable through the Application Settings in the Central Administration Page (the administrator is the one who will normally be the one to alter that number, that is why developers and administrators need to have a mutual understanding).
The developer will also need to realise that this upper limit can be hit even if the operation they attempt to perform does not look like it should be throttled. For example, if a developer is working against a list that hosts 10.000 items (permitted by the system, since the maximum is 5mil as we said earlier), trying to sort a subset of that list (say a subset of 2.000 items as displayed by the current view) will fail! In such a case, the system will have to go through all 10.000 items of the list in order to sort them -so that the subset will also be sorted- in order to display the sorted subset in the current view.
It is important to note here however, that although both users (end users) and developers will have to comply with those throttling limits, developers are in fact allowed to programmatically move past the specified throttle (thus making that throttle applicable to end users alone). This however does not essentially mean that developers can programmatically create queries and operations that will in fact destabilise the system. It simply allows SharePoint Administrators to define a second upper limit (a second throttle) specifically created for those who have sufficient security rights to override the initial throttle and for developers who can programmatically achieve the same results. Of course, in order for this second throttle to be available, the appropriate on-off switch needs to be set on (although it is on by default) by the administrator. Just below the switch (as is displayed in the screenshot below) there is a field where the absolute maximum will be defined (the throttle that cannot be overriden in any way).
Another thing to keep in mind however, as far as the override switch is concerned, is that although developers and auditors (and whoever has sufficient rights) can override the throttle through an Object Model override, the code that allows this override to happen also has a failsafe switch to… override the override through Windows Powershell. What that means is that even if someone overrides the first throttle, the administrators can cancel that override through Windows Powershell. They always have the final say…
The above screenshot displays where the Resource Throttling options are located in the Central Administration. The following screenshot displays a subset of the options configurable in the Resource Throttling menu.
An important thing to keep in mind is that throttled operations raise an event so that the developer can gracefully fail their code by catching that exception. This exception is the SPQueryThrottledException (which, makes sense actually).
Another aspect of lists that can be (and by default is) throttled is the number of lookup columns permitted. SharePoint 2010 will by default throttle the maximum number of lookup columns to 8. This is essential for the unobstructed operation of the system since the more lookup columns in a list, the more strain they put on the server.
Another option of importance is the Daily Time Window for Large Queries. What this essentially means is that for a number of hours defined by the administrator, queries and operations that involve data larger than what is allowed by the aforementioned throttles will be allowed (that means that operations that would actually fail, will work just fine but only during that time window). This is something very useful that will help the developers immensely, however, the administrator will need to make sure that they define a time window outside of the working hours in order to ensure that server responsiveness does not deteriorate.
Probably the most important thing for the developer to note is that, throttles do not apply to the SharePoint Administrator! That means that when working in the development environment, where it is probable that the developer is also the SharePoint administrator, the developer will fail to notice that his/her code is hitting a throttle. Moving that code to the production environment will be catastrophic since users (who will obviously have a severely trimmed subset of permissions) will hit the throttles when running the code and receive errors from the system. The developer needs to make sure that he/she tests custom code with a number of user credentials, ideally, one for each security level permitted in the production environment!
Powered by Zoundry Raven
Really useful article, though you have me thinking; The default throttle limit of 8 lookup columns. It is possible to specify in SP2010 that columns from the same row as the lookup value also come through when that value is selected in the linked list. Do they count as lookups as far as the throttling goes, or just the main lookup field?
Thank you Alan for stopping by. I would have to say that although I haven’t worked on such a scenario, I believe that the throttling should still apply.
Please, do understand that the throttling does not count against normal fields. That means that, a query will not fail when it has to return a bunch of text fields, for example. However, if your query needs to return a mixed selection of lookup, person or workflow status columns, then, if that selection counts more than 8 members, it will be throttled.
Hope that helps.
~ Actually, I’ll try to get a working example of the scenario and test it. Might be some time but I will. It’s good knowledge to have before actually attempting it in a real life operation. Thanks for asking!
[…] has also introduced throttles to ensure server performance. Copying word-for-word from my post aboutSharePoint 2010 Enhancements for Stability, “probably the most important thing for the developer to note is that, throttles do not apply […]