DP-300 Monitor, configure, and optimize database resources Practice Test 4 — 15 Questions

Question 1

You are troubleshooting a performance issue in Azure SQL Database. You suspect that parameter sniffing is causing suboptimal query plans. What feature can you use to mitigate this without code changes?

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

Accepted Answer

Set the database to FORCE_PARAMETERIZATION = ON.. Setting FORCE_PARAMETERIZATION = ON at the database level instructs SQL Server to parameterize all literals in queries automatically, which reduces the number of distinct plans and can mitigate parameter sniffing by promoting plan reuse. This is a database-scoped configuration that requires no application code changes, making it the correct answer. It is particularly useful when an application sends ad-hoc queries with literal values that lead to plan cache bloat and inconsistent performance.

Answer

Reorganize indexes regularly.

Answer

Use Query Store to force a good plan.

Answer

Add OPTIMIZE FOR UNKNOWN query hint to all queries.

Question 2

Which TWO actions can help you identify and resolve performance bottlenecks related to I/O in an Azure SQL Database?

Accepted Answer

Query sys.dm_exec_requests and filter on wait_type like PAGEIOLATCH.. Options A and B are correct. A: Querying sys.dm_exec_requests with a filter on PAGEIOLATCH wait type identifies queries waiting for I/O, pinpointing I/O bottlenecks. B: Data compression reduces the size of data pages, decreasing I/O operations for large tables. C: Increasing the service tier adds compute resources (CPU/memory) but does not directly address I/O bottlenecks; it may only mask the issue. D: sys.dm_db_resource_stats provides average I/O per minute at the database level, which is useful for monitoring but not for per-query I/O bottleneck identification. E: sys.dm_os_performance_counters for CPU usage tracks CPU performance, not I/O.

Answer

Increase the database service tier to add more compute resources.

Answer

Use sys.dm_db_resource_stats to see average I/O per minute.

Answer

Monitor sys.dm_os_performance_counters for CPU usage.

Question 3

What effect does the command in the exhibit have on the database?

Accepted Answer

Enables auto-pause with a delay of 1 minute.. Setting AUTO_PAUSE_DELAY = 0 is not a valid value in Azure SQL Database serverless. The minimum allowed delay is 1 minute. Therefore, the database interprets a value of 0 as the minimum, effectively setting the auto-pause delay to 1 minute.

Answer

Configures the database to pause after 1 second of inactivity.

Answer

Disables auto-pause for the database.

Answer

Sets auto-pause delay to 1 hour.

Question 4

You are troubleshooting a performance issue in Azure SQL Database. You suspect that parameter sniffing is causing suboptimal query plans. What feature can you use to mitigate this without code changes?

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

Accepted Answer

Stale statistics caused the optimizer to underestimate cardinality. Stale statistics can cause the optimizer to significantly underestimate cardinality. In this scenario, the optimizer mistakenly estimated a low number of rows, leading it to choose a nested loops join (optimal for small datasets) instead of a hash join (better for larger datasets). This is a classic plan regression due to statistics staleness.

Option A is incorrect: parameter sniffing typically results in a plan optimized for the initial parameter values, but it does not directly cause a cardinality underestimation that forces a switch from hash to nested loops.

Option B is incorrect: dropping an index would likely cause a table scan or a different plan (e.g., clustered index scan), but the specific change from hash join to nested loops is not characteristic of a missing index; it is more indicative of a cardinality misestimate.

Option C is incorrect: forced parameterization encourages plan reuse and can prevent plan changes, not cause them.

Answer

Parameter sniffing caused the optimizer to choose a plan for atypical parameter values

Answer

An index was dropped, forcing a table scan

Answer

Forced parameterization was enabled

Question 5

Refer to the exhibit. You apply this JSON configuration to an Azure SQL Database server. Which actions will be audited?

Accepted Answer

Only successful and failed database authentication attempts. The configuration only includes SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP. Option A is wrong because it includes actions not in the config. Option B is wrong because it includes data modification actions. Option D is wrong because it includes all actions.

Answer

All successful and failed login attempts and all schema changes

Answer

All database-level actions

Answer

All successful and failed logins and all data modification statements

Question 6

Refer to the exhibit. You retrieve the configuration of an Azure SQL Database. Based on the JSON output, which feature is enabled?

Accepted Answer

Intelligent Insights. The JSON output shows "isIntelligentInsightsOn": true, indicating that the Intelligent Insights feature is enabled for the Azure SQL Database. Option A is incorrect because "isLedgerOn" is false. Option B is incorrect because there is no mention of availability groups in the JSON. Option D is incorrect because even though "preferredEnclaveType" is set to VBS, Always Encrypted with secure enclaves requires explicit configuration on columns, and the setting alone does not mean the feature is enabled.

Answer

Ledger tables

Answer

Always On availability groups

Answer

Always Encrypted with secure enclaves

Question 7

Refer to the exhibit. You review the configuration of an Azure SQL Database. Which statement about this database is true?

Accepted Answer

The database can automatically increase storage.. The storage profile shows autogrow enabled, allowing the database to automatically increase storage when needed. Option B is wrong because geo-redundant backup storage is not enabled; it is configured as locally redundant. Option C is wrong because the high availability configuration is not zone-redundant; it uses locally redundant storage. Option D is wrong because the service tier shown is General Purpose, not Hyperscale.

Answer

Geo-redundant backup storage is enabled.

Answer

The database uses zone-redundant high availability.

Answer

The database is using the Hyperscale service tier.

Question 8

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is consistently high, causing performance issues. Which metric should you monitor to identify the log generation rate?

Accepted Answer

