Post
Ask Question

Detailed Insight On The SharePoint 5000 List View Threshold Limit Issue

Introduction

 
The threshold concept was introduced in SharePoint 2010 considering the performance issue while loading a big list to improve the query performance. There is an incorrect perception about the SharePoint list view threshold 5000 error - many users think that the SharePoint list/library just can store 5000 items but that is not true, in fact, SharePoint list can store 30 million items. Then why are we getting this error? Before we analyze about this error I recommend you to read my previous articles "Overview Of SharePoint Column Indexing" and "Creating Indexed Column In SharePoint"
 

Why do we get a 5000 threshold error?

 
This error actually comes from SQL, not from SharePoint. As I have explained in my previous article "Overview Of SharePoint Column Indexing" there we have seen that the SharePoint stores all of its list data and list columns in single SQL table, i.e. "AllUserData" table.
 
Now we need to learn how the query gets processed.
 
When we browse a list or library SharePoint sends a query to SQL and SQL process that query and send the result to SharePoint, then finally we can see the result i.e. nothing but list or library items in the browser. Let's take an example of "All Items" view (assuming that no filters were applied on it) - when we click on this view SharePoint will build a query and pass this on to SQL, then SQL processes that query and sends the result to SharePoint. Now if the results returned by the query are more than 5000 then the user gets the below-mentioned list view threshold error.
 
 
 

Why does this limitation set as 5,000 in SQL?

 
Due to the performance reasons, if SQL server performs the query on a single table (here it is: “AllUserData” table) which would result in the output being more than 5000 items, in this case, the SQL server will lock the entire table while the query execution is on – this is the default behavior of SQL by the designed.
 
So when the "AllUserData" table is locked – what will happen?
 
So far as we have learned that SharePoint stores all list/library data in a single table i.e “AllUserData” – a single query which returns more than 5,000 items will lock all of the list and library data for that entire content database.
 
In this scenario – the business users who were accessing content in SharePoint from the same content site collection (content database) while the “AllUserData” table is locked, will have to wait a longer time for their results to be displayed.
 
By now we have learned about the root cause of threshold error and we need to focus on how to move on with this situation as the business has to run.
 

How to overcome the 5000 items list view threshold issue?

 
We can increase the list items limit from 5000 to 50000 (fifty thousand) in SharePoint on-premise, in fact, we can disable the list view threshold limit which is not all recommended but in SharePoint online we don’t have control over it, so we need to move on with the whatever limitation is set i.e. 5000.
 
Here based on my experience, I will list out some tips and tricks to avoid this issue which will work both for SharePoint online and on-premise.
  • Planning – before creating the list or library, analyze the data and plan accordingly. Try to split the data in multiple list or library.
  • Create multiple views and add “AND” condition in the list filter criteria, never add “OR” condition, “OR” condition will try to get all data.
  • Create index column – we can only have 20 indexed columns in a given list, so we need to plan this thoughtfully!
  • We cannot create an indexed column if the list is already hitting the list view threshold limit.
  • Maintain the archive list – here we need to review the list items periodically and need to check whether some items can be deleted or archived to another list – here we can write some automation job which will be moving the items periodically to another archive list automatically.
  • In the list filter criteria, we need to filter the list items such a way that at any given point of time list view will return less than 5000 items using filter on created column column technique(please see the explanation in the below).

Create filter using [Today]- "<number of days>" technique

 
Here one of the techniques is – create a filter on "Created" column and use the [Today]- “number of days” with less than or equal to condition, something like below:
 
 
 
Note: The logic behind the filter on the created column is – this will try to get the items that will return less than 5000 items. If this condition also returns more than 5000 items, we need to split this up created column condition with multiple “AND” condition with the combination of some other column to ensure the filter returns less than 5000 items as a below example – though the implementation of this technique is time consuming.
 

