Performance issues on Sharepoint : Detect over 2000 items for Moss2007 and over 5000 items for SPS2010 in folders

Hi All,

As you know a performance problem is not an easy task because it can caused by anything in your system, slow queries, swap file configuration,hardware bottlenecks, long compile times,badly coded custom applications, other services etc and etc and etc . It could be anything.

In this article i am going to tell about just one issue to detect which can cause mostly sharepoint performance problems in other tens of thousands possibilities 🙂
Lets have a look Sharepoint Boundries article on technet about this thresholds.
http://technet.microsoft.com/en-us/library/cc262787.aspx
http://technet.microsoft.com/en-us/library/cc262787(v=office.12).aspx

For SharePoint 2010 :

List view threshold: 5000 item per view
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.

For Sharepoint 2007 it is defined as 2000 item per view.

So what does this rules means. Simply i could say you have one million items in your list  but you have to limits your queries by max 2000 (5000 for sharepoint 2010) items to return becuase of performace issues it is by design of SharePoint Database Architecture. In SharePoint 2010 you can store 30,000,000 item on list and it is far far away than the item per view threshold but if you over this threshold that is a problem.

Then what we can do :
There are so many articles that define what could you do about resolve this problem if you have a this kind of performance issues.
Here some good resources:
White Paper: Working with large lists in Office SharePoint^® Server 2007
http://office.microsoft.com/download/afile.aspx?AssetID=AM102377231033
Designing large lists and maximizing list performance (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/cc262813.aspx
http://blogs.technet.com/b/speschka/archive/2009/10/27/working-with-large-lists-in-sharepoint-2010-list-throttling.aspx
http://sharepoint.microsoft.com/blogs/GetThePoint/Lists/Posts/Post.aspx?ID=162

Basically you can have two options. (I am not tell how could you do that it is out of scope for this article)
1) Limit any query which is send to SQL Server from Sharepoint must return lower count of results than threshold.
You may do it by Sharepoint Gui or by use some code technics for your custom codes.Even if you have set the view limits or row limits in your code or SharePoint gui still some open points to query this lists by not any limitation for example Windows Explorer view or some queries which created by workflow actions or not managable 3rd party applications .
So second step is folderize .
2) Use the Folders . Make and keep your list content in folderize hiearchy.

Ok. Now i assume that you learn item per view threshold and how to fix or optimize your large lists. But imagine that you have so many lists and subsites in your site content database.How could you find these lists ?

Unfortunately there is not any reporting tool exists in oob. You can use Gui’s but it will take so much time. So running some sqls for report purpose will help us.
By the way don’t forget that you never change any data in SharePoint Database it is not permitted and makes your system out of support.

Sharepoint has use 3 active tables for most of your queries has read from them when you working ,browsing ,updateing or deleting list operations.
These are AllLists , AllUserData ,AllDocs tables. Very simply AllLists tables has store your lists information, AllUserData has store your list items metadata ,and AllDocs has store that if you have attached any document for lists.

Fallowing Query will return us in your item counts grouped  by list

select webs.Title,webs.FullUrl, tp_Title,tp_ServerTemplate,UserDataCount,DocsCount
from AllLists left outer join
(select tp_ListId, COUNT(*) as UserDataCount from AllUserData
group by tp_ListId ) E On E.tp_ListId = AllLists.tp_ID
left outer join
(select ListId, COUNT(*) as DocsCount from dbo.Docs
group by ListId ) P ON AllLists.tp_ID = P.ListId
left outer join Webs ON AllLists.tp_WebId = Webs.id
order by DocsCount desc 

  

Title :is Web Title:
FullUrl : is Web Url
tp_Title : is List Title
tp_ServerTemplate : is list type id . You can find list type id definition in this article
https://blog.bugrapostaci.com/2010/09/22/sharepoint-list-type-numbers/
UserDataCount : record count of stored in AllUserData table for the list. (Deleted items not included)
DocsCount : record count of stored in AllDocs table for the list (Deleted items not included)

As you see on the picture for the first row the list  has contains 39793 record in AllUserData and 118724 data in AllDocs tables.But it does not give us any information about folder counts .
So if you run fallowing query

select tp_listid,webs.Title,webs.FullUrl, tp_Title,tp_ServerTemplate,UserDataCount,DocsCount, 
	(select COUNT(*) from (
	select COUNT(*) as c from Docs A
	where A.listId = AllLists.tp_ID
	Group by A.Dirname having count(*) > 2000 ) Q) as OverLoadDocsFolders,
	(select COUNT(*) from (
	select COUNT(*) as c from UserData B
	where B.tp_listId = AllLists.tp_ID
	Group by B.tp_dirname having count(*) > 2000 ) Z) as OverLoadUserDataFolders
from AllLists left outer join 
(select tp_ListId, COUNT(*) as UserDataCount  from AllUserData 
group by tp_ListId ) E On E.tp_ListId = AllLists.tp_ID
left outer join 
(select ListId, COUNT(*) as DocsCount  from dbo.Docs
group by ListId ) P ON AllLists.tp_ID = P.ListId
left outer join Webs ON AllLists.tp_WebId = Webs.id
order by DocsCount desc 

You can also add fallowing expression to your SQL for just query  lists which contains over size folders
where OverLoadDocsFolders > 0 or OverLoadUserDataFolders >0

OverLoadDocsFolders : the folder count of contains over 2000 items for the list.
OverLoadUserDataFolders : the folder count of contains over 2000 items for the list
Which means the first and sixth record’s list are not have an over loaded folder. They are folderized well.
The other lists are have at least one overloaded folders.

If you want to get more details about over size folders of a list you can query with fallowing SQL

select DirName,COUNT(*)from Docs where Docs.ListId=‘<List Guid Here>’
group by docs.DirName havingCOUNT(*)>2000

select tp_DirName,COUNT(*)from UserData where tp_ListId=‘<List Guid Here>’
group by tp_DirName havingCOUNT(*)>2000

(By the way 2000 is for MOSS 2007 if you have SharePoint 2010 you may replace all 2000 to 5000 .)