DS0-001 Data Systems Management Practice Test 2 — 15 Questions

Question 1

A data systems administrator at a healthcare analytics company maintains a 5 TB PostgreSQL data warehouse on a single server. The nightly ETL load has grown to 8 hours, and analysts report that concurrent reporting queries are slow. The server has 64 GB RAM, 16 CPU cores, and a RAID 10 array of 15K RPM SAS drives. Monitoring shows disk I/O is the bottleneck, with frequent sequential scans on fact tables. The administrator must improve read performance for analytical queries without changing the schema or the ETL logic. Which action should the administrator take?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create appropriate B-tree indexes on the columns most frequently used in WHERE and JOIN clauses.. The bottleneck is disk I/O from sequential scans on large fact tables. Creating B-tree indexes on frequently filtered or joined columns allows PostgreSQL to use index scans, reducing the volume of data read. This is a native, non-schema-changing optimization that directly targets the reported I/O issue. The other options either are not available on PostgreSQL, provide only partial caching benefit, or do not address queries without date filters.

Answer

Create columnstore indexes on the fact tables to improve analytical query performance.

Answer

Implement table partitioning on the fact tables by date range to enable partition pruning.

Answer

Add more RAM and increase the shared_buffers setting to cache the fact tables in memory.

Question 2

A database administrator manages a MySQL 8.0 InnoDB database with a 500 GB table that experiences heavy insert and update activity. The table has a composite primary key on (customer_id, order_date). A nightly batch process runs UPDATE statements that modify the order_status column for millions of rows based on a range of order_date values. The DBA notices that these updates are slow and cause significant locking. The DBA must reduce locking and improve update performance without changing the application's query patterns. Which action should the DBA take?

Accepted Answer

Partition the table by RANGE on order_date and update each partition in separate transactions.. Partitioning by RANGE on order_date enables partition pruning for the nightly batch updates, limiting each transaction to a subset of data and reducing lock contention. This directly targets the slow, lock-heavy range updates while preserving application query patterns. The other options either require massive schema changes, do not address write locking, or provide only partial I/O relief without solving concurrency.

Answer

Change the primary key to a surrogate auto-increment column and create a secondary index on (customer_id, order_date).

Answer

Set the transaction isolation level to READ UNCOMMITTED for the batch process.

Answer

Increase the innodb_buffer_pool_size to cache the entire table in memory.

Question 3

A data systems manager oversees a MongoDB replica set with three nodes. The primary node handles all writes, and the two secondary nodes replicate data. During a network partition, the primary becomes isolated from the secondaries. The application continues to write to the primary. After the partition heals, the manager observes that some writes made during the partition were rolled back. Which MongoDB mechanism caused this behavior?

Accepted Answer

The primary stepped down after the partition healed because it could not see a majority, and unreplicated writes were rolled back.. When a MongoDB primary is isolated and cannot reach a majority, it steps down upon reconnection, and writes not replicated to a majority are rolled back. This preserves consistency. The other options mischaracterize write concern, read concern, or the election process as the direct cause of the rollbacks.

Answer

The secondaries automatically promoted a new primary during the partition, causing the old primary's writes to be discarded.

Answer

The write concern was set to w:1, so the primary acknowledged writes without waiting for replication.

Answer

The replica set used majority read concern, which allowed reading uncommitted data.

Question 4

A database administrator is configuring a new PostgreSQL 14 instance for a high-traffic web application. The application performs many small read and write transactions. The administrator must optimize the configuration for this workload. Which TWO actions should the administrator take? (Choose two.)

Accepted Answer

Increase the value of work_mem to allow more memory for sorting and hash operations per query.. For a high-traffic OLTP PostgreSQL instance, setting shared_buffers to about 25% of RAM and increasing work_mem moderately are recommended to improve caching and reduce disk-based operations. The other options either risk memory exhaustion, compromise durability, or misconfigure the planner's cache estimate.

Answer

Enable synchronous_commit = off to improve write performance.

Answer

Increase the value of max_connections to a very high number to handle many concurrent users.

Answer

Set effective_cache_size to the total amount of RAM to inform the planner about available cache.

Question 5

A DBA is configuring an Always On Availability Group in SQL Server. To ensure that the secondary node can be used for read-only reporting tasks, which setting must be correctly configured in the Availability Group replica settings?

Accepted Answer

Readable Secondary. The Readable Secondary setting in the Availability Group replica configuration determines whether a secondary replica can accept read-only connections. Setting it to 'Read-intent only' or 'Yes' allows reporting queries to be routed to the secondary, offloading the primary. Without this setting, the secondary is not accessible for read-only workloads even if it is synchronized.

Answer

Automatic Failover

Answer

Synchronous Commit

Answer

Primary Role

Question 6

In PostgreSQL, you need to monitor the performance of your long-running queries. Which view is the most appropriate to inspect the query execution statistics for all active sessions?

Accepted Answer

pg_stat_activity. pg_stat_activity is the PostgreSQL system view that shows one row per server process, including the current query text, state (active, idle, idle in transaction), wait events, and timing information for every active session. It is the canonical view for monitoring long-running queries and session-level activity.

Answer

pg_stat_database

Answer

pg_locks

Answer

pg_stat_user_tables

Question 7

