DP-300 Practice Test 28 — 15 Questions

Question 1

Your organization runs a critical application on Azure SQL Managed Instance. You notice that the `tempdb` database is experiencing contention, leading to PAGELATCH_EX waits. The managed instance has 16 vCores and is using the General Purpose service tier. You need to reduce `tempdb` contention without altering the application code or changing the service tier. What should you do?

Accepted Answer

Add 16 tempdb data files of equal size, matching the number of vCores.. Option D is correct because increasing the number of tempdb data files to match the number of vCores (or slightly higher) reduces allocation contention. Option A is wrong because memory-optimized tempdb metadata is for system metadata, not data pages. Option B is wrong because it only adds more files, but optimal number is vCores. Option C is wrong because accelerated database recovery does not address tempdb contention.

Answer

Enable memory-optimized tempdb metadata to reduce allocation contention.

Answer

Add 4 additional tempdb data files of equal size.

Answer

Enable accelerated database recovery to reduce transaction log overhead.

Question 2

Which TWO metrics in Azure SQL Database's Intelligent Insights can indicate a performance degradation due to increased resource consumption? (Choose two.)

Accepted Answer

Increased query duration.. Options A and B are correct. High DTU consumption and increased query duration are direct indicators of performance issues. Option C is a security metric. Option D is a connectivity metric. Option E is an availability metric.

Answer

High number of deadlocks.

Answer

Increased transaction log usage.

Answer

Failed authentication attempts.

Question 3

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

Accepted Answer

Enable row or page compression on large tables.. Options A and C are correct. Enabling data compression reduces data size, lowering storage costs. Moving unused data to Azure Blob Storage using Elastic Database Jobs and external tables can also reduce costs. Option B is wrong because automatic tuning does not affect storage. Option D is wrong because increasing service tier increases cost. Option E is wrong because changing backup retention does not reduce storage for data files.

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 4

You need to configure monitoring for an Azure SQL Database to meet the following requirements: 
- Alert when average DTU consumption exceeds 90% for 10 minutes.
- Track failed logins.
- Analyze query performance over the last 30 days.
Which THREE Azure services or features should you use? (Choose three.)

Accepted Answer

SQL auditing. Option A correct because Azure Monitor alerts can alert on DTU. Option C correct because audit logs track failed logins. Option D correct because Query Store retains query performance data. Option B wrong because SQL Assessment checks best practices, not performance. Option E wrong because Autoscale is not manual monitoring.

Answer

Automatic tuning

Answer

Azure SQL Assessment

Question 5

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 can reduce logical reads by enabling seeks instead of scans. Updating statistics helps the optimizer choose efficient plans. Option A and C are correct. Option B is wrong because increasing memory does not reduce logical reads directly. Option D is wrong because rewriting as a stored procedure 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 6

You are configuring workload management for an Azure SQL Database using the JSON exhibit above for a classifier named 'MyWorkloadClassifier'. The classifier is intended to assign high importance to queries from user 'User1' in the 'SalesDB' database. However, after deployment, you notice that queries from 'User1' are not getting the expected resource guarantees. What is the most likely reason?

Accepted Answer

The 'memberName' in the context is not correctly formatted; it should be a single user or group name without a backslash.. Option C is correct because the 'context' should be 'memberName' as a string, but the value has a backslash which is not escaped in JSON; also the classifier name is not referenced in the context correctly. Option A is wrong because importance is correctly set. Option B is wrong because resource percentiles are valid. Option D is wrong because the classifier is not a built-in role.

Answer

The 'importance' property is set to 'high' but the classifier requires 'importance' to be an integer.

Answer

The 'min_percentile_resource' value is too low to guarantee resources.

Answer

The classifier is not associated with a workload group.

Question 7

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 DTU percentage and CPU percentage are standard metrics for autoscaling. Option B is wrong because deadlocks are an event, not a continuous metric. Option C is wrong because log write throughput is not typically used for autoscale. Option E is wrong because Query Store is not a metric source.

Answer

Log write throughput

Answer

Deadlock count

Answer

Query Store size

Question 8

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

Accepted Answer

Increase the log rate limit by scaling up the service level objective. Increasing the log rate limit allows more transactions per second. Option D is correct. Option A is wrong because Hyperscale does not have compute replicas for log rate. Option B is wrong because changing backup retention does not affect log rate. Option C is wrong because increasing max degree of parallelism may not reduce log generation.

Answer

Increase max degree of parallelism

Answer

Reduce backup retention period

Answer

Add more compute replicas

Question 9

You are monitoring an Azure SQL Database using Intelligent Insights. The built-in intelligence detects a performance issue and suggests a specific index to create. The database is running the Business Critical service tier. You want to automatically implement this recommendation without manual intervention. What should you configure?