Log IO percent. In Azure SQL Database Hyperscale, the log generation rate is surfaced through the Log IO percent metric, which reflects how much of the log throughput limit the workload is consuming. A consistently high Log IO percent indicates the transaction log is the bottleneck, which is exactly the symptom described. Monitoring this metric lets you identify whether log-heavy operations (large batch inserts, index maintenance) are saturating the log.

Answer

DTU used

Answer

CPU percent

Answer

Data IO percent

Question 9

You have an Azure SQL Database with Intelligent Insights enabled. You receive an alert that 'SQLInsights: Resource utilization is consistently high'. You need to determine whether the issue is caused by an increase in user workload or a degradation in query performance. Which Intelligent Insights dimension should you review?

Accepted Answer

Impact. Intelligent Insights reports on database performance using dimensions including Resource type, Metric, Severity, and Impact. The 'Impact' dimension describes the effect on the workload — specifically whether the issue stems from increased user workload (more queries/connections) or from query performance degradation (a specific query plan regression). Reviewing Impact tells you which of the two root causes applies.

Answer

Metric

Answer

Severity

Answer

Resource type

Question 10

You are tuning an Azure SQL Database workload. Which TWO actions can help reduce PAGELATCH_EX contention?

Accepted Answer

Add multiple data files to the filegroup. Adding multiple data files to the filegroup helps reduce PAGELATCH_EX contention by distributing page allocation activity across multiple files. When a single data file is used, all concurrent insert operations compete for the same allocation page (e.g., PFS, GAM, SGAM), causing exclusive latches. With multiple files, each file has its own set of allocation structures, allowing parallel allocation operations to proceed with less contention.

Answer

Increase MAXDOP

Answer

Use a columnstore index

Answer

Configure a readable secondary replica

Question 11

You are troubleshooting a transaction log growth issue on an Azure SQL Database. Which THREE conditions can cause the transaction log to grow unexpectedly?

Accepted Answer

Replication that has not delivered transactions to the subscriber. Options A, C, and D are correct. A long-running uncommitted transaction (C) prevents log truncation, causing growth. Replication with undelivered transactions (A) holds log space until delivery completes. Excessive updates due to a missing index (D) generate many log records, increasing log size. Option B is incorrect because memory-optimized tables use a different logging mechanism (e.g., native redo) that does not inherently cause more log growth than disk-based tables. Option E is incorrect because page compression reduces the size of data pages, which can decrease log record size for changes, not cause growth.

Answer

A memory-optimized table with a large number of rows

Answer

Page compression enabled on tables

Question 12

You are the database administrator for a large e-commerce application hosted on Azure SQL Database in the Business Critical tier (8 vCores). The database has a table named Orders with 500 million rows. The table is partitioned by OrderDate using a monthly partition function. The application frequently runs queries that aggregate sales by month for the current year. Users report that these queries are slow. You examine the query execution plans and notice that the queries are scanning all partitions, not just the relevant ones. You need to improve query performance without changing the application code. Which action should you take?

Accepted Answer

Re-create the partition function with a different boundary type (e.g., RIGHT instead of LEFT) to align with query patterns.. The issue is that partition elimination is not occurring, meaning the query scans all partitions instead of only the relevant ones for the current year. Since the table is partitioned by OrderDate using a monthly partition function, the alignment of partition boundaries with the query predicate is critical. If the current partition function uses LEFT boundaries, the boundary values represent the start of each partition, but queries for the current year might include dates that cross boundaries in a way that prevents elimination. Re-creating the partition function with RIGHT boundaries ensures that each boundary value is the first day of the next partition, which often aligns better with range queries like 'WHERE OrderDate >= '2025-01-01' AND OrderDate < '2025-12-31''. This can enable partition elimination without changing application code. Option A (update statistics) does not affect partition elimination. Option B (columnstore index) improves compression and query speed but does not force partition elimination. Option D (indexed view) requires code changes and does not directly cause partition elimination. Therefore, option C is correct.

Answer

Update statistics for the Orders table.

Answer

Create a columnstore index on the Orders table.

Answer

Create an indexed view that pre-aggregates sales by month.

Question 13

You have an Azure SQL Database that uses the SQL Server Agent to run a daily maintenance job. The job fails intermittently with the error 'Login failed for user'. The job uses a SQL Server authentication login. What is the most likely cause and solution?

Accepted Answer

The login password has expired; update the password in the job step.. The intermittent 'Login failed for user' error with a SQL Server authentication login strongly indicates a password expiration issue. Azure SQL Database enforces password expiration policies by default, and if the password for the SQL authentication login used by the job step has expired, the job will fail until the password is updated. This is the most likely cause because the failure is intermittent (occurring after the password expires) and the job uses SQL authentication, which is subject to password policies.

Answer

The database is in a failover group and the secondary is read-only; connect to the primary.

Answer

The job schedule is conflicting with another job; change the schedule.

Answer

The SQL Server Agent is not running; start the agent.

Question 14

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

Question 15

You manage an Azure SQL Database that uses automatic tuning. The database has the FORCE_LAST_GOOD_PLAN option enabled. A critical query suddenly starts performing poorly after a plan change. What is the expected behavior?

Accepted Answer

The database will automatically revert to the last good plan and log the event in sys.dm_db_tuning_recommendations.. When FORCE_LAST_GOOD_PLAN is enabled and automatic tuning detects a plan regression, Azure SQL Database automatically reverts to the last known good plan. The event is logged in sys.dm_db_tuning_recommendations for audit and analysis. This behavior is part of the automatic plan correction feature, which does not require manual intervention.

Answer

The query will continue with the poor plan until you manually force a plan.

Answer

You will receive a recommendation to revert the plan change.

Answer

The database will automatically revert to the last good plan without notification.