Maintenance Processes DS0-001 Practice Questions (Page 5)

Questions 21–25

1. 21foundation · easy

   Which of the following is a key input for database capacity planning?

   • AHistorical data growth trends
   • BThe color scheme of the database management interface
   • CThe number of database users' birthdays
   • DThe database vendor's marketing materials
   Reveal Solution Discussion Select an answer first
2. 22foundation · easy

   Which of the following is a best practice for optimizing a query that filters on a date column?

   • AApplying a function to the date column in the WHERE clause
   • BCasting the date column to a string for comparison
   • CUsing a wildcard with the date column
   • DUsing a range condition on the date column
   Reveal Solution Discussion Select an answer first
4. 23application · medium

   An e-commerce application has a table named Orders with millions of rows. The most common query retrieves orders for a specific CustomerID and sorts them by OrderDate descending. The query execution plan shows a key lookup for every row returned. Which indexing strategy will best resolve this performance issue?

   • ACreate a clustered index on CustomerID.
   • BCreate separate nonclustered indexes on CustomerID and OrderDate.
   • CCreate a nonclustered index on OrderDate only.
   • DCreate a covering nonclustered index on (CustomerID, OrderDate DESC) including all other selected columns.
   Reveal Solution Discussion Select an answer first
5. 24application · medium

   A reporting query that runs nightly is performing poorly. The query uses a function in the WHERE clause: WHERE YEAR(OrderDate) = 2023. The OrderDate column has a nonclustered index. The execution plan shows an index scan instead of an index seek. What is the most effective query optimization technique?

   • ARemove the index on OrderDate to force a different plan.
   • BRewrite the query to use a range condition: WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01'.
   • CIncrease the statistics sampling rate for the OrderDate index.
   • DCreate a new index on YEAR(OrderDate).
   Reveal Solution Discussion Select an answer first
6. 25foundation · easy

   What is a common reason to allocate additional CPU resources to a database server?

   • ATo make the database compatible with more operating systems
   • BTo improve the database's security
   • CTo handle increased query concurrency
   • DTo reduce the amount of disk storage needed
   Reveal Solution Discussion Select an answer first