Thresholds were introduced by Microsoft in SharePoint 2010 in
order to prevent large queries from occurring which have an impact on
performance of the SharePoint environment.
What happens behind
the scenes when you access many items in a list or library?
The following diagram summarizes the key points about what happens
behind the scenes when you access many items in a list or library.
- List or library data in a site collection is stored in a SQL Server database table, which uses queries, indexes and locks to maintain overall performance, sharing, and accuracy.
- Filtered views with column indexes (and other operations) create database queries that identify a subset of columns and rows and return this subset to your computer.
- Thresholds and limits help throttle operations and balance resources for many simultaneous users.
- Privileged developers can use object model overrides to temporarily increase thresholds and limits for custom applications.
- Administrators can specify dedicated time windows for all users to do unlimited operations during off-peak hours.
- Information workers can use appropriate views, styles, and page limits to speed up the display of data on the page.
What happens when the list threshold is reached?
So
what happens when the list threshold is reached in a particular list or
library? End-users, instead of seeing the content they requested, will see a
friendly error message stating that their content cannot be returned.
The
administrator has even more problems. Many operations are blocked when the list
exceeds the threshold including, Add/Remove/Update list columns, managing a
column index, deleting a list, deleting a site, save list as a template with
data, showing totals in list views, enable/disable attachments. Many of these
operations are blocked because when executed they affect every item in the
list, which could trigger the threshold limits.
A
reminder of the Microsoft definitions:
·
Boundary
= static limit that cannot be exceeded by design
·
Threshold
= configurable limits that can be exceeded to accommodate specific requirements
·
Supported
= configurable limits that have been set by default to a tested value.
So
a boundary is fixed – you can’t change it. Threshold means the limit to maintain
performance will depend on behaviour. e.g. for comparable performance, a highly
collaborative site, with content changing frequently should be kept much
smaller in size than a long-term archive where content is rarely accessed and
never changes. Supported means that if you exceed the limit and experience
problems. Of course they won’t, but you will need to bring the servers back
within support limits to receive any help troubleshooting what’s gone wrong.
(You can also expect to be asked to remove any bespoke code and third party
software, but that’s a whole other topic.) The other key consideration with
supported limits is that they are based on the hardware and sample (sterile)
content Microsoft used to stress test. Your own hardware is unlikely to exceed
it and performance may be impacted long before you reach the official supported
limits.
Web Application Limits
The following table lists the recommended guidelines for web applications.
| Limit | Maximum value | Limit type | Notes |
| Web application | 20 per farm | Supported | We recommended limiting the number of web applications as much as possible. Create additional host named site collections where possible instead of adding web applications. |
| Zone | 5 per web application | Boundary | The number of zones defined for a farm is hard-coded to 5. Zones include Default, Intranet, Extranet, Internet, and custom. |
| Managed path for host-named site collections | 20 per farm | Supported | Managed paths for host-named site collections apply at the farm level. Each managed path that is created can be applied in any Web application. |
| Managed path for path-based site collections | 20 per web application | Supported | Managed paths are cached on the web server, and CPU resources are used to process incoming requests against the managed path list. Managed paths for path-based site collections apply at the Web application level. You can create a different set of managed paths for each Web application. Exceeding 20 managed paths per web application adds more load to the web server for each request. If you plan to exceed twenty managed paths in a given web application, we recommend that you test for acceptable system performance. |
| Solution cache size | 300 MB per web application | Threshold | The solution cache allows the InfoPath Forms service to hold solutions in cache in order to speed up retrieval of the solutions. If the cache size is exceeded, solutions are retrieved from disk, which may slow down response times. You can configure the size of the solution cache by using the Windows PowerShell cmdlet Set-SPInfoPathFormsService. For more information, see Set-SPInfoPathFormsService. |
Web server and application server limits
The following table lists the recommended guidelines for web servers on the farm.
| Limit | Maximum value | Limit type | Notes |
| Web application | 20 per farm | Supported | We recommended limiting the number of web applications as much as possible. Create additional host named site collections where possible instead of adding web applications. |
| Zone | 5 per web application | Boundary | The number of zones defined for a farm is hard-coded to 5. Zones include Default, Intranet, Extranet, Internet, and custom. |
| Managed path for host-named site collections | 20 per farm | Supported | Managed paths for host-named site collections apply at the farm level. Each managed path that is created can be applied in any Web application. |
| Managed path for path-based site collections | 20 per web application | Supported | Managed paths are cached on the web server, and CPU resources are used to process incoming requests against the managed path list. Managed paths for path-based site collections apply at the Web application level. You can create a different set of managed paths for each Web application. Exceeding 20 managed paths per web application adds more load to the web server for each request. If you plan to exceed twenty managed paths in a given web application, we recommend that you test for acceptable system performance. |
| Solution cache size | 300 MB per web application | Threshold | The solution cache allows the InfoPath Forms service to hold solutions in cache in order to speed up retrieval of the solutions. If the cache size is exceeded, solutions are retrieved from disk, which may slow down response times. You can configure the size of the solution cache by using the Windows PowerShell cmdlet Set-SPInfoPathFormsService. For more information, see Set-SPInfoPathFormsService. |
Content Database Limits
The following table lists the recommended guidelines for content databases.| Limit | Maximum value | Limit type | Notes | ||
| Number of content databases | 500 per farm | Supported | The maximum number of content databases per farm is 500. With 500 content databases per web application, end user operations such as opening the site or site collections are not affected. But administrative operations such as creating a new site collection will experience decrease in performance. We recommend that you use Windows PowerShell to manage the web application when a large number of content databases are present, because the management interface might become slow and difficult to navigate. With 200GB per content database, and 500 content databases per farm, SharePoint Server 2013 supports 100TB of data per farm. | ||
| Content database size (general usage scenarios) | 200 GB per content database | Supported | The default file size is 50 MB, which can be increased to a maximum of 2 GB. You can fit 100 files in each content database. Multiple site collections can share a single content database. Each site collection needs to be fully stored in a single content database. We strongly recommended limiting the size of content databases to 200 GB, except when the circumstances in the following rows in this table apply. If you are using Remote BLOB Storage (RBS), the total volume of remote BLOB storage and metadata in the content database must not exceed the 200GB limit. | ||
| Content database size (all usage scenarios) | 4 TB per content database | Supported | Content databases of up to 4 TB are supported when the following requirements are met:
| ||
| Content database size (document archive scenario) | No explicit content database limit | Supported | Content databases with no explicit size limit for use in document archive scenarios are supported when the following requirements are met:
| ||
| Content database items | 60 million items including documents and list items | Supported | The largest number of items per content database that has been tested on SharePoint Server 2013 is 60 million items, including documents and list items. If you plan to store more than 60 million items in SharePoint Server 2013, you must deploy multiple content databases. | ||
| Site collections per content database | 10,000 maximum (2,500 non-Personal site collections and 7,500 Personal Sites, or 10,000 Personal Sites alone) | Supported | We strongly recommended limiting the number of site collections in a content database to 5,000. However, up to 10,000 site collections in a database are supported. Note that in a content database with up to 10,000 total site collections, a maximum of 2,500 of these can be non-Personal site collections. It is possible to support 10,000 Personal site collections if they are the only site collections within the content database. These limits relate to speed of upgrade. The larger the number of site collections in a database, the slower the upgrade with respect to both database upgrade and site collection upgrades. The limit on the number of site collections in a database is subordinate to the limit on the size of a content database that has more than one site collection. Therefore, as the number of site collections in a database increases, the average size of the site collections it contains must decrease. Exceeding the 5,000 site collection limit puts you at risk of longer downtimes during upgrades. If you plan to exceed 5,000 site collections, we recommend that you have a clear upgrade strategy to address outage length and operations impact, and obtain additional hardware to speed up the software updates and upgrades that affect databases. To set the warning and maximum levels for the number of sites in a content database, use the Windows PowerShell cmdlet Set-SPContentDatabase with the -WarningSiteCount parameter. For more information, see Set-SPContentDatabase. | ||
| Remote BLOB Storage (RBS) storage subsystem on Network Attached Storage (NAS) | Time to first byte of any response from the NAS should remain within 40 milliseconds 95% of the time. | Boundary | When SharePoint Server 2013 is configured to use RBS, and the BLOBs reside on NAS storage, consider the following supported limit. From the time that SharePoint Server 2013 requests a BLOB, until it receives the first byte from the NAS, 95% of the time no more than 40 milliseconds can pass. |
Important: Site Collection Limits
The following table lists the recommended guidelines for site collections.| Limit | Maximum value | Limit type | Notes |
| Site collections per farm | 750,000 (500,000 Personal Sites and 250,000 other sites per farm) | Supported | The maximum recommended number of site collections per farm is 500,000 Personal Sites plus 250,000 for all other site templates. The Sites can all reside on one web application, or can be distributed across multiple web applications. Note that this limit is affected by other factors that might reduce the effective number of site collections that can be supported by a given content database. Care must be exercised to avoid exceeding supported limits when a container object, such as a content database, contains a large number of other objects. For example, if a farm contains a smaller total number of content databases, each of which contains a large number of site collections, farm performance might be adversely affected long before the supported limit for the number of site collections is reached. For example, Farm A contains a web application that has 200 content databases, a supported configuration. If each of these content databases contains 1,000 site collections, the total number of site collections in the web application will be 200,000, which falls within supported limits. However, if each content database contains 10,000 site collections, even though this number is supported for a content database, the total number of site collections in the farm will be 2,000,000, which exceeds the limit for the number of site collections per web application. Memory usage on the web servers should be monitored, as memory usage is dependent on usage patterns and how many sites are being accessed in given timeframe. Similarly, the crawl targets might also exhibit memory pressure, and if so the application pool should be configured to recycle before available memory on any web server drops to less than 2 GB. |
| Web site | 250,000 per site collection | Supported | The maximum recommended number of sites and subsites is 250,000 sites. You can create a very large total number of web sites by nesting subsites. For example, in a shallow hierarchy with 100 sites, each with 1,000 subsites, you would have a total of 100,000 web sites. Or a deep hierarchy with 100 sites, each with 10 subsite levels would also contain a total of 100,000 web sites. Note: Deleting or creating a site or subsite can significantly affect a site’s availability. Access to the site and subsites will be limited while the site is being deleted. Attempting to create many subsites at the same time may also fail. |
| Site collection size | Maximum size of the content database | Supported | A site collection can be as large as the content database size limit for the applicable usage scenario. For more information about the different content database size limits for specific usage scenarios, see the Content database limits table in this article. In general, we strongly recommend limiting the size of site collections to 100 GB for the following reasons:
|
| Number of device channels per publishing site collection | 10 | Boundary | The maximum allowed number of device channels per publishing site collection is 10. |
List and Library Limits
The following table lists the recommended guidelines for lists and libraries. For more information| Limit | Maximum value | Limit type | Notes |
| List row size | 8,000 bytes per row | Boundary | Each list or library item can only occupy 8,000 bytes in total in the database. 256 bytes are reserved for built-in columns, which leaves 7,744 bytes for end-user columns. For details on how much space each kind of field consumes, see Column limits. |
| File size | 2 GB | Boundary | The default maximum file size is 250 MB. This is a configurable limit that can be increased up to 2 GB (2,047 MB). However, a large volume of very large files can affect farm performance. |
| Documents | 30,000,000 per library | Supported | You can create very large document libraries by nesting folders, or using standard views and site hierarchy. This value may vary depending on how documents and folders are organized, and by the type and size of documents stored. |
| Major versions | 400,000 | Supported | If you exceed this limit, basic file operations—such as file open or save, delete, and viewing the version history— may not succeed. |
| Minor versions | 511 | Boundary | The maximum number of minor file versions is 511. This limit cannot be exceeded. |
| Items | 30,000,000 per list | Supported | You can create very large lists using standard views, site hierarchies, and metadata navigation. This value may vary depending on the number of columns in the list and the usage of the list. |
| Rows size limit | 6 table rows internal to the database used for a list or library item | Supported | Specifies the maximum number of table rows internal to the database that can be used for a list or library item. To accommodate wide lists with many columns, each item may be wrapped over several internal table rows, up to six rows by default. This is configurable by farm administrators through the object model only. The object model method is SPWebApplication.MaxListItemRowStorage. |
| Bulk operations | 100 items per bulk operation | Boundary | The user interface allows a maximum of 100 items to be selected for bulk operations. |
| List view lookup threshold | 8 join operations per query | Threshold | Specifies the maximum number of joins allowed per query, such as those based on lookup, person/group, or workflow status columns. If the query uses more than eight joins, the operation is blocked. This does not apply to single item operations. When using the maximal view via the object model (by not specifying any view fields), SharePoint will return up to the first eight lookups. |
| List view threshold | 5,000 | Threshold | Specifies the maximum number of list or library items that a database operation, such as a query, can process at the same time outside the daily time window set by the administrator during which queries are unrestricted. |
| List view threshold for auditors and administrators | 20,000 | Threshold | Specifies the maximum number of list or library items that a database operation, such as a query, can process at the same time when they are performed by an auditor or administrator with appropriate permissions. This setting works with Allow Object Model Override. |
| Subsite | 2,000 per site view | Threshold | The interface for enumerating subsites of a given web site does not perform well as the number of subsites surpasses 2,000. Similarly, the All Site Content page and the Tree View Control performance will decrease significantly as the number of subsites grows. |
| Coauthoring in Word and PowerPoint for .docx, .pptx and .ppsx files | 10 concurrent editors per document | Threshold | Recommended maximum number of concurrent editors is 10. The boundary is 99. If there are 99 co-authors who have a single document opened for concurrent editing, each successive user sees a "File in use" error, and can only open a read-only copy. More than 10 co-editors will lead to a gradually degraded user experience with more conflicts, and users might have to go through more iterations to successfully upload their changes to the server. |
| Security scope | 50,000 per list | Threshold | The maximum number of unique security scopes set for a list cannot exceed 50,000. For most farms, we recommend that you consider lowering this limit to 5,000 unique scopes. For large lists, consider using a design that uses as few unique permissions as possible. When the number of unique security scopes for a list exceeds the value of the list view threshold (set by default at 5,000 list items), additional SQL Server round trips take place when the list is viewed, which can adversely affect list view performance. A scope is the security boundary for a securable object and any of its children that do not have a separate security boundary defined. A scope contains an Access Control List (ACL), but unlike NTFS ACLs, a scope can include security principals that are specific to SharePoint Server 2013. The members of an ACL for a scope can include Windows users, user accounts other than Windows users (such as forms-based accounts), Active Directory groups, or SharePoint groups. |
Column Limits
SharePoint Server 2013 data is stored in SQL Server tables. To allow for the maximum number of possible columns in a SharePoint list, SharePoint Server 2013 will create several rows in the database when data will not fit on a single row. This is called row wrapping.
Each time that a row is wrapped in SQL Server, an additional query load is put on the server when that item is queried because a SQL join must be included in the query. To prevent too much load, by default a maximum of six SQL Server rows are allowed for a SharePoint item. This limit leads to a particular limitation on the number of columns of each type that can be included in a SharePoint list. The following table describes the limits for each column type.
The row wrapping parameter can be increased beyond six, but this may result in too much load on the server. Performance testing is recommended before exceeding this limit.
Each column type has a size value listed in bytes. The sum of all columns in a SharePoint list cannot exceed 8,000 bytes. Depending on column usage, users can reach the 8,000 byte limitation before reaching the six-row row wrapping limitation.
| Limit | Maximum value | Limit type | Size per column | Notes |
| Single line of text | 276 | Threshold | 28 bytes | SQL Server row wrapping occurs after each 64 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 384 Single line of text columns per SharePoint list (6 * 64 = 384). However, because the limit per SharePoint list item is 8,000 bytes, of which 256 bytes are reserved for built-in SharePoint columns, the actual limit is 276 Single line of text columns. |
| Multiple Lines of Text | 192 | Threshold | 28 bytes | SQL Server row wrapping occurs after each 32 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 192 Multiple lines of text columns per SharePoint list (6 * 32 = 192). |
| Choice | 276 | Threshold | 28 bytes | SQL Server row wrapping occurs after each 64 columns in a SharePoint list. The default row wrapping value of 6 allows for a maximum of 384 Choice columns per SharePoint list (6 * 64 = 384); ); however because the limit per SharePoint list item is 8,000 bytes, of which 256 bytes are reserved for built-in SharePoint columns, the actual limit should be 276 Choice columns. |
| Number | 72 | Threshold | 12 bytes | SQL Server row wrapping occurs after each 12 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 72 Number columns per SharePoint list (6 * 12 = 72). |
| Currency | 72 | Threshold | 12 bytes | SQL Server row wrapping occurs after each 12 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 72 Currency columns per SharePoint list (6 * 12 = 72). |
| Date and Time | 48 | Threshold | 12 bytes | SQL Server row wrapping occurs after each eight columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 48 Date and Time columns per SharePoint list (6 * 8 = 48). |
| Lookup | 96 | Threshold | 4 bytes | SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 single value Lookup columns per SharePoint list (6 * 16 = 96). |
| Yes / No | 96 | Threshold | 5 bytes | SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 Yes / No columns per SharePoint list (6 * 16 = 96). |
| Person or group | 96 | Threshold | 4 bytes | SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 Person or Group columns per SharePoint list (6 * 16 = 96). |
| Hyperlink or picture | 138 | Threshold | 56 bytes | SQL Server row wrapping occurs after each 32 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 192 Hyperlink or Picture columns per SharePoint list (6 * 32 = 192) ); however because the limit per SharePoint list item is 8,000 bytes, of which 256 bytes are reserved for built-in SharePoint columns, the actual limit should be 138 Hyperlink or Picture columns. |
| Calculated | 48 | Threshold | 28 bytes | SQL Server row wrapping occurs after each eight columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 48 Calculated columns per SharePoint list (6 * 8 = 48). |
| GUID | 6 | Threshold | 20 bytes | SQL Server row wrapping occurs after each column in a SharePoint list. The default row wrapping value of six allows for a maximum of 6 GUID columns per SharePoint list (6 * 1 = 6). |
| Int | 96 | Threshold | 4 bytes | SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 Int columns per SharePoint list (6 * 16 = 96). |
| Managed metadata | 94 | Threshold | 40 bytes for the first, 32 bytes for each subsequent | The first Managed Metadata field added to a list is allocated four columns:
|
For example, given an External Content Type “Customer” which has fields like “ID”, “Name”, “Country”, and “Description”, when you add an External Data column of type “Customer” to a list, you can add secondary fields to show the “ID”, “Name” and “Description” of the Customer. Overall these are the columns that get added:
- Primary column: A text field.
- Hidden Id column: A multi-line text field.
- Secondary columns: Each secondary column is a text/number/Boolean/multi-line text that is based on the data type of the secondary column as defined in the Business Data Catalog model. For example, ID might be mapped to a Number column; Name might be mapped to a Single line of text column; Description might be mapped to a Multiple lines of text column.
Page Limits
The following table lists the recommended guidelines for pages.| Limit | Maximum value | Limit type | Notes |
| Web parts | 25 per wiki or Web Part page | Threshold | This figure is an estimate based on simple Web Parts. The complexity of the Web Parts dictates how many Web Parts can be used on a page before performance is affected. |
Security Limits
| Limit | Maximum value | Limit type | Notes |
| Number of SharePoint groups a user can belong to | 5,000 | Supported | This is not a hard limit but it is consistent with Active Directory guidelines. There are several things that affect this number:
|
| Users in a site collection | 2 million per site collection | Supported | You can add millions of people to your web site by using Microsoft Windows security groups to manage security instead of using individual users. This limit is based on manageability and ease of navigation in the user interface. When you have many entries (security groups of users) in the site collection (more than one thousand), you should use Windows PowerShell to manage users instead of the UI. This will provide a better management experience. |
| Active Directory Principles/Users in a SharePoint group | 5,000 per SharePoint group | Supported | SharePoint Server 2013 enables you to add users or Active Directory groups to a SharePoint group. Having up to 5,000 users (or Active Directory groups or users) in a SharePoint group provides acceptable performance. The activities most affected by this limit are as follows:
|
| SharePoint groups | 10,000 per site collection | Supported | Above 10,000 groups, the time to execute operations is increased significantly. This is especially true of adding a user to an existing group, creating a new group, and rendering group views. |
| Security principal: size of the Security Scope | 5,000 per Access Control List (ACL) | Supported | The size of the scope affects the data that is used for a security check calculation. This calculation occurs every time that the scope changes. There is no hard limit, but the bigger the scope, the longer the calculation takes. |
0 comments: