CompTIA DataSys+ DS0-002: Database Management and Maintenance

Topic snapshot

Sample questions

These original IT Mastery practice questions are aligned to this topic area. Use them for self-assessment, scope review, and deciding what to drill next.

Question 1

Topic: Database Management and Maintenance

A DBA is tuning a monthly cleanup job that deletes expired rows from an AuditEvent table during business hours. Users report timeouts only while this job runs.

Exhibit: Monitoring summary

Which maintenance action is the best first choice?

Options:

• A. Rewrite reports to use partition pruning

• B. Run the cleanup in smaller committed batches

• C. Add another index on expires_at

• D. Increase CPU and storage IOPS

Best answer: B

Explanation: Performance tuning should target the bottleneck shown by the evidence. Here, the delete already uses an index and server resources are not saturated. The user impact comes from a large transaction holding locks while 1,800,000 rows are deleted before a single commit. A practical first action is to process the cleanup in smaller batches, committing between batches, so locks are held for shorter periods and blocking is reduced.

Adding resources helps when CPU, memory, or I/O is the constraint. Index work helps when access paths are inefficient. Partitioning can help with very large time-based maintenance, but the immediate symptom is transaction scope and blocking.

• Extra index is not the best first move because the plan already uses the relevant expires_at index.
• More resources does not address lock waits when CPU and I/O are below peak capacity.
• Report partition pruning targets read-query access patterns, not the cleanup transaction causing blocking.

Question 2

Topic: Database Management and Maintenance

A DBA must plan weekly integrity checks and index/statistics maintenance for a 24x7 OLTP order database. The maintenance should minimize customer latency and avoid taking the database offline.

Exhibit: Average transaction volume

Which action is the BEST professional decision?

Options:

• A. Schedule maintenance during 02:00-03:00 and prioritize online checks and targeted index/statistics tasks

• B. Run full offline index rebuilds during 09:00-10:00 to finish before evening peak

• C. Schedule maintenance during 18:00-19:00 because recent data is most active then

• D. Skip integrity checks and only update statistics during low-volume periods

Best answer: A

Explanation: Transaction volume patterns help determine when maintenance will create the least operational impact. For a 24x7 OLTP workload, integrity checks, index optimization, and statistics maintenance can consume I/O, CPU, and locks depending on the platform and method used. The 02:00-03:00 period has the lowest average transaction rate, so it is the best maintenance window among the choices. Targeting only needed index/statistics work and using online-capable operations where available also aligns with the no-offline constraint. Peak business windows should be protected from avoidable maintenance load, and integrity checks should not be skipped when they are part of the requirement.

• Peak-hour maintenance increases latency risk because 18:00-19:00 has the highest transaction volume.
• Offline rebuilds violate the no-offline constraint and are poorly timed during heavy morning activity.
• Skipping integrity checks ignores a stated maintenance requirement, even if statistics updates are useful.

Question 3

Topic: Database Management and Maintenance

A DBA starts the morning shift and reviews the following maintenance alerts for a production order database. Which action should be performed first?

Options:

• A. Disable the retired service account causing login failures

• B. Fix the transaction log backup failure and relieve log storage pressure

• C. Rerun the nightly index statistics refresh job

• D. Open a capacity-planning ticket for the storage growth trend

Best answer: B

Explanation: Operational maintenance should prioritize issues that threaten availability, recoverability, or data integrity before routine performance tasks. Here, the failed transaction log backups and rapidly filling log volume are linked: if the log volume fills, writes may stop, and failed log backups also weaken point-in-time recovery. That makes it the first item to address. The statistics job can affect performance, and the retired service account should be cleaned up, but neither is described as causing an active outage or recovery gap as severe as the log backup and storage condition. Capacity planning is useful after the immediate risk is contained.

• Statistics refresh is a routine maintenance task and is less urgent than a condition that can stop writes or break recovery objectives.
• Retired account failures should be investigated, but the stem does not show successful access or current user impact.
• Capacity ticket only delays the immediate response needed for a nearly full transaction log volume.

Question 4

Topic: Database Management and Maintenance

A DBA must schedule a database change for an upcoming application release. Which schedule best coordinates the database, application, and operational constraints shown in the exhibit?

Options:

• A. Add column Tue 21:00; backfill Tue 21:15; deploy app Tue 22:30

• B. Add column Tue 21:00; deploy app Tue 22:30; backfill Wed 23:00

• C. Add column Thu 21:00; deploy app Thu 22:30; backfill Fri 23:00

• D. Deploy app Tue 22:30; add column Thu 21:00; backfill Fri 23:00

Best answer: B

