Well, I'm on a new job, and am investigating performance problems. The first thing I'm looking at is everything that is causing deadlocks.
Every time, the root procedure is performing selects against multiple tables where the only indexes are on the PK, and a rowguid for replication. That's it. There queries are performing table/CI scans constantly. Adding a few indexes has helped tremendously with the stability of the server and their application.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes