DS0-001 Data Systems Management — 40 Questions

Question 1

A database administrator wants to move a heavily accessed table to a different physical storage tier to improve performance. In SQL Server, what is the best way to move the table data while retaining the clustered index?

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

Accepted Answer

DROP and RECREATE index on a new filegroup. In SQL Server, a clustered index IS the table's physical storage — the data rows live in the clustered index's leaf level. To move the table to a different filegroup, you must rebuild the clustered index with a new filegroup target: CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON) ON [NewFileGroup]. This physically relocates the data while preserving the index definition and any nonclustered indexes that reference it.

Answer

Use the Move-Item cmdlet

Answer

Shrink the database

Answer

Disable the index

Question 2

A database administrator needs to ensure that a SQL Server transaction log does not grow uncontrollably during a high-volume ETL process. Which recovery model should be configured to allow for periodic log truncation while maintaining a balance between performance and recovery needs?

Accepted Answer

Bulk-Logged. The Bulk-Logged recovery model is designed for high-volume bulk operations like ETL. It minimally logs bulk operations (e.g., BULK INSERT, SELECT INTO, CREATE INDEX) while still logging other transactions fully, allowing the transaction log to be truncated at log backups and preventing uncontrolled growth. It balances performance and recovery by reducing log space usage during bulk loads while still supporting point-in-time recovery (except during the bulk operation).

Answer

Differential

Answer

Full

Answer

Simple

Question 3

A DBA needs to implement transparent data encryption (TDE) for an entire database. Which component is required to protect the Database Encryption Key (DEK) in a SQL Server environment?

Accepted Answer

Certificate. In SQL Server TDE, the Database Encryption Key (DEK) is protected by a certificate stored in the master database, or alternatively by an asymmetric key protected by an EKM provider. The certificate is the standard mechanism that wraps the DEK so it can be securely stored and later opened to decrypt the database. Without this certificate (or its backup), the database cannot be recovered.

Answer

Active Directory group

Answer

Service Master Key

Answer

SSL/TLS certificate

Question 4

You are managing a MariaDB cluster. You notice that nodes are falling out of sync. Which status variable should you check to verify the replication lag between the primary and the replica?

Accepted Answer

Seconds_Behind_Master. Seconds_Behind_Master is the MariaDB/MySQL status variable that reports the replication lag — the number of seconds the replica's SQL thread is behind the primary's binary log. It is the standard metric for detecting replication delay and is read from SHOW SLAVE STATUS or performance_schema.replication_applier_status. A growing value indicates the replica is falling behind.

Answer

Innodb_buffer_pool_reads

Answer

Slave_IO_Running

Answer

Threads_connected

Question 5

A DBA is tuning a query that performs a large join. The execution plan shows a 'Hash Match' operation. What can the DBA do to potentially change the join algorithm if the Hash Match is causing high memory pressure?

Accepted Answer

Create an index on join columns. A Hash Match join builds a hash table in memory, and when the build side is large, it can spill to tempdb and cause memory pressure. Creating an index on the join columns can enable the optimizer to choose a Nested Loops or Merge Join instead, because indexed access provides sorted or seekable input that makes those algorithms cheaper. With appropriate indexes, the optimizer often abandons the hash join entirely.

Answer

Set the isolation level to Read Uncommitted

Answer

Force the join order

Answer

Update statistics

Question 6

You are auditing database logins to ensure compliance. You need to identify users who have not logged in for over 90 days. Which system view or dynamic management function is most appropriate?

Accepted Answer

SQL Server Audit logs. sys.dm_server_principals (or sys.syslogins in older versions) combined with session monitoring is typical, but in SQL Server, there is no direct 'last login' property; however, auditing tools are the standard way to track this.

Answer

sys.dm_os_memory_nodes

Answer

sys.dm_exec_requests

Answer

sys.database_principals

Question 7

Which TWO of the following actions can a DBA take to reduce the size of a database data file?

Accepted Answer

Reorganize or rebuild fragmented indexes. Option A (Reorganize or rebuild fragmented indexes) is correct because index rebuilds (ALTER INDEX ... REBUILD) recreate the index's B-tree compactly, reclaiming the internal and external fragmentation space that inflates the data file, and reorganize (ALTER INDEX ... REORGANIZE) compacts leaf-level pages, both of which can reduce the space consumed within the data file. Option B (Execute DBCC SHRINKFILE) is correct because DBCC SHRINKFILE directly reduces the size of a specified data or log file by moving used pages from the end of the file toward the front and releasing the freed space back to the operating system, which is the canonical command for shrinking a database data file. Option C (Create a new database user) is not correct because creating a user only adds a row to system catalog metadata and does not reclaim or reduce data file space. Option D (Perform a full database backup) is not correct because a full backup copies data out to a .bak file and does not shrink the source data file. Option E (Clear the SQL Server error log) is not correct because the error log is an OS-level text file managed by sp_cycle_errorlog, unrelated to database data file size.

Answer

Create a new database user

Answer

Perform a full database backup

Answer

Clear the SQL Server error log

Question 8

Which THREE of the following are valid SQL Server authentication modes?

Accepted Answer

SQL Server Authentication. SQL Server supports two native authentication mechanisms, and the exam treats the combination as a third valid mode. Option B (SQL Server Authentication) is correct because it uses logins and passwords stored inside SQL Server itself, validated by the instance rather than by the operating system. Option C (Windows Authentication) is correct because it authenticates users via Windows/Active Directory security principals (Kerberos or NTLM) and trusts the OS token. Option E (Mixed Mode Authentication) is correct because it is the instance-level configuration that permits both Windows and SQL Server logins simultaneously, selected during setup or via the server property. Options A (LDAP Authentication) and D (OAuth Authentication) are not SQL Server authentication modes: LDAP is a directory-access protocol used by other products, and OAuth is a token-based authorization framework used by Microsoft Entra ID/Entra ID scenarios, not a native SQL Server login mode.

