DS0-001 Data Systems Management — All Questions With Answers

Question 1mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 2mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 3hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 4mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 5hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 6mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 7mediummulti select

Read the full Data Systems Management explanation →

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

Question 8easymulti select

Read the full Data Systems Management explanation →

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

Question 9easymultiple choice

Read the full Data Systems Management explanation →

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?

Question 10easymultiple choice

Read the full Data Systems Management explanation →

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?

Question 11hardmulti select

Read the full Data Systems Management explanation →

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.)

Question 12hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 13mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 14mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 15easymultiple choice

Read the full Data Systems Management explanation →

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?

Question 16mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 17hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 18mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 19mediummulti select

Read the full Data Systems Management explanation →

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.)

Question 20mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 21mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 22easymultiple choice

Read the full Data Systems Management explanation →

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?

Question 23hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 24hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 25easymultiple choice

Read the full Data Systems Management explanation →

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?

Question 26easymultiple choice

Read the full Data Systems Management explanation →

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

Question 27mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 28hardmultiple choice

Read the full Data Systems Management explanation →

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

Question 29easymultiple choice

Read the full Data Systems Management explanation →

A database administrator needs to perform a schema-only export of a database. Which tool parameter is typically used for this purpose in mysqldump?

Question 30mediummultiple choice

Read the full Data Systems Management explanation →

A database server is consistently hitting high CPU usage. You suspect an unoptimized query is causing a full table scan. Which tool should you use to capture the execution plan of currently running queries?

Question 31hardmultiple choice

Read the full Data Systems Management explanation →

A database is configured with a high-availability cluster. During a planned maintenance window, you need to fail over to the secondary node manually. Which command or action is used in a Windows Server Failover Cluster (WSFC) environment?

Question 32mediummultiple choice

Read the full Data Systems Management explanation →

You are managing a database that uses transparent data encryption. If you need to back up the database, what additional component must be included to ensure the backup can be restored on a different server?

Question 33mediummultiple choice

Read the full Data Systems Management explanation →

A database administrator notices that the transaction log file is nearly full. Which operation will shrink the file to free up space?

Question 34easymultiple choice

Read the full Data Systems Management explanation →

Which database object is specifically designed to enforce referential integrity between two tables?

Question 35hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 36easymultiple choice

Read the full Data Systems Management explanation →

Which tool provides a graphical interface for managing SQL Server instances, including configuring security and creating maintenance plans?

Question 37mediummultiple choice

Read the full Data Systems Management explanation →

You are performing a backup for a database that is in a 24/7 environment. Which backup type should you use if you want to avoid interfering with the existing backup sequence of full and differential backups?

Question 38easymultiple choice

Read the full Data Systems Management explanation →

What is the primary function of an index in a database?

Question 39hardmultiple choice

Read the full Data Systems Management explanation →

A production database server is running out of memory. Which setting in SQL Server limits the amount of memory the instance can consume from the OS?

Question 40mediummultiple choice

Read the full Data Systems Management explanation →

You need to change the collation of a database. What is the impact of performing this operation?

Question 41mediummulti select

Read the full Data Systems Management explanation →

Which THREE of the following are valid methods to improve database performance for read-heavy workloads?

Question 42hardmulti select

Read the full Data Systems Management explanation →

Which TWO of the following are common risks associated with performing a direct 'DROP' operation on a large production table?

Question 43easymulti select

Read the full Data Systems Management explanation →

Which THREE of the following are essential components of a robust disaster recovery plan for a mission-critical database?

Question 44hardmulti select

Read the full Data Systems Management explanation →

Which THREE of the following are necessary prerequisites for implementing database mirroring in a SQL Server environment?

Question 45mediummulti select

Read the full Data Systems Management explanation →

Which TWO of the following are common symptoms of an undersized transaction log file?

Question 46easymulti select

Read the full Data Systems Management explanation →

Which THREE of the following are standard database maintenance tasks?

Question 47easymulti select

Read the full Data Systems Management explanation →

Which THREE of the following are common types of database backups?

Question 48hardmulti select

Read the full Data Systems Management explanation →

Which THREE of the following are indicators of significant index fragmentation in a database?

Question 49mediummulti select

Read the full Data Systems Management explanation →

Which TWO of the following are key considerations when configuring a database for high availability?

Question 50hardmulti select

Read the full Data Systems Management explanation →