Explanation: Release scheduling for database changes should sequence dependencies while respecting maintenance, application, and operations windows. Here, the app v4 deployment depends on the new nullable column being present, so the column should be added before the app release in the approved Tuesday DB window. The app then deploys in its approved Tuesday window. The backfill must wait until after app v4 is deployed and should avoid the daily ETL period and the Thursday reporting freeze, making a later non-conflicting window the safest choice. The key is not just picking an available database window, but coordinating it with application readiness and operational blackout periods.

• App before schema fails because app v4 requires the column before deployment.
• Thursday scheduling conflicts with the reporting freeze and does not match the stated app release window.
• Early backfill fails because the backfill is allowed only after app v4 is deployed.

Question 5

Topic: Database Management and Maintenance

A DBA is reviewing a consistency complaint for an inventory table during concurrent order processing. Which interpretation is supported by the exhibit?

Exhibit: Concurrent transaction trace

Session A
BEGIN TRANSACTION;
UPDATE Inventory SET quantity_on_hand = 9 WHERE sku = 'BK-101';
-- no COMMIT yet

Session B
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT quantity_on_hand FROM Inventory WHERE sku = 'BK-101';
Result: 9

Session A
ROLLBACK;
Committed value after rollback: 10

Options:

• A. Session B encountered a phantom read.

• B. The table requires denormalization.

• C. Session A caused a deadlock.

• D. Session B performed a dirty read.

Best answer: D

Explanation: The exhibit shows a transaction isolation issue under READ UNCOMMITTED. Session A changed quantity_on_hand but did not commit the transaction. Session B then read that uncommitted value. When Session A rolled back, the value Session B saw no longer existed as committed data. That is a dirty read, a consistency problem allowed by low isolation levels that do not prevent reads of uncommitted changes.

Higher isolation levels such as READ COMMITTED are commonly used to prevent this specific issue by allowing reads only of committed data. Phantom reads involve new or removed rows matching a predicate, not a rolled-back value in an existing row.

• Phantom read refers to rows appearing or disappearing between repeated range queries, not reading an uncommitted update.
• Deadlock requires competing locks where sessions block each other; the trace shows no mutual blocking or deadlock victim.
• Denormalization is a design choice for query patterns and does not address reading rolled-back transaction data.

Question 6

Topic: Database Management and Maintenance

A reporting table stores customer_name and customer_region in every order row to make daily sales reports faster. After several customers move to a new region, support tickets show that reports disagree depending on whether they query the customer table or the reporting table. Which design choice best addresses this trade-off?

Options:

• A. Add more duplicate customer columns to the order rows

• B. Keep denormalization, but add a controlled synchronization process

• C. Increase the transaction isolation level for reports

• D. Remove all indexes from the reporting table

Best answer: B

Explanation: Denormalization intentionally stores duplicate or derived data to reduce joins or speed read-heavy workloads. The trade-off is maintenance: when source data changes, every copied value must be updated consistently. In this scenario, the reporting table duplicates customer attributes, so region changes create disagreement between the normalized customer table and the reporting copy. A controlled synchronization process, such as a scheduled refresh, trigger-based update, or managed ETL step, addresses the consistency risk while preserving the reporting benefit. If strict consistency is more important than report speed, normalization may be preferred, but the symptom here points to unmanaged denormalized data.

• More duplication increases the maintenance burden and creates more places for customer data to become stale.
• Removing indexes may slow reports and does not fix inconsistent duplicate values.
• Higher isolation helps with concurrent read/write visibility, not with stale copies stored in another table.

Question 7

Topic: Database Management and Maintenance

A DBA is reviewing why a nightly customer import took three times longer than normal. No application errors were reported. Which interpretation is best supported by the monitoring exhibit?

Options:

• A. CPU starvation during query execution

• B. Storage I/O saturation from write-heavy activity

• C. Disk capacity exhaustion

• D. Memory pressure causing paging

Best answer: B

Explanation: The exhibit points to a storage performance bottleneck, not a compute or capacity issue. CPU utilization and free memory are within the normal baseline, so they do not explain the slowdown. Free disk space is also normal, so the database is not running out of storage capacity. The decisive metrics are disk IOPS near the provisioned maximum, a much higher disk queue length, and a large increase in metered writes. Together, these indicate that write-heavy activity is overwhelming the storage subsystem and causing requests to wait. A reasonable next step would be to investigate the import’s write pattern, indexes, logging, or batch size, and then tune or provision additional IOPS if needed.

