DP-300 Practice Test 12 — 15 Questions

Question 1

You are configuring automatic tuning for an Azure SQL Database. Which THREE recommendations can be applied automatically without manual approval?

Accepted Answer

FORCE LAST GOOD PLAN. Options A, C, and D are correct. Automatic tuning for Azure SQL Database can automatically apply FORCE LAST GOOD PLAN, CREATE INDEX, and DROP INDEX recommendations. Option B is incorrect because modifying statistics is not an automatic tuning option; it requires manual execution. Option E is incorrect because enabling database compression is not part of automatic tuning.

Answer

Modify statistics

Answer

Enable database compression

Question 2

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. This DMV provides fragmentation details (e.g., avg_fragmentation_percent) for all indexes in a database with minimal overhead when using limited scanning. Option A (sys.indexes) does not include fragmentation statistics. Option C (SET SHOWPLAN_XML ON) shows query execution plans, not index fragmentation. Option D (sys.dm_db_missing_index_details) suggests new indexes to improve performance, but does not report existing index fragmentation.

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 3

Which THREE actions can help reduce the frequency of parameter-sensitive plan (PSP) problems in Azure SQL Database? (Choose three.)

Accepted Answer

Use the Optimize for ad hoc workloads setting (or OPTIMIZE FOR UNKNOWN).. Options A, C, and D are correct. Option A (Optimize for ad hoc workloads or OPTIMIZE FOR UNKNOWN) helps by using average distribution instead of sniffing parameter values, reducing parameter-sensitive plan (PSP) problems. Option C (RECOMPILE hint) forces a new plan per execution, avoiding stale plans caused by parameter sniffing. Option D (forced parameterization) creates multiple cached plans for different parameter values, directly addressing PSP. Option B (disabling parameter sniffing) is not a recommended approach and can lead to suboptimal plans. Option E (Query Store) is a monitoring tool that captures query data but does not directly reduce PSP frequency.

Answer

Disable parameter sniffing by using the DISABLE_PARAMETER_SNIFFING hint.

Answer

Enable Query Store and use the Performance Dashboard.

Question 4

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 5

You are a database administrator for a large e-commerce platform using Azure SQL Database. You notice that a specific query frequently causes high CPU usage during peak hours. The query is a SELECT with multiple JOINs and a WHERE clause on a non-clustered index. You have already updated statistics and rebuilt indexes. What should you do next to optimize performance?

Accepted Answer

Use Query Store to identify and force a better execution plan.. Query Store can identify query performance regressions and provide plan forcing. Option B is wrong because automatic tuning might not address the specific query without Query Store data. Option C is wrong because index tuning may not help if the query plan is suboptimal. Option D is wrong because read replicas offload read traffic but do not optimize CPU usage of a single query on the primary.

Answer

Enable automatic tuning to let Azure SQL Database handle the issue.

Answer

Add more indexes on the columns used in JOINs and WHERE clause.

Answer

Create a read replica and offload the query to it.

Question 6

Which TWO database properties can be configured to optimize performance for a read-heavy workload in Azure SQL Database?

Accepted Answer

Query Store. For a read-heavy workload in Azure SQL Database, enabling Query Store (option A) helps monitor query performance, identify regressions, and optimize query plans. Read scale-out (option E) offloads read-only queries to a readable secondary replica, reducing load on the primary and improving performance. Options B (Data masking) is a security feature, C (Long-term retention policy) is for backup retention, and D (Automatic tuning - CREATE INDEX) creates indexes but is not specifically for read-heavy workloads; it can help but is not a primary property for read offload.

Answer

Data masking

Answer

Long-term retention policy

Answer

Automatic tuning (CREATE INDEX)

Question 7

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is consistently high, causing performance issues. Which metric should you monitor to identify the log generation rate?

Accepted Answer

Log IO percent. 'Log IO percent' in Azure SQL Database reflects the log generation rate, which is critical for monitoring in the Hyperscale tier. Option B is incorrect because 'DTU used' is specific to DTU-based tiers, not Hyperscale. Option C is incorrect because 'CPU percent' measures processor usage, not log rate. Option D is incorrect because 'Data IO percent' measures data file I/O, not log I/O.

Answer

DTU used

Answer

CPU percent

Answer

Data IO percent

Question 8

You are troubleshooting a performance degradation on an Azure SQL Database. You notice that the database is hitting the maximum DTU limit frequently. Which action should you take first to reduce DTU consumption?

Accepted Answer

Use Query Performance Insight to identify and optimize top resource-consuming queries. Query Performance Insight helps identify the top resource-consuming queries. Optimizing those queries is the most direct way to reduce DTU consumption. Option A is wrong because increasing the log rate limit does not reduce DTU consumption. Option B is wrong because scaling up increases cost and may not address the root cause. Option D is wrong because rebuilding all indexes can increase DTU temporarily.

Answer

Increase the log rate limit

Answer

Scale up the database to a higher service tier

Answer

Rebuild all indexes in the database

