D330 Data Systems Administration - Set 2 - Part 3

Correct Answer: Forecasting future storage and performance needs to provision resources

Capacity planning projects future data growth and workload so storage, memory, and compute resources are provisioned before performance or space problems occur.

Correct Answer: Performance degradation, errors, and resource exhaustion

Monitoring tracks metrics such as query latency, connection counts, disk space, and error rates, alerting administrators before issues cause outages.

Correct Answer: Scheduled backups, index rebuilds, and statistics updates

A maintenance plan automates routine tasks such as backups, index reorganization or rebuilds, statistics updates, and integrity checks to keep the database healthy.

Correct Answer: The query optimizer relies on them to choose efficient execution plans

The optimizer uses statistics about data distribution to estimate costs and choose execution plans. Stale statistics lead to poor plan choices and slow queries.

Correct Answer: Disorder in index pages that reduces read efficiency over time

As data changes, index pages become logically disordered and partially filled, increasing the I/O needed for scans. Rebuilding or reorganizing indexes restores efficiency.

Correct Answer: The structural definition of tables, columns, types, and relationships

A schema defines the logical structure of the database, including tables, columns, data types, constraints, and relationships between objects.

Correct Answer: Applying versioned structural changes to a database over time

Schema migration applies controlled, versioned changes to database structure as applications evolve, allowing changes to be tracked, repeated across environments, and rolled back.

Correct Answer: Documented procedures to restore data and service after a major failure

A disaster recovery plan documents the procedures, responsibilities, and resources needed to restore database service and data after a significant failure or disaster.

Correct Answer: How long data is kept before archival or secure deletion

A retention policy defines how long different data categories are kept, balancing business need and legal obligations against storage cost and privacy requirements.

Correct Answer: It enables consistent rebuilds, troubleshooting, and knowledge transfer

Documentation of configuration, dependencies, and procedures allows administrators to rebuild systems consistently, troubleshoot effectively, and transfer knowledge between staff.