DP-300 Practice Test 33 — 15 Questions

Question 1

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?

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

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 2

You manage an Azure SQL Database that is part of an Always On availability group in Azure SQL Managed Instance. You notice that the secondary replica is experiencing high log send queue size. The primary replica has ample CPU and I/O resources. The network latency between replicas is low. What is the most likely cause of the high log send queue?

Accepted Answer

The secondary replica's log apply rate is slower than the log generation rate on the primary.. The most likely cause of high log send queue is that the secondary replica's log apply rate is slower than the log generation rate on the primary (Option C). This typically occurs when the secondary replica is under-provisioned or has high I/O contention, causing it to fall behind in applying received log records. Option A (asynchronous commit) is incorrect because asynchronous commit can actually reduce log send queue pressure by not waiting for the secondary, but it does not directly cause queue buildup. Option B (log flush delays on primary) is unlikely because the primary has ample CPU and I/O resources. Option D (insufficient network bandwidth) is unlikely given that network latency is low and the primary resources are ample.

Answer

The secondary replica is configured with asynchronous commit.

Answer

The primary replica is experiencing log flush delays.

Answer

The network bandwidth between replicas is insufficient.

Question 3

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 (Azure Monitor Workbooks) is wrong because although it can be used, it requires manual configuration. Option B (Query Performance Insight) is wrong because it is per-database. Option D (Azure Data Studio) is wrong because it is a client tool.

Answer

Azure Monitor Workbooks

Answer

Query Performance Insight

Answer

Azure Data Studio

Question 4

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You run a query against `sys.dm_exec_query_stats` to find the top 10 queries by total worker time. Several queries show high worker time but low logical reads. The database is not experiencing any blocking or deadlocks. What is the most likely cause of the high worker time?

Accepted Answer

The queries are CPU-bound due to inefficient query plans.. High worker time (CPU time) with low logical reads indicates that the queries are CPU-bound rather than I/O-bound. Inefficient query plans, such as those with large hash joins, sorts, or non-sargable predicates, can cause excessive CPU consumption without generating many logical reads. Option A is incorrect because parameter sniffing typically leads to varying plan quality, but the consistent high worker time across multiple queries suggests a systematic plan efficiency issue, not necessarily parameter sniffing. Option B is incorrect because memory pressure would cause increased I/O activity (lazy writes) which is not observed with low logical reads. Option C is incorrect because transaction log writes are I/O operations and would not cause high worker time with low I/O.

Answer

The queries suffer from parameter sniffing leading to suboptimal plans.

Answer

The queries are experiencing memory pressure causing excessive lazy writes.

Answer

The queries are waiting on transaction log writes.

Question 5

You have an Azure SQL Database with automatic tuning enabled. You notice that a query that previously ran quickly is now running slower. Automatic tuning has implemented a plan correction. However, the new plan is performing worse. What should you do to revert to the previous behavior?

Accepted Answer

Revert the automatic tuning plan correction using the Azure portal or T-SQL. When automatic tuning implements a plan correction that performs worse, you can manually revert the plan correction using the Azure portal or T-SQL commands (e.g., `ALTER DATABASE ... SET AUTOMATIC_TUNING ...` or querying `sys.dm_db_tuning_recommendations` and using the `revert` action). Option A is incorrect because dropping and recreating the index does not revert the plan correction; it may force a new plan but is not the direct revert method. Option B is incorrect because automatic tuning does not automatically revert a plan correction that is performing worse; it may later adjust, but manual intervention is the reliable way. Option C is incorrect because disabling automatic tuning would only prevent future corrections but not revert the current one; the plan would remain until manually reverted or statistics change.

Answer

Drop and recreate the index used by the query

Answer

Wait for automatic tuning to revert the plan automatically

Answer

Disable automatic tuning for the database

Question 6

Which TWO metrics in Azure SQL Database indicate that the database might need to be scaled up?

Accepted Answer

