DP-300 Practice Test 31 — 15 Questions

Question 1

Your Azure SQL Database is running in the General Purpose tier. You notice that read queries are experiencing high latency. You need to improve read performance without changing the compute size. What should you implement?

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

Accepted Answer

Create columnstore indexes on the tables. Creating columnstore indexes can improve read performance by compressing data and enabling batch processing for analytical queries, which reduces I/O and latency without requiring a change in compute size or service tier. Read scale-out (option B) is not available on the General Purpose tier, so it cannot be implemented without upgrading to a higher tier, which might be considered a change beyond compute size. Hyperscale (option A) changes the service tier and may not directly address read latency. In-memory OLTP (option D) is optimized for transactional workloads, not general read queries.

Answer

Upgrade to the Hyperscale service tier

Answer

Enable read scale-out

Answer

Use in-memory OLTP for the tables

Question 2

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?

Accepted Answer

Network latency between the primary and secondary regions. Active geo-replication asynchronously replicates transaction log records from the primary to the secondary database. High log write latency on the secondary is most commonly caused by network latency between the regions. Option A is incorrect because the secondary database's log IOPS are not directly the issue; the log is applied from the primary. Option C is incorrect because primary CPU usage does not directly impact secondary log write latency. Option D is incorrect because read workload on the secondary does not affect log write latency; the secondary applies log regardless of read activity.

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 3

Which THREE actions are required to configure Microsoft Entra ID authentication for an Azure SQL Database? (Choose three.)

Accepted Answer

Set a Microsoft Entra ID administrator for the Azure SQL Database server.. Setting a Microsoft Entra ID administrator for the Azure SQL Database server is a mandatory step to enable Entra ID authentication at the server level. This action configures the server to accept authentication tokens from the specified Entra ID tenant and allows the creation of contained database users mapped to Entra identities.

Answer

Configure a firewall rule to allow connections from the Microsoft Entra ID service.

Answer

Ensure that SQL authentication is enabled as a fallback.

Question 4

You are designing a new Azure SQL Database for an e-commerce application. The database will contain sensitive customer data and must be isolated from other tenants. Which deployment option provides the highest level of resource isolation?

Accepted Answer

Managed Instance. Azure SQL Managed Instance provides the highest level of resource isolation among the listed options. It runs as an isolated instance with its own dedicated set of CPU, memory, and storage resources, and it is deployed into a dedicated virtual network, ensuring network isolation as well. This makes it ideal for scenarios requiring strict tenant separation, such as an e-commerce application handling sensitive customer data. In contrast, a single database in Azure SQL Database shares some underlying resources at the logical server level and does not offer the same degree of network isolation.

Answer

Elastic pool

Answer

Hyperscale

Answer

Single database

Question 5

Refer to the exhibit. You are reviewing an Elastic Database Job definition for Azure SQL Database. The job is scheduled to run once on January 15, 2026. Which statement about the job's target is correct?

Accepted Answer

The job will target all databases in the server except 'db1'.. The target group uses membershipType 'Exclude' with a members list containing only 'db1'. This means that the job will target all databases in the logical server except 'db1'. Option A is incorrect because the membership type is 'Exclude', not 'Include', so it does not target only 'db1'. Option C is incorrect because the target group is at the database level within a server, not the entire server itself. Option D is incorrect because the target group excludes 'db1', so it targets all databases on the server, but not the server as a whole.

Answer

The job will target only the database named 'db1'.

Answer

The job will target the entire logical server.

Answer

The job will target the server and all its databases.

Question 6

Your Azure SQL Managed Instance stores sensitive financial data. You must prevent unauthorized access from Azure services and ensure that only specific virtual networks can connect. You also need to audit all failed login attempts. Which combination of configurations should you implement?

Accepted Answer

Create a private endpoint, set 'Public network access' to 'Deny', and enable Azure SQL Auditing with a log analytics workspace.. Creating a private endpoint for Azure SQL Managed Instance ensures that traffic to the instance traverses the Microsoft backbone network and is only accessible from within the specified virtual network. Setting 'Public network access' to 'Deny' explicitly blocks all public internet traffic, including traffic from other Azure services that do not originate from the approved VNet. Enabling Azure SQL Auditing with a Log Analytics workspace captures all failed login attempts, meeting the audit requirement. This combination directly addresses the need to prevent unauthorized access from Azure services and enforce VNet-only connectivity while auditing failed logins.

