Lesson 8: Monitoring, Performance Tuning, and Capacity Planning

A database should not be tuned blindly. Good tuning starts with observation.

A DBA commonly monitors:

• response time of important queries
• CPU and memory usage
• disk I/O and storage growth
• active sessions and locks
• long-running transactions
• deadlocks or blocking
• backup success/failure
• replication or standby health
• unusual access patterns

The main goal is not to collect every possible metric. The goal is to identify what affects service quality most.

For example, if an enrollment system slows down during peak registration, the DBA must determine whether the cause is:

• poor query design
• missing indexes
• locking contention
• insufficient hardware
• oversized reports running during peak hours

Monitoring gives evidence for decisions.

A good tuning workflow is usually:

1. Identify the symptom — Example: "Student ledger query takes 18 seconds."
2. Measure the current behavior — Which query? How often? At what time? On which tables?
3. Find probable causes — Missing indexes? Too many joins? Old statistics? Blocking? Large scans?
4. Apply one change at a time — Add or revise index, rewrite query, archive data, adjust schedule.
5. Measure again — Did the change actually help? Did it create side effects?

This method is better than random tweaking.

Typical tuning actions include:

• indexing the right columns
• reducing unnecessary SELECT *
• filtering earlier
• simplifying joins
• updating statistics where applicable
• archiving old records
• moving heavy reports outside peak hours

A DBA also needs discipline: performance is not permanent. A query that was fast last semester may become slow after table growth or workload change.