I found two great posts about biztalk server 2004/2006 DB optimization, deadlock solutions.
I suggest everyone who work with biztalk to read this posts.
- Biztalk core engine's WebLog - What you can and can't do with the Messagebox Database server.
- Don't enable/use statistics.
- Shutdown the parallelism.
- Don't built maintenance plan for indexes, use only bts_RebuildIndexes stored procedure, shutdown biztalk engine first.
- Don't change the stored procedure code.