Which THREE of the following items should be monitored to identify performance bottlenecks in a database?

Question 51mediummulti select

Read the full Data Systems Management explanation →

Which TWO of the following can be used to prevent unauthorized access to sensitive database tables?

Question 52mediummultiple choice

Read the full Data Systems Management explanation →

A junior DBA runs the following statement against a production table that contains millions of rows with an active workload:

TRUNCATE TABLE Sales.Transactions;

What is the most important characteristic of this operation that the junior DBA should have understood before executing it?

Question 53hardmultiple choice

Read the full Data Systems Management explanation →

A DBA is designing a backup strategy for a database that must meet a recovery point objective (RPO) of 15 minutes and a recovery time objective (RTO) of 1 hour. The database is in the FULL recovery model. Which combination of backup types and scheduling best satisfies these objectives?

Question 54mediummultiple choice

Read the full Data Systems Management explanation →

A data systems team runs an analytics workload on a database that is also used for OLTP. Reports have become slower over time, and the team suspects that an index is being scanned instead of seeked. They want to capture the actual execution plan for a specific report query to confirm the operator used. Which action should they take?

Question 55hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 56mediummultiple choice

Read the full Data Systems Management explanation →

You are a database administrator for a high-traffic e-commerce platform. The order history table has grown to 500 GB and queries filtering on order_date are performing poorly. You need to implement a partitioning strategy that allows you to quickly archive old orders by switching out partitions. Which partitioning approach should you use?

Question 57hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 58mediummultiple choice

Read the full Data Systems Management explanation →

A database administrator is designing a backup strategy for a 2 TB OLTP database that must support point-in-time recovery with a maximum data loss window of 15 minutes. Full backups complete in 4 hours, and the transaction log is backed up every 10 minutes. What is the most appropriate recovery model for this database?

Question 59mediummultiple choice

Read the full Data Systems Management explanation →

You are managing a PostgreSQL database that supports a global application. Users in different regions experience slow query performance when filtering on a timestamp column that stores UTC values. You need to ensure that queries filtering on local time are efficient. What should you do?

Question 60hardmultiple choice

Read the full Data Systems Management explanation →

A database administrator is configuring a new SQL Server instance that will host a database with sensitive financial data. The compliance team requires that all data at rest be encrypted, and that the encryption keys be managed by a hardware security module (HSM). Which SQL Server feature should the administrator implement to meet these requirements?

Question 61mediummultiple choice

Read the full Data Systems Management explanation →

A retail company's order-processing database runs on a single server with RAID 10 storage. The database is 800 GB, with a 20 GB transaction log. The recovery model is FULL, and log backups run every 15 minutes. During a power failure, the server shuts down unexpectedly. After restart, the database is marked as 'In Recovery' and takes 45 minutes to become available. The CIO wants to reduce this recovery time. Which action should the database administrator take first?

Question 62mediummultiple choice

Read the full Data Systems Management explanation →

A database administrator is planning a disaster recovery strategy for a critical SQL Server database. The business requires a recovery point objective (RPO) of 15 minutes and a recovery time objective (RTO) of 1 hour. The database is currently using the simple recovery model. Which backup strategy should the administrator implement to meet these objectives?

Question 63hardmultiple choice

Read the full Data Systems Management explanation →

A database administrator is troubleshooting a query that occasionally runs slowly. The query uses a parameterized stored procedure. The execution plan shows that sometimes the query uses an index seek and sometimes a clustered index scan. The DBA suspects parameter sniffing. Which action should the DBA take to mitigate the issue while preserving the ability to use the optimal plan for each execution?

Question 64mediummulti select

Read the full Data Systems Management explanation →

A database administrator is implementing an auditing solution for a SQL Server database that must track both successful and failed logins, as well as data modification statements on a specific table. The solution must minimize performance overhead and store audit data in a secure, tamper-evident format. (Choose two.)

Question 65easymultiple choice

Read the full Data Systems Management explanation →

A database administrator is deploying a new SQL Server instance that will host a data warehouse. The instance must store data and log files on separate physical volumes to maximize performance and simplify backup operations. The server has four drive letters available: C, D, E, and F. The administrator wants to ensure that tempdb is also isolated. Which configuration should the administrator use?

Question 66hardmultiple choice

Read the full Data Systems Management explanation →

