View Full CompTIA DataSys+ DS0-001 Exam Dumps and Practice Test Dumps

 

Question 61.

A database administrator wants to verify whether a slow query is reading far more rows than necessary. Which tool or output is MOST useful?

1. Query execution plan and runtime statistics
2. Backup retention policy
3. Password history
4. User role list

Correct Answer: 1. Query execution plan and runtime statistics

Explanation:

Execution plans and runtime statistics show how a query accesses data, which indexes are used, how many rows are estimated or processed, and where expensive operations occur. This information can reveal unnecessary scans, poor join strategies, or inaccurate estimates. Backup policies and security settings are important operational controls, but they do not directly help diagnose inefficient query access paths.

Question 62.

Which database operation permanently saves the changes made by a successful transaction?

1. ROLLBACK
2. COMMIT
3. SELECT
4. GRANT

Correct Answer: 2. COMMIT

Explanation:

COMMIT makes the changes performed by a transaction permanent and visible according to the database’s transaction isolation behavior. Once committed, the database’s durability mechanisms are expected to preserve the changes. ROLLBACK discards uncommitted changes, SELECT retrieves data, and GRANT assigns permissions. Transactions should be designed carefully so commits occur only after required operations have completed successfully.

Question 63.

A transaction makes several changes, but an error occurs before completion. Which command is used to undo the uncommitted changes?

1. DROP
2. TRUNCATE
3. ROLLBACK
4. ALTER

Correct Answer: 3. ROLLBACK

Explanation:

ROLLBACK reverses changes made during the current transaction that have not yet been committed. It helps preserve consistency when an operation fails or business validation determines that the transaction should not continue. Once changes are committed, ordinary rollback of that transaction is no longer available, so recovery may require other mechanisms such as backups or compensating transactions.

Question 64.

Which transaction isolation issue occurs when one transaction reads data written by another transaction that has not yet committed?

1. Lost backup
2. Replication lag
3. Index fragmentation
4. Dirty read**

Correct Answer: 4. Dirty read

Explanation:

A dirty read occurs when a transaction reads changes made by another transaction before those changes are committed. If the second transaction later rolls back, the first transaction has used data that was never permanently valid. Higher isolation levels can prevent dirty reads, although stronger isolation may increase locking, blocking, or other concurrency overhead depending on the database system.

Question 65.

Which database characteristic ensures that a transaction moves the database from one valid state to another valid state while respecting defined rules?

1. Consistency
2. Atomicity
3. Durability
4. Compression

Correct Answer: 1. Consistency

Explanation:

Consistency is the ACID property concerned with preserving valid database state according to constraints, rules, and integrity requirements. Transactions should not leave the database violating defined relationships or business rules. Atomicity ensures all-or-nothing execution, isolation manages concurrent transaction interaction, and durability ensures committed data survives failures.

Question 66.

Which ACID property helps prevent concurrently executing transactions from interfering with one another in ways that produce incorrect results?

1. Durability
2. Isolation
3. Compression
4. Replication

Correct Answer: 2. Isolation

Explanation:

Isolation controls how concurrent transactions can see and affect one another’s intermediate work. Appropriate isolation helps prevent anomalies such as dirty reads and other inconsistent behaviors. Database systems offer different isolation levels that balance concurrency and consistency. Stronger isolation may reduce anomalies but can increase blocking or resource usage depending on implementation.

Question 67.

A database administrator needs to find which queries are consuming the most CPU during peak periods. Which monitoring data is MOST relevant?

1. Table names only
2. User account creation dates
3. Query performance and resource utilization statistics
4. Backup file names

Correct Answer: 3. Query performance and resource utilization statistics

Explanation:

Query performance statistics can identify statements with high CPU usage, long duration, excessive reads, or frequent execution. Correlating these metrics with server CPU, memory, and I/O utilization helps determine which workloads are responsible for pressure. This evidence allows administrators to focus optimization efforts on the queries with the greatest operational impact.

