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

Question 1

You need to monitor the long-running queries in an Azure SQL Database. Which dynamic management view should you query to see queries that have been running for more than 30 seconds?

Accepted Answer

sys.dm_exec_requests. Sys.dm_exec_requests returns currently executing requests, including their start time and elapsed time, which can be used to identify queries running longer than 30 seconds. Option B (sys.dm_db_resource_stats) is incorrect because it provides resource consumption metrics (CPU, IO, memory) over time, not currently running queries. Option C (sys.dm_exec_sessions) is incorrect because it shows active sessions but does not include query execution details like elapsed time. Option D (sys.dm_exec_query_stats) is incorrect because it contains aggregated query performance statistics, not currently running queries.

Answer

sys.dm_db_resource_stats

Answer

sys.dm_exec_sessions

Answer

sys.dm_exec_query_stats

Question 2

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 3

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.

Question 4

You are troubleshooting a performance issue in Azure SQL Database. You suspect that parameter sniffing is causing suboptimal query plans. What feature can you use to mitigate this without code changes?

Accepted Answer

Set the database to FORCE_PARAMETERIZATION = ON.. FORCE_PARAMETERIZATION = ON is a database-wide setting that forces parameterization of all ad-hoc queries, helping to mitigate parameter sniffing issues without requiring code changes. Option A (reorganizing indexes) addresses fragmentation, not parameter sniffing. Option B (Query Store plan forcing) is used to pin a specific plan after a regression, not to prevent sniffing proactively. Option C (OPTIMIZE FOR UNKNOWN) is a query hint that requires modifying query code, not a code-less solution.

Answer

Reorganize indexes regularly.

Answer

Use Query Store to force a good plan.

Answer

Add OPTIMIZE FOR UNKNOWN query hint to all queries.

Question 5

Which TWO actions can help you identify and resolve performance bottlenecks related to I/O in an Azure SQL Database?

Accepted Answer

Query sys.dm_exec_requests and filter on wait_type like PAGEIOLATCH.. Options A and B are correct. A: Querying sys.dm_exec_requests with a filter on PAGEIOLATCH wait type identifies queries waiting for I/O, pinpointing I/O bottlenecks. B: Data compression reduces the size of data pages, decreasing I/O operations for large tables. C: Increasing the service tier adds compute resources (CPU/memory) but does not directly address I/O bottlenecks; it may only mask the issue. D: sys.dm_db_resource_stats provides average I/O per minute at the database level, which is useful for monitoring but not for per-query I/O bottleneck identification. E: sys.dm_os_performance_counters for CPU usage tracks CPU performance, not I/O.

Answer

Increase the database service tier to add more compute resources.

Answer

Use sys.dm_db_resource_stats to see average I/O per minute.

Answer

Monitor sys.dm_os_performance_counters for CPU usage.

Question 6

Which THREE metrics should you monitor to proactively detect potential performance issues in an Azure SQL Database?

Accepted Answer

Log IO percentage (sys.dm_db_resource_stats). Options A, D, and E are correct. Log IO percentage (A) from sys.dm_db_resource_stats indicates transaction log throughput bottlenecks. Wait statistics (D) from sys.dm_os_wait_stats show where queries are waiting, revealing contention or resource pressure. Query Store (E) tracks query plan regressions and performance degradation over time. Option B is incorrect because log backup frequency affects recovery point objectives (RPO), not proactive performance monitoring. Option C is incorrect because database size and growth rate relate to storage capacity planning, not real-time performance.

Answer

Log backup frequency

Answer

Database size and growth rate

Question 7

Which TWO options are valid methods to optimize query performance in Azure SQL Managed Instance?

Accepted Answer

Use columnstore indexes on large tables. Options A and D are correct. Columnstore indexes (A) improve analytical query performance by reducing I/O and using batch processing. Query Store (D) helps identify regressions by tracking execution plans and performance metrics. Option B is incorrect; setting database compatibility level to 150 may enable new features but is not a direct query optimization method. Option C is incorrect; increasing storage size does not improve query performance. Option E is incorrect; Transparent Data Encryption (TDE) secures data but does not enhance performance.

Answer

Set database compatibility level to 150

Answer

Increase the maximum storage size

Answer

Enable Transparent Data Encryption (TDE)

Question 8

The database 'mydb' is experiencing performance issues during peak hours. Based on the exhibit, what is the most likely cause?

Accepted Answer

The service tier S3 is not sufficient for the workload.. The exhibit indicates the database is using the S3 service tier, which provides limited DTUs (100 DTU). During peak hours, this low tier is insufficient for the workload, leading to resource contention and performance degradation. Option A is incorrect because zone redundancy is related to high availability and failover, not peak-hour performance. Option C is incorrect because read scale is used for offloading read-only workloads, not for mitigating primary performance issues. Option D is incorrect because auto-pause delay (3600 seconds) affects serverless databases in idle periods, not active peak-hour usage.

Answer

Zone redundancy is disabled, causing failover delays.

Answer

Read scale is disabled, increasing load on primary.

Answer

The database is auto-pausing frequently due to autoPauseDelay.

Question 9

You observe that the average of Maximum DTU consumption over the last hour is consistently above 90%. What should you do next?

