SharePoint Database Tips

Recently, I have been working on a number of SharePoint database issues related to large number of workflow instances.  Here are a few things I encountered:

 

• System.Runtime.InteropServices.COMException (0x80004005) is a catch all for all database and other interop errors  – You can get this error for any number of reasons – for example out of transaction log space, deadlocks etc. I have seen a number of posts on the web attributing this error to one condition or another.

 

• Turn on WF tracing (see my previous post for more information). Trace logs provide a great source of information on how a given workflow instance has progressed. One can find the workflow instance that encountered an error. Once you have the workflow instance, you can query the workflow and workflow association tables to determine the related SPWeb, SPTask etc.

 

• Because SharePoint uses GUID as a the primary key it can result in a fragmentation (Kimblerly Tripp has a nice post about this) that one needs to be aware of.

 

• We found that despite the weekly timer job ( note that there is a difference between SP1 and SP2)statistics on certain tables can be off, resulting in less than optimal plans, which in turn  can cause deadlocks in some instances.

 

• It is important to review the content database maintenance plan and tweak it as needed – Another useful post from Kimberly Tripp – http://www.sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-I-e28093-clarifying-ambiguous-recommendations-for-Sharepoint.aspx

 

• Check if max degrees of parallelism is having an impact? Try re-running your tests after MAXDOP to 1.

 

• Look for lock escalation warnings – Try altering the locking threshold using the trace flags (1211, 1224).

 

• Review your code for potentially expensive WSS OM calls such as BreakRoleInheritance. Perform static analysis using tool such as SPDisposeCheck to make sure resources are being correctly disposed.

 

• Because of their episodic nature, workflows are difficult to automate the testing of workflows – There is a nice MSDN  article on unit testing workflows.

Load testing workflows is even harder. We developed a tool that allowed us to load test our workflows. This tool is itself a workflow program that drives a configured number of workflows – you basically supply the workflow types, users, how the tasks need to be updated (accepted, rejected etc ) and in what order. Having a load test tool allowed us to consistently recreate some of the issues we were encountering in production.

 

• Active management of lists is very important. Archive old items from lists regularly as appropriate.