Question 68.

A query becomes slow because table statistics no longer accurately represent current data distribution. What action is MOST appropriate?

1. Drop all primary keys
2. Disable backups
3. Remove transaction logging
4. Update or refresh the optimizer statistics**

Correct Answer: 4. Update or refresh the optimizer statistics

Explanation:

Query optimizers use statistics about data distribution and cardinality to choose execution plans. When statistics become stale after significant data changes, the optimizer may choose inefficient access methods or join strategies. Refreshing statistics can help it make better estimates. Indexing and query structure should still be reviewed if performance problems remain.

Question 69.

Which maintenance task can improve performance when database indexes have become heavily fragmented, depending on the platform and workload?

1. Reorganize or rebuild affected indexes
2. Remove all constraints
3. Disable user authentication
4. Delete database audit logs

Correct Answer: 1. Reorganize or rebuild affected indexes

Explanation:

Index fragmentation can increase page reads, waste space, and reduce efficiency for some workloads. Reorganizing or rebuilding affected indexes may restore more efficient structures. Administrators should base maintenance on actual fragmentation, workload, and platform guidance rather than rebuilding every index routinely. Maintenance operations can consume resources and may affect availability.

Question 70.

A database is expected to grow by 30% over the next year. Which administrative activity BEST addresses this requirement proactively?

1. Normalization review only
2. Capacity planning
3. User auditing only
4. Password rotation only

Correct Answer: 2. Capacity planning

Explanation:

Capacity planning evaluates current usage trends and forecasts future needs for storage, memory, CPU, network, backup capacity, and related infrastructure. Anticipating growth helps avoid service disruption caused by exhausted resources. Administrators should monitor trends over time and account for expected business growth, retention requirements, replication, indexes, and backup overhead.

Question 71.

A database disk is nearly full because transaction log files have grown unexpectedly. What should the administrator do FIRST?

1. Delete the active transaction log file
2. Disable all logging permanently
3. Investigate log growth, backup behavior, long transactions, and recovery configuration
4. Drop large tables immediately

Correct Answer: 3. Investigate log growth, backup behavior, long transactions, and recovery configuration

Explanation:

Unexpected log growth can result from long-running transactions, missing log backups, replication issues, recovery settings, or unusual write activity. Deleting active log files can corrupt the database and should not be used as a shortcut. The administrator should identify the cause, protect available storage, and correct the underlying problem using database-supported procedures.

Question 72.

Which storage characteristic MOST directly affects database workloads that perform many random reads and writes?

1. Monitor screen size
2. Number of database users only
3. Password complexity
4. Storage latency and IOPS**

Correct Answer: 4. Storage latency and IOPS

Explanation:

Database performance can depend heavily on storage input/output operations per second and latency, particularly for workloads with frequent random reads and writes. Slow storage can increase query response times, checkpoint duration, and transaction latency. Administrators should monitor storage performance alongside CPU, memory, and query-level metrics to identify actual bottlenecks.

Question 73.

Which configuration change is MOST likely to reduce physical disk reads for frequently accessed database pages when sufficient memory is available?

1. Increase effective database cache or buffer memory appropriately
2. Disable all indexes
3. Reduce available memory
4. Encrypt every query result twice

Correct Answer: 1. Increase effective database cache or buffer memory appropriately

Explanation:

Database buffer caches keep frequently accessed pages in memory, reducing the need for repeated disk reads. Providing an appropriately sized cache can improve performance when the server has sufficient memory. Allocating too much memory can harm the operating system or other services, so tuning should be based on workload measurements and platform recommendations.

Question 74.

A database server is using nearly all available memory, but most of it is intentionally assigned to the database cache and performance remains stable. What should the administrator do?

1. Assume there is definitely a memory leak
2. Evaluate memory pressure indicators rather than relying only on percentage used
3. Restart the server immediately
4. Disable database caching