Answer

Enable Microsoft Defender for SQL and set up anomaly detection alerts.

Answer

Enable SQL vulnerability assessment and configure email notifications.

Answer

Configure VNet service endpoints and set the firewall to allow only your VNet.

Question 7

Refer to the exhibit. A DBA is creating an Azure SQL Managed Instance using the ARM template snippet shown. The DBA needs to ensure the instance can survive a regional outage without data loss. What change should be made to the template?

Accepted Answer

Set zoneRedundant to true and configure an auto-failover group with a secondary region. Azure SQL Managed Instance does not natively support zone redundancy for regional outage protection; instead, it requires configuring an auto-failover group with a secondary instance in a paired region. Setting zoneRedundant to true alone only protects against zone failures within a single region, not a full regional outage. The auto-failover group ensures continuous replication and automatic failover to the secondary region, meeting the requirement of surviving a regional outage without data loss.

Answer

Set collation to SQL_Latin1_General_CP1_CS_AS

Answer

Set zoneRedundant to true

Answer

Change storageAccountType to RA-GRS

Question 8

Refer to the exhibit. An automatic tuning recommendation to force the last good plan is active. What should the database administrator do next?

Accepted Answer

Monitor the query performance to confirm the forced plan resolves the regression. When an automatic tuning recommendation to force the last good plan is active, the correct next step is to monitor the query performance to confirm that the forced plan resolves the regression. This is because plan forcing is a corrective action that may or may not improve performance; validation through monitoring ensures the change is beneficial before taking further steps like creating or dropping indexes.

Answer

Immediately implement the DROP_INDEX recommendation to reduce overhead

Answer

Create the recommended index to improve performance

Answer

Revert the plan force because it is causing regression

Question 9

You are tuning a query in Azure SQL Database that uses a nonclustered columnstore index. The query is supposed to use batch mode execution but shows row mode. What is the most likely cause?

Accepted Answer

The query does not have enough memory grant. Batch mode execution requires a sufficient memory grant. If the query does not get enough memory, it falls back to row mode. Option B is incorrect: while compatibility level 130 or higher is required for batch mode, the question states the index is a nonclustered columnstore index, and the most common cause of row mode despite having such an index is insufficient memory. Option C is incorrect: a filter predicate on a columnstore index does not prevent batch mode. Option D is incorrect: MAXDOP 1 does not disable batch mode; batch mode can work with a single degree of parallelism.

Answer

The database compatibility level is below 130

Answer

The index is defined with a filter predicate

Answer

The query hint MAXDOP 1 is used

Question 10

You are analyzing query performance in an Azure SQL Database. The query in the exhibit returns a list of queries ordered by total_logical_reads. What does high total_logical_reads typically indicate?

Accepted Answer

The query is reading many pages from the buffer pool, possibly due to missing indexes. High total_logical_reads typically indicates that the query is reading many pages from the buffer pool, which often points to missing or inefficient indexes. Option D is correct. Option A is incorrect because high logical reads relate to buffer pool access, not necessarily I/O latency (which is indicated by high physical reads). Option B is incorrect because CPU time is measured by worker_time, not logical reads. Option C is incorrect because while logical reads can increase memory usage, the primary indicator of memory usage is the memory grant, not logical reads.

Answer

The query is experiencing I/O latency

Answer

The query is using a lot of CPU time

Answer

The query is using a lot of memory

Question 11

You are reviewing an Azure Resource Manager template snippet for configuring long-term backup retention for an Azure SQL Database. The deployment fails with an error indicating the storage account is not accessible. What is the most likely cause?

Accepted Answer

The SAS token has expired or is invalid. The error indicates that the storage account is not accessible. In the template, a SAS token is used to authenticate access to the storage container for long-term backup retention. The most common reason for this error is that the SAS token has expired or is invalid, as SAS tokens have an expiration date. Therefore, Option C is correct. Option A is incorrect because the server name mismatch would cause a different error. Option B is incorrect because the URI format may be valid, but the token is the issue. Option D is incorrect because cross-region storage is supported for long-term retention.

