DS0-001 Practice Test 21 — 15 Questions

Question 1

A database administrator is deploying a new online transaction processing (OLTP) database on a four-socket server with 512 GB of RAM and a storage area network (SAN) that uses solid-state drives. The application performs many small, random reads and writes. The administrator needs to configure the database instance to minimize physical I/O and improve response time. Which configuration should the administrator implement?

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

Accepted Answer

Increase the database buffer pool size to cache frequently accessed data pages in memory.. The buffer pool caches data pages in memory, so frequently accessed rows are served without touching the SAN. With 512 GB of RAM and a random-access OLTP workload, a large buffer pool directly reduces physical I/O and response time. Other options address write throughput, sorting, or storage size, none of which target the primary random-read bottleneck.

Answer

Enable table compression on all tables to reduce the storage footprint on the SAN.

Answer

Configure a larger sort area to allow all ORDER BY operations to complete in memory.

Answer

Set the database to use asynchronous I/O and increase the number of writer processes.

Question 2

A database administrator is designing a backup strategy for a mission-critical database that must support point-in-time recovery. The database is in the FULL recovery model. They plan to take a full backup every Sunday, differential backups nightly, and transaction log backups every 15 minutes. A failure occurs on Wednesday at 10:07 AM. What is the minimum set of backups required to restore the database to the point of failure?

Accepted Answer

The most recent full backup, the most recent differential backup, and all transaction log backups since that differential backup.. To recover to a point in time with a full, differential, and log backup strategy, you restore the latest full backup, then the latest differential backup, then all transaction log backups taken after that differential. This minimizes restore time while preserving the log chain needed to reach the exact point of failure.

Answer

The most recent full backup and the most recent differential backup only.

Answer

The most recent full backup and all transaction log backups since that full backup.

Answer

The most recent full backup, all differential backups since that full backup, and all transaction log backups since the full backup.

Question 3

A database administrator is reviewing a query that joins a large Sales table with a small Regions table. The query filters on a column in the Regions table and returns only matching rows from both tables. The administrator notices that the query plan shows a nested loop join. Which factor most likely influenced the optimizer's choice of this join method?

Accepted Answer

The Regions table is small and has an index on the join column, making it efficient to probe for each row of the Sales table.. The optimizer likely chose a nested loop join because the Regions table is small and, with an index on the join column, can be efficiently probed for each row of the larger Sales table. This access pattern minimizes I/O and is well-suited for joining a large table with a small, indexed table. Other join methods would be less efficient in this context.

Answer

The query lacks a WHERE clause, causing the optimizer to default to a nested loop join for all rows.

Answer

The join condition uses an inequality, which is only supported by nested loop joins.

Answer

The Sales table is large and has no indexes, forcing a full table scan for each iteration of the outer loop.

Question 4

When decommissioning a legacy server, what is the most secure way to handle the hard drives containing sensitive data?

Accepted Answer

Physical Destruction or Cryptographic Erasure. Physical destruction (shredding, degaussing, incineration) renders the platters unreadable, while cryptographic erasure destroys the encryption key so the ciphertext on the drive becomes permanently unrecoverable. Both are recognized by standards such as NIST SP 800-88 as acceptable sanitization methods for media leaving organizational control. They guarantee sensitive data cannot be reconstructed even with advanced forensic recovery tools.

Answer

Quick Format

Answer

Moving drives to a dev environment

Answer

Deleting the Database Files

Question 5

When creating an ETL pipeline, which stage involves validating that the data meets business rules (e.g., age must be positive)?

Accepted Answer

Transformation. The Transformation stage is where data is cleansed, standardized, and validated against business rules before loading into the target system. Checking that age is positive, that dates fall within valid ranges, or that codes match a reference list are all transformation-layer validations. Extraction only pulls raw data, and Loading only writes it to the destination.

Answer

Extraction

Answer

Loading

Answer

Reporting

Question 6

A database is configured with an 'Asynchronous Commit' replication mode. What is the potential risk during a primary server failure?

Accepted Answer

Potential data loss. In asynchronous commit replication, the primary acknowledges a transaction to the client before the standby has confirmed receipt or applied it. If the primary fails before the WAL/binlog is shipped and replayed on the standby, those acknowledged transactions are lost when failover occurs — this is the classic RPO > 0 trade-off. The risk is data loss, not latency or locking.

Answer

Increased latency on the primary server

Answer

Deadlocks on the primary

Answer

Inability to perform reads on the secondary

Question 7

A DBA is troubleshooting a slow query. The execution plan indicates an 'Implicit Conversion' is occurring, which prevents the use of an index seek. What is the most likely cause?

Accepted Answer

Data type mismatch between column and parameter. Implicit conversion occurs when SQL Server must convert a value from one data type to another to compare it, such as when a varchar parameter is compared to an int column. Because the conversion is applied to the column (the higher-precedence type), the optimizer cannot seek the index and must scan, which is why the execution plan shows the warning. Matching parameter data types to column data types eliminates the conversion and restores index seeks.

Answer

The database collation is incorrect

Answer

The table is not partitioned

Answer

The statistics are outdated

Question 8

A database administrator is reviewing a schema for a reporting database. The team needs to ensure that queries against a large 'Sales' table return consistent results even if a long-running report is executing while new sales are being inserted. Which TWO of the following techniques help provide a consistent snapshot of data for the report? (Choose two.)

Accepted Answer

Query a read-only replica that is synchronized through log shipping. Snapshot or repeatable-read isolation and querying a synchronized read-only replica both give the report a stable view of the data while writers continue. Dirty-read techniques such as READ UNCOMMITTED or NOLOCK sacrifice consistency, and log sizing affects recovery rather than read consistency.