A production database is experiencing blocking and deadlocks during peak hours. The DBA suspects that long-running transactions are holding locks. Which dynamic management view (DMV) should the DBA query to identify the blocking chain and the SQL text of the blocking session?

Question 67easymultiple choice

Read the full Data Systems Management explanation →

A database administrator is asked to create a new database that will store historical data and will not be updated frequently. The database will be read-only most of the time, and the administrator wants to minimize storage space and improve query performance for large scans. Which SQL Server feature should the administrator implement?

Question 68hardmultiple choice

Read the full Data Systems Management explanation →

A database administrator is troubleshooting a query that runs slowly only when executed by a specific application login. The same query runs quickly when executed by the administrator's login. The administrator suspects parameter sniffing. Which action should the administrator take to resolve the issue while minimizing the impact on other queries?

Question 69hardmulti select

Read the full Data Systems Management explanation →

A DBA is designing a backup strategy for a large SQL Server database that must support point-in-time recovery and minimize backup windows. The database is in the full recovery model. Which two backup types should the DBA include in the strategy to achieve these goals? (Choose two.)

Question 70hardmulti select

Read the full Data Systems Management explanation →

A database administrator is configuring a new SQL Server instance for a high-volume online transaction processing (OLTP) system. The administrator needs to optimize tempdb performance. Which two actions should the administrator take? (Choose two.)

Question 71hardmultiple choice

Read the full Data Systems Management explanation →

A data systems technician is asked to recommend a partitioning strategy for a very large PostgreSQL table that stores time-series sensor readings. Queries almost always filter on the reading_timestamp column and return a narrow time window, while older data must remain available for compliance for seven years. The team wants the planner to prune partitions automatically and wants to keep maintenance simple. Which approach should the technician recommend?

Question 72easymultiple choice

Read the full Data Systems Management explanation →

A database administrator is responsible for a PostgreSQL database that stores financial transactions. The compliance team requires that all changes to the transactions table be recorded with the user, timestamp, and old and new values for auditing. The application performs INSERT, UPDATE, and DELETE operations. The DBA must implement a solution that captures all changes without modifying application code. Which PostgreSQL feature should the DBA use?

Question 1mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 2mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 3hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 4mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 5hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 6mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 7mediummulti select

Read the full Data Systems Management explanation →

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

Question 8easymulti select

Read the full Data Systems Management explanation →

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

Question 9easymultiple choice

Read the full Data Systems Management explanation →

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?

Question 10easymultiple choice

Read the full Data Systems Management explanation →

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?

Question 11hardmulti select

Read the full Data Systems Management explanation →

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.)

Question 12hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 13mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 14mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 15easymultiple choice

Read the full Data Systems Management explanation →

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?

Question 16mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 17hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 18mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 19mediummulti select

Read the full Data Systems Management explanation →

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.)

Question 20mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 21mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 22easymultiple choice

Read the full Data Systems Management explanation →

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?

Question 23hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 24hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 25easymultiple choice

Read the full Data Systems Management explanation →

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?

Question 26easymultiple choice

Read the full Data Systems Management explanation →

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

Question 27mediummultiple choice

Read the full Data Systems Management explanation →

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?

Question 28hardmultiple choice

Read the full Data Systems Management explanation →

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

Question 29easymultiple choice

Read the full Data Systems Management explanation →

A database administrator needs to perform a schema-only export of a database. Which tool parameter is typically used for this purpose in mysqldump?

Question 30mediummultiple choice

Read the full Data Systems Management explanation →

A database server is consistently hitting high CPU usage. You suspect an unoptimized query is causing a full table scan. Which tool should you use to capture the execution plan of currently running queries?

Question 31hardmultiple choice

Read the full Data Systems Management explanation →

A database is configured with a high-availability cluster. During a planned maintenance window, you need to fail over to the secondary node manually. Which command or action is used in a Windows Server Failover Cluster (WSFC) environment?

Question 32mediummultiple choice

Read the full Data Systems Management explanation →

You are managing a database that uses transparent data encryption. If you need to back up the database, what additional component must be included to ensure the backup can be restored on a different server?

Question 33mediummultiple choice

Read the full Data Systems Management explanation →

A database administrator notices that the transaction log file is nearly full. Which operation will shrink the file to free up space?

Question 34easymultiple choice

Read the full Data Systems Management explanation →

