DP-300 Practice Test 5 — 25 Questions

Question 1

You manage an Azure SQL Database that uses automatic tuning. The database has the FORCE_LAST_GOOD_PLAN option enabled. A critical query suddenly starts performing poorly after a plan change. What is the expected behavior?

Accepted Answer

The database will automatically revert to the last good plan and log the event in sys.dm_db_tuning_recommendations.. When FORCE_LAST_GOOD_PLAN is enabled and automatic tuning detects a plan regression, Azure SQL Database automatically reverts to the last known good plan. The event is logged in sys.dm_db_tuning_recommendations for audit and analysis. This behavior is part of the automatic plan correction feature, which does not require manual intervention.

Answer

The query will continue with the poor plan until you manually force a plan.

Answer

You will receive a recommendation to revert the plan change.

Answer

The database will automatically revert to the last good plan without notification.

Question 2

You run the KQL query shown in the exhibit. The query returns a list of query IDs. What is the purpose of this query?

Accepted Answer

Identify the top 10 queries by average duration.. The KQL query in the exhibit uses `summarize` with `avg(duration)` and then `top 10 by avg_duration`, which explicitly calculates the average duration per query_id and returns the top 10 queries with the highest average duration. This directly matches the purpose of identifying the top 10 queries by average duration.

Answer

Identify queries consuming the most CPU.

Answer

Identify queries with the highest total wait time.

Answer

Identify queries with the highest execution count.

Question 3

You are configuring automatic tuning for an Azure SQL Database. The database has a heavy OLTP workload. You want to automatically correct query plan choice regressions without manual intervention. Which automatic tuning option should you enable?

Accepted Answer

FORCE_LAST_GOOD_PLAN. Option D, FORCE_LAST_GOOD_PLAN, is the correct automatic tuning option for Azure SQL Database to automatically correct query plan choice regressions. When the database engine detects that a newly compiled query plan performs worse than the previously known good plan, it can automatically force the last known good plan without manual intervention, which is ideal for a heavy OLTP workload where performance stability is critical.

Answer

DROP_INDEX

Answer

CREATE_INDEX

Answer

CORRECT_INDEX

Question 4

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is consistently high. You need to reduce the log write latency. What should you do?

Accepted Answer

Increase the log rate limit in the Hyperscale service tier. In the Hyperscale service tier, log write latency is directly influenced by the log rate limit. Increasing this limit allocates more I/O bandwidth to the log write path, reducing latency under high log generation. This is the correct action because the log rate limit is a configurable parameter in Hyperscale that controls how fast log records can be written to the log service.

Answer

Enable data compression on the tables

Answer

Enable Accelerated Database Recovery

Answer

Change the service tier to General Purpose

Question 5

You have a SQL Managed Instance that hosts a critical OLTP database. You notice that the average query wait time has increased significantly over the past hour. You need to identify the top resource waits. What should you use?

Accepted Answer

sys.dm_os_wait_stats. C is correct because sys.dm_os_wait_stats is the dynamic management view that aggregates wait statistics across all sessions in the SQL Server instance, including SQL Managed Instance. It provides cumulative wait times categorized by wait type (e.g., PAGEIOLATCH, LCK_M_S), making it the appropriate tool to identify top resource waits when average query wait time increases.

Answer

sys.dm_exec_query_stats

Answer

Query Store Wait Stats in SSMS

Answer

sys.dm_db_index_usage_stats

Question 6

You have an Azure SQL Database that is experiencing performance degradation. You suspect that parameter sniffing is causing suboptimal execution plans. What should you do to mitigate this issue without changing application code?

Accepted Answer

Enable forced parameterization for the database.. Forced parameterization (option D) is the correct mitigation because it instructs the SQL Server query optimizer to treat all literal values in queries as parameters, which reduces the likelihood of parameter sniffing causing suboptimal plans for repeated queries. This is done at the database level without requiring any changes to application code, making it ideal for scenarios where code modification is not feasible.

Answer

Add the OPTION (RECOMPILE) query hint to all queries.

Answer

Clear the plan cache using DBCC FREEPROCCACHE.

Answer

Enable the 'Optimize for Ad Hoc Workloads' setting.

Question 7

You are configuring a private endpoint for an Azure SQL Database. The exhibit shows the current network ACLs. You need to ensure that only traffic from a specific subnet in VNet1 is allowed, and all other traffic is denied. What should you do?

Accepted Answer

No changes needed; the configuration already meets the requirement.. The exhibit shows that the private endpoint is configured with a network ACL that has a deny-all default action and an explicit allow rule for the specific subnet in VNet1. Since private endpoints use network policies (like NSG rules) to filter traffic, and the ACL already denies all traffic except the allowed subnet, no changes are needed. The configuration meets the requirement because the private endpoint's network ACLs are evaluated in order, and the explicit allow for the subnet overrides the default deny for all other traffic.

