About "exceeding the list threshold required by the Administrator"

This morning, I received a call from dimei saying that the bpiou website has a problem, some users sent an email saying they could not view the course in "My courses" (this is the default list view of SharePoint ), the error page is shown as follows:

Go in and take a look. We found that the project in the user course reservation list has exceeded 5000 and reached the list threshold set by default in Sharepoint.

OK. The above is a real story. The story tells you to pause it and see what the so-called "list threshold limit" is.

We know that in the era of SharePoint 2007, the most complained questions about SharePoint are roughly summarized as follows: (1) List performance; (2) Permission allocation is too cumbersome; (3) workflow.

The first problem greatly limits the application scenarios of SharePoint. Many users have read the famous White Paper (I won't give it a link) and have heard many courses we have talked about, two numbers are impressive: 2000 and 20000 -- two recommended values in the White Paper: no more than 2000 entries in a view/folder and no more than 20000 entries in a list. Of course, this is just a suggestion. KB gave a detailed description of this number in this blog (Click here. Today, we don't want to talk about these two numbers, but about the improvement of list performance in SharePoint 2010.

First, according to Microsoft's official statement, in SharePoint 2010, a list can support tens of millions of entries (You are not mistaken, it is tens of millions of entries, but the prerequisite is to plan the list capacity. On the "Management Center"> "Web Application Management"> "resource limits" page in "2010", we can see some threshold limits for the list added in "2010", the first of which is the most important, is the list view threshold. The default value is 5000 (SEE ).

In the SharePoint online help document, I have an explanation of the history of the number 5000 (which also explains why an excessive list of content in 2007 will cause a significant performance impact ), excerpt: to minimize database contention, SQL Server often uses row-level locking policies to ensure accurate updates without negatively affecting other users accessing other rows. However, if a read or write database operation (such as a query) locks more than 5,000 rows at a time, SQL Server will temporarily upgrade the lock to the entire table until the database operation is completed, will be more effective. Note that the actual number is not always 5,000, which may vary depending on the activity volume of your website, database, and website configuration. If you perform this lock upgrade, other users will not be able to access the table. If this locking upgrade happens frequently, all users will experience a decline in system performance. Therefore, to help minimize the impact of resource-intensive database operations and balance the needs of all users, thresholds and restrictions are essential.

Therefore, in section 2010, once a SharePoint user finds that the query result may exceed 5000 during View query, the two errors at the beginning of the story may occur. This setting can prevent user operations that may affect the overall performance of the server, so as not to affect the normal use of other users because of the operations of individual users.

Here, let's take a look at how to solve this problem. Back to the story scenario: the number of course reservation list entries exceeds 5000, but each user does not need to view all course reservation records (except for administrators ), you only need to care about the courses you have subscribed. Therefore, the "my courses" view and course reservation are filtered by the "creator" to display only the course reservation records created by the current user.

To solve the problem above, you only need to create an index for the "creator" field (go to the list settings page, manage the index, and add "creator ").

When SharePoint determines that the first filter condition (Note: only the first one) of the current view has been indexed, the limit of the 5000 threshold is not for the entries in the entire list, the number of results queried under this index field. Can a user book more than 5000 courses? Of course not. There are not so many classes in total. Therefore, after this setting is modified, the system can run as before, but it takes a little longer to book a course, allow SharePoint time to maintain its own indexes.

The story has come to an end, but it is not over.

In the "resource limit" interface of the management center, some related settings are related to the number of thresholds. these settings are mainly applicable to the following scenarios:

Scenario 1: I am an administrator and need to have privileges.

Yes, the Administrator does have some operations in the system that are different from those of common users, such as viewing courses subscribed by all users. If the number of courses is limited according to the 5000 threshold, it seems unreasonable. SharePoint separately sets another threshold for administrators. The default value is 20000 (a familiar number). As long as the administrator's view is filtered by the index column, the total number of results cannot exceed 20000, And the administrator can work normally.

Scenario 2: I am a background program and need some special operations.

For people, the total number of 20000 is basically enough, and for programs running on some machines, the number of 20000 is far from enough. For example, we need to perform a statistical analysis on the courses subscribed by all users every night. This type of terrible data query may not affect normal users (except those who stay up late and play games, occasionally, the website night owl). Therefore, Sharepoint can set a special time period. During this time period, the user's query does not use any threshold value, and the user can run programs or operations at will.

The above is only one of the improvements that SharePoint 2010 brings to the performance of list entries. Due to space limitations, this article does not cover all capacity planning settings (such as the newly added metadata navigation in 2010, is a method that can be used to plan and manage ultra-large data volumes. These planning and settings can be found on the technet website, you can also view it in SharePoint online help (do you see a link in the first error interface? Click here to get online help ).