Resource throttles and limits
The following table summarizes information about resource throttles and limits that you need to be aware of. These throttles and limits are set on the Resource Throttling page in Central Administration. Contact your administrator for specific limits and requests for your site.
Note To assist central administration, the computer administrator of the front-end computer and the central administrator site owner accessing a list or library with many items are not subject to the following resource throttles and limits.
| Threshold or Limit |
Default value |
Description |
|---|---|---|
| List View Threshold | 5,000 | Specifies the maximum number of list or library items that a database operation, such as a query, can process at one time. Operations that exceed this limit are blocked.To give you time to make alternative plans, SharePoint 2010 warns you on the List Settings page when your list has exceeded 3,000 items. The warning contains a help link to this topic. |
| Unique permissions limit | 50,000 | Specifies the maximum number of unique permissions allowed for a list or library.Every time you break the inheritance of permissions for an item or folder, it is counted as 1 unique permission toward this limit. If you try to add an item that would lead to exceeding this limit, you are prevented from doing so. |
Ways to manage lists and libraries with many items
The following sections provide recommendations, techniques, and tips for ensuring that you and other users can quickly access many items in a list or library and help to keep your site operating smoothly.
Top of Page
Creating SharePoint indexed columns
To help improve the performance of a large list or library, you can index up to 20 columns. In general, an index on a column enables you to quickly find the rows you want based on the values in that column, even when working with millions of items. When you combine indexes with filtered views, you can quickly retrieve the items you want.
It is important to consider the following when you create and use indexed columns. Each additional column index consumes extra resources in the database and adds some overhead to every operation to maintain the index. Therefore, you should add indexes only to columns that will be used actively for filtering in views on the list or library. It’s a good idea to look at all the views and see which columns are used most commonly across different views for filtering to help choose the right columns to be indexed. Note that any column that you define to have a unique value requires an index.
The following table summarizes what columns can and cannot be indexed.
| Supported Column Types | Unsupported Column Types |
|---|---|
| Single line of text | Multiple lines of text |
| Choice (single value) | Choice (multi-valued) |
| Number | Calculated |
| Currency | Hyperlink or Picture |
| Date and Time | Custom Columns |
| Lookup (single value) Note Indexing the column in the other list or library does not improve performance of the lookup operation. | Lookup (multi-valued) |
| Person or Group (single value) | Person or Group (multi-valued) |
| Yes/No | External data |
| Managed Metadata |
Creating filtered views based on column indexes
For a view to quickly filter through a large number of items, the first column that you specify in the filter must be indexed. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes. You also need to make sure that the first column of the filter does not return more items than the List View Threshold, even if the final result of the filtered view returns less than the List View Threshold. If the first column of the filter returns more items than the List View Threshold, you can use a filter with two or more columns When you define a filtered view that uses two or more columns, using an AND operator will usually limit the total number of items returned. But even in this case, you still need to specify as the first column in the filter the column that most likely returns the lesser amount of data
Organizing items into folders
Although folders (also called containers) are not required to use large lists and libraries, you can still use them to help organize your data and improve the efficiency of your data access. When you create a folder, behind the scenes you are creating an internal index. This internal index is also created for the root folder, or top-level of a list or library. When you access items in a folder, you are effectively using this internal index to access the data. Bear in mind that if a folder contains subfolders, each subfolder is counted as an item (but not any items in that subfolder).
Even when the total number of items in a list or library is very large, a view of a single folder is at least as fast as a view that filters the total number of items by using an indexed column. In some scenarios, it may be possible to distribute all of the items in a list or library into multiple folders such that no folder has more than five thousand items.
It is important to consider the following when you use folders to organize a large list or library:
- A folder can contain more items than the List View Threshold, but to avoid being blocked by SharePoint 2010, you may still need to use a filtered view based on column indexes.
- If you choose the Show all items without folders option in the Folders section when you create or modify a view in this list or library, you must then use a filter that is based on an index to ensure you don’t reach the List View Threshold.
There are two classifications that come into the picture when you have large lists: “List Exceeds The List Value Threshold” and “Container Exceeds The List View Threshold”.
There are operations that can be blocked when the size of the entire list exceeds the list view threshold. This occurs even if the items are placed into folders. These operations include managing and checking versions, operations of all items, and recursive queries. The views that return all items without folders can also be prevented. There are operations that affect a complete list too including adding a column or deleting indexes and they can be blocked.
There can be operations prevented due to the folder for the list containing more items than the list view threshold allows. You won’t be able to rename it or to delete it so you do need to be careful. The list view threshold can prevent you from performing some common actions when you setup your list. This is why you should configure the columns and indexes for a list before the size is greater than the list view threshold.
Cross list query– This includes the various queries by the Content Query Web Part. It follows the list view threshold setting for auditors and administration. The default for it is 20,000. For operations above that threshold it will fail.
Fine grain security permissions– This fails when the list or the folder that is being set has fine grained permissions that contain more items than the list view threshold as too many rows are affected. You can use the fine grain permissions on documents in a large list. However, you can’t set the permissions of the list or of the folders if they contain more than the list view threshold.
http://sqlblog.com/blogs/kalen_delaney/archive/2009/05/03/controlling-lock-granularity.aspx
Lock escalation occurs in the following situations (taken from “SQL Server 2008 Internals”, MS Press 2009):
- The number of locks held by a single statement on one object, or on one partition of one object, exceeds a threshold. Currently that threshold is 5000 locks, but it might change in future service packs. The lock escalation will not occur if the locks are spread over multiple objects in the same statement—for example, 3000 locks in one index and 3000 in another.
- Memory taken by lock resources exceeds 40 percent of the non-AWE (32-bit) or regular (64-bit) enabled memory and the locks configuration option is set to 0. (In this case, the lock memory is allocated dynamically as needed, so the 40 percent value is not a constant.) If the locks option is set to a non-zero value, memory reserved for locks is statically allocated when SQL Server starts. Escalation will occur when SQL Server is using more than 40 percent of the reserved lock memory for lock resources.
SQL Server 2008 provides us with a bit more control, with a new option to ALTER TABLE:
ALTER TABLE <table_name> SET (LOCK_ESCALATION = [TABLE | AUTO |DISABLE]);
The default is escalation is TABLE, and that was the only possibility for escalation prior to SQL 2008. If you set the option to AUTO, locks can escalate to a table or to a partition, if the table is partitioned. The third option is to completely disable escalation for this table. This option is much more manageable that my trick of having another transaction lock a single row, and much more fine-grained that disallowing escalation on the entire instance.
But what if you want the opposite behavior? What if you always want to take table locks in order to conserve resources? Yes, there are lock hints available to request TABLOCK or TABLOCKX, but those must be specified in every query. What if there are only a few tables that you want to always lock at the table level.
SQL Server provides an option to ALTER INDEX to disallow ROW and PAGE locks. (In SQL 7 and 2000 you could use the system procedure sp_indexoption to do the same thing.) If the index is a clustered index, this means ROW and PAGE locks will be disallowed for the table. But, if the table is a heap, you can’t use this option.
ALTER INDEX <index_name> ON <table_name> SET (ALLOW_PAGE_LOCKS = OFF);
Also note that if you set ALLOW_PAGE_LOCKS to OFF, you cannot REORGANIZE (defragment) the index.
Here’s a couple of cool aspects of this feature that aren’t apparent from a single nifty screenshot:
- Metadata navigation lets you slice and dice multiple ways. I might have a bunch of taxonomies on my library that classify content in different ways; for example, I might have a Products field, a Region field and a Competitors field, all bound to domain-specific taxonomies that classify the content along those dimensions. Depending on my current task, it might make more sense to filter by the Region field (for example, if I’m looking for the latest sales figures for the North America region). I get more filters than just my virtual folder; I can combine this filter with any number of key filters or list view column filters to drill down to just the content I want (for example, I want to see all demonstration scripts by the ECM team created after 2007).
- Metadata navigation thinks about indices and large lists so you don’t have to. Hey, remember just a few minutes ago when we were talking about large lists, indices, and being throttled? Well, metadata navigation thinks a lot about indices and how to run queries the “right way” to make them perform well and prevent throttling from happening. For starters, all the fields you configure as navigation hierarchies and key filters get indexed, and the resulting queries are written in a way that ensures the best index is used to make the query succeed.
You aren’t immune from the laws of physics; if you ask for documents tagged with demonstration scripts and there are 10,000 demonstration scripts, we’re not going to be able to show you all of them. In this case, though, you get something better than a simple fallback; you get an indexed fallback, which means that instead of considering the entire list, the query considers only the items that match the indexed portion of your query.
When filtering views with metadata navigation you should consider using per location views. They create unfiltered views for metadata navigation pivots.
With SharePoint Server 2010, as discussed in previous posts, the new list view threshold significantly changes the approach for how to use view with a large list. Users may get errors if they attempt to return more results than the list view threshold allows. The use of totals on the large list will be blocked when that occurs. The number of items have to be scanned and that is what is important versus the number of rows that are returned.
When a list is organized a folder shouldn’t contain more items than the list view threshold. When you show all items outside of folders you should avoid the mechanics being in place where they can filter the results.
