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
http://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 .)

Advertisements

When try to change application pool identity for a sharepoint iis site getting “keyset does not exists” error

One day you noticed that your application pools getting stopped and when try to run again it is stopping again and after a while you suspected that the problem may be caused by identity account corruption and decided that change application pool identity or reset current identity’s password but then upps your getting fallowing error.
“Keyset does not exists (Exception from HRESULT: 0x80090016)

If you get this error first you have to check your MACHINE Keys.By the way i have to say the machine key not only used by IIS or your web sites. So many process can use machine keys to encrypt or decrypt secures like strings,passwords,connection strings etc. As you noticed if some how your machine keys are changed or deleted you may have a big problem if you dont have any proper backup.

but how could that happens ? There is so many possibility here , Malwares,Group Policies,Permission issues, User mistakes , Cleanup programs, misconfigrurations.
So you may first check the fallowing
http://support.microsoft.com/kb/977754

The LOCAL SERVICE account is the service account of the IIS Web Management Service (also known as WMSvc).  This problem occurs because the LOCAL SERVICE account does not have Read access on the iisWasKey key that is located in the following folder: %ALLUSERSPROFILE%\Microsoft\Crypto\RSA\MachineKeys
The following is the file name of the iisWasKey key: 76944fb33636aeddb9590521c2e8815a_GUID
To resolve this problem, follow these steps:

  1. Locate the following folder:
    %ALLUSERSPROFILE%\Microsoft\Crypto\RSA\MachineKeys
  2. Right-click the following file, and then click Properties:
    76944fb33636aeddb9590521c2e8815a_GUID
  3. Click the Security tab, and then click Edit. If you are asked whether you want to continue the operation, click Continue. Then, the list of group names and user names that have access to this key file appears in the Permissions dialog box.
  4. Click Add. Then, the Select Users, Computers, Service Accounts, or Groups dialog box appears.
  5. Type  LOCAL SERVICE, and then click Check Names.
  6. Click OK.
  7. In the Group or user names list, click LOCAL SERVICE. Make sure that the Read check box is checked in the Permissions for LOCAL SERVICE list.
  8. Click OK.

Compare HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Cryptography MachineGuid for records in
%ALLUSERSPROFILE%\Microsoft\Crypto\RSA\MachineKeys folder .

So what if you have missing keys:
There is two option.
1) Restore missing keys from newest good backup.
2) I am sorry to say that but second option is Total recovery:
You need to reinstall¬†¬†SharePoint or IIS even may need all machine. And after do that¬†you¬†must¬†reset all your sharepoint managed accounts’s password.

Machine Keys are important for security and always consider to save them.If you are suspecting that your machine keys stolen you may need to reset your all passwords not for sharepoint all accounts for that machine in use and dont forget machine keys not use only by IIS.

if you think that the issue related a permission issue you may use “Process Monitor” to find out which process can not reach your data.
http://technet.microsoft.com/en-us/sysinternals/bb896645

 

Unable to delete Search Service Application (SSA) in SharePoint 2010

Hello,

If you have a bull headed Enterprise Search Service Application and you can not able to delete this service application by Central Administration or fallowing powershell script
$ssa = Get-SPServiceApplication -name “<Search Service Application¬† Name>”
Remove-SPServiceApplication $ssa

The symptoms is when you attempt to  delete this service application powershell or web GUI is hanging  until timeout.

So you may try fallowing powershell command to force to delete:

1) Open the SharePoint PowerShell on the SharePoint server and run the following commands:
2) Run fallowing command which will list all Enterprize search applications and their GUIDs
Get-SPEnterpriseSearchServiceApplication
Take note of problematic service application guid
3) Type fallowing command:
$ssa = Get-SPEnterpriseSearchServiceApplication -id “<noted guid here>”
$ssa.unprovision(1)

if the above command is successful the SSA will be deleted, if it is hung, then try fallowing:
1) Open a new PowerShell:
2) Run fallowing command which will list all Enterprize search applications and their GUIDs
Get-SPEnterpriseSearchServiceApplication
Take note of problematic service application guid

$ssa = Get-SPEnterpriseSearchServiceApplication -id “<noted guid here>”
$ssa.Delete()

I hope this will resolve your issues.