DP-300 Practice Test 27 — 15 Questions

Question 1

Your organization uses Azure SQL Database with elastic pools. One of the databases in the pool, 'OrdersDB', consistently uses more eDTUs than other databases, causing performance degradation for other databases. You need to isolate the resource usage of 'OrdersDB' to prevent it from affecting others. The solution should minimize cost and administrative overhead. What should you do?

Accepted Answer

Move 'OrdersDB' to a higher service tier, such as a standalone database with a higher DTU limit.. Option D is correct because moving the database to a higher service tier or a separate pool isolates its resource usage and prevents impact on others. Option A is wrong because scaling the pool affects all databases. Option B is wrong because query store does not limit resources. Option C is wrong because reserved capacity is a billing benefit, not a resource isolation mechanism.

Answer

Purchase reserved capacity for the pool to reduce costs.

Answer

Use Query Store to identify and kill the high-usage queries.

Answer

Increase the eDTU limit of the elastic pool to accommodate the high usage.

Question 2

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.. Option A is correct because enabling READ COMMITTED SNAPSHOT isolation reduces deadlocks by using row versioning. Option B is wrong because increasing instance size does not prevent deadlocks. Option C is wrong because automatic tuning does not handle deadlocks. Option D is wrong because deadlock graph captures events but doesn't prevent them.

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 3

You need to optimize costs for SalesDB, which is used only during business hours (8 AM to 6 PM). The database currently runs 24/7. Which two changes should you make?

Accepted Answer

Enable serverless with auto-pause enabled.. Option D is correct because enabling auto-pause will pause the database during off-hours, reducing costs. Option A is wrong because reducing vCores might help but auto-pause is more effective. Option B is wrong because changing to Hyperscale may not reduce costs for this pattern. Option C is wrong because storage reduction is not as impactful.

Answer

Reduce storage to 512 GB.

Answer

Change tier to Hyperscale.

Answer

Reduce capacity to 2 vCores.

Question 4

You are reviewing the audit configuration of an Azure SQL Database using the above JSON from Azure Resource Manager. Based on the exhibit, which of the following is true?

Accepted Answer

Audit logs are sent to Azure Monitor. Option B is correct because isAzureMonitorTargetEnabled is true, meaning audit logs are sent to Azure Monitor. Option A is wrong because state is Enabled. Option C is wrong because retentionDays is 90. Option D is wrong because isStorageSecondaryKeyInUse is false.

Answer

Audit logs are written using the secondary storage key

Answer

Auditing is currently disabled

Answer

Audit logs are retained for 30 days

Question 5

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 exhibit shows a SAS token in the template. The most common issue is that the SAS token has expired or is invalid. Option B is correct. Option A is wrong because the storage container URI is valid. Option C is wrong because the location is valid. Option D is wrong because the SAS token is used for authentication.

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 6

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 logical reads often indicate that queries are reading more data than necessary, possibly due to missing indexes or inefficient queries. Option A is correct. Option B is wrong because CPU time is measured by worker_time. Option C is wrong because high logical reads can increase memory usage. Option D is wrong because it does not indicate I/O directly.

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 7

Which TWO actions can you take to optimize query performance in Azure SQL Database without changing the application code? (Choose two.)

Accepted Answer

Create missing indexes identified by the Database Engine Tuning Advisor.. Options A and B are correct. Creating missing indexes and updating statistics are common performance optimization tasks that do not require code changes. Option C requires code changes. Option D is not a direct optimization. Option E is about hardware, but still no code change; however, it's not a typical DBA action for query performance.

Answer

Rewrite queries to use sargable predicates.

Answer

Upgrade to a higher service tier.

Answer

Enable row-level security on sensitive tables.

Question 8

You are designing a database monitoring solution for a critical Azure SQL Database. You need to retain performance data for 90 days and receive alerts when DTU usage exceeds 80%. Which combination of tools should you use?

Accepted Answer

Azure SQL Analytics (now integrated in Azure Monitor) and configure alerts.. Option C is correct because Azure SQL Analytics (now part of Azure Monitor) provides long-term retention and alerting capabilities. Option A is wrong because Query Store default retention is limited. Option B is wrong because Intelligent Insights provides analysis but not configurable retention. Option D is wrong because DMVs only show current state.

Answer

Query Store with retention set to 90 days.