Which database object is specifically designed to enforce referential integrity between two tables?

Question 35hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 36easymultiple choice

Read the full Data Systems Management explanation →

Which tool provides a graphical interface for managing SQL Server instances, including configuring security and creating maintenance plans?

Question 37mediummultiple choice

Read the full Data Systems Management explanation →

You are performing a backup for a database that is in a 24/7 environment. Which backup type should you use if you want to avoid interfering with the existing backup sequence of full and differential backups?

Question 38easymultiple choice

Read the full Data Systems Management explanation →

What is the primary function of an index in a database?

Question 39hardmultiple choice

Read the full Data Systems Management explanation →

A production database server is running out of memory. Which setting in SQL Server limits the amount of memory the instance can consume from the OS?

Question 40mediummultiple choice

Read the full Data Systems Management explanation →

You need to change the collation of a database. What is the impact of performing this operation?

Question 41mediummulti select

Read the full Data Systems Management explanation →

Which THREE of the following are valid methods to improve database performance for read-heavy workloads?

Question 42hardmulti select

Read the full Data Systems Management explanation →

Which TWO of the following are common risks associated with performing a direct 'DROP' operation on a large production table?

Question 43easymulti select

Read the full Data Systems Management explanation →

Which THREE of the following are essential components of a robust disaster recovery plan for a mission-critical database?

Question 44hardmulti select

Read the full Data Systems Management explanation →

Which THREE of the following are necessary prerequisites for implementing database mirroring in a SQL Server environment?

Question 45mediummulti select

Read the full Data Systems Management explanation →

Which TWO of the following are common symptoms of an undersized transaction log file?

Question 46easymulti select

Read the full Data Systems Management explanation →

Which THREE of the following are standard database maintenance tasks?

Question 47easymulti select

Read the full Data Systems Management explanation →

Which THREE of the following are common types of database backups?

Question 48hardmulti select

Read the full Data Systems Management explanation →

Which THREE of the following are indicators of significant index fragmentation in a database?

Question 49mediummulti select

Read the full Data Systems Management explanation →

Which TWO of the following are key considerations when configuring a database for high availability?

Question 50hardmulti select

Read the full Data Systems Management explanation →

Which THREE of the following items should be monitored to identify performance bottlenecks in a database?

Question 51mediummulti select

Read the full Data Systems Management explanation →

Which TWO of the following can be used to prevent unauthorized access to sensitive database tables?

Question 52mediummultiple choice

Read the full Data Systems Management explanation →

A junior DBA runs the following statement against a production table that contains millions of rows with an active workload:

TRUNCATE TABLE Sales.Transactions;

What is the most important characteristic of this operation that the junior DBA should have understood before executing it?

Question 53hardmultiple choice

Read the full Data Systems Management explanation →

A DBA is designing a backup strategy for a database that must meet a recovery point objective (RPO) of 15 minutes and a recovery time objective (RTO) of 1 hour. The database is in the FULL recovery model. Which combination of backup types and scheduling best satisfies these objectives?

Question 54mediummultiple choice

Read the full Data Systems Management explanation →

A data systems team runs an analytics workload on a database that is also used for OLTP. Reports have become slower over time, and the team suspects that an index is being scanned instead of seeked. They want to capture the actual execution plan for a specific report query to confirm the operator used. Which action should they take?

Question 55hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 56mediummultiple choice

Read the full Data Systems Management explanation →

You are a database administrator for a high-traffic e-commerce platform. The order history table has grown to 500 GB and queries filtering on order_date are performing poorly. You need to implement a partitioning strategy that allows you to quickly archive old orders by switching out partitions. Which partitioning approach should you use?

Question 57hardmultiple choice

Read the full Data Systems Management explanation →

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?

Question 58mediummultiple choice

Read the full Data Systems Management explanation →

A database administrator is designing a backup strategy for a 2 TB OLTP database that must support point-in-time recovery with a maximum data loss window of 15 minutes. Full backups complete in 4 hours, and the transaction log is backed up every 10 minutes. What is the most appropriate recovery model for this database?

Question 59mediummultiple choice

Read the full Data Systems Management explanation →

You are managing a PostgreSQL database that supports a global application. Users in different regions experience slow query performance when filtering on a timestamp column that stores UTC values. You need to ensure that queries filtering on local time are efficient. What should you do?

Question 60hardmultiple choice