Answer

Add an IP rule to allow the subnet's IP range.

Answer

Set ignoreMissingVnetServiceEndpoint to true.

Answer

Change defaultAction to Allow.

Question 8

You are monitoring an Azure SQL Database that is experiencing high DTU consumption. You need to identify the queries that are causing high resource usage. Which two data sources can you use? (Choose two.)

Accepted Answer

Query Store. Query Store (Option B) captures a history of query execution plans and runtime statistics, allowing you to identify queries with high CPU, I/O, or duration. sys.dm_exec_query_stats (Option C) returns aggregate performance statistics for cached query plans, including total CPU time and logical reads, which directly points to resource-intensive queries. Both are valid sources for diagnosing high DTU consumption in Azure SQL Database.

Answer

sys.dm_os_wait_stats

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_io_virtual_file_stats

Question 9

You are monitoring an Azure SQL Database that is running a mission-critical workload. You notice that the DTU consumption is consistently above 90% during peak hours. You need to recommend a solution to reduce the DTU consumption. What should you recommend?

Accepted Answer

Scale up to a higher service tier or increase DTUs. When DTU consumption consistently exceeds 90%, the database is resource-constrained, leading to performance degradation. Scaling up to a higher service tier or increasing DTUs directly provides more CPU, memory, and I/O resources, alleviating the bottleneck and reducing DTU utilization percentage. This is the standard corrective action for sustained high DTU usage in Azure SQL Database.

Answer

Scale down to a lower service tier

Answer

Enable geo-replication

Answer

Enable read scale-out

Question 10

You have an Azure SQL Database that is configured with geo-replication. You need to optimize read performance for reporting queries that run on the secondary replica. Which three actions should you take? (Choose three.)

Accepted Answer

Enable automatic tuning on the secondary.. Option A is correct because enabling automatic tuning on the secondary replica allows the database to automatically create, drop, and force index recommendations based on the workload running on that replica. This is particularly beneficial for reporting queries, as the secondary can independently optimize its own index strategy without affecting the primary. Automatic tuning on a readable secondary helps maintain query performance by adapting to the read-heavy workload patterns typical of reporting scenarios.

Answer

Configure the secondary as a readable secondary.

Answer

Force query plans on the primary replica to benefit the secondary.

Question 11

You are monitoring an Azure SQL Database and notice that the average CPU usage is 80% and the average data IO percentage is 70%. You need to identify the most likely cause of the high resource usage. What should you check first?

Accepted Answer

Use Query Store to identify top resource-consuming queries. High average CPU (80%) and data IO (70%) suggest that the database is under sustained load from inefficient or resource-intensive queries. Query Store captures query execution plans, runtime statistics, and resource consumption per query, making it the fastest way to pinpoint the top resource consumers. Checking Query Store first allows you to identify the specific queries driving CPU and IO, which is the most direct diagnostic step.

Answer

Check for long-running maintenance tasks

Answer

Check for connection pooling issues

Answer

Check for blocking and deadlocks

Question 12

You are monitoring an Azure SQL Database that hosts a financial application. You notice that the average DTU consumption is 20%, but occasionally spikes to 95% for 5-minute intervals. Users report slow response times during these spikes. You need to ensure consistent performance without over-provisioning resources. What should you do?

Accepted Answer

Identify and optimize the queries running during the spike periods, possibly rescheduling a heavy ETL job.. Option D is correct because the issue is not a consistent resource shortage but periodic spikes caused by specific queries, likely from a heavy ETL job. By identifying and optimizing those queries or rescheduling the job, you can eliminate the spikes without permanently scaling up resources, which would waste cost and capacity. This aligns with the DP-300 focus on performance tuning and resource optimization rather than blind scaling.

Answer

Migrate the database to the Hyperscale service tier.

Answer

Scale the database to a higher service tier to absorb the spikes.

Answer

Enable Query Store and use the Regressed Queries feature to find slow queries.

Question 13

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 14

You are tuning an Azure SQL Database workload. Which TWO actions can help reduce PAGELATCH_EX contention?

Accepted Answer

Add multiple data files to the filegroup. Adding multiple data files to the filegroup helps reduce PAGELATCH_EX contention by distributing page allocation activity across multiple files. When a single data file is used, all concurrent insert operations compete for the same allocation page (e.g., PFS, GAM, SGAM), causing exclusive latches. With multiple files, each file has its own set of allocation structures, allowing parallel allocation operations to proceed with less contention.

Answer

Increase MAXDOP

Answer

