Checklist (at least as start point) for database stuff when query suddenly becomes slow ( -> action):
1. Inadequate statistics on tables (not updated, updated on "empty" tables,sps not recompiled)
(needs to "update" bad plan to the good one somehow).
(-> update statistics on adequate data. sp_recompile,drop/create sps).
To analyze query plan (good/bad or "broken") ->
sp_showplan spid, null,null,null (current),
set showplan on, set statistics io on, set statistics time on
set fmtonly on
2. Index/table fragmentation (-> drop/recreate indexes, reorg rebuild)
3. Dropped index (table scan instead of index is running) (-> setup index)
4. Db cache does not have enough memory and server has to read from disk.(->incr. memory, bind db)
5. CPU (Unix server busy by something else)
(Add CPU, get rid of "something else")
6. Locking contention (one process is waiting for other to release lock on some table/page/row)
(check sp_lock at first, then think ...)
7. Network slowness (->tcp_no_delay)
8. Table/index/db corrupt (->dbcc)
9. User connections not enough (->sp_configure)
10. Stacktraces in Syb.errorlog with infected by 11, server might be not stable afterward.
(->restart server)
11.Log suspended.(everything is wating till trlog dump or increase happened) at that time.
(-> incr.trlog space)
12.Wrong SQL query (perhaps with cartesian product) filling up db/tempdb space.
(-> find culprit)