Answer

The server name in the template does not match the actual server

Answer

The storage container URI is incorrectly formatted

Answer

The database is not in the same region as the storage account

Question 12

You are a junior DBA at a startup that uses Azure SQL Database for its SaaS application. The application uses a single database and the development team frequently makes schema changes. You need to implement security measures to:

1. Ensure that all connections from the application use TLS encryption.
2. Prevent unauthorized access from the internet.
3. Allow your office IP range (203.0.113.0/24) to connect for management.
4. Enable auditing of all schema changes.
5. Encrypt the database at rest using Azure-managed keys.

You have already enabled Transparent Data Encryption (TDE) with service-managed keys. What should you do next to meet the remaining requirements?

Accepted Answer

Set the minimal TLS version to 1.2. Add a firewall rule for the office IP range and remove the default 'Allow Azure services' rule. Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP.. It addresses all remaining requirements: setting the minimal TLS version to 1.2 ensures encrypted connections (requirement 1), adding a firewall rule for the office IP range and removing the default 'Allow Azure services' rule prevents unauthorized internet access while allowing management traffic (requirements 2 and 3), and enabling auditing with SCHEMA_OBJECT_CHANGE_GROUP captures schema changes (requirement 4). TDE with service-managed keys already satisfies requirement 5, so no further action is needed for encryption at rest.

Answer

Set the minimal TLS version to 1.2. Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP.

Answer

Add a firewall rule for the office IP range. Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP.

Answer

Add a firewall rule for the office IP range. Remove the default 'Allow Azure services' rule.

Question 13

Refer to the exhibit. You are reviewing the audit settings for an Azure SQL Database. What will this configuration do?

Accepted Answer

Audit logs are written to Azure Monitor (Log Analytics).. The exhibit shows that the audit destination is set to 'Log Analytics' (Azure Monitor), which means audit logs will be sent to a Log Analytics workspace. This configuration does not require a storage endpoint because Log Analytics is the designated target. Option B correctly identifies that audit logs are written to Azure Monitor (Log Analytics).

Answer

Auditing is disabled because no storage endpoint is provided.

Answer

Only failed authentication attempts are audited.

Answer

Audit logs are written to an Azure Storage account.

Question 14

You manage an Azure SQL Database that uses the General Purpose service tier. You need to reduce storage costs by archiving old data that is not frequently accessed. The archived data must still be queryable occasionally. What should you do?

Accepted Answer

Implement table partitioning and move old partitions to a separate database with a lower service tier.. Implementing table partitioning allows you to efficiently manage large tables by splitting them into smaller, more manageable partitions. You can then move older partitions to a separate database with a lower service tier (e.g., from General Purpose to Basic), which reduces storage costs while keeping the archived data queryable when needed. Stretch Database (option A) is deprecated and no longer recommended. Elastic Query (option C) is used to query remote databases, not to archive data. Hyperscale (option D) is designed for high scalability and performance, not for cost reduction on infrequently accessed data.

Answer

Enable Stretch Database to transparently stretch old data to Azure Blob Storage.

Answer

Use Elastic Query to query historical data stored in Azure Blob Storage.

Answer

Migrate to the Hyperscale service tier to take advantage of tiered storage.

Question 15

Your SQL Server is experiencing deadlocks. You enable trace flag 1222 to capture deadlock graphs in the error log. Where can you retrieve the deadlock information?

Accepted Answer

The SQL Server error log, viewable using sp_readerrorlog.. Trace flag 1222 writes deadlock information to the SQL Server error log. This log can be read using sp_readerrorlog, xp_readerrorlog, or the Log File Viewer in SQL Server Management Studio. Option D refers to this error log, though retrieving it via sp_readerrorlog is the direct method; the original mention of sys.messages and sys.fn_get_audit_file is incorrect and replaced.

Answer

sys.dm_exec_sessions

Answer

sys.query_store_query_text

Answer

sys.dm_exec_requests