• CPU focus fails because CPU usage during the import is still within the normal operating range.
• Memory focus fails because free memory did not drop below the baseline, so paging is not supported.
• Capacity focus fails because free disk space stayed normal even though write throughput increased.
• Metering alone is not just a cost signal here; combined with IOPS and queue length, it supports an I/O bottleneck.

Question 8

Topic: Database Management and Maintenance

A DBA is triaging morning alerts for a production OLTP database. The service has an RPO of 15 minutes and an RTO of 2 hours. The reporting job can be rerun before noon, and failed login attempts are from a disabled retired service account.

Which action is the BEST professional decision?

Options:

• A. Expand storage before investigating other alerts.

• B. Rerun the reporting aggregate immediately.

• C. Re-enable the retired service account.

• D. Fix and validate transaction log backups first.

Best answer: D

Explanation: Operational triage should prioritize the alert with the highest immediate business and recovery risk. Here, failed transaction log backups already exceed the 15-minute RPO, so the DBA should restore backup protection and validate that backups are completing. The storage alert needs planning, but 280 GB free at 6 GB/day is not an immediate outage. The reporting aggregate is recoverable because source rows are unchanged and it can be rerun before noon. The blocked login attempts are not breaking a valid service because the account is retired. The key takeaway is to address recoverability-threatening failures before lower-urgency capacity, reporting, or access-noise issues.

• Storage first fails because the free capacity and growth rate indicate planning work, not an immediate production risk.
• Reporting first fails because the failed aggregate is rerunnable and does not threaten the OLTP database recovery objective.
• Re-enable access fails because the account is retired, so restoring it would create unnecessary security risk.

Question 9

Topic: Database Management and Maintenance

A DBA is adding monitoring for a busy order-processing database. Users report that some short transactions occasionally fail and must be retried, even though CPU and disk metrics are normal. Which monitoring evidence would best confirm that transactions are blocking each other in a deadlock?

Options:

• A. A login audit showing repeated failed connection attempts

• B. A deadlock event showing two sessions holding and requesting each other’s locks

• C. A CPU alert showing sustained utilization above the baseline

• D. A storage alert showing free disk space below the threshold

Best answer: B

Explanation: Deadlock monitoring focuses on lock dependency cycles, not general resource saturation. In a deadlock, two or more transactions each hold a resource lock while waiting for a lock held by another transaction in the same cycle. Database engines typically detect this condition, choose a victim transaction, roll it back, and allow the others to continue. Evidence such as a deadlock event, deadlock graph, victim transaction, lock owner, and lock waiter directly supports the diagnosis. Normal CPU and disk metrics make resource pressure less likely, and the symptom of short transactions failing and needing retries fits a deadlock victim pattern.

• CPU saturation can slow queries, but it does not show transactions holding conflicting locks.
• Low disk space may cause failures or growth problems, but it is not evidence of a lock cycle.
• Failed logins indicate authentication or access issues, not sessions blocking each other inside transactions.

Question 10

Topic: Database Management and Maintenance

A DBA is investigating a report query that has become slow after the Orders table grew from 200,000 to 8,000,000 rows. The query filters by customer and date range, then sorts by date.

Exhibit: Query and plan summary

SELECT order_id, order_date, total_amount
FROM Orders
WHERE customer_id = 10422
  AND order_date >= '2026-01-01'
ORDER BY order_date DESC;

What is the best next action?

Options:

• A. Create a composite index on customer_id and order_date

• B. Add an index only on status

• C. Convert the query to a stored procedure

• D. Increase the database server memory allocation

Best answer: A

Explanation: The exhibit points to indexing inefficiency. The query filters on customer_id and order_date, but the only listed index is on status, which does not help this predicate. A full scan of 8,000,000 rows to return 126 rows is a classic sign that the database cannot efficiently seek to the needed rows. A composite index beginning with customer_id and including order_date supports the equality filter, the range filter, and may also reduce sort work for the ORDER BY clause. The key maintenance action is targeted index optimization based on query behavior, not a general server change.

• More memory may reduce some pressure, but it does not fix the missing access path shown by the full table scan.
• Stored procedure conversion changes packaging, not the underlying scan caused by the lack of a useful index.
• Status indexing is irrelevant because the query does not filter or sort on status.

Continue with full practice

Use the CompTIA DataSys+ DS0-002 Practice Test page for the full IT Mastery practice bank, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try CompTIA DataSys+ DS0-002 on Web View CompTIA DataSys+ DS0-002 Practice Test

Related focused pages

• Free CompTIA DataSys+ DS0-002 Full-Length Practice Exam
• Database Fundamentals
• Database Deployment
• Data and Database Security
• Business Continuity
• Data Integration

Free review resource

Use the full IT Mastery practice page above for the latest review links and practice page.