Accepted Answer

Enable automatic tuning for 'CREATE INDEX' in the Azure portal.. Option A is correct because Azure SQL Database's automatic tuning can automatically implement index recommendations. Option B is wrong because automatic tuning for plan correction is separate. Option C is wrong because it only provides recommendations. Option D is wrong because Query Store is a monitoring tool, not an automatic tuning feature.

Answer

Enable automatic tuning for 'FORCE LAST GOOD PLAN' in the Azure portal.

Answer

Set up Query Store to capture the recommended index execution.

Answer

Configure Azure Advisor to email you the recommendation.

Question 10

Which THREE actions can you take to monitor and optimize database resources in Azure SQL Database? (Choose three.)

Accepted Answer

Use Query Store to track query performance over time.. Options A, C, and D are correct. Query Store, automatic tuning, and dynamic management views are core monitoring and optimization tools. Option B is for security. Option E is for threat protection.

Answer

Enable Microsoft Defender for Azure SQL to detect vulnerabilities.

Answer

Configure Microsoft Sentinel to monitor database activity.

Question 11

Which THREE factors should you consider when choosing between a Gen5 and a Premium-series hardware configuration for an Azure SQL Database? (Select three.)

Accepted Answer

Memory-to-vCore ratio.. Options A, B, and D are correct. Gen5 and Premium-series differ in IOPS, memory-to-vCore ratio, and compute generation. Option C is wrong because vCore count is not a factor; it's independent of hardware series. Option E is wrong because both support the same service tiers.

Answer

Number of vCores available.

Answer

Support for Business Critical tier.

Question 12

You need to monitor the performance of a set of Azure SQL Databases from a single dashboard. Which Azure service should you use?

Accepted Answer

Azure SQL Analytics. Azure SQL Analytics provides a unified monitoring solution for multiple Azure SQL databases. Option C is correct. Option A is wrong because Query Performance Insight is per-database. Option B is wrong because Azure Monitor Workbooks can be used but require manual configuration. Option D is wrong because Azure Data Studio is a client tool.

Answer

Azure Monitor Workbooks

Answer

Query Performance Insight

Answer

Azure Data Studio

Question 13

Your company uses Azure SQL Database with Active Geo-Replication for disaster recovery. During a routine failover drill, you observe that after failover to the secondary region, the application experiences significantly higher latency for write operations. The secondary database is in a different Azure region and has the same service objective. What is the most likely cause of the increased write latency?

Accepted Answer

The secondary database does not have a local read-scale replica configured.. Option B is correct because geo-replicated secondaries are readable and do not accept write traffic until failover; after failover, the new primary has no geo-secondary configured initially, so writes are not replicated asynchronously, but latency is likely due to the lack of a local secondary. Option A is wrong because service objective is same. Option C is wrong because geo-replication delay affects read scalability, not write latency. Option D is wrong because the secondary is readable but not writable before failover.

Answer

Geo-replication introduces additional latency for all write operations.

Answer

The secondary database has a lower service objective than the primary.

Answer

The secondary database is not configured to accept write traffic.

Question 14

You are analyzing the exhibit KQL query that queries Azure Diagnostics logs for Query Store runtime statistics. The query is intended to show average CPU time per hour for each database. However, the result shows no data for the last 24 hours, although Query Store is enabled on all databases. What is the most likely reason?

Accepted Answer

The diagnostic settings are not configured to send QueryStoreRuntimeStatistics to Log Analytics.. Option B is correct because Query Store runtime statistics are sent to Azure Diagnostics only if diagnostic settings are configured to collect that category. Option A is wrong because Query Store is enabled. Option C is wrong because the query is correct. Option D is wrong because the query does not have time filter.

Answer

Query Store is not enabled on the databases.

Answer

The time range in the query is too narrow and excludes the last 24 hours.

Answer

The query syntax is incorrect and needs to use 'project' before 'summarize'.

Question 15

You manage a SQL Server on Azure VM. Users report slow queries during peak hours. You suspect memory pressure. Which metric should you monitor in Azure Monitor to confirm this?

Accepted Answer

Target Server Memory (KB). Option B is correct because Target Server Memory (KB) shows the amount of memory SQL Server is trying to acquire; consistently high values relative to Total Server Memory indicate memory pressure. Option A is wrong because Buffer Cache Hit Ratio indicates cache efficiency but not directly memory pressure. Option C is wrong because Page Life Expectancy is affected by memory but is a secondary indicator. Option D is wrong because Batch Requests/sec measures throughput, not memory.

Answer

Page Life Expectancy

Answer

Batch Requests/sec

Answer

Buffer Cache Hit Ratio