DS0-001 Data Systems Management — 15 Questions

Question 1

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 2

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' property must be set to 'Read-intent only' or 'Yes' to allow the secondary node to accept connections for reporting tasks.

Answer

Automatic Failover

Answer

Synchronous Commit

Answer

Primary Role

Question 3

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?

Accepted Answer

DROP and RECREATE index on a new filegroup. Moving the clustered index to a new filegroup moves the data pages of the table to that filegroup, effectively changing the physical storage location.

Answer

Use the Move-Item cmdlet

Answer

Shrink the database

Answer

Disable the index

Question 4

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 Full recovery model requires log backups to truncate the transaction log. The Simple recovery model automatically truncates the log, but the Bulk-Logged recovery model is specifically designed to minimize log space usage during large-scale bulk-load operations.

Answer

Differential

Answer

Full

Answer

Simple

Question 5

In PostgreSQL, you need to monitor the performance of your long-running queries. Which view is the most appropriate to inspect the query execution statistics for all active sessions?

Accepted Answer

pg_stat_activity. pg_stat_activity provides information about the current state of each backend process, including the query being executed.

Answer

pg_stat_database

Answer

pg_locks

Answer

pg_stat_user_tables

Question 6

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. The Certificate or Asymmetric Key is required to protect the DEK, which in turn is protected by the Master Key in the master database.

Answer

Active Directory group

Answer

Service Master Key

Answer

SSL/TLS certificate

Question 7

When planning a backup strategy, a company requires that the Recovery Point Objective (RPO) be set to 15 minutes. Which backup schedule meets this requirement?

Accepted Answer

Transaction Log Backups every 10 minutes. An RPO of 15 minutes implies that the maximum amount of data loss acceptable is 15 minutes. Therefore, transaction log backups must occur at least every 15 minutes.

Answer

Daily Full Backups

Answer

Weekly Full and Daily Differential Backups

Answer

Hourly Transaction Log Backups

Question 8

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. The ALTER TABLE ... SHRINK SPACE command is the standard way to defragment and reclaim space in an Oracle table while maintaining the table's structure and indexes.

Answer

TRUNCATE TABLE

Answer

ANALYZE TABLE ... COMPUTE STATISTICS

Answer

DROP AND RECREATE

Question 9

A MySQL DBA needs to diagnose a deadlock issue. Which tool or command provides the most detailed information regarding the specific InnoDB internal locks held by transactions at the time of a deadlock?

Accepted Answer

SHOW ENGINE INNODB STATUS. The command 'SHOW ENGINE INNODB STATUS' provides a section specifically detailing the last detected deadlock, including the locks held by the involved transactions.

Answer

CHECK TABLE

Answer

EXPLAIN

Answer

SHOW PROCESSLIST

Question 10

During a disaster recovery test, the DBA realizes the database restoration takes too long. Which action is the most effective way to improve the restore speed of a large database?

Accepted Answer

Use striped backups. Using the 'WITH MOVE' and 'FILE=' options is standard, but using multiple backup files ('striped backups') allows for parallel reading and writing during the restore process, significantly increasing speed.

Answer

Disable antivirus

Answer

Run in single-user mode

Answer

Increase the recovery interval

Question 11

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 standard variable to check the replication delay in a traditional MariaDB replication setup.

Answer

Innodb_buffer_pool_reads

Answer

Slave_IO_Running

Answer

Threads_connected

Question 12

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. Adding an index on the join columns allows the optimizer to consider a 'Merge Join' or 'Nested Loop' join, which may be more memory-efficient than a Hash Match.

Answer

Set the isolation level to Read Uncommitted

Answer

Force the join order

Answer

Update statistics

Question 13

Which database backup strategy is recommended to ensure that you can restore a database to a specific point in time?

Accepted Answer

Full and Transaction Log backups. Transaction log backups, when used in conjunction with full backups and the Full recovery model, allow for point-in-time recovery.

Answer

Differential backups only

Answer

Full backups only

Answer

Copy-only backups

Question 14

A DBA needs to automate the maintenance of database statistics in SQL Server. Which tool is the standard approach to manage this across all user databases?

Accepted Answer

SQL Server Agent Maintenance Plans. Maintenance Plans allow for the automation of tasks like updating statistics, reorganizing indexes, and backing up databases.

Answer

Active Directory Users and Computers

Answer

SQL Server Configuration Manager

Answer

Windows Task Scheduler

Question 15

A database is experiencing high I/O wait times on the log drive. What is the most effective configuration change to alleviate this bottleneck?

Accepted Answer

Move the log to a high-performance dedicated drive. Moving the transaction log to a dedicated physical drive with high write performance (low latency) is the most effective way to resolve I/O bottlenecks related to transaction logging.

Answer

Use data compression

Answer

Increase the buffer pool size

Answer

Reduce the database recovery interval