Maintenance Processes DS0-001 Practice Questions (Page 1)

Questions 1–5

1. 1application · medium

   A database server has 64 GB of RAM. The primary database is 200 GB, and the secondary database is 50 GB. The administrator notices that the primary database's buffer pool hit ratio is low, causing excessive disk I/O. The secondary database is rarely used. What is the most effective resource allocation change to improve the primary database's performance?

   • ADecrease the memory allocated to the secondary database and allocate it to the primary database.
   • BIncrease the memory allocated to the secondary database.
   • CDisable the secondary database entirely.
   • DMove the secondary database to a different server.
   Reveal Solution Discussion Select an answer first
2. 2foundation · easy

   What is the primary function of a database performance monitoring tool?

   • ATo replace the need for database administrators
   • BTo create database backups
   • CTo track performance metrics and identify bottlenecks
   • DTo automatically rewrite all queries for optimal performance
   Reveal Solution Discussion Select an answer first
4. 3foundation · easy

   Which of the following is a common technique used to optimize a query that retrieves only a subset of columns from a large table?

   • AAdding a wildcard to the WHERE clause
   • BUsing a covering index to satisfy the query
   • CRemoving the WHERE clause to simplify the query
   • DUsing SELECT * to retrieve all columns
   Reveal Solution Discussion Select an answer first
5. 4expert · medium

   A database server hosts two databases: a critical OLTP database and a less critical reporting database. The OLTP database experiences frequent lock contention during peak hours. The reporting database runs large queries that consume significant I/O. The administrator wants to minimize the impact of the reporting workload on the OLTP database. What is the most effective resource allocation strategy?

   • AIncrease the memory for the reporting database to speed up its queries.
   • BUse Resource Governor to limit the CPU and I/O resources available to the reporting database.
   • CSchedule the reporting queries to run only during off-peak hours.
   • DMove the reporting database to a different server.
   Reveal Solution Discussion Select an answer first
6. 5expert · medium

   A database administrator is planning to apply a quarterly cumulative update to a SQL Server instance that hosts a mission-critical application. The update includes both security fixes and a new feature that the application team wants to use. The maintenance window is only 2 hours. The database is 2 TB and the server has limited disk space for backups. What is the most important consideration before starting the patch?

   • ACheck the vendor's release notes for known issues with the patch.
   • BEnsure a full database backup is taken and verified, and a rollback plan is documented.
   • CVerify that the new feature is compatible with the current application version.
   • DConfirm that the server has enough CPU and memory to install the patch.
   Reveal Solution Discussion Select an answer first