A big problem with large systems is blocking. This article can help troubleshoot why your database is "just hanging out" when it should be doing something.
Troubleshooting Deadlocks in SQL Server
Jonathan Kehayias, March 17, 2008
One of the more difficult/frustrating things to isolate and eliminate in SQL Server are deadlocks. In all but the rarest exception, deadlocks are a side effect of blocking and not a SQL Server Bug. They can generally be resolved by changing application or stored procedure code to access tables in the same logic order, or by changing the actual database schema or indexing structures. The first step in Deadlock elimination is to identify what two statements are actually causing the deadlocks to occur. In some cases, there may be more than just two statements that contribute to a deadlock. The methods available to troubleshoot deadlocks in SQL Server differ between SQL Server 2000 and SQL Server 2005.