DP-300 Practice Test 7 — 10 Questions

Question 1

Your company uses Azure SQL Database with active geo-replication. You notice that the secondary database in a different region has a high log write latency. Users report that the primary database performance is normal. What is the most likely cause?

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

Accepted Answer

Network latency between the primary and secondary regions. In Azure SQL Database active geo-replication, the secondary database continuously receives transaction log from the primary over the network. High log write latency on the secondary while the primary performs normally points to network latency between the two regions, since the log must traverse the inter-region link and be hardened on the secondary.

Answer

Insufficient log IOPS on the secondary database

Answer

High CPU usage on the primary database

Answer

Excessive read workload on the secondary database

Question 2

You are a database administrator for an Azure SQL Database. You need to ensure that only specific client IP addresses can connect to the database, while all other traffic is blocked. You also need to allow Azure services to access the database. What should you configure?

Accepted Answer

Configure server-level firewall rules to allow the specific client IP addresses and enable the 'Allow Azure services and resources to access this server' setting.. Azure SQL Database uses server-level firewall rules to control inbound access. By adding rules for specific client IP addresses and enabling the 'Allow Azure services and resources to access this server' setting, you restrict connections to only those IPs while permitting Azure internal services (e.g., Azure Logic Apps, Azure Functions) to connect. This setting leverages the Azure SQL firewall, which evaluates source IP addresses against the configured rules before allowing a connection.

Answer

Disable public network access and configure a service endpoint.

Answer

Configure a private endpoint and disable public network access.

Answer

Configure network security group (NSG) rules on the subnet where the Azure SQL Database is deployed.

Question 3

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.. For Query Store runtime statistics to appear in Log Analytics, the Azure SQL Database diagnostic settings must explicitly route the QueryStoreRuntimeStatistics category to the Log Analytics workspace. If that category is not enabled, the KQL query will return no rows even though Query Store is enabled on the databases, because the data never reaches the workspace. This is the most likely cause of the empty result set.

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 4

You are configuring performance monitoring for Azure SQL Managed Instance. You need to collect and analyze query performance data with minimal overhead. Which solution should you use?

Accepted Answer

Query Store. Query Store is built-in and designed for low overhead query performance monitoring. Option B is wrong because Azure Monitor metrics provide resource-level metrics, not query-level details. Option C is wrong because Extended Events can have higher overhead and is more for custom event collection. Option D is wrong because SQL Server Profiler is deprecated and has high overhead.

Answer

Azure Monitor metrics

Answer

Extended Events

Answer

SQL Server Profiler

Question 5

You are deploying a new application on Azure SQL Database. The application requires that all connections use a specific login, 'AppUser', with the least privileges necessary. The login should only be able to execute stored procedures in the 'Sales' schema and should not have direct access to underlying tables. What should you do?

Accepted Answer

Create a database role, grant EXECUTE on the 'Sales' schema to the role, and add 'AppUser' to the role.. Creating a database role, granting EXECUTE on the 'Sales' schema to that role, and adding 'AppUser' to the role follows the principle of least privilege. This allows 'AppUser' to execute stored procedures in the Sales schema without granting direct table access, because stored procedures execute with ownership chaining or elevated permissions. This is the most maintainable and secure approach.

Answer

Grant the SELECT permission on the 'Sales' schema to 'AppUser'.

Answer

Add 'AppUser' to the db_datareader role.

Answer

Grant the EXECUTE permission on each stored procedure individually to 'AppUser'.

Question 6

You are monitoring an Azure SQL Database and notice high PAGELATCH waits. What is the most likely cause?

Accepted Answer

Concurrent inserts into a table with a clustered index causing last-page contention.. High PAGELATCH waits in Azure SQL Database are most commonly caused by concurrent inserts into a table with a clustered index, leading to last-page contention. Multiple threads trying to insert into the same page serialize on the page latch, causing PAGELATCH waits. This is a classic OLTP pattern where a monotonically increasing key (like an IDENTITY column) causes all inserts to target the last page.

Answer

Insufficient buffer pool size leading to frequent reads from disk.

Answer

High CPU usage due to inefficient queries.

Answer

Long-running transactions blocking other queries.

Question 7

Your company wants to ensure business continuity for an Azure SQL Database that is used by a critical application. The database must remain available in the event of a single availability zone failure within a region. Which configuration should you use?

Accepted Answer

Configure zone-redundant availability for the database. Zone-redundant availability for Azure SQL Database replicates the database across multiple availability zones within a region, providing high availability if one zone fails. This ensures the database remains available during a single zone outage, meeting the business continuity requirement.

Answer

Use active geo-replication to a different region

Answer

Deploy the database with locally redundant storage

Answer

Configure read scale-out with a secondary replica

Question 8

You have an Azure SQL Managed Instance used for an e-commerce platform. During a flash sale, you experience a deadlock that causes transaction rollbacks. You need to minimize deadlock occurrences in the future. What should you implement?

Accepted Answer

Enable READ COMMITTED SNAPSHOT isolation level.. Enabling READ COMMITTED SNAPSHOT isolation (RCSI) makes readers use row versioning from tempdb instead of taking shared locks, which eliminates the classic reader-writer deadlock pattern that plagues e-commerce workloads mixing SELECTs and UPDATEs. This directly reduces deadlock frequency without changing application code.

Answer

Configure deadlock graph in Extended Events.

Answer

Enable automatic tuning to force last good plan.

Answer

Increase the instance vCores to improve concurrency.

Question 9

Your company uses Azure SQL Database with active geo-replication. You notice that the secondary database in a different region has a high log write latency. Users report that the primary database performance is normal. What is the most likely cause?

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

Accepted Answer

Create database users from Microsoft Entra ID groups and grant roles to those users.. Azure SQL Database supports creating database users mapped to Microsoft Entra ID (formerly Azure AD) groups. By creating a user for the Entra ID group and then granting database roles to that group user, all members of the group automatically inherit the assigned permissions. This directly satisfies the requirement for role assignment based on group membership without custom scripting or triggers.

Answer

Use Azure RBAC to assign roles to the Entra ID groups.

Answer

Configure a SQL Server Agent job to update database roles based on group membership.

Answer

Create a DDL trigger that assigns roles when users log in.

Question 10

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in wait time for PAGEIOLATCH_SH. You need to identify the most likely cause. What should you investigate first?

Accepted Answer

Insufficient IOPS or throughput at the database level. PAGEIOLATCH_SH waits indicate I/O subsystem pressure, often due to insufficient IOPS or throughput. Option A is incorrect because out-of-date statistics cause cardinality estimation errors, not I/O waits. Option C is incorrect because missing indexes typically cause table scans but not necessarily PAGEIOLATCH waits. Option D is incorrect because blocking causes waits like LCK_M_*, not PAGEIOLATCH.

Answer

Out-of-date statistics

Answer

Missing indexes

Answer

Blocking from long-running transactions