Performance issues with a list in MOSS 2007
Last week, I visited one of my favorite Customers in Belfast. They experienced performance issues in their Customer's MOSS 2007 production environment.
The symptoms were the followings: a list item takes 10-20 seconds to open, sometimes more. Looking closer, it turns out that those list items are slow to open that are in a list that has a workflow associated and the workflow already finished running for the list item. The more time the list item is modified, the more time it takes to open it. My Customer is really a cool troubleshooter and he already found out these things, together with a SQL trace where it turns out that when the item is opened, the stored prodecure proc_GetListItemWorkflows is called too many times. If there were 2 modifications on the list item (so, the workflow ran 2 times already), it is called about 46 times, if it's 3, it's almost 100 times and so on. The stored procedure itself is just a SELECT statement and it's pretty fast, but the overhead to call the SP takes some time - especially when you do it 46 times. :) If the store procedure's contents are deleted, the problem is solved, but your MOSS box enters into an UNSUPPORTED STATE in this case. We found that the problem occurs only with sites based on the Content Publishing site template - so this means that WSS 3 sites are not affected.
The problem was quite hard to identify. The reason why it was hard, because there were 2 bugs with the very same symptoms and we didn't know whether we fixed one and the other one was still there. Let's call them bug #1 and bug #2.
- Bug #1 was resolved by installing the latest official *CUMULATIVE* hotfix (Q939599) for MOSS 2007. For getting the hotfix, check the details at the end of this article. MOSS 2007 SP1 will contain the fix - and that's the best way to have this bug fixed in your production environment.
- Bug #2 is still there but we found a way to eliminate it (turn off the Office SharePoint Server Publishing feature on the Site features).
Even if we installed the hotfix and disabled the feature, the problem didn't disappear. We had to create a brand new site on the patched server and disable the feature on that one - in this case, the problem got solved on this very site.
What happens with data already in the list?
Data that was entered on the "bad" site before the hotfix is installed is “corrupted”. We tried stsadm -o export and stsadm -o import to export-import it to a patched site but it didn't work either. So, it seems that the patch causes list items to be stored in a different way - but the list items already stored on the "bad" site are in the bad format. My guess is that using the Datagrid view in the list to copy and paste the items will work. I will come back later with an update whether this one helped or not.
Getting the hotfix
You can obtain the hotfix by opening a support incident at Microsoft Product Support Services (see http://support.microsoft.com/gp/services/?LN=id for more info), contact CSS on the numbers at http://www.microsoft.com/services/microsoftservices/srv_support.mspx or use the email hotfix service at https://support.microsoft.com/contactus2/emailcontact.aspx?scid=sw;en;1410&WS=hotfix. In neither case you will be charged for obtaining the hotfix.