Answer

LDAP Authentication

Answer

OAuth Authentication

Question 9

A database administrator needs to ensure that a new table storing customer credit card numbers is encrypted at rest and that the encryption keys are managed by the database engine. Which SQL Server feature should they implement?

Accepted Answer

Column-level encryption with a symmetric key stored in the database. Column-level encryption with a symmetric key stored and managed in the database engine provides granular encryption of sensitive columns at rest. It allows the database engine to manage the keys, unlike Always Encrypted, and offers more control than TDE, which encrypts the entire database without column-level granularity.

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Answer

Always Encrypted

Question 10

A database administrator needs to ensure that a critical database can be recovered to any point in time within the last 30 days. The database is currently using the simple recovery model. What should the administrator do?

Accepted Answer

Change the recovery model to full and perform a full backup. Changing to the full recovery model and performing a full backup allows transaction log backups to be taken, which are necessary for point-in-time recovery. This enables restoration to any point within the retention period, meeting the 30-day requirement. The simple recovery model does not support log backups, so it cannot provide point-in-time recovery.

Answer

Change the recovery model to bulk-logged and perform a full backup

Answer

Enable transparent data encryption and perform a full backup

Answer

Keep the simple recovery model and perform full backups every hour

Question 11

You are designing a database schema for a new application that requires high write throughput and strong consistency. You need to choose a data distribution strategy that minimizes contention and supports efficient range scans. Which two strategies should you consider? (Choose two.)

Accepted Answer

Hash partitioning on a high-cardinality key. Hash partitioning on a high-cardinality key distributes writes evenly, reducing contention and supporting high write throughput. Composite partitioning with range on time and hash on a unique identifier combines the benefits of even write distribution with efficient range scans on time. Together, these strategies address both high write throughput and range scan requirements.

Answer

Range partitioning on a monotonically increasing key

Answer

List partitioning on a low-cardinality status column

Answer

Round-robin partitioning without a partitioning key

Question 12

A database administrator manages a SQL Server database that uses page compression on several large tables. Users report that a critical report query has become slower after compression was enabled, even though the execution plan is identical. The administrator suspects that compression is causing additional CPU overhead during decompression. Which action should the administrator take to confirm the cause?

Accepted Answer

Use Extended Events to capture the page_compression and page_decompression events for the query.. Extended Events include page_compression and page_decompression events that fire when SQL Server compresses or decompresses pages. Capturing these events for the slow query lets the administrator quantify decompression activity and tie it to the performance regression. Wait statistics, fragmentation checks, and physical stats DMVs provide indirect or unrelated information and cannot confirm decompression as the specific cause.

Answer

Run DBCC SHOWCONTIG on the compressed tables to check for fragmentation.

Answer

Query sys.dm_db_index_physical_stats to compare average page density before and after compression.

Answer

Compare the wait statistics before and after compression, focusing on CXPACKET and SOS_SCHEDULER_YIELD.

Question 13

A DBA is configuring a new SQL Server instance. The security policy requires that all connections be encrypted and that the server present a valid certificate to clients. Which action should the DBA take to enforce encryption for all connections?

Accepted Answer

Configure a server certificate in SQL Server Configuration Manager and set Force Encryption to Yes.. Enforcing encryption for all connections requires server-side configuration. The DBA must provision a certificate and set Force Encryption to Yes in SQL Server Configuration Manager. This ensures that the server requires encrypted connections from all clients. Client-side settings or non-existent options do not meet the requirement.

Answer

Enable the 'Encrypt Connection' option in each client application's connection string.

Answer

Create a login with the ENCRYPTION option and grant it to all users.

Answer

Use the SQL Server Configuration Manager to enable the 'Force Protocol Encryption' setting under the protocol properties for TCP/IP.

Question 14

A database administrator is implementing a backup strategy for a SQL Server database that must support point-in-time recovery. The database is in FULL recovery model. The administrator wants to minimize the number of backup files and administrative overhead while ensuring that any point in time can be restored. Which backup sequence should the administrator use?

Accepted Answer

Full backup weekly, differential backup daily, transaction log backup every 15 minutes.. A weekly full backup establishes the base, daily differentials capture changes since the last full to speed restore, and 15-minute transaction log backups enable point-in-time recovery. This combination minimizes the number of full backup files and administrative effort while meeting the requirement to restore to any point in time. Omitting differentials or the full backup increases restore complexity or breaks the chain.

Answer

Full backup weekly, transaction log backup every 15 minutes.

Answer

Differential backup daily, transaction log backup every 15 minutes.

Answer

Full backup daily, transaction log backup every hour.

Question 15

A DBA needs to create a new database that will store large binary objects (BLOBs) such as images and videos. The database will be used primarily for read-heavy operations. Which filegroup configuration should the DBA use to optimize performance for BLOB storage?

Accepted Answer

Store the BLOBs in a separate filegroup placed on a dedicated disk array.. Separating BLOBs into a dedicated filegroup on its own disk array isolates their I/O from other database operations, enhancing read performance and allowing for independent management. This is a common best practice for databases with large binary objects. Other options either mix BLOB data with other data or propose unsupported configurations.

Answer

Store the BLOBs in the PRIMARY filegroup to simplify management.

Answer

Create a single filegroup with multiple files to distribute I/O.

Answer

Use a FILESTREAM filegroup to store the BLOBs on a network share.

Question 16

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?

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 17

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 18

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 19

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 20

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