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

Questions 26–30

1. 26application · medium

   Users report that a critical application is intermittently unresponsive. While investigating, you run a query against sys.dm_exec_requests and notice that several sessions are in a WAIT type of LCK_M_X with a high wait time. You also see a blocking_session_id value for these sessions. What should you do first to resolve the issue?

   • AIncrease the MAXDOP setting for the database to reduce lock contention.
   • BIdentify the session in the blocking_session_id column and evaluate its current transaction and the resources it holds.
   • CRestart the Azure SQL Database service to clear all locks.
   • DImmediately kill all sessions that are in a waiting state to free up resources.
   Reveal Solution Discussion Select an answer first
2. 27application · medium

   You identify a blocking chain in a production database. The head blocker is a session that has been running a transaction for over an hour and appears to be waiting on a user response. The application does not have a timeout for this transaction. You need to resolve the blocking with minimal impact. What should you do?

   • AKill all blocked sessions to reduce load on the server.
   • BRestart the database service to clear all locks.
   • CTerminate the head blocker session after confirming it is not performing critical work.
   • DWait for the user to respond, as the transaction may complete soon.
   Reveal Solution Discussion Select an answer first
4. 28foundation · easy

   Which statement is used to remove an index from a table?

   • AALTER TABLE TableName DROP INDEX IndexName
   • BREMOVE INDEX IndexName FROM TableName
   • CDROP INDEX IndexName ON TableName
   • DDELETE INDEX IndexName FROM TableName
   Reveal Solution Discussion Select an answer first
5. 29application · medium

   A developer wrote a query that uses a scalar user-defined function (UDF) in the WHERE clause. The query runs slowly and you see high CPU usage. You review the execution plan and notice that the UDF is executed once per row. What should you recommend?

   • AAdd an index on the column used in the UDF's argument.
   • BIncrease the database's compatibility level to the latest version.
   • CUse a cursor to process rows one at a time.
   • DRewrite the query to use an inline table-valued function instead of the scalar UDF.
   Reveal Solution Discussion Select an answer first
6. 30expert · hard

   You are optimizing a database for an application that runs both high-frequency point lookups and large analytical scans on the same table. The table is frequently updated. You need to improve performance for both workloads without causing excessive index maintenance overhead. What should you do?

   • ACreate multiple nonclustered indexes to cover all possible query patterns.
   • BCreate a clustered rowstore index for point lookups and a nonclustered columnstore index for analytical scans.
   • CCreate a clustered columnstore index and rely on it for both workloads.
   • DUse a heap and create a single covering index for the most common query.
   Reveal Solution Discussion Select an answer first