It it a well known fact that MOSS 2007 caused some rising opinions on the subject of list scalability and performance. Many developers operated under the misconception that SharePoint lists only allowed 2000 list items before croaking out with bad performance. Nothing could be further from the truth.
The following article talks about this issue of large lists in great depth and highlights the point that SharePoint can actually handle many more than 2000 list items in any one list. However, “query” this data is affected by the item count and SharePoint architects should design their data access and presentation of data accordingly.
http://technet.microsoft.com/en-us/library/cc262813.aspx
Microsoft has added a number of new enhancements to lists in SharePoint 2010 to handle larger capacity and the query of this data, and the following is a short summary of the enhancements:
List Column Indexing
SP2010 now allows list designers to create up to 20 indices (some of multiple columns) on any one list. These indices allow for faster query of data when the list size exceeds that of typical.
Under list settings and in the columns section; users now see a link to Indexed Columns.
The following is a list of column types usable as indexed columns:
· Single line of text
· Choice field, but not multi choice
· Number
· Currency
· Date/Time
· Look up, but not a multi value look up
· Person or group, but not multi value
· Title, except in a document library
List Throttling
SharePoint administrators now have the capability to better control list queries so that developers (or general users) may issue list queries on large lists that may potentially bring down the server. Specifically:
Administrators may define some limits at the web application level:
- Configure the number of items fetched for queries
- Administrators may receive warnings when thresholds exceeded
- Ability to configure time periods for expensive queries to operate
- Limit the size of list items (default to 8k)
- Limit the number of columns in a join (default to 6)
The following code will display the list throttling limits for the site collection:
using (SPSite site = new SPSite(siteUrl)) { Console.WriteLine("MaxItemsPerThrottledOperation:{0}", site.WebApplication.MaxItemsPerThrottledOperation); Console.WriteLine("MaxItemsPerThrottledOperationOverride:{0}", site.WebApplication.MaxItemsPerThrottledOperationOverride); Console.WriteLine("MaxItemsPerThrottledOperationWarningLevel:{0}", site.WebApplication.MaxItemsPerThrottledOperationWarningLevel); }
To enable list throttling on any list be sure to toggle the setting with the following:
SPList.EnableThrottling = true
MaxItemsPerThrottledOperationWarningLevel – If a list exceeds the number of items specified in this threshold then a warning is displayed on the list settings page.
What MaxItemsPerThrottledOperation – This indicates the number of list items returned to non-administrators. Administrators can query up to the threshold in What MaxItemsPerThrottledOperationOverride but will receive a warning on the list settings page.
If administrators wish for users to execute expensive operations in specific window of time they can do so by using the following method on the WebApplication object: SetDailyUnthrottledPrivilegedOperationWindow
RBS Storage (Remote Blob Storage)
In some cases the use of document libraries to store large files is no longer scalable and causes content databases to become unmanageable. An example situations where a public web site, hosted in SharePoint, provides users with rich media content – web files and large images – is once such example of the large blob storage issue.
In MOSS, hosting content in the database provided certain benefits, such as single storage location, versioning, and access of files via the object model. Whereas file based storage provided better scalability at the cost of orphaned content from the object model. SP2010 solves this issue with RBS. Site Architects can now store large files (blobs) in alternate locations to that of the SharePoint content database without relinquishing access via the OM. From an and developer standpoint, the data is accessed as if it were in the content database, but the content is actually in a remote location.
To enable RBS you’re farm will need to use at least SQL Server 2008 R2.
Marking blobs as external at the content database level enables SharePoint to store the meta-data associated with blobs in the database while storing the actual blob content outside the content database. Because RBS is handled at the database level, SharePoint is unaware that data is not stored in the content database but in another location.
in a future time, vendors will bring RBS providers for SP2010 to the table, but in the meantime Microsoft has provided RBS for SQL server as an extra download:
http://go.microsoft.com/fwlink/?LinkId=177388
See my next blog post on configuring RBS.