Answer

DMVs queried by a custom script.

Answer

Intelligent Insights and configure email notifications.

Question 9

Your Azure SQL Database is configured with the Hyperscale service tier. You observe increased redo log latency. Which resource is most likely the bottleneck?

Accepted Answer

Log service throughput. Option B is correct because in Hyperscale, the log service is a separate component that handles redo; high latency there directly affects redo speed. Option A is wrong because compute node CPU affects query processing, not redo latency specifically. Option C is wrong because page servers handle data reads, not redo. Option D is wrong because storage IO is distributed and rarely the bottleneck for redo in Hyperscale.

Answer

Compute node CPU

Answer

Page server IO

Answer

Remote storage IOPS

Question 10

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. Option C is correct because the query filters for queries with average duration > 1000 ms and orders by duration. Option A is wrong because it doesn't identify plan changes. Option B is wrong because it doesn't sort by frequency. Option D is wrong because it doesn't compare over 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 11

You are monitoring an Azure SQL Database and notice that the average CPU usage is consistently above 90%. The database is using the S3 service tier. What should you do first to resolve this performance issue?

Accepted Answer

Scale up to a higher service tier. Scaling up to a higher service tier (e.g., S4) provides more CPU resources. Option A is correct. Option B is wrong because the database is not at the maximum tier (S12 is max for Standard). Option C is wrong because enabling read scale-out does not increase CPU. Option D is wrong because query store recommendations take time.

Answer

Enable read scale-out

Answer

Wait for Automatic tuning to provide recommendations

Answer

Scale down to a lower service tier

Question 12

You are a database administrator for a large e-commerce platform using Azure SQL Database. The application experiences intermittent performance degradation during peak hours. Query Store data shows that a specific stored procedure, `usp_getProducts`, has multiple query variants with different execution plans. Some plans are suboptimal. You need to ensure consistent and optimal performance for this stored procedure without rewriting the application code. What should you do?

Accepted Answer

Enable forced parameterization on the database to promote plan reuse.. Option C is correct because enabling forced parameterization makes the database treat literal values as parameters, allowing query plans to be reused and reducing plan cache bloat. Option A is wrong because plan guides are for specific queries, not the entire stored procedure. Option B is wrong because it's an after-the-fact analysis tool. Option D is wrong because index tuning is a reactive measure, not a proactive plan stability solution.

Answer

Create a plan guide to force a specific execution plan for the stored procedure.

Answer

Implement Query Store hints to force the last known good plan for the stored procedure.

Answer

Enable automatic tuning in Azure SQL Database and rely on the 'FORCE LAST GOOD PLAN' feature.

Question 13

Which THREE factors should you consider when configuring automatic tuning for an Azure SQL Database? (Choose three.)

Accepted Answer

Whether to allow automatic index dropping.. Options A, B, and C are correct. Automatic tuning options include CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN. Option D is not an automatic tuning option; it's a manual feature. Option E is not part of automatic tuning.

Answer

Whether to enable automatic plan guide creation.

Answer

Whether to enable automatic statistics update.

Question 14

You are reviewing a deployment template for an Azure SQL Database. The above snippet configures a security alert policy. What is a potential issue with this configuration?

Accepted Answer

The retentionDays is set to 0, which may cause logs to be deleted immediately. Option D is correct because retentionDays set to 0 disables retention, meaning audit logs will not be retained in storage. Option A is wrong because state Enabled is fine. Option B is wrong because emailAddresses is provided. Option C is wrong because emailAccountAdmins true is fine.

Answer

The state is not set to "Disabled"

Answer

The emailAccountAdmins property is set to true

Answer

The emailAddresses array is missing an entry

Question 15

You need to configure alerts for an Azure SQL Database to notify the operations team when the database exceeds 80% DTU consumption for more than 10 minutes. What should you use?

Accepted Answer

Create a metric alert in Azure Monitor. Azure Monitor can create metric alerts based on DTU consumption. Option B is correct. Option A is wrong because SQL Agent cannot send alerts based on DTU. Option C is wrong because Azure Advisor provides recommendations but not real-time alerts. Option D is wrong because Azure SQL Auditing is for compliance.

Answer

Configure a SQL Agent alert

Answer

Configure Azure SQL Auditing

Answer

Use Azure Advisor recommendations