Lesson 7 — Configuration Management, Instances, and Performance
Developing database instances
This work turns the approved Physical Data Model into controlled operational structures: schemas/tables/files, indexes, constraints, partitions/clusters, capacity, storage placement, and other DBMS-specific details.
Configuration management tracks what should exist, what changed, and whether Production matches the approved design.
Four SCM procedures named by Chapter 6:
- configuration identification
- configuration change control
- configuration status accounting
- configuration audits
Two configuration audits
- Physical configuration audit: was the database/storage installed according to detailed design and approved configuration?
- Functional configuration audit: does the implemented system achieve required functional/performance attributes?
Think installed as designed vs works/performs as required.
Access management, logging/auditing, intrusion detection, vulnerability management, legal/licensing restrictions on loaded data, and repeatable automated loads also sit around controlled instance operation.
Performance = availability + speed
Chapter 6 treats performance as two interdependent facets. An unavailable database has effective performance zero.
Proactive work
Establish baselines, monitor trends, project growth, keep optimizer statistics useful, monitor memory/cache, indexes, workload, query behavior, and service-level trends before users experience severe impact.
Reactive triage
- memory contention → investigate/tune buffers/cache/capacity;
- locking/blocking/deadlock → identify transactions/code patterns; DBMS may terminate a deadlock participant, but recurring cases need root-cause correction;
- stale statistics → refresh relevant statistics so optimizer plan selection reflects data distribution;
- poor SQL / procedural logic → optimize code;
- complex joins → evaluate views/design/query changes without creating opaque layers that worsen optimization;
- insufficient indexes → add useful indexes, balancing read benefit against storage/update cost;
- overloaded server → redistribute workloads/add capacity/archive inactive data as justified;
- runaway query → governor/ranking controls can protect shared service before deeper remediation.
Do not jump to denormalization
For OLTP, structural denormalization should be exceptional after less disruptive remedies—statistics, SQL, indexes, resources, views, workload—have been investigated. Read-heavy analytical environments use denormalized structures more commonly.
Source: pp. 194–201.