Practically – let us see what happens when a list exceeds the 5000 items

 
To check this we need to have a list which should have more than 5000 items. So I have created a list named "Test List" were adding more than 5000 items using the below PowerShell script to see what happens when it crosses the 5000 limit.
  1. CLS 
  2. #Load SharePoint CSOM Assemblies  
  3. #Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"  
  4. #Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  
  5.     
  6. $fileName = "Adding_Multiple_Items_Report"  
  7. $enddate = (Get-Date).tostring("yyyyMMddhhmmss")  
  8. $logFileName = $fileName +"_"+ $enddate+"_Log.txt"     
  9. $invocation = (Get-Variable MyInvocation).Value    
  10. $directoryPath = Split-Path $invocation.MyCommand.Path   
  11.   
  12. $directoryPathForLog=$directoryPath+"\"+"LogFiles"  
  13. if(!(Test-Path -path $directoryPathForLog))    
  14.         {    
  15.             New-Item -ItemType directory -Path $directoryPathForLog  
  16.               
  17.         }     
  18.           
  19. $logPath = $directoryPathForLog + "\" + $logFileName    
  20. $isLogFileCreated = $False   
  21.   
  22. #DLL location  
  23. $directoryPathForDLL=$directoryPath+"\"+"Dependency Files"  
  24. if(!(Test-Path -path $directoryPathForDLL))    
  25.         {    
  26.             New-Item -ItemType directory -Path $directoryPathForDLL  
  27.                  
  28.         }   
  29.   
  30. #DLL location  
  31. $clientDLL=$directoryPathForDLL+"\"+"Microsoft.SharePoint.Client.dll"  
  32. $clientDLLRuntime=$directoryPathForDLL+"\"+"Microsoft.SharePoint.Client.dll"  
  33.   
  34. Add-Type -Path $clientDLL  
  35. Add-Type -Path $clientDLLRuntime  
  36.     
  37.   
  38. function Write-Log([string]$logMsg)    
  39. {     
  40.     if(!$isLogFileCreated){     
  41.         Write-Host "Creating Log File..."     
  42.         if(!(Test-Path -path $directoryPath))    
  43.         {    
  44.             Write-Host "Please Provide Proper Log Path" -ForegroundColor Red     
  45.         }     
  46.         else     
  47.         {     
  48.             $script:isLogFileCreated = $True     
  49.             Write-Host "Log File ($logFileName) Created..."     
  50.             [string]$logMessage = [System.String]::Format("[$(Get-Date)] - {0}", $logMsg)     
  51.             Add-Content -Path $logPath -Value $logMessage     
  52.         }     
  53.     }     
  54.     else     
  55.     {     
  56.         [string]$logMessage = [System.String]::Format("[$(Get-Date)] - {0}", $logMsg)     
  57.         Add-Content -Path $logPath -Value $logMessage     
  58.     }     
  59. }   
  60.   
  61.   
  62. #variables region.  
  63. $siteURL="https://globalsharepoint.sharepoint.com/sites/TestSite/"  
  64. $spUserName="[email protected]"  
  65. $password = "ThisIsTestPassWord"  
  66. $spListName="Test List"  
  67. #$numberOfItemsToCreate="5001"  
  68. #variables region end.  
  69.   
  70. $securePassword= $Password | ConvertTo-SecureString -AsPlainText -Force  
  71.     
  72. #Setup the Context  
  73.   
  74. try  
  75. {  
  76. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)  
  77. $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($spUserName, $securePassword)  
  78.     
  79.   
  80. #Get the list  
  81. $spList = $ctx.Web.Lists.GetByTitle($spListName)  
  82. $ctx.Load($spList)  
  83. $ctx.ExecuteQuery()  
  84.   
  85. #Loop thru to create the list items.  
  86. for($i=1; $i -le 5001; $i++)  
  87. {  
  88.   $listItemCreationInformationInSPOnline = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation  
  89.   $newListItemInSPOnline = $spList.AddItem($listItemCreationInformationInSPOnline)  
  90.   $newListItemInSPOnline["Title"] = "CustomItemNumberAddedThruCode_1_$($i)"  
  91.   $newListItemInSPOnline["CustomItemNumber"] = $i;  
  92.   $newListItemInSPOnline.Update()  
  93.   $ctx.ExecuteQuery()  
  94.   
  95.   write-host "Item created in SP Online list: $spListName  CustomItemNumberAddedThruCode_$($i)"  
  96. }      
  97. }  
  98. catch   
  99. {  
  100.     
  101.     $errorMessage = $_.Exception.Message +"in adding mulitple items in SP Online list using CSOM PowerShell script";  
  102.     Write-Host $errorMessage -BackgroundColor Red  
  103.     Write-Log $errorMessage   
  104. }  
  105.            
  106. Write-Host "####################################################################"  -ForegroundColor Green   
  107. Write-Host "The script execution has been completed!" -ForegroundColor Green   
  108. Write-Host "###################################################################"  
Execute the above script like below:
 
 
Now if we go the list setting page (example as “Test List”), we can see the message like below which says this list exceeds the 5000 threshold limit.
 
 

Is the 5000 limitation for all users?

 
No, 5000 limation is for the normal users and for the administrator limitation is higher; i.e., 20000.
 
What does it mean? 
 
When a normal user accesses a SharePoint list which has more than 5000 items, that user will get a 5000 listview threshold error mentioned above and list will not be opened with the data but the same list will be loaded with the data if it accessed by an administrator as it has higher limit i.e. 20000.  
 
  

Summary

 
Thus, in this article we have learned the below concept with respect to SharePoint list view threshold:
  • What is the threshold issue in SharePoint list.
  • Why do we get a 5000 threshold error?
  • Why does this limitation set as 5,000 in SQL?
  • How to overcome the 5000 items list view threshold issue?
  • Practically – saw what happens when a list exceeds the 5000 items.
  • What is the limitation for normal user and administrator? 
Note:
 
Having described the threshold limit issue and their possible workarounds to fix this in SharePoint – it's an impending open issue over a decade from Microsoft, which really Microsoft needs to fix or come up with some inbuilt alternate technique. This issue is one of the drawbacks of SharePoint which leads to many business user's dissatisfaction towards SharePoint. So Microsoft really needs to fix this issue in the long run!!!


Similar Articles