SQLWebpedia

The SQLCAT team has a top ten list of OLTP performance issues: http://sqlcat.com/top10lists/archive/2007/11/21/top-sql-server-2005-performance-issues-for-oltp-applications.aspx

Summary

1. Database Design issue if….(Too many table joins for frequent queries; Too many indexes on frequently updated; Big IOs such as table and range scans due to missing indexes; Unused indexes should be eliminated.)
2. CPU bottleneck if… (Signal waits > 25% of total waits; Plan re-use < 90%; Parallel wait type cxpacket > 10% of total waits.)
3. Memory bottleneck if… (Consistently low average page life expectancy; Sudden big drop in page life expectancy; Pending memory grants; Sudden drops or consistenty low SQL Cache hit ratio.)
4. IO bottleneck if… (High average disk seconds per read; Big IOs such as table and range scans due to missing indexes.)
5. Blocking bottleneck if… (Index contention; High average row lock or latch waits; Top wait statistics are LCK_x; High number of deadlocks.)
6. Network bottleneck if… (High network latency coupled with an application that incurs many round trips to the database.)