DS0-001 Data Systems Management — Timed 10 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?

10 minute time limit — choose an answer to begin.

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