OK, well where to start with the replies... Maybe this check list...
Lavanyasri (8/7/2012)
hi ,1. Check for missing indexes
2. Do you running rebuild index\reorg ,update statistics on weekend?
3.Try to find out which queries taking more cpu time from which application ?
4. Check for query optimization
5. Check the mdf files and ldf files in the same location
6. Run perfmon and check memory bottleneck and cpu bottleneck
7. Run a trace and find which queries are running during the site slowness
8 .Ask application team to check all app servers in load balancer or not ?
9.check for I\O operations [disk read and write per sec]
1. Done, and done. 2 cycles of adding indexes
2. Yes, weekly.
3. There are several apps that are doing ugly things. Some I can kill to relieve the problem momentarily and others that are less easy to whack. When possible I take down some of them.
4. The worst of the queries are not directly fixable with just code changes. Partly due to the way the app is structured and partly due to lack of process--e.g. de-normalized, pre-aggregated data rather than walking the full business logic for every query. What they are currently doing is a rewrite of the app, but that will take many months.
5. Week 1 was: a) back up system db's (never done prior to that) b) move logs to their own drive c) move temp db to its own drive and split it to multiple files, and d) set up weekly maintenance on indexes.
6, 7 & 9. A private consulting company had just done a review of db performance. From what they left from their traces & analysis the main culprit seems to point to the application blocking itself.
8. Interesting question. I have not asked that.