Correct Answer: 2. Evaluate memory pressure indicators rather than relying only on percentage used

Explanation:

Database systems often use available memory aggressively for caching because unused memory provides little benefit. High utilization alone does not prove a problem. Administrators should review paging, memory pressure, cache behavior, operating-system availability, and workload performance. Decisions should be based on multiple metrics rather than a single utilization percentage.

Question 75.

A company wants to prevent developers from viewing real customer Social Security numbers in a test database. Which control is BEST?

1. Replication
2. Query caching
3. Data masking or suitable anonymization
4. Index compression

Correct Answer: 3. Data masking or suitable anonymization

Explanation:

Masking or anonymization can replace sensitive production values with non-sensitive substitutes while preserving enough format or characteristics for testing. This reduces the risk of exposing personal information in lower environments. The method should be appropriate to the sensitivity of the data and should prevent unauthorized reconstruction of original values when required.

Question 76.

A database administrator wants to confirm that only approved users can access a highly sensitive table. Which action is MOST appropriate?

1. Remove the table’s primary key
2. Increase table size
3. Disable auditing
4. Review object permissions and role memberships**

Correct Answer: 4. Review object permissions and role memberships

Explanation:

Reviewing permissions and role membership confirms which identities can access sensitive objects and what actions they can perform. Access should follow least privilege and business need. Periodic access reviews are important because users may change roles or retain permissions that are no longer necessary. Auditing can complement this by showing actual access activity.

Question 77.

Which security principle is MOST directly supported by separating database administration, security administration, and application development responsibilities?

1. Separation of duties
2. Data compression
3. Query optimization
4. Replication

Correct Answer: 1. Separation of duties

Explanation:

Separation of duties distributes sensitive responsibilities so one individual does not control every stage of a critical process. For example, the person developing a change may not be the same person approving or deploying it. This reduces fraud, accidental misuse, and unauthorized changes. It complements least privilege and auditing in database security programs.

Question 78.

An employee leaves the organization. What should happen to the employee’s database access?

1. Keep the account active for convenience
2. Disable or remove access promptly according to the deprovisioning process
3. Share the account with a replacement employee
4. Convert the account to an administrator account

Correct Answer: 2. Disable or remove access promptly according to the deprovisioning process

Explanation:

Accounts belonging to departed users should be disabled or removed promptly to prevent unauthorized access. A formal deprovisioning process should cover direct accounts, role memberships, credentials, API keys, and related permissions. Shared reuse of former employee accounts weakens accountability. Required historical audit records can be retained without keeping the login active.

Question 79.

A database has hundreds of unused user accounts that have not logged in for years. What is the BEST action?

1. Grant them stronger permissions
2. Ignore them because they are inactive
3. Review and disable or remove unnecessary accounts according to policy
4. Convert them all to shared accounts

Correct Answer: 3. Review and disable or remove unnecessary accounts according to policy

Explanation:

Unused accounts increase attack surface and may retain permissions that are no longer justified. Administrators should periodically review inactive accounts, confirm business ownership, and disable or remove those that are unnecessary. This supports least privilege and good identity lifecycle management. Appropriate approval and retention policies should be followed before permanent deletion.

Question 80.

Which statement BEST describes effective database performance tuning?

1. Apply every available tuning recommendation automatically
2. Add indexes to every column
3. Restart the database whenever performance declines
4. Measure the workload, identify bottlenecks, make targeted changes, and validate the results**

Correct Answer: 4. Measure the workload, identify bottlenecks, make targeted changes, and validate the results

Explanation:

Effective tuning is evidence-based. Administrators should establish a baseline, identify actual bottlenecks using query and resource metrics, apply targeted changes, and compare results afterward. Changes may involve queries, indexing, configuration, storage, memory, or application behavior. Blindly applying generic recommendations can create new problems or shift bottlenecks rather than improving overall performance.