Log write percent consistently above 90%. High DTU/CPU consumption (E) and high log write percent (C) both indicate that the database is approaching or hitting resource limits, suggesting a need to scale up. Options A, B, and D are incorrect because consistently low metrics (data IO, session percent, memory) indicate underutilization, not a need to scale up.

Answer

Data IO percentage consistently below 20%

Answer

Session percent consistently below 10%

Answer

Memory consumption consistently below 30%

Question 7

You run the above KQL query in Azure Monitor Log Analytics to investigate performance issues in SalesDB. What is the primary purpose of this query?

Accepted Answer

Identify queries with high average duration. The query filters for queries with average duration > 1000 ms and orders by duration, identifying high-duration queries. Option B is wrong because plan changes are not detected. Option C is wrong because it does not sort by frequency. Option D is wrong because it does not compare across time intervals.

Answer

Identify queries that have had plan changes

Answer

Find the most frequently executed queries

Answer

Compare query performance over different time intervals

Question 8

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.. Adding tempdb data files equal to the number of vCores (16) is the recommended best practice to reduce allocation contention. With 16 vCores, 16 files minimize PAGELATCH_EX waits. Option A is incorrect because memory-optimized tempdb metadata helps only with system metadata contention, not data page allocation. Option B is incorrect because adding only 4 files is insufficient; the recommended number is equal to the number of vCores. Option D is incorrect 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 9

You are tasked with automating the creation of user databases in an Azure SQL Managed Instance after deployment. Which tool should you use?

Accepted Answer

Azure Automation with PowerShell runbooks. Azure Automation with PowerShell runbooks is ideal for automating post-deployment tasks such as creating user databases. Azure Resource Manager templates deploy infrastructure but not subsequent database operations. SQL Agent jobs are available in Azure SQL Managed Instance, but Azure Automation provides more orchestration flexibility. Elastic Database Jobs are supported in Azure SQL Managed Instance (in preview), but they are primarily designed for cross-database query scheduling and management, making Azure Automation a more appropriate choice for database creation automation.

Answer

Elastic Database Jobs

Answer

Azure Resource Manager templates

Answer

SQL Agent jobs

Question 10

Your company has a policy to automatically pause Azure SQL Databases during non-business hours to save costs. You need to implement this with minimal administrative overhead. What should you use?

Accepted Answer

Serverless compute tier with auto-pause enabled. The serverless compute tier can be configured to automatically pause the database after a period of inactivity, meeting the requirement with minimal overhead. Option A is incorrect because Elastic Database Jobs require creating a job to pause the database, adding overhead. Option C is incorrect because Azure Logic Apps with the SQL connector would require building a workflow, adding complexity. Option D is incorrect because Azure Automation runbooks require scripting and maintenance, which is more overhead than the serverless auto-pause feature.

Answer

Elastic Database Job that pauses the database

Answer

Azure Logic Apps with SQL connector to execute pause/resume

Answer

Azure Automation runbook with a schedule to pause/resume

Question 11

Which TWO Azure services can be used to automate the execution of T-SQL scripts on a schedule against Azure SQL Database?

Accepted Answer

Azure Automation Hybrid Runbook Worker with PowerShell Invoke-SqlCmd.. Options B and D are correct. Azure Automation Hybrid Runbook Worker can execute T-SQL scripts using PowerShell Invoke-SqlCmd on a schedule, making it suitable for automating script execution against Azure SQL Database. Elastic Database Jobs are specifically designed to run T-SQL scripts on a schedule across one or more Azure SQL databases. Option A is incorrect because Azure Logic Apps with SQL connector is better suited for workflows and not primarily for scheduled T-SQL script execution. Option C is incorrect because Azure Functions, while capable of running code with timer triggers, is not the optimal service for scheduled T-SQL scripts; Elastic Database Jobs is the dedicated solution. Option E is incorrect because Azure Data Factory is focused on data movement and orchestration, not direct scheduled execution of T-SQL scripts.

