DP-300 Practice Test 41 — 15 Questions

Question 1

You are optimizing an Azure SQL Database that uses the General Purpose service tier. The database has a high volume of small transactions and you observe wait statistics showing significant WRITELOG waits. You need to reduce WRITELOG waits for this database. What should you do?

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

Accepted Answer

Change the service tier to Business Critical.. WRITELOG waits indicate the transaction log is a bottleneck. In the General Purpose tier, the log resides on remote Azure storage, which has higher latency than local SSD. Moving to Business Critical provides local SSD for the log, reducing latency and increasing throughput, which directly mitigates WRITELOG waits. The other options do not address the root cause of log write latency.

Answer

Configure the database to use In-Memory OLTP.

Answer

Increase the database max size.

Answer

Enable Accelerated Database Recovery (ADR).

Question 2

A database administrator manages an Azure SQL Managed Instance that hosts a mission-critical database. The administrator needs to automate the execution of a T-SQL script that performs index maintenance and then sends an email notification with the results. The solution must use native Azure SQL Managed Instance capabilities and minimize external dependencies. Which two actions should the administrator perform? (Choose two.)

Accepted Answer

Configure Database Mail on the Managed Instance and add a notification step to the job.. SQL Server Agent and Database Mail are both supported natively on Azure SQL Managed Instance. Using a SQL Server Agent job with a T-SQL step and a notification step satisfies the automation and email requirements while keeping the solution self-contained on the instance.

Answer

Configure a Logic App that triggers on a schedule and sends an email via Office 365.

Answer

Create an Elastic Database Job that targets the Managed Instance database.

Answer

Deploy an Azure Automation runbook that connects to the Managed Instance and runs the script.

Question 3

You are deploying an Azure SQL Database for a new line-of-business application. The application's workload is unpredictable, with periods of near-zero activity and sudden bursts of high read/write volume. The database must scale compute resources automatically without manual intervention, and you want to minimize cost during idle periods. You create the database using the vCore purchasing model. Which service tier should you select?

Accepted Answer

Serverless. Serverless compute automatically scales vCores up and down based on workload demand and pauses the database during inactivity, billing only for storage while paused. This directly addresses the need for automatic scaling and cost minimization during idle periods. Other service tiers require manual scaling and do not auto-pause, so they cannot meet the stated requirements.

Answer

General Purpose

Answer

Business Critical

Answer

Hyperscale

Question 4

You are responsible for an Azure SQL Database that hosts a mission-critical application. You need to configure an alert that fires when the database's CPU usage exceeds 90% for more than 10 minutes. You want to use the built-in Azure Monitor metrics for Azure SQL Database. Which metric should you use?

Accepted Answer

cpu_percent. The cpu_percent metric directly measures the percentage of CPU utilized by the Azure SQL Database. It is the appropriate metric to use for an alert on CPU usage exceeding a threshold. Other metrics like dtu_consumption_percent, physical_data_read_percent, and log_write_percent measure different resources and would not accurately reflect CPU usage. Using cpu_percent ensures the alert fires only when CPU is the bottleneck.

Answer

dtu_consumption_percent

Answer

log_write_percent

Answer

physical_data_read_percent

Question 5

You administer an Azure SQL Database named HRDB. The security team requires that any connection to HRDB from outside the corporate network be blocked, but on-premises applications must continue to connect over the existing site-to-site VPN. The database currently has a public endpoint and a firewall rule allowing all Azure services. You need to restrict access so that only the VPN subnet can reach HRDB. What should you configure?

Accepted Answer

Set the database's Public Network Access to Disabled and create a private endpoint in the VPN-connected virtual network.. The requirement is network isolation combined with continued VPN access. Disabling public network access closes the internet-facing endpoint, and a private endpoint in the VPN-connected VNet provides a private IP that on-premises systems can reach over the tunnel. Firewall rules and threat detection do not remove the public listener, so only the private-endpoint approach meets the stated condition.

Answer

Enable Microsoft Defender for SQL and set the Advanced Threat Protection alert type to 'Access from unusual location'.

Answer

Configure a database-level firewall rule that allows only the on-premises application's service account and deny all other logins.