Use a columnstore index

Answer

Configure a readable secondary replica

Question 15

You have an Azure SQL Database that uses the SQL Server Agent to run a daily maintenance job. The job fails intermittently with the error 'Login failed for user'. The job uses a SQL Server authentication login. What is the most likely cause and solution?

Accepted Answer

The login password has expired; update the password in the job step.. The intermittent 'Login failed for user' error with a SQL Server authentication login strongly indicates a password expiration issue. Azure SQL Database enforces password expiration policies by default, and if the password for the SQL authentication login used by the job step has expired, the job will fail until the password is updated. This is the most likely cause because the failure is intermittent (occurring after the password expires) and the job uses SQL authentication, which is subject to password policies.

Answer

The database is in a failover group and the secondary is read-only; connect to the primary.

Answer

The job schedule is conflicting with another job; change the schedule.

Answer

The SQL Server Agent is not running; start the agent.

Question 16

You are troubleshooting a performance issue in an Azure SQL Database. You need to identify the queries that are consuming the most CPU over the last hour. Which two methods can you use? (Choose two.)

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats (Option A) returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), which can be filtered by creation_time or last_execution_time to focus on the last hour. Query Store's top resource consuming queries report (Option E) provides a built-in, graphical view of queries ranked by CPU, duration, or other metrics over a configurable time window, making it ideal for identifying high-CPU queries in the last hour.

Answer

sys.dm_os_wait_stats

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_exec_requests

Question 17

You manage an Azure SQL Database that uses a Serverless compute tier. You notice that during idle periods, the database auto-pauses and then auto-resumes when a connection is made. However, users report that the first query after a pause is slow. You need to improve the performance of the first query. What should you do?

Accepted Answer

Disable auto-pause for the serverless database. Option C is correct because the slow first query after auto-resume is caused by the cold-start latency of the serverless compute tier, which includes provisioning resources and warming the buffer pool. Disabling auto-pause ensures the database remains online and the buffer pool stays populated, eliminating the cold-start delay for the first query.

Answer

Increase the maximum vCores

Answer

Create a SQL Agent job to ping the database every hour

Answer

Enable Query Store

Question 18

Your Azure SQL Database is configured with Active Geo-Replication to a secondary region for disaster recovery. During a routine failover drill, you notice that after failover, the application cannot connect to the new primary because the login credentials fail. The logins are contained in the master database. What is the most likely cause?

Accepted Answer

The SQL logins in the master database are not replicated to the secondary server.. When Active Geo-Replication is configured for Azure SQL Database, the secondary server is a separate logical server in a different region. The master database, which contains server-level logins, is not replicated as part of geo-replication; only the user databases are replicated. Therefore, after a failover, the new primary server does not have the server-level logins from the original primary, causing authentication failures for applications using those logins.

Answer

The DNS name of the secondary server changed after failover.

Answer

The firewall rules on the secondary server do not allow connections from the application IP.

Answer

The application uses contained database users, which are not replicated.

Question 19

You are planning a migration of on-premises SQL Server databases to Azure SQL Managed Instance. The source databases use SQL Server Agent jobs, cross-database queries, and SQL Server Integration Services (SSIS) packages. Which migration approach minimizes the need to modify existing code or processes?

Accepted Answer

Migrate to Azure SQL Managed Instance and use Azure Data Factory to run SSIS packages with an Azure-SSIS integration runtime.. Azure SQL Managed Instance provides near-100% compatibility with on-premises SQL Server, including SQL Server Agent and cross-database queries. For SSIS packages, the Azure-SSIS integration runtime in Azure Data Factory allows you to lift-and-shift existing SSIS packages without rewriting them, minimizing code changes.

Answer

Migrate to SQL Server on Azure Virtual Machines and keep all existing functionality unchanged.

Answer

Migrate to Azure Database for PostgreSQL and rewrite all SQL Agent jobs as pg_cron tasks.

Answer

Migrate to Azure SQL Database and use elastic jobs to replace SQL Server Agent.

Question 20

You have an Azure SQL Database in the General Purpose tier. You need to implement high availability with automatic failover to a secondary database in a different Azure region. The solution must provide an RPO of 5 seconds and RTO of 1 hour. Which TWO components must you configure? (Select TWO.)

Accepted Answer

Configure active geo-replication. Active geo-replication (Option C) creates a readable secondary database in a different Azure region, supporting cross-region disaster recovery. When combined with an auto-failover group, it provides automatic failover with an RPO of 5 seconds and an RTO of 1 hour, meeting the requirements for a General Purpose tier database.

Answer

Change the service tier to Business Critical

Answer

Enable zone redundancy on the primary database

Answer

Create an auto-failover group