Answer

Azure Logic Apps with SQL connector to execute stored procedures.

Answer

Azure Functions with timer trigger and SqlConnection.

Answer

Azure Data Factory with a SQL Server Stored Procedure activity.

Question 12

You are a database administrator for a logistics company that uses Azure SQL Managed Instance. The instance hosts a database that tracks shipments. You need to automate the process of updating statistics for all tables in the database every night. The update should only include tables that have had more than 10% of rows modified since the last statistics update. The automation must be efficient and not rebuild statistics unnecessarily. Additionally, the solution should be self-contained within the Managed Instance and not rely on external services. What should you do?

Accepted Answer

Create a SQL Agent job with a T-SQL script that uses sys.dm_db_stats_properties to identify tables needing statistics updates based on modification counters, then executes UPDATE STATISTICS only on those tables.. SQL Agent is available on Azure SQL Managed Instance, and you can create a T-SQL script that uses sys.dm_db_stats_properties to check modification counters and then executes UPDATE STATISTICS only on tables that have had more than 10% of rows modified. This meets the efficiency requirement without rebuilding statistics unnecessarily. Option B is incorrect because automatic tuning does not update statistics based on modification counters; it manages indexes and query plan issues. Option C is incorrect because Azure Automation requires external dependencies like a hybrid worker and is not self-contained within the Managed Instance. Option D is incorrect because Elastic Database Jobs are not available for Azure SQL Managed Instance; they are for Azure SQL Database.

Answer

Enable automatic tuning for the database to automatically update statistics.

Answer

Use Azure Automation with a PowerShell runbook that connects to the instance and runs sp_updatestats.

Answer

Use Elastic Database Jobs to schedule a T-SQL script that updates all statistics unconditionally.

Question 13

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

Accepted Answer

Batch transactions and reduce log writes.. Batching transactions reduces the number of log write operations, lowering the log write throughput below the service tier limit and avoiding throttling. Option A is incorrect because increasing the max log size does not affect the log write rate; it only provides more storage. Option C is incorrect because accelerated database recovery (ADR) improves recovery time and may reduce log I/O for crash recovery, but it does not directly reduce the sustained log write throughput from transaction processing. Option D is incorrect because the requirement explicitly states not to move to Business Critical.

Answer

Increase the max log size using ALTER DATABASE.

Answer

Enable accelerated database recovery to reduce log I/O.

Answer

Move to Business Critical tier.

Question 14

You need to automate the execution of a T-SQL script against an Azure SQL Database every hour. Which Azure service should you use?

Accepted Answer

Azure Automation. Azure Automation with a runbook can run PowerShell or Python scripts that execute T-SQL against Azure SQL Database on a schedule. Option A is wrong because Azure Data Factory is focused on data movement and transformation, not direct T-SQL execution. Option B is wrong because Elastic Database Jobs require a job agent and are designed for management tasks across multiple databases, not simple hourly script execution. Option C is wrong because Azure Logic Apps is geared towards integration workflows, and while it can execute SQL, it is less efficient than Azure Automation for this straightforward scheduling task.

Answer

Azure Data Factory

Answer

Elastic Database Jobs

Answer

Azure Logic Apps

Question 15

Your Azure SQL Database is experiencing high DTU consumption. You need to identify the top resource-consuming queries. What should you do?

Accepted Answer

Use the Query Store reports in the Azure portal. Query Store in Azure SQL Database provides built-in reports to identify top resource-consuming queries by CPU, IO, and duration, making it the easiest and most direct method. Option B (SQL Server Profiler) is not supported in Azure SQL Database. Option C (Extended Events) is more complex and not the simplest approach for this task. Option D (sys.dm_exec_query_stats) can be used but lacks persistent historical data and is more effort than using Query Store reports.

Answer

Use SQL Server Profiler

Answer

Create an Extended Events session to capture query events

Answer

Query sys.dm_exec_query_stats