Answer

Add a server-level firewall rule for the VPN subnet's public IP address range and keep the public endpoint enabled.

Question 6

You manage an Azure SQL Database that runs an online transaction processing (OLTP) workload. Users report that transactions are slow during business hours. You query sys.dm_os_wait_stats and notice a high number of PAGEIOLATCH_SH waits. You need to reduce these waits without changing the application. What should you do?

Accepted Answer

Increase the database's service tier to add more memory and IOPS.. PAGEIOLATCH_SH waits occur when SQL Server waits for a data page to be read from disk into the buffer pool. The most direct way to reduce these waits without changing the application is to increase the service tier, which provides more memory for the buffer pool and higher storage IOPS. This reduces the need to read pages from disk and speeds up the reads that do occur.

Answer

Configure Query Store to capture wait statistics for the database.

Answer

Enable Accelerated Database Recovery (ADR) on the database.

Answer

Enable Read Scale-Out and redirect read-only queries to the secondary replica.

Question 7

You are planning the deployment of a new Azure SQL Database for a line-of-business application. The application's workload is not yet known, and you must keep the monthly cost as low as possible while still being able to scale compute resources up or down without redeploying the database. You also need to ensure that storage is billed based on the actual data and log used rather than a pre-provisioned maximum. Which purchasing model and service tier should you choose?

Accepted Answer

Serverless compute tier with General Purpose service tier. The serverless compute tier in the General Purpose service tier is designed for unpredictable workloads and cost optimisation. It automatically scales compute based on demand and can auto-pause during inactive periods, billing only for compute used. Storage is billed based on actual data and log usage. Provisioned tiers reserve compute capacity and bill continuously, making them less suitable when the workload is unknown and cost must be minimised.

Answer

Provisioned compute tier with General Purpose service tier

Answer

Hyperscale service tier with provisioned compute

Answer

Provisioned compute tier with Business Critical service tier

Question 8

You are configuring monitoring for an Azure SQL Database that uses the vCore purchasing model. The database is in the General Purpose service tier. You need to receive an alert when the database's CPU consumption exceeds 90 percent for 10 minutes. What should you create?

Accepted Answer

A metric alert rule in Azure Monitor on the CPU percent metric.. Azure Monitor metric alerts are the correct mechanism for alerting on a specific performance metric such as CPU percent. They allow you to set a threshold, an aggregation window, and an evaluation frequency. For a requirement to alert when CPU exceeds 90 percent for 10 minutes, a metric alert rule on the CPU percent metric with a 10-minute aggregation window is the direct and supported solution.

Answer

A smart detection alert in Application Insights.

Answer

An activity log alert rule on the database's administrative operations.

Answer

A log search alert rule based on the AzureDiagnostics table.

Question 9

You are optimizing an Azure SQL Database that uses the vCore purchasing model. The database is experiencing high RESOURCE_SEMAPHORE waits. You need to identify two actions that can reduce these waits. (Choose two.)

Accepted Answer

Scale up to a higher service tier or compute size.. RESOURCE_SEMAPHORE waits indicate insufficient query workspace memory. Scaling up the compute size provides more memory for grants. Optimizing queries reduces the memory needed per query. Together, these actions lower the demand and increase the supply of memory grants, reducing waits. The other options either do not affect workspace memory or could worsen the situation.

Answer

Increase the memory-optimized filegroup size.

Answer

Enable read-scale out.

Answer

Increase the maximum degree of parallelism (MAXDOP).

Question 10

You manage an Azure SQL Database that is critical for a financial application. The database has a read-heavy workload, and you need to monitor and diagnose performance issues. You want to enable a feature that automatically captures detailed information about query plans and runtime statistics for later analysis. Which feature should you enable?

Accepted Answer

Query Store. Query Store is the built-in feature that automatically captures query plans and runtime statistics, storing them in the database for historical analysis. It is designed for performance troubleshooting and is a prerequisite for automatic tuning. For a read-heavy workload, it provides the necessary insights to identify and resolve performance regressions.

Answer

Extended Events

Answer

Automatic tuning

Answer

Dynamic management views (DMVs)