Accepted Answer

Scale up the database to a higher service tier or increase DTU.. When average DTU consumption is consistently above 90%, it indicates high resource usage. Scaling up the database to a higher service tier or increasing DTUs directly addresses the resource pressure and improves performance. Option B is incorrect because enabling Query Store is a diagnostic tool to identify performance issues, but it does not provide immediate relief. Option C is incorrect because rebuilding indexes can help with fragmentation but may not address the underlying resource limitation. Option D is incorrect because sustained high DTU can lead to performance degradation and should be addressed.

Answer

Enable Query Store to analyze top queries.

Answer

Rebuild all indexes in the database.

Answer

Do nothing; it's normal for DTU to be high.

Question 10

You manage an Azure SQL Database that experiences periodic performance degradation. You need to identify the top queries by CPU consumption over the last hour. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_query_stats. (sys.dm_exec_query_stats) is correct because this DMV returns aggregated performance statistics for cached query plans, including total CPU time. It can be filtered to look at queries over the last hour. Option A (sys.dm_exec_sessions) shows current session-level information, not query-level CPU. Option B (sys.dm_exec_query_plan) returns the actual execution plan, not performance statistics. Option D (sys.dm_exec_requests) shows currently executing requests, not historical CPU consumption.

Answer

sys.dm_exec_sessions

Answer

sys.dm_exec_query_plan

Answer

sys.dm_exec_requests

Question 11

Your Azure SQL Managed Instance is experiencing high PAGELATCH_SH waits. You need to reduce this contention. What should you implement?

Accepted Answer

Add more data files to the filegroup. Adding more data files to the filegroup spreads out page allocations, reducing contention for allocation structures and thus decreasing PAGELATCH_SH waits. Option A is incorrect because scaling up may provide more resources but does not directly address the page latch contention caused by allocation bottlenecks. Option B is incorrect because delayed durability only affects transaction log write behavior, not page latches. Option C is incorrect because a readable secondary replica does not alleviate latch contention on the primary; it is designed for read workload offloading, not contention reduction.

Answer

Scale up the managed instance to a higher service tier

Answer

Enable delayed durability

Answer

Configure a readable secondary replica

Question 12

You are troubleshooting a performance issue on an Azure SQL Database. Query Store shows a significant increase in query duration for a specific query. The execution plan has changed from a hash join to a nested loops join. What is the most likely cause?

Accepted Answer

Stale statistics caused the optimizer to underestimate cardinality. Stale statistics can cause the optimizer to significantly underestimate cardinality. In this scenario, the optimizer mistakenly estimated a low number of rows, leading it to choose a nested loops join (optimal for small datasets) instead of a hash join (better for larger datasets). This is a classic plan regression due to statistics staleness.

Option A is incorrect: parameter sniffing typically results in a plan optimized for the initial parameter values, but it does not directly cause a cardinality underestimation that forces a switch from hash to nested loops.

Option B is incorrect: dropping an index would likely cause a table scan or a different plan (e.g., clustered index scan), but the specific change from hash join to nested loops is not characteristic of a missing index; it is more indicative of a cardinality misestimate.

Option C is incorrect: forced parameterization encourages plan reuse and can prevent plan changes, not cause them.

Answer

Parameter sniffing caused the optimizer to choose a plan for atypical parameter values

Answer

An index was dropped, forcing a table scan

Answer

Forced parameterization was enabled

Question 13

Refer to the exhibit. You apply this JSON configuration to an Azure SQL Database server. Which actions will be audited?

Accepted Answer

Only successful and failed database authentication attempts. The configuration only includes SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP. Option A is wrong because it includes actions not in the config. Option B is wrong because it includes data modification actions. Option D is wrong because it includes all actions.

Answer

All successful and failed login attempts and all schema changes

Answer

All database-level actions

Answer

All successful and failed logins and all data modification statements

Question 14

Refer to the exhibit. You retrieve the configuration of an Azure SQL Database. Based on the JSON output, which feature is enabled?

Accepted Answer

Intelligent Insights. The JSON output shows "isIntelligentInsightsOn": true, indicating that the Intelligent Insights feature is enabled for the Azure SQL Database. Option A is incorrect because "isLedgerOn" is false. Option B is incorrect because there is no mention of availability groups in the JSON. Option D is incorrect because even though "preferredEnclaveType" is set to VBS, Always Encrypted with secure enclaves requires explicit configuration on columns, and the setting alone does not mean the feature is enabled.

Answer

Ledger tables

Answer

Always On availability groups

Answer

Always Encrypted with secure enclaves

Question 15

Refer to the exhibit. You review the configuration of an Azure SQL Database. Which statement about this database is true?

Accepted Answer

The database can automatically increase storage.. The storage profile shows autogrow enabled, allowing the database to automatically increase storage when needed. Option B is wrong because geo-redundant backup storage is not enabled; it is configured as locally redundant. Option C is wrong because the high availability configuration is not zone-redundant; it uses locally redundant storage. Option D is wrong because the service tier shown is General Purpose, not Hyperscale.

Answer

Geo-redundant backup storage is enabled.

Answer

The database uses zone-redundant high availability.

Answer

The database is using the Hyperscale service tier.