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

Questions 36–40

1. 36application · medium

   Users report that a nightly reporting job is slow. You query sys.dm_exec_requests and find that the reporting session is waiting on a lock held by a session that has been running for over an hour. The blocking session is executing a transaction that updates a large table. You need to resolve the blocking with minimal disruption to the application. What should you do first?

   • AEnable read committed snapshot isolation to eliminate all blocking.
   • BRebuild the indexes on the large table to reduce lock duration.
   • CIdentify the application and user associated with the blocking session, then determine if the transaction is still active.
   • DKill the blocking session using the KILL command.
   Reveal Solution Discussion Select an answer first
2. 37expert · hard

   You manage an Azure SQL Database that supports a seasonal application. Intelligent Insights has detected an anomaly in DTU usage, but you are unsure if it is a seasonal pattern or a new issue. You need to determine whether the anomaly is expected. What should you do?

   • AQuery sys.dm_exec_query_stats to see if specific queries are consuming more DTU.
   • BCompare the current Intelligent Insights report with historical reports from the same period in previous years.
   • CImmediately scale up the database to handle the increased DTU usage.
   • DDisable Intelligent Insights to prevent false alerts.
   Reveal Solution Discussion Select an answer first
4. 38application · medium

   You are tuning a database that supports an e-commerce application. A frequently executed query filters on a non-clustered column and also sorts by another column. The query currently performs a key lookup for each row. You need to improve performance by reducing the number of key lookups. What should you implement?

   • AA columnstore index on the table.
   • BA statistics update on the non-clustered column.
   • CA covering index that includes both the filter and sort columns.
   • DA filtered index on the non-clustered column only.
   Reveal Solution Discussion Select an answer first
5. 39foundation · easy

   Which query construct can cause unnecessary resource consumption and should be avoided in favor of set-based operations?

   • AUsing a JOIN to combine tables
   • BUsing a cursor to process rows one by one
   • CUsing a WHERE clause to filter rows
   • DUsing an ORDER BY to sort results
   Reveal Solution Discussion Select an answer first
6. 40application · medium

   You manage an Azure SQL Database and receive an Intelligent Insights alert that CPU usage has increased significantly over the past hour. The alert indicates that a specific query is responsible. You need to verify the alert and get more details about the query's execution. What should you do?

   • AOpen the Intelligent Insights report in the Azure portal to see the root-cause analysis.
   • BUse Query Store to force the query to use a different plan.
   • CQuery sys.dm_exec_query_stats to find the query with the highest CPU.
   • DRestart the database to clear the CPU spike.
   Reveal Solution Discussion Select an answer first