Question 11

You are planning to deploy an Azure SQL Managed Instance to host several databases migrated from an on-premises SQL Server. The applications use cross-database queries, SQL Server Agent jobs, and CLR assemblies. You need to ensure the instance can support these features and that the network configuration allows the instance to be reached from an on-premises network over a site-to-site VPN. What should you do first?

Accepted Answer

Create a virtual network with a dedicated subnet delegated to Microsoft.Sql/managedInstances and configure a site-to-site VPN gateway.. Azure SQL Managed Instance must be placed in a dedicated subnet delegated to Microsoft.Sql/managedInstances, and that subnet cannot host other resources. To connect from on-premises over a site-to-site VPN, you also need a VPN gateway in the virtual network or a peered network. The managed instance natively supports cross-database queries, SQL Server Agent, and CLR, so the network and subnet configuration is the critical first step.

Answer

Create a virtual network with a gateway subnet and configure a site-to-site VPN gateway, then deploy the instance into the same subnet as the gateway.

Answer

Create a virtual network with a subnet delegated to Microsoft.Sql/managedInstances and configure a private endpoint for the instance.

Answer

Create a virtual network with a subnet delegated to Microsoft.Sql/managedInstances and enable public endpoint access on the instance.

Question 12

You have an Azure SQL Database named SalesDB. You need to grant a user named 'ReportingUser' the ability to read all data in the Sales schema but not modify any data. You want to follow the principle of least privilege. What should you do?

Accepted Answer

Grant SELECT permission on the Sales schema to ReportingUser.. Granting SELECT on the Sales schema directly to ReportingUser provides read-only access to only that schema, adhering to least privilege. The other options grant broader permissions that either include unnecessary access to other schemas or allow data modification, which is not required.

Answer

Add ReportingUser to the db_datareader role.

Answer

Grant CONTROL permission on the Sales schema to ReportingUser.

Answer

Add ReportingUser to the db_owner role.

Question 13

You are managing an Azure SQL Database that supports a reporting application. Users report that queries are slow during business hours. You suspect that the database is experiencing CPU pressure. Which metric should you monitor to confirm this?

Accepted Answer

CPU percentage. CPU percentage is the direct metric for CPU utilization in Azure SQL Database. Unlike DTU percentage, which aggregates multiple resources, CPU percentage isolates processor usage. When CPU percentage is consistently high, it confirms CPU pressure as the cause of slow queries. The other metrics measure I/O or log throughput, which are unrelated to CPU bottlenecks.

Answer

DTU percentage

Answer

Log write percentage

Answer

Data IO percentage

Question 14

You manage an Azure SQL Database that experiences high PAGELATCH_EX waits on tempdb during peak transaction processing. You need to reduce these waits. Which two actions should you perform? (Choose two.)

Accepted Answer

Use memory-optimized tempdb metadata.. PAGELATCH_EX waits on tempdb indicate contention on allocation pages during concurrent temp object creation. Adding more tempdb data files spreads the allocation load, and enabling memory-optimized tempdb metadata removes latch contention on metadata pages. These two actions directly target the cause. Other options like ADR or scaling tiers do not address the allocation bottleneck.

Answer

Enable Accelerated Database Recovery (ADR).

Answer

Switch to the Business Critical service tier.

Answer

Increase the database's max size.

Question 15

You manage an Azure SQL Database named InventoryDB. The security team requires that all data in the database be encrypted at rest using a key that your organization controls and can revoke. You need to implement this requirement with minimal administrative overhead. What should you do?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault.. Transparent Data Encryption with a customer-managed key in Azure Key Vault provides encryption at rest while giving the organization control over the key, including the ability to revoke it. This meets the requirement for organizational control and revocation. Service-managed keys do not offer that control, and Always Encrypted and dynamic data masking address different security concerns. TDE with customer-managed keys is the appropriate solution for encrypting all data at rest with minimal administrative overhead.

Answer

Configure Always Encrypted with column encryption keys stored in Azure Key Vault.

Answer

Implement dynamic data masking on all sensitive columns.

Answer

Enable Transparent Data Encryption (TDE) with a service-managed key.