Answer

Add a NOLOCK hint to every table in the report query

Answer

Set the transaction isolation level to READ UNCOMMITTED for the report

Answer

Increase the database transaction log size to reduce checkpoint frequency

Question 9

A municipal government agency must publish an open dataset of building permits. The governance policy requires that individual applicants not be re-identifiable from the published file, while aggregate statistics remain accurate. Which technique should the data steward apply before publication?

Accepted Answer

Apply k-anonymity generalization and suppression to quasi-identifiers. Re-identification risk in public releases stems from combinations of quasi-identifiers, not only direct identifiers. Generalizing and suppressing quasi-identifiers to satisfy k-anonymity makes each published record indistinguishable from others in its group, preserving aggregate accuracy while protecting individuals. Encryption, hashing, and simple column removal leave linkable patterns or residual attributes that still permit re-identification.

Answer

Remove the applicant name column only

Answer

Encrypt the applicant name column with AES-256

Answer

Hash applicant names with SHA-256 before publication

Question 10

Which THREE actions should be performed during a data incident response procedure involving a potential PII leak?

Accepted Answer

Analyze access logs for suspicious activity. Option A is correct because analyzing access logs for suspicious activity is a core containment and investigation step that helps determine the scope, source, and timeline of a potential PII leak, which is essential for both remediation and breach-notification obligations. Option C is correct because PII leaks typically trigger mandatory breach-notification requirements under regulations such as GDPR, HIPAA, or CCPA, so notifying the relevant regulatory bodies within required timeframes is a required part of the incident response procedure. Option D is correct because isolating the affected data nodes limits the blast radius, prevents further exfiltration or lateral movement, and preserves the environment for forensic analysis. Option B is not appropriate because altering the database schema is a change-management task unrelated to containing or investigating a PII leak and could destroy evidence. Option E is not appropriate because deleting all historical backups destroys forensic evidence and may violate data-retention and legal-hold requirements, and backups are needed for recovery rather than deletion during an incident.

Answer

Update the database schema

Answer

Delete all historical backups

Question 11

A data warehouse contains a fact table with billions of rows. Analysts frequently run aggregation queries that group by product_category and calculate total sales. These queries are slow because they scan the entire fact table. You need to improve performance without changing the queries. What should you implement?

Accepted Answer

A clustered columnstore index on the fact table. A clustered columnstore index is ideal for large fact tables in data warehousing because it stores data column-wise, enabling high compression and efficient batch-mode processing. Aggregation queries that group by product_category and sum sales benefit from reading only the necessary columns and leveraging segment elimination, resulting in faster performance without query changes.

Answer

A nonclustered index on the product_category column

Answer

An indexed view that pre-aggregates total sales by product_category

Answer

Table partitioning on the product_category column

Question 12

A data engineer is profiling a customer table and finds that the 'email' column contains 12% NULL values, while the 'customer_id' column is fully populated and unique. The business rule states that every customer record must have a valid email address. Which action should the engineer take to enforce this rule in the pipeline?

Accepted Answer

Implement a validation rule that quarantines records with NULL or malformed email addresses for remediation.. The business rule requires every customer record to have a valid email address. Quarantining records with NULL or malformed emails prevents invalid data from entering the trusted dataset while keeping those records for remediation. This enforces the rule without discarding data or fabricating values, and it handles both missing and invalid formats.

Answer

Drop the email column from the customer table and rely on customer_id for identification.

Answer

Replace NULL email values with a default placeholder such as 'unknown@example.com' and load the rows.

Answer

Add a NOT NULL constraint to the email column and reject rows that fail the constraint.

Question 13

Which THREE of the following are valid methods for securing cloud-based data storage?

Accepted Answer

Configuring Virtual Private Cloud (VPC) endpoints. Option A is correct because configuring VPC endpoints (e.g., AWS PrivateLink gateway or interface endpoints for S3) keeps traffic to cloud storage on the private AWS network instead of traversing the public internet, reducing exposure and enabling endpoint policies. Option B is correct because enabling server-side encryption (SSE-S3, SSE-KMS, or SSE-C) ensures data at rest in the storage service is encrypted, protecting confidentiality if the underlying media is accessed. Option D is correct because enforcing IAM roles for storage access applies least-privilege, identity-based policies and temporary credentials, eliminating long-lived static keys and tightly scoping who can read or write objects. Option C is not valid because sharing storage URLs publicly exposes objects to anonymous access and is the opposite of securing storage. Option E is not valid because using a password for the root cloud account is a weak, non-scalable practice; root accounts should use MFA and be locked away, with IAM roles/users handling storage access instead.

Answer

Sharing storage URLs publicly

Answer

Using a password for the root cloud account

Question 14

A database administrator is planning a new SQL Server 2022 deployment that must support a read-only reporting workload without impacting the primary OLTP workload. The reporting queries are long-running and scan large tables. Which two features should the administrator implement to meet this requirement? (Choose two.)

Accepted Answer

Configure an Always On availability group with a readable secondary replica.. A readable secondary replica in an Always On availability group moves reporting queries off the primary instance, and a columnstore index makes the large scans those queries perform far more efficient through columnar storage and compression. Together they isolate the reporting workload while accelerating it, which is exactly what the scenario demands.

Answer

Enable snapshot isolation on the primary database to reduce blocking.

Answer

Configure transactional replication to push data to a separate reporting server.

Answer

Enable Resource Governor to cap CPU usage for reporting sessions.

Question 15

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