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

Questions 6–10

1. 6foundation · easy

   Which command can be used to terminate a blocking session in SQL Server?

   • AKILL <session_id>
   • BALTER SESSION <session_id> KILL
   • CCANCEL <session_id>
   • DDROP SESSION <session_id>
   Reveal Solution Discussion Select an answer first
2. 7application · medium

   You are monitoring a database with Query Store enabled. You notice that a specific query has multiple execution plans, and the average duration has increased significantly for the most recent plan. You need to force the previously good plan to stabilize performance. What should you do?

   • ADisable Query Store to stop capturing new plans.
   • BRebuild all indexes on the table used by the query.
   • CClear the Query Store to remove all historical plans.
   • DUse the Query Store reports to identify the good plan and force it for the query.
   Reveal Solution Discussion Select an answer first
4. 8application · medium

   Your organization runs a critical OLTP database in Azure SQL Database. You notice that a specific stored procedure occasionally degrades in performance, but the issue is intermittent and difficult to reproduce. You need to capture and analyze the query plans and runtime statistics for that procedure over time without affecting current performance. What should you configure?

   • ASet the database to READ_COMMITTED_SNAPSHOT to reduce blocking and improve the procedure's performance.
   • BEnable Query Store with a data flush interval of 60 seconds and set the query store capture mode to Auto.
   • CRun a server-side trace with a filter on the stored procedure's object ID to capture all executions.
   • DEnable automatic tuning with the 'CREATE INDEX' option to force a better plan for the stored procedure.
   Reveal Solution Discussion Select an answer first
5. 9application · medium

   You are reviewing an execution plan for a query that joins two tables. The plan shows a nested loops join with a large number of executions and a high estimated rows to actual rows ratio. The query is slower than expected. What is the most likely cause?

   • AThe query is using a hash join, which is always inefficient.
   • BThe database is using the wrong compatibility level.
   • COutdated statistics causing the optimizer to choose a poor join strategy.
   • DThe query is missing a WHERE clause, so it returns too many rows.
   Reveal Solution Discussion Select an answer first
6. 10foundation · easy

   Which column in sys.dm_exec_requests indicates the session ID of the session that is blocking the current request?

   • Ablocking_session_id
   • Bwait_type
   • Csession_id
   • Dcommand
   Reveal Solution Discussion Select an answer first