When planning a backup strategy, a company requires that the Recovery Point Objective (RPO) be set to 15 minutes. Which backup schedule meets this requirement?

Accepted Answer

Transaction Log Backups every 10 minutes. RPO defines the maximum acceptable data loss measured in time, so a 15-minute RPO requires backups no less frequent than every 15 minutes. Transaction log backups every 10 minutes capture all committed transactions within that window, guaranteeing at most 10 minutes of potential data loss — well within the 15-minute target.

Answer

Daily Full Backups

Answer

Weekly Full and Daily Differential Backups

Answer

Hourly Transaction Log Backups

Question 8

In an Oracle Database environment, a DBA identifies that specific queries are performing poorly due to fragmented tables. Which command should the DBA execute to reclaim unused space and defragment the table without dropping the table structure?

Accepted Answer

ALTER TABLE ... SHRINK SPACE. ALTER TABLE ... SHRINK SPACE is the Oracle command that reclaims unused space below the high-water mark and defragments the segment without dropping or rebuilding the table. It moves rows downward into free space, compacting the segment so full-table scans read fewer blocks and query performance improves.

Answer

TRUNCATE TABLE

Answer

ANALYZE TABLE ... COMPUTE STATISTICS

Answer

DROP AND RECREATE

Question 9

A MySQL DBA needs to diagnose a deadlock issue. Which tool or command provides the most detailed information regarding the specific InnoDB internal locks held by transactions at the time of a deadlock?

Accepted Answer

SHOW ENGINE INNODB STATUS. SHOW ENGINE INNODB STATUS is the canonical MySQL command for diagnosing InnoDB deadlocks — its LATEST DETECTED DEADLOCK section prints the exact transactions involved, the SQL statements they were executing, the locks each held and waited for, and the transaction that was rolled back. This is the most detailed lock-level forensic output available without enabling additional instrumentation.

Answer

CHECK TABLE

Answer

EXPLAIN

Answer

SHOW PROCESSLIST

Question 10

During a disaster recovery test, the DBA realizes the database restoration takes too long. Which action is the most effective way to improve the restore speed of a large database?

Accepted Answer

Use striped backups. Striped backups split the backup (and therefore the restore) across multiple files or devices, allowing parallel read/write streams that dramatically reduce restore time for large databases. SQL Server's BACKUP ... TO DISK='f1', DISK='f2', ... and RESTORE with multiple MOVE targets, or Oracle RMAN with multiple channels, exploit this. The bottleneck in large restores is usually I/O throughput, and striping multiplies it.

Answer

Disable antivirus

Answer

Run in single-user mode

Answer

Increase the recovery interval

Question 11

Which database backup strategy is recommended to ensure that you can restore a database to a specific point in time?

Accepted Answer

Full and Transaction Log backups. Point-in-time recovery requires a full backup as the base plus a continuous chain of transaction log backups. Restoring the full backup and then replaying transaction logs up to a specific timestamp (STOPAT) reconstructs the database to any moment within the log chain's coverage.

Answer

Differential backups only

Answer

Full backups only

Answer

Copy-only backups

Question 12

A DBA needs to automate the maintenance of database statistics in SQL Server. Which tool is the standard approach to manage this across all user databases?

Accepted Answer

SQL Server Agent Maintenance Plans. SQL Server Agent Maintenance Plans are the built-in, standard mechanism for scheduling recurring administrative tasks like updating statistics, rebuilding indexes, and running backups across all user databases. They integrate directly with SQL Server Agent jobs and provide a wizard-driven or T-SQL-driven way to target multiple databases in one plan. This is the canonical answer for automating statistics maintenance in SQL Server.

Answer

Active Directory Users and Computers

Answer

SQL Server Configuration Manager

Answer

Windows Task Scheduler

Question 13

A database is experiencing high I/O wait times on the log drive. What is the most effective configuration change to alleviate this bottleneck?

Accepted Answer

Move the log to a high-performance dedicated drive. The log drive is the write-ahead log (WAL/redo) target, and it is written sequentially and synchronously on every commit. If that drive is slow or shared with data files, commits stall and I/O wait spikes. Moving the log to a dedicated high-performance device (SSD/NVMe, or a dedicated volume) removes contention and gives the sequential log stream the IOPS and low latency it needs.

Answer

Use data compression

Answer

Increase the buffer pool size

Answer

Reduce the database recovery interval

Question 14

A database administrator needs to perform a schema-only export of a database. Which tool parameter is typically used for this purpose in mysqldump?

Accepted Answer

--no-data. The --no-data flag in mysqldump suppresses row data and exports only the table structures (CREATE TABLE statements), which is exactly a schema-only export. It is commonly used to replicate schema to a new environment without copying potentially sensitive or bulky data.

Answer

--quick

Answer

--add-drop-table

Answer

--all-databases

Question 15

A database server is consistently hitting high CPU usage. You suspect an unoptimized query is causing a full table scan. Which tool should you use to capture the execution plan of currently running queries?

Accepted Answer

Extended Events. Extended Events is a lightweight, highly scalable event-handling system in SQL Server that can capture execution plans of currently running queries. It provides detailed diagnostic information, including query plans, waits, and resource usage, making it the ideal tool for identifying full table scans and other performance issues.

Answer

Performance Monitor

Answer

SQL Server Configuration Manager

Answer

Windows Event Viewer