Question 9

Which TWO actions can you take to optimize query performance in Azure SQL Database without changing the application code? (Choose two.)

Accepted Answer

Create missing indexes identified by the Database Engine Tuning Advisor.. Creating missing indexes (option D) and updating statistics (option E) are performance optimization techniques that do not require changes to application code. Indexes recommended by the Database Engine Tuning Advisor can reduce query execution time by enabling efficient data access paths, while updating statistics helps the query optimizer generate better execution plans. Options A and B require code changes or direct query modifications, option C is a security feature that may impact performance but not optimize it, and upgrading the service tier (option B) is a scaling action that increases resources but is not a targeted query optimization.

Answer

Rewrite queries to use sargable predicates.

Answer

Upgrade to a higher service tier.

Answer

Enable row-level security on sensitive tables.

Question 10

You are designing a database monitoring solution for a critical Azure SQL Database. You need to retain performance data for 90 days and receive alerts when DTU usage exceeds 80%. Which combination of tools should you use?

Accepted Answer

Azure SQL Analytics (now integrated in Azure Monitor) and configure alerts.. Azure SQL Analytics (now part of Azure Monitor) provides long-term retention and alerting capabilities. Option A is wrong because Query Store default retention is limited. Option B is wrong because DMVs only show current state and are not designed for long-term retention and alerting. Option D is wrong because Intelligent Insights provides analysis but not configurable retention.

Answer

Query Store with retention set to 90 days.

Answer

DMVs queried by a custom script.

Answer

Intelligent Insights and configure email notifications.

Question 11

You are monitoring an Azure SQL Database and notice that the average CPU usage is consistently above 90%. The database is using the S3 service tier. What should you do first to resolve this performance issue?

Accepted Answer

Scale up to a higher service tier. Scaling up to a higher service tier (e.g., S4) provides more CPU resources, which directly addresses the high CPU usage. Option D is correct. Option A is incorrect because enabling read scale-out does not increase CPU capacity. Option B is incorrect because automatic tuning may provide recommendations, but it is not the first action to take for immediate CPU pressure. Option C is incorrect because scaling down to a lower tier would reduce CPU resources, making the problem worse.

Answer

Enable read scale-out

Answer

Wait for Automatic tuning to provide recommendations

Answer

Scale down to a lower service tier

Question 12

Which TWO actions can reduce storage costs for an Azure SQL Database? (Select two.)

Accepted Answer

Enable row or page compression on large tables.. To reduce storage costs for an Azure SQL Database, you can enable row or page compression on large tables (option B) to decrease data size. Archiving historical data to Azure Blob Storage using external tables (option E) moves cold data out of the database, reducing storage consumption. Option A is incorrect because increasing the service tier typically increases storage costs. Option C is wrong because reducing backup retention does not affect storage used for data files, only backup storage. Option D is incorrect because automatic tuning optimizes query performance, not storage costs. Therefore, the correct answers are B and E.

Answer

Increase the service tier to get more storage.

Answer

Reduce backup retention to 1 day.

Answer

Enable automatic tuning to optimize query plans.

Question 13

You need to configure alerts for an Azure SQL Database to notify the operations team when the database exceeds 80% DTU consumption for more than 10 minutes. What should you use?

Accepted Answer

Create a metric alert in Azure Monitor. Azure Monitor can create metric alerts based on DTU consumption. Option D is correct. Option A is wrong because SQL Agent cannot send alerts based on DTU. Option B is wrong because Azure SQL Auditing is for compliance. Option C is wrong because Azure Advisor provides recommendations but not real-time alerts.

Answer

Configure a SQL Agent alert

Answer

Configure Azure SQL Auditing

Answer

Use Azure Advisor recommendations

Question 14

You are tuning a query in Azure SQL Database. Which TWO actions can reduce logical reads?

Accepted Answer

Create a nonclustered index on the columns used in WHERE clause. Creating appropriate indexes, such as a nonclustered index on columns used in WHERE clauses, can reduce logical reads by enabling index seeks instead of full table scans. Updating statistics helps the query optimizer choose efficient execution plans, potentially reducing logical reads. Adding query hints may force an index but does not guarantee reduced reads and can be counterproductive. Rewriting as a stored procedure does not directly reduce logical reads. Increasing database max memory does not reduce logical reads.

Answer

Add query hints to force index usage

Answer

Rewrite the query as a stored procedure

Answer

Increase the database max memory setting

Question 15

Which TWO metrics are available in Azure Monitor for an Azure SQL Database that can be used to set autoscale rules? (Select two.)

Accepted Answer

CPU percentage. Options A and D are correct because CPU percentage and DTU percentage are standard metrics for autoscaling Azure SQL Database. Option B is wrong because Log write throughput is not typically used for autoscale rules. Option C is wrong because Deadlock count is an event, not a continuous metric. Option E is wrong because Query Store size is not a metric source for autoscaling.

Answer

Log write throughput

Answer

Deadlock count

Answer

Query Store size