DP-300 Monitor, configure, and optimize database resources Practice Test 3 — 15 Questions

Question 1

You are the database administrator for a hybrid environment with on-premises SQL Server 2022 and Azure SQL Database. All databases use the AdventureWorks schema. You notice that a critical stored procedure runs slower on Azure SQL Database than on-premises. Both have identical indexes and statistics. What is the most likely cause?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

The version of the cardinality estimator differs between environments.. The cardinality estimator (CE) version can differ between on-premises SQL Server and Azure SQL Database, even with identical indexes and statistics. The CE version is determined by the database compatibility level, and Azure SQL Database may have a different default compatibility level than on-prem, leading to different query plans and performance. Option B is incorrect because DTU vs vCore is a purchasing model that does not inherently slow queries; it affects resource limits but not plan choices. Option C is incorrect because automatic tuning in Azure SQL Database typically helps performance, and index fragmentation levels would be similar with identical indexes. Option D is incorrect because intelligent query processing features are available on both platforms and generally beneficial, not detrimental.

Answer

Azure SQL Database uses a different resource governance model (DTU vs vCore).

Answer

The index fragmentation level is higher on Azure SQL Database due to automatic tuning.

Answer

Azure SQL Database has intelligent query processing features that are not available on-premises.

Question 2

Your Azure SQL Database is experiencing high CPU usage. You suspect a specific query is causing the issue. You have enabled Query Store. How can you identify the query that has consumed the most cumulative CPU time over the last hour?

Accepted Answer

Use the Query Store Top Resource Consuming Queries report and filter by the last hour.. The Query Store Top Resource Consuming Queries report is purpose-built to rank queries by resource consumption (CPU, duration, execution count, etc.) over a selectable time window, including the last hour. It aggregates runtime statistics persisted by Query Store, so you can directly identify the query with the highest cumulative CPU time in the specified interval without writing DMV queries. This is the fastest, most accurate path for the stated scenario.

Answer

Use sys.dm_exec_query_stats to sort by total_worker_time.

Answer

Use sys.dm_exec_requests to find queries with high CPU.

Answer

Use the Query Store Regressed Queries report.

Question 3

Your Azure SQL Database has a recurring job that rebuilds indexes weekly. After a recent change, the job is taking much longer to complete. You suspect that the index fragmentation is higher than usual. What is the most efficient way to check index fragmentation across the database?

Accepted Answer

Use the sys.dm_db_index_physical_stats dynamic management function.. sys.dm_db_index_physical_stats is the dedicated DMV that returns fragmentation data (avg_fragmentation_in_percent, page_count, fragment_count) for indexes and heaps. It can be scoped to a specific database, table, or index, and supports LIMITED, SAMPLED, and DETAILED scan modes, making it the most efficient and accurate way to assess fragmentation across an entire database before deciding on REORGANIZE vs REBUILD.

Answer

Query sys.indexes to check the fragmentation percentage.

Answer

Use SET SHOWPLAN_XML ON and run sample queries.

Answer

Use the sys.dm_db_missing_index_details DMV.

Question 4

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the Business Critical service tier. Which THREE configuration changes can improve query performance for reporting queries without significantly impacting OLTP operations?

Accepted Answer

Create nonclustered columnstore indexes on large reporting tables.. Option B is correct because nonclustered columnstore indexes use column-based compression and batch-mode execution, which dramatically speeds up the large scans and aggregations typical of reporting queries while remaining a secondary index that does not alter OLTP rowstore access paths. Option C is correct because Business Critical supports read-scale out via a built-in readable secondary replica (ApplicationIntent=ReadOnly), letting reporting queries run on a separate copy of the data so they consume no primary compute and do not contend with OLTP transactions. Option E is correct because result set caching in Azure SQL Database caches the full result set of identical, deterministic queries, so repeated reporting queries return instantly without re-executing against the primary, reducing resource pressure on OLTP. Option A is not appropriate because raising MAXDOP to 8 increases parallelism and CPU consumption, which can degrade OLTP responsiveness rather than isolate reporting load. Option D is not appropriate because disabling automatic tuning removes beneficial features such as automatic plan correction and index creation, and does nothing to improve reporting performance.

Answer

Increase MAXDOP for the database to 8.

Answer

Disable automatic tuning to prevent plan changes.

Question 5

You are monitoring an Azure SQL Database that is experiencing high DTU usage. Which TWO metrics should you examine to determine whether the bottleneck is CPU or I/O?

Accepted Answer

