Data Systems Management

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

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?

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?

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

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?

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

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?

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?

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?

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?

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

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?

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?

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?

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

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?

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?

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?

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?

Which THREE of the following are standard database maintenance tasks?

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

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

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?

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

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

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

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

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?

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

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

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

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

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

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?

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?

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?

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?

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

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?

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

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?

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?

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

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?

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?

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?

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?

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?

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

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?

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?

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

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?