Database Performance

Many database performance issues are caused by the following (as discussed in KB article 952555: How to maintain and troubleshoot BizTalk Server databases):

Here are some additional possibilities:

Hardware disk issues on the SQL Server computer
High CPU
Blocking and deadlocking on the SQL Server computer
NIC card settings, including drivers and Duplex configuration
Indexes that need to be rebuilt. For more information, see KB article 952555: How to maintain and troubleshoot BizTalk Server databases.
TCP settings, such as disabling SynAttackProtect on the SQL Server computer, and disabling TCPChimney on the BizTalk and SQL Server computers. For more information, see KB article 970406: TCP Settings that can impact BizTalk Server.

To troubleshoot:

Use MsgBox Viewer to collect information from the BizTalk Server computer gather information about the status of your BizTalk environment. MsgBox Viewer is available at http://blogs.technet.com/jpierauc/pages/msgboxviewer.aspx.
Enable DBCC Trace flags on the SQL Server computer, if there is deadlock scenario. For more information, see KB article 952555: How to maintain and troubleshoot BizTalk Server databases. Additionally, this SQL blog shows how to interpret the results:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx.
Execute sp_who3 (internal tool) for a blocking scenario.
Collect PSSDiag on the SQL Server computer. This typically requires contacting Microsoft Customer Service and Support (CSS) because of the configuration options in PSSDiag that are needed. There are many times when it has been configured, but with incorrect settings. As a result, the data might have to be re-captured.
Investigate orphaned DTC transactions, which show up as -2 SPID.