Read the full Data Systems Management explanation →

A database administrator is configuring a new SQL Server instance that will host a database with sensitive financial data. The compliance team requires that all data at rest be encrypted, and that the encryption keys be managed by a hardware security module (HSM). Which SQL Server feature should the administrator implement to meet these requirements?

Question 61mediummultiple choice

Read the full Data Systems Management explanation →

A retail company's order-processing database runs on a single server with RAID 10 storage. The database is 800 GB, with a 20 GB transaction log. The recovery model is FULL, and log backups run every 15 minutes. During a power failure, the server shuts down unexpectedly. After restart, the database is marked as 'In Recovery' and takes 45 minutes to become available. The CIO wants to reduce this recovery time. Which action should the database administrator take first?

Question 62mediummultiple choice

Read the full Data Systems Management explanation →

A database administrator is planning a disaster recovery strategy for a critical SQL Server database. The business requires a recovery point objective (RPO) of 15 minutes and a recovery time objective (RTO) of 1 hour. The database is currently using the simple recovery model. Which backup strategy should the administrator implement to meet these objectives?

Question 63hardmultiple choice

Read the full Data Systems Management explanation →

A database administrator is troubleshooting a query that occasionally runs slowly. The query uses a parameterized stored procedure. The execution plan shows that sometimes the query uses an index seek and sometimes a clustered index scan. The DBA suspects parameter sniffing. Which action should the DBA take to mitigate the issue while preserving the ability to use the optimal plan for each execution?

Question 64mediummulti select

Read the full Data Systems Management explanation →

A database administrator is implementing an auditing solution for a SQL Server database that must track both successful and failed logins, as well as data modification statements on a specific table. The solution must minimize performance overhead and store audit data in a secure, tamper-evident format. (Choose two.)

Question 65easymultiple choice

Read the full Data Systems Management explanation →

A database administrator is deploying a new SQL Server instance that will host a data warehouse. The instance must store data and log files on separate physical volumes to maximize performance and simplify backup operations. The server has four drive letters available: C, D, E, and F. The administrator wants to ensure that tempdb is also isolated. Which configuration should the administrator use?

Question 66hardmultiple choice

Read the full Data Systems Management explanation →

A production database is experiencing blocking and deadlocks during peak hours. The DBA suspects that long-running transactions are holding locks. Which dynamic management view (DMV) should the DBA query to identify the blocking chain and the SQL text of the blocking session?

Question 67easymultiple choice

Read the full Data Systems Management explanation →

A database administrator is asked to create a new database that will store historical data and will not be updated frequently. The database will be read-only most of the time, and the administrator wants to minimize storage space and improve query performance for large scans. Which SQL Server feature should the administrator implement?

Question 68hardmultiple choice

Read the full Data Systems Management explanation →

A database administrator is troubleshooting a query that runs slowly only when executed by a specific application login. The same query runs quickly when executed by the administrator's login. The administrator suspects parameter sniffing. Which action should the administrator take to resolve the issue while minimizing the impact on other queries?

Question 69hardmulti select

Read the full Data Systems Management explanation →

A DBA is designing a backup strategy for a large SQL Server database that must support point-in-time recovery and minimize backup windows. The database is in the full recovery model. Which two backup types should the DBA include in the strategy to achieve these goals? (Choose two.)

Question 70hardmulti select

Read the full Data Systems Management explanation →

A database administrator is configuring a new SQL Server instance for a high-volume online transaction processing (OLTP) system. The administrator needs to optimize tempdb performance. Which two actions should the administrator take? (Choose two.)

Question 71hardmultiple choice

Read the full Data Systems Management explanation →

A data systems technician is asked to recommend a partitioning strategy for a very large PostgreSQL table that stores time-series sensor readings. Queries almost always filter on the reading_timestamp column and return a narrow time window, while older data must remain available for compliance for seven years. The team wants the planner to prune partitions automatically and wants to keep maintenance simple. Which approach should the technician recommend?

Question 72easymultiple choice

Read the full Data Systems Management explanation →

A database administrator is responsible for a PostgreSQL database that stores financial transactions. The compliance team requires that all changes to the transactions table be recorded with the user, timestamp, and old and new values for auditing. The application performs INSERT, UPDATE, and DELETE operations. The DBA must implement a solution that captures all changes without modifying application code. Which PostgreSQL feature should the DBA use?