Log write bytes per second.. Option B (Average CPU percentage) is correct because it directly shows how much of the database's CPU capacity is being consumed, so a high value indicates the bottleneck is CPU-bound rather than I/O-bound. Option A (Log write bytes per second) is correct because it measures the rate of write activity to the transaction log, which is a key I/O indicator; elevated log write throughput points to an I/O bottleneck on the log device. Option C (Number of deadlocks per second) reflects concurrency/blocking problems, not whether the bottleneck is CPU or I/O. Option D (Used storage space in GB) is a capacity metric and does not indicate CPU versus I/O pressure. Option E (Number of active sessions) shows connection concurrency but does not distinguish CPU from I/O saturation.

Answer

Number of deadlocks per second.

Answer

Used storage space in GB.

Answer

Number of active sessions.

Question 6

Refer to the exhibit. You are analyzing query performance using sys.dm_exec_query_stats. Based on the output, which query is the best candidate for optimization to reduce overall CPU usage?

Accepted Answer

Query 2 because it has the highest average CPU per execution.. Query 2 is the best candidate for optimization because it has the highest average CPU per execution, indicating that each run of this query consumes significantly more CPU resources than the others. Reducing the CPU cost per execution for this query will yield the greatest per-execution savings, making it the most efficient target for reducing overall CPU usage.

Answer

Query 1 because it has the highest total_cpu_time.

Answer

All queries should be optimized equally.

Answer

Query 3 because it runs most frequently.

Question 7

Your organization runs a critical e-commerce application on Azure SQL Database. You notice that during peak hours, query performance degrades significantly. You have enabled Intelligent Insights and Query Store. Which diagnostic metric would you analyze first to identify the root cause of the performance degradation?

Accepted Answer

DTU/CPU consumption. DTU/CPU consumption is the first metric to check because it directly indicates whether the database is hitting its resource ceiling during peak load, which is the most common cause of Azure SQL performance degradation. If DTU or CPU is saturated near 100%, the bottleneck is resource contention and you then drill into Query Store to find the specific offending queries. This metric quickly confirms or rules out resource exhaustion before deeper analysis.

Answer

Query store wait statistics

Answer

Deadlock count

Answer

Failed connections

Question 8

Your company uses Azure SQL Database with the Hyperscale service tier. You notice that index maintenance operations are taking longer than expected. What is the most likely reason for this performance issue?

Accepted Answer

The log write throughput is insufficient. In Azure SQL Database Hyperscale, index rebuild operations generate a large volume of log records. Although Hyperscale uses a distributed architecture with fast log commit, the log write throughput per service objective is finite and can become a bottleneck during intensive write operations like index rebuilds. This is the most common cause of slow index maintenance. Option A (General Purpose tier) is irrelevant as the question specifies Hyperscale. Option B (page compression) does not significantly impact index rebuild duration. Option D (simple recovery) is not the cause; even with simple recovery, the log write throughput during rebuild can be insufficient.

Answer

The database is using the General Purpose tier

Answer

The index is using page compression

Answer

The transaction log is set to simple recovery

Question 9

Your company plans to migrate a large on-premises SQL Server database to Azure SQL Managed Instance. The database uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. How should you manage the TDE protector after migration to ensure minimal administrative overhead and high availability?

Accepted Answer

Use Azure Key Vault to store the TDE protector. Using Azure Key Vault to store the TDE protector provides centralized management, automatic key rotation, and high availability without manual intervention. Option B is incorrect because service-managed keys offer less control and are not recommended for scenarios requiring bring your own key (BYOK) compliance. Option C is incorrect because SQL Managed Instance does not support storing TDE certificates directly in the instance; instead, you can use Azure Key Vault to bring your own key. Option D is incorrect because TDE cannot be disabled after migration without decrypting the database, and Always Encrypted is a different feature for column-level encryption, not a replacement for TDE.

Answer

Use the service-managed TDE key provided by Azure

Answer

Bring your own certificate and store it in the managed instance

Answer

Disable TDE after migration and use Always Encrypted instead

Question 10

Your Azure SQL Database is experiencing high CPU utilization due to frequent recompilations of a stored procedure. Which TWO actions should you take to reduce recompilations?

Accepted Answer

Use the KEEPFIXED PLAN query hint. Option B is correct because the KEEPFIXED PLAN query hint forces the optimizer to reuse the cached plan and prevents recompilation caused by statistics changes or schema changes, directly reducing recompilations. Option E is correct because when parameter data types do not match the underlying column data types, SQL Server may perform implicit conversions that prevent plan reuse and trigger recompilations; ensuring matching data types promotes plan cache reuse. Option A is incorrect because sp_refreshsqlmodule updates metadata for a module and can actually invalidate cached plans, potentially increasing recompilations. Option C is incorrect because OPTIMIZE FOR UNKNOWN addresses parameter sniffing by using average statistics, but it does not prevent recompilations. Option D is incorrect because the RECOMPILE query hint forces a new plan to be generated on every execution, which increases rather than reduces recompilations.

Answer

Execute sp_refreshsqlmodule on the stored procedure

Answer

Use the OPTIMIZE FOR UNKNOWN query hint

Answer

Use the RECOMPILE query hint

Question 11

You are troubleshooting a performance issue on an Azure SQL Database. Which THREE Dynamic Management Views (DMVs) should you query to identify the most resource-intensive queries?

Accepted Answer

sys.dm_exec_requests. The correct answers are A, C, and E. Option A (sys.dm_exec_requests) shows currently executing requests, which helps identify active resource-intensive queries. Option C (sys.dm_exec_query_plan) provides the execution plan for cached queries, enabling analysis of query performance. Option E (sys.dm_exec_query_stats) returns aggregated performance statistics (e.g., CPU time, logical reads) for cached query plans, making it ideal for identifying resource-intensive queries across all executions. Option B (sys.dm_os_wait_stats) is incorrect because it shows instance-level wait statistics, not query-specific resource usage. Option D (sys.dm_exec_sessions) is incorrect as it provides session-level information, not query-level resource metrics.

Answer

sys.dm_os_wait_stats

Answer

sys.dm_exec_sessions

Question 12

Your Azure SQL Managed Instance is experiencing high latency for write transactions. You have identified that log write latency is the bottleneck. The instance uses Premium SSD with 5000 IOPS and 200 MB/s throughput. You observe that the log file is 500 GB and has grown significantly. What is the most likely cause and solution?

Accepted Answer

The disk is not fast enough for random writes; switch to Ultra Disk.. The log write latency bottleneck on Azure SQL Managed Instance with Premium SSD is most likely due to the disk's IOPS/throughput limits being insufficient for the write workload. Premium SSD has higher latency than Ultra Disk, especially for random writes. Ultra Disk provides sub-millisecond latency and can be scaled independently for IOPS and throughput, making it the correct solution for log write latency. The log file size itself is not the cause; it's the disk performance.

Answer

Increase the log file size to allow better write performance.

Answer

The instance has insufficient CPU; scale up the managed instance.

Answer

The log file is too large causing fragmentation; shrink it to reduce latency.

Question 13

Your Azure SQL Database is experiencing deadlocks. You have enabled deadlock graphs in the extended events session. After capturing a deadlock, you need to analyze it to determine which queries are involved. What should you use?

Accepted Answer

Open the deadlock graph file in SQL Server Management Studio (SSMS).. Deadlock graphs captured by the system_health or a custom Extended Events session are stored as XML, and SQL Server Management Studio can open the .xdl file and render it as a visual graph showing the two (or more) sessions, the resources they hold, and the queries involved. This visual representation makes it straightforward to identify the victim query and the conflicting statements. SSMS is the standard tool for analyzing deadlock graphs.

Answer

Query sys.dm_exec_requests with a filter on blocking.

Answer

Azure Monitor for SQL and view deadlock metrics.

Answer

Query Store and review the regressed queries.

Question 14

Your Azure SQL Database is hitting the DTU limit frequently during business hours. You need to identify which queries are consuming the most DTU. What should you do?

Accepted Answer

Query sys.dm_exec_query_stats and correlate with sys.dm_db_resource_stats.. Querying sys.dm_exec_query_stats and correlating with sys.dm_db_resource_stats allows you to identify which specific queries are consuming the most DTU. sys.dm_db_resource_stats provides DTU consumption data at the database level, while sys.dm_exec_query_stats gives query-level performance metrics. By joining these two DMVs, you can pinpoint the top DTU-consuming queries during business hours.

Answer

Query sys.dm_db_resource_stats to see DTU usage per minute.

Answer

Use Azure Metrics for SQL Database to view DTU consumption per database.

Answer

Enable Query Store and review the Wait Statistics report.

Question 15

You are migrating an on-premises SQL Server database to Azure SQL Database. The database uses Service Broker for asynchronous messaging. After migration, you notice that performance is degraded. What should you do to optimize?

Accepted Answer

Replace Service Broker with Azure Queue Storage or Event Grid.. Service Broker is not fully supported in Azure SQL Database; it has performance and reliability limitations. The recommended approach for asynchronous messaging is to use Azure-native services like Queue Storage or Event Grid. Therefore, the correct action is to replace Service Broker (Option C). Option A is incorrect because while ENABLE_BROKER can be used, it is not recommended for production due to poor performance. Option B is incorrect because scaling up does not resolve the fundamental feature incompatibility. Option D is incorrect because external activation via Azure Functions does not address the root cause and adds complexity.

Answer

Enable Service Broker in Azure SQL Database by setting ENABLE_BROKER.

Answer

Scale up the database to a higher service tier to improve Service Broker performance.

Answer

Configure Service Broker to use external activation via Azure Functions.