Monitor and Optimize Query Performance DP-300 Practice Questions (Page 1)

Questions 1–5

1. 1expert · hard

   You are the DBA for a healthcare application on Azure SQL Database. A critical batch process runs at night and updates a large table. During the batch, you notice that several user queries are blocked for long periods. You need to minimize blocking while ensuring the batch completes within its maintenance window. The batch currently uses a single large transaction. What should you do?

   • ABreak the batch into smaller transactions and commit periodically to release locks.
   • BKill the batch process if it blocks user queries.
   • CIncrease the batch's MAXDOP to speed it up.
   • DUse the READ UNCOMMITTED isolation level for the user queries to avoid blocking.
   Reveal Solution Discussion Select an answer first
2. 2expert · hard

   A critical application is experiencing frequent blocking. You identify that the blocking is caused by a long-running transaction that updates a large table. The application cannot be changed to use snapshot isolation. You need to reduce the duration of locks to minimize blocking. What should you do?

   • AUse the READ UNCOMMITTED isolation level for all sessions.
   • BIncrease the database's lock timeout setting.
   • CBreak the large transaction into smaller batches that commit more frequently.
   • DRebuild the table's clustered index to reduce fragmentation.
   Reveal Solution Discussion Select an answer first
4. 3foundation · easy

   Which query modification is likely to improve performance by reducing the amount of data processed?

   • ARemoving indexes from the table
   • BUsing a function on the indexed column in the WHERE clause
   • CSelecting all columns with SELECT *
   • DAdding a WHERE clause to filter rows early
   Reveal Solution Discussion Select an answer first
5. 4application · medium

   You are reviewing a query that uses a correlated subquery in the SELECT list. The execution plan shows that the subquery is executed once per row of the outer query, causing high CPU usage. You need to recommend a query construct change to improve performance. What should you recommend?

   • AUse a table hint to force a hash join.
   • BRewrite the correlated subquery as a JOIN or use a window function.
   • CAdd an index on the outer query's join column.
   • DIncrease the database's MAXDOP to improve parallelism.
   Reveal Solution Discussion Select an answer first
6. 5application · medium

   You are optimizing a database with a large fact table. A query that aggregates data by a date column is slow. You review the execution plan and see a clustered index scan. The table has a clustered index on an identity column. What index change would most likely improve this query?

   • ACreate a columnstore index on the table.
   • BCreate a non-clustered index on the date column and include the aggregated columns.
   • CChange the clustered index to the date column.
   • DUpdate statistics on the date column.
   Reveal Solution Discussion Select an answer first