Optimize Database Performance DP-800 Practice Questions (Page 3)

Questions 11–15

1. 11foundation · easy

   In Azure SQL Database, which tool provides a high-level overview of query performance issues, including the top queries by CPU, duration, and execution count?

   • AQuery Performance Insight
   • BAzure Monitor Workbooks
   • CSQL Server Profiler
   • DDynamic Management Views
   Reveal Solution Discussion Select an answer first
2. 12foundation · easy

   Which transaction isolation level allows a transaction to read only committed data but does not prevent other transactions from modifying data that the current transaction has read, potentially causing non-repeatable reads?

   • ASNAPSHOT
   • BREAD COMMITTED
   • CREPEATABLE READ
   • DREAD UNCOMMITTED
   Reveal Solution Discussion Select an answer first
4. 13application · medium

   A database administrator needs to identify which queries are causing the most IO pressure on an Azure SQL database. The administrator wants to see the total logical reads and the query text for the top 10 queries. Which DMV query should the administrator run?

   • ASELECT TOP 10 qs.total_logical_reads, st.text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY qs.total_logical_reads DESC;
   • BSELECT TOP 10 * FROM sys.dm_exec_query_plan ORDER BY total_logical_reads DESC;
   • CSELECT TOP 10 * FROM sys.dm_exec_query_stats ORDER BY total_logical_reads DESC;
   • DSELECT TOP 10 * FROM sys.dm_exec_requests ORDER BY logical_reads DESC;
   Reveal Solution Discussion Select an answer first
5. 14application · medium

   A company runs a critical OLTP database on Azure SQL Database. The database is experiencing high CPU usage due to a few queries that are executed frequently. Query Store shows that these queries have multiple plans with varying performance. The company wants to ensure consistent performance for these queries. What should you do?

   • AEnable automatic plan correction in Query Store.
   • BIncrease the database's DTU quota.
   • CSet the database to READ COMMITTED SNAPSHOT isolation.
   • DRebuild all indexes on the tables used by these queries.
   Reveal Solution Discussion Select an answer first
6. 15foundation · easy

   What is a deadlock in a database system?

   • AA situation where a query takes too long to execute
   • BA situation where a transaction is rolled back due to a constraint violation
   • CA situation where two or more transactions are waiting indefinitely for each other to release locks
   • DA situation where a transaction is waiting for a resource held by another transaction that will eventually release it
   Reveal Solution Discussion Select an answer first