Knowledge + Practice

CCNA Monitor Optimize Db Questions

75 of 211 questions · Page 2/3 · Monitor Optimize Db topic · Answers revealed

Practice these questions Exam hub All questions

76

MCQhard

You are reviewing an ARM template for Azure SQL Database. The exhibit shows the database settings. You notice the database is not being automatically paused. What is the most likely explanation?

A.The minCapacity is set too low

B.The autoPauseDelay is set to 60 minutes

C.The licenseType is set to BasePrice

D.The database uses VBS enclaves which are incompatible with serverless

AnswerD

Serverless does not support VBS enclaves.

Why this answer

Auto-pause is only supported for General Purpose serverless databases, and the use of VBS enclave (preferredEnclaveType: "VBS") indicates Always Encrypted with secure enclaves, which is not supported with serverless. Option A is wrong because minCapacity 0.5 is valid for serverless. Option B is wrong because licenseType BasePrice does not affect auto-pause.

Option C is wrong because autoPauseDelay 60 minutes is valid; the default is 60.

Practice this question →

77

MCQhard

You have an Azure SQL Managed Instance with a large number of databases. You need to monitor the storage space used by each database to proactively manage capacity. Which tool should you use?

A.Query Performance Insight

B.Automatic tuning

C.Intelligent Insights

D.Azure SQL Analytics (preview)

AnswerD

Azure SQL Analytics provides monitoring for multiple databases including storage.

Why this answer

Azure SQL Analytics (preview) in Azure Monitor provides a consolidated monitoring solution for Azure SQL databases and managed instances, including detailed storage usage per database. Option A is wrong because Query Performance Insight focuses on query-level metrics like query duration and CPU usage, not per-database storage. Option B is wrong because Automatic tuning is a feature that automatically indexes and optimizes query plans, not a monitoring tool.

Option C is wrong because Intelligent Insights provides proactive performance diagnostics and anomaly detection, but does not directly per-database storage monitoring. Therefore, only Azure SQL Analytics meets the requirement.

Practice this question →

78

MCQmedium

Your Azure SQL Managed Instance is experiencing high PAGELATCH_SH waits. You need to reduce this contention. What should you implement?

A.Scale up the managed instance to a higher service tier

B.Enable delayed durability

C.Configure a readable secondary replica

D.Add more data files to the filegroup

AnswerD

Distributes page allocation and reduces contention.

Why this answer

Adding more data files to the filegroup spreads out page allocations, reducing contention for allocation structures and thus decreasing PAGELATCH_SH waits. Option A is incorrect because scaling up may provide more resources but does not directly address the page latch contention caused by allocation bottlenecks. Option B is incorrect because delayed durability only affects transaction log write behavior, not page latches.

Option C is incorrect because a readable secondary replica does not alleviate latch contention on the primary; it is designed for read workload offloading, not contention reduction.

Practice this question →

79

MCQeasy

You need to monitor the performance of an Azure SQL Database and set up alerts when the DTU consumption exceeds 80% for more than 5 minutes. Which Azure service should you use?

A.Azure Monitor metric alerts

B.Azure Advisor

C.Azure SQL Insights (preview)

D.Log Analytics workspace

AnswerA

Can alert on DTU percentage metric.

Why this answer

Azure Monitor metric alerts can be configured on DTU percentage. Option B is wrong because Azure Advisor provides recommendations but not real-time alerts. Option C is wrong because Azure SQL Insights is for visualization, not alerting.

Option D is wrong because Log Analytics workspaces store logs but do not natively provide metric alerts.

Practice this question →

80

MCQmedium

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert indicating 'Degradation in performance due to increased log write wait time'. What is the most likely cause of this issue?

A.High CPU utilization on the database server

B.Long-running blocking transactions

C.The log rate limit has been reached due to high transaction throughput

D.Insufficient storage space for data files

AnswerC

Log rate limits are a common cause of log write waits, especially in Business Critical or Hyperscale tiers.

Why this answer

High log write wait times typically indicate that the transaction log throughput is a bottleneck, often due to the log rate limit. Option A is wrong because high CPU utilization would cause other wait types like SOS_SCHEDULER_YIELD, not WRITELOG. Option B is wrong because long-running blocking transactions cause wait types like LCK_M_*, not increased log write wait time.

Option D is wrong because insufficient storage space for data files causes different symptoms, such as write errors or data file growth issues, but not specifically log write wait.

Practice this question →

81

MCQhard

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?

A.The query does not have enough memory grant

B.The database compatibility level is below 130

C.The index is defined with a filter predicate

D.The query hint MAXDOP 1 is used

AnswerA

Insufficient memory grant forces row mode execution.

Why this answer

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.

Practice this question →

82

Multi-Selecthard

You are monitoring an Azure SQL Database using Query Performance Insight and notice that a specific query has a high average duration and high CPU usage. The query plan shows a clustered index scan on a large table. Which two actions should you take to optimize performance? (Choose two.)

Select 2 answers

A.Rebuild the clustered index to reduce fragmentation.

B.Update statistics on the table to ensure the optimizer has current information.

C.Force a different query plan using Query Store hints.

D.Increase the DTU service tier of the database.

E.Create a covering nonclustered index on the columns used in the query.

AnswersB, E

Up-to-date statistics help the optimizer choose efficient plans.

Why this answer

Updating statistics provides the query optimizer with current data distribution information, which can lead to a more efficient query plan, potentially avoiding the clustered index scan. In Azure SQL Database, stale statistics are a common cause of suboptimal plans, and updating them is a low-cost, non-disruptive first step before considering index changes.

Exam trap

The trap here is that candidates often jump to index rebuilds or scaling up resources, overlooking that stale statistics are a frequent and easily fixable cause of poor query plans in Azure SQL Database.

Practice this question →

83

Multi-Selectmedium

You are configuring performance monitoring for an Azure SQL Database. You need to identify which two tools can be used to analyze query performance over time. Which TWO should you select?

Select 2 answers

A.Dynamic Management Views (DMVs)

B.Intelligent Insights

C.Extended Events

D.Azure SQL Analytics (Azure Monitor)

E.Query Store

AnswersD, E

Azure SQL Analytics provides historical performance metrics.

Why this answer

The correct answers are D and E. Query Store (E) captures and retains query execution metrics and plans over time, enabling historical performance analysis. Azure SQL Analytics (D), now part of Azure Monitor, provides a comprehensive view of query performance across multiple databases with historical data.

Option A (DMVs) displays current state and recent activity but lacks long-term history. Option B (Intelligent Insights) offers automated diagnostics and summaries but not detailed query-level history. Option C (Extended Events) is designed for real-time event capture, not historical analysis.

Practice this question →

84

MCQhard

You run the query in the exhibit on an Azure SQL Database. The result shows high wait_time_ms for PAGEIOLATCH_SH waits. What does this indicate?

A.I/O subsystem bottleneck for read operations

B.CPU bottleneck

C.Blocking between concurrent transactions

D.Memory pressure

AnswerA

PAGEIOLATCH_SH waits occur when waiting for I/O to complete for reading pages.

Why this answer

PAGEIOLATCH_SH waits indicate that a query is waiting for a data page to be read from disk into the buffer pool, which is an I/O operation. High wait_time_ms for this wait type typically points to an I/O subsystem bottleneck for read operations, making option A correct. Option B (CPU bottleneck) is incorrect because PAGEIOLATCH_SH is related to I/O, not CPU.

Option C (blocking) is incorrect because blocking is associated with LOCK waits, not PAGEIOLATCH_SH. Option D (memory pressure) is incorrect; while memory pressure can increase physical I/O, the wait type itself specifically indicates I/O latency for reading pages from disk.

Practice this question →

85

MCQhard

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?

A.The state is not set to "Disabled"

B.The emailAccountAdmins property is set to true

C.The retentionDays is set to 0, which may cause logs to be deleted immediately

D.The emailAddresses array is missing an entry

AnswerC

RetentionDays set to 0 disables retention, meaning audit logs will not be retained in storage.

Why this answer

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 D is wrong because emailAccountAdmins true is fine.

Practice this question →

86

MCQeasy

You need to configure Azure SQL Database to automatically scale up based on CPU usage. Which feature should you use?

A.Autoscale settings for the database

B.Elastic job

C.Elastic pool

D.Automatic tuning

AnswerA

Enables automatic scaling based on workload.

Why this answer

Azure SQL Database can use autoscale settings (in the DTU or vCore model) to automatically adjust resources based on metrics like CPU usage. Option B (Elastic job) is for running scheduled tasks across databases, not scaling. Option C (Elastic pool) is for managing multiple databases with shared resources, but it does not automatically scale a single database.

Option D (Automatic tuning) optimizes query performance, not resource scaling.

Practice this question →

87

MCQhard

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

A.Intelligent Insights report

B.Automatic Tuning recommendations

C.Azure Monitor metrics for the database

D.Query Store to review query execution plans and wait statistics

AnswerD

Query Store provides detailed query performance data for root cause analysis.

Why this answer

Query Store is the correct tool because it captures historical execution plans, runtime statistics, and wait statistics for individual queries, allowing you to pinpoint the root cause of a performance regression. Intelligent Insights provides high-level diagnostics but not the granular per-query plan and wait data needed for deep analysis of a specific query issue.

Exam trap

The trap here is that candidates confuse Intelligent Insights' automated diagnostics with the granular, query-level historical data that Query Store provides, assuming the alert's source (Intelligent Insights) is also the tool for deep manual investigation.

How to eliminate wrong answers

Option A is wrong because Intelligent Insights provides automated root cause analysis and recommendations at the database level, but it does not expose detailed per-query execution plans or wait statistics for manual investigation. Option B is wrong because Automatic Tuning focuses on automatically applying index and plan regression fixes, not on providing a historical record of query execution plans and waits for root cause analysis. Option C is wrong because Azure Monitor metrics (e.g., DTU/CPU usage, IOPS) show aggregate resource consumption, not per-query execution plans or wait statistics, so they cannot isolate the specific query's performance issue.

Practice this question →

88

Multi-Selecthard

Which THREE metrics should you monitor to proactively detect potential performance issues in an Azure SQL Database?

Select 3 answers

A.Log IO percentage (sys.dm_db_resource_stats)

B.Log backup frequency

C.Database size and growth rate

D.Wait statistics (sys.dm_os_wait_stats)

E.Query Store for query performance regressions

AnswersA, D, E

High log IO can indicate transaction throughput issues.

Why this answer

Options A, D, and E are correct. Log IO percentage (A) from sys.dm_db_resource_stats indicates transaction log throughput bottlenecks. Wait statistics (D) from sys.dm_os_wait_stats show where queries are waiting, revealing contention or resource pressure.

Query Store (E) tracks query plan regressions and performance degradation over time. Option B is incorrect because log backup frequency affects recovery point objectives (RPO), not proactive performance monitoring. Option C is incorrect because database size and growth rate relate to storage capacity planning, not real-time performance.

Practice this question →

89

MCQhard

You are a database consultant for a financial services company that uses an Azure SQL Managed Instance (MI) in the General Purpose tier (16 vCores, 1024 GB storage) for a critical application. The MI hosts a database that processes large batch transactions every night. Recently, the batch jobs have been failing due to timeout errors. You notice that the log write throughput is hitting the service tier limit (50 MB/s for General Purpose). The business requires the batch to complete within the same time window. You cannot change the application code or move to Business Critical tier due to budget constraints. You need to ensure the batch jobs complete successfully. What should you recommend?

A.Migrate the database to Business Critical tier.

B.Modify the batch jobs to use bulk insert with TABLOCK and batch inserts into smaller transactions.

C.Increase the managed instance storage to 2048 GB to improve log throughput.

D.Enable accelerated database recovery to reduce log I/O.

AnswerB

Minimally logged operations reduce log writes, staying within throughput limit.

Why this answer

To resolve the timeout errors, you need to reduce the log write throughput to stay within the 50 MB/s limit of the General Purpose tier. Option B achieves this by modifying batch jobs to use bulk insert with TABLOCK, which enables minimal logging, and breaking transactions into smaller batches to reduce the log write rate per transaction. Option A is incorrect because moving to Business Critical is not possible due to budget constraints.

Option C is incorrect because increasing storage does not affect the log write throughput limit; it only increases data and log file space. Option D is incorrect because accelerated database recovery reduces the amount of version store I/O during transaction rollback, not the log write throughput during normal operations.

Practice this question →

90

MCQeasy

Refer to the exhibit. You executed the Azure CLI command to list databases. You need to resume db3 to make it available for connections. Which command should you use?

A.az sql db restart --resource-group rg1 --server server1 --name db3

B.az sql db resume --resource-group rg1 --server server1 --name db3

C.az sql db start --resource-group rg1 --server server1 --name db3

D.az sql db update --resource-group rg1 --server server1 --name db3 --set status=Online

AnswerB

Correct command to resume a paused database.

Why this answer

`az sql db resume` is the command to resume a paused database. Option A is wrong because `az sql db restart` restarts an online database but does not resume a paused one. Option C is wrong because `az sql db start` is not a valid command for Azure SQL Database.

Option D is wrong because `az sql db update` can modify properties but cannot resume a paused database; resuming requires a dedicated command.

Practice this question →

91

MCQhard

Your Azure SQL Managed Instance is experiencing high latency for write transactions. You have identified that log write latency is the bottleneck. The instance uses Premium SSD with 5000 IOPS and 200 MB/s throughput. You observe that the log file is 500 GB and has grown significantly. What is the most likely cause and solution?

A.Increase the log file size to allow better write performance.

B.The instance has insufficient CPU; scale up the managed instance.

C.The disk is not fast enough for random writes; switch to Ultra Disk.

D.The log file is too large causing fragmentation; shrink it to reduce latency.

AnswerC

Correct. Ultra Disk provides very low latency and high throughput for sequential writes, directly addressing the log write latency bottleneck.

Why this answer

For Azure SQL Managed Instance, log write latency is critical for transaction write performance. Premium SSD P30 with 5000 IOPS and 200 MB/s throughput may become a bottleneck under heavy write workloads. Switching to Azure Ultra Disk provides significantly lower latency and higher IOPS/throughput, which can reduce log write latency.

Option D is incorrect because shrinking the transaction log does not improve performance and can cause severe fragmentation and performance degradation. Proper log backup scheduling is the correct method to manage log size.

Exam trap

Many candidates mistakenly believe shrinking a large log file improves write performance, but it actually degrades it due to increased fragmentation.

Practice this question →

92

MCQmedium

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?

A.Identify queries with high average duration

B.Identify queries that have had plan changes

C.Find the most frequently executed queries

D.Compare query performance over different time intervals

AnswerA

Filters for avg_duration > 1000 ms and orders descending.

Why this answer

The query filters for queries with average duration > 1000 ms and orders by duration, identifying high-duration queries. Option B is wrong because plan changes are not detected. Option C is wrong because it does not sort by frequency.

Option D is wrong because it does not compare across time intervals.

Practice this question →

93

MCQmedium

Your company uses Azure SQL Database with Active Geo-Replication for disaster recovery. During a routine failover drill, you observe that after failover to the secondary region, the application experiences significantly higher latency for write operations. The secondary database is in a different Azure region and has the same service objective. What is the most likely cause of the increased write latency?

A.Geo-replication introduces additional latency for all write operations.

B.The secondary database has a lower service objective than the primary.

C.The secondary database is not configured to accept write traffic.

D.The secondary database does not have a local read-scale replica configured.

AnswerD

After failover, the new primary may not have a readable secondary, so all read-write workloads hit the primary, increasing load and latency.

Why this answer

After failover to the secondary region, the new primary (formerly the secondary) is in a different Azure region. The secondary database did not have a local read-scale replica configured before failover, so the new primary lacks a read-scale replica to offload read traffic. This can cause resource contention and increased latency for write operations, as the database handles both reads and writes without dedicated replicas.

Option A is incorrect because geo-replication does not inherently add latency to all writes—only replicating writes asynchronously may cause slight delay, but not significant latency. Option B is incorrect because the service objective is stated to be the same. Option C is incorrect because after failover, the secondary becomes writable and accepts write traffic.

Practice this question →

94

MCQhard

You have a SQL Managed Instance with a large database. You notice that the automatic tuning recommendations are not being applied. You need to ensure that automatic tuning is enabled and that recommendations are automatically executed. What should you do?

A.Set the server-level automatic_tuning option to INHERIT and set database-level FORCE_LAST_GOOD_PLAN to ON

B.Set the database-level automatic tuning option to INHERIT

C.Set the database-level query_store_desired_state to ON

D.Set the server-level automatic_tuning option to OFF

AnswerA

Automatic tuning must be enabled at server level and FORCE_LAST_GOOD_PLAN must be ON for automatic execution.

Why this answer

To enable automatic tuning in Azure SQL Managed Instance, you must set the server-level automatic_tuning option to INHERIT (which allows the server to inherit the default Azure tuning behavior) and then set the database-level FORCE_LAST_GOOD_PLAN to ON. This configuration ensures that the automatic tuning system can both generate recommendations and automatically apply them (specifically the FORCE_LAST_GOOD_PLAN recommendation) without manual intervention.

Exam trap

The trap here is that candidates often confuse enabling Query Store (option C) with enabling automatic tuning, not realizing that Query Store is only a prerequisite and does not itself apply tuning recommendations automatically.

How to eliminate wrong answers

Option B is wrong because setting only the database-level automatic tuning option to INHERIT does not enable automatic execution of recommendations; it merely defers to the server-level setting, which by default is OFF, so no recommendations will be applied automatically. Option C is wrong because setting query_store_desired_state to ON only enables Query Store, which is a prerequisite for automatic tuning but does not itself enable automatic tuning or the automatic execution of recommendations. Option D is wrong because setting the server-level automatic_tuning option to OFF explicitly disables automatic tuning at the server level, preventing any recommendations from being applied automatically.

Practice this question →

95

Multi-Selectmedium

You are troubleshooting a performance issue on an Azure SQL Database. Which TWO actions should you prioritize to identify the root cause of high resource consumption?

Select 2 answers

A.Rebuild all indexes to improve query performance.

B.Change the database recovery model to Simple.

C.Scale the database to a higher service tier to mitigate the issue.

D.Review the Query Store Top Resource Consuming Queries report.

E.Query sys.dm_exec_query_stats to find queries with high total_worker_time.

AnswersD, E

Identifies queries consuming the most resources historically.

Why this answer

To identify the root cause of high resource consumption, you should use diagnostic tools that analyze query performance. The Query Store's Top Resource Consuming Queries report (D) provides historical insight into which queries consumed the most resources. Additionally, querying sys.dm_exec_query_stats (E) allows you to find queries with high total_worker_time, indicating CPU-intensive queries.

Options A (rebuilding indexes) and B (changing recovery model) are corrective actions, not diagnostic. Option C (scaling to a higher service tier) is a reactive mitigation that does not identify the root cause. Therefore, options D and E are the correct prioritized actions.

Practice this question →

96

MCQhard

You are configuring workload management for an Azure SQL Database using the JSON exhibit above for a classifier named 'MyWorkloadClassifier'. The classifier is intended to assign high importance to queries from user 'User1' in the 'SalesDB' database. However, after deployment, you notice that queries from 'User1' are not getting the expected resource guarantees. What is the most likely reason?

A.The 'importance' property is set to 'high' but the classifier requires 'importance' to be an integer.

B.The 'memberName' in the context is not correctly formatted; it should be a single user or group name without a backslash.

C.The 'min_percentile_resource' value is too low to guarantee resources.

D.The classifier is not associated with a workload group.

AnswerB

The backslash is not a valid JSON escape; memberName should be a simple user name like 'User1'.

Why this answer

The most likely reason is that the classifier's context is incorrectly formatted. In Azure SQL Database workload management, the 'memberName' property within the classifier context must be specified as a single user or group name without a backslash. If the JSON exhibit contains a backslash (e.g., 'SalesDB\User1' instead of 'User1'), the classifier fails to match the user.

Option A is incorrect because 'importance' is a string ('high'), not an integer. Option C is incorrect because 'min_percentile_resource' is not the issue; resource guarantees depend on the workload group's allocation. Option D is incorrect because a classifier does not need to be associated with a workload group directly; it is defined at the database level.

Practice this question →

97

MCQeasy

You are managing an Azure SQL Database that has Automatic Tuning enabled. You receive an alert that a query plan regression was detected and a plan correction was automatically applied. You want to verify the performance improvement. What should you use?

A.Use sys.dm_exec_query_stats to view current performance.

B.Review the Azure Monitor alert details.

C.Query the Query Store to compare query performance before and after the plan change.

D.Check the automatic tuning log in the Azure portal.

AnswerC

Query Store tracks performance over time, allowing comparison.

Why this answer

Query Store provides detailed query performance data, including plan regressions and improvements. Option A is wrong because sys.dm_exec_query_stats gives current performance metrics but does not provide historical comparison. Option B is wrong because Azure Monitor alert details only notify that a regression occurred, not the performance improvement.

Option D is wrong because the automatic tuning log shows actions taken but not detailed performance metrics for comparison.

Practice this question →

98

MCQeasy

You need to recommend a performance monitoring solution for a new Azure SQL Managed Instance deployment. The solution must provide historical query performance data and the ability to compare performance before and after index changes. What should you include in the recommendation?

A.Query Store with custom retention settings

B.SQL Server DMVs

C.Azure SQL Analytics solution in Log Analytics

D.Azure SQL Database Intelligent Insights

AnswerA

Correct. Query Store with custom retention settings provides historical query performance data and enables performance comparison before and after index changes.

Why this answer

Query Store captures historical query performance data and allows comparing performance before and after index changes, making it the correct choice for this requirement. SQL Server DMVs (Option B) only provide current state, not historical trends. Azure SQL Analytics solution (Option C) provides aggregated metrics but lacks per-query historical comparison.

Intelligent Insights (Option D) offers diagnostic analysis but not detailed historical query data.

Exam trap

Many candidates confuse Query Store with DMVs or Azure SQL Analytics. Remember that Query Store is specifically designed for historical query performance tracking and plan regression analysis.

Practice this question →

99

MCQeasy

Your Azure SQL Database has a recurring job that rebuilds indexes weekly. After a recent change, the job is taking much longer to complete. You suspect that the index fragmentation is higher than usual. What is the most efficient way to check index fragmentation across the database?

A.Query sys.indexes to check the fragmentation percentage.

B.Use the sys.dm_db_index_physical_stats dynamic management function.

C.Use SET SHOWPLAN_XML ON and run sample queries.

D.Use the sys.dm_db_missing_index_details DMV.

AnswerB

This DMF returns fragmentation details efficiently.

Why this answer

Sys.dm_db_index_physical_stats. This DMV provides fragmentation details (e.g., avg_fragmentation_percent) for all indexes in a database with minimal overhead when using limited scanning. Option A (sys.indexes) does not include fragmentation statistics.

Option C (SET SHOWPLAN_XML ON) shows query execution plans, not index fragmentation. Option D (sys.dm_db_missing_index_details) suggests new indexes to improve performance, but does not report existing index fragmentation.

Practice this question →

100

MCQhard

You are the database administrator for an Azure SQL Managed Instance hosting a data warehouse workload. You notice that the storage space consumed by the database is significantly larger than expected. The database has multiple large tables with clustered columnstore indexes. You suspect that the columnstore indexes have become fragmented and that deleted rows are consuming space. You need to reclaim storage space with minimal impact on query performance during business hours. What should you do?

A.Perform an ALTER INDEX REBUILD on the affected columnstore indexes after business hours.

B.Perform an ALTER INDEX REORGANIZE with the COMPRESS_ALL_ROW_GROUPS option on the affected columnstore indexes.

C.Perform an ALTER INDEX REORGANIZE on the affected columnstore indexes.

D.Rebuild the entire database by creating a new database and copying data.

AnswerB

This online operation reorganizes and compresses all row groups, reclaiming space from deleted rows with minimal impact.

Why this answer

REORGANIZE with COMPRESS_ALL_ROW_GROUPS compresses all row groups, including those in the delta store, and removes deleted rows from columnstore indexes. This operation is online and can be performed during business hours with minimal impact on query performance, unlike a rebuild which is offline and resource-intensive. It directly addresses the fragmentation and deleted row space consumption in columnstore indexes.

Exam trap

The trap here is that candidates often assume any REORGANIZE is sufficient, but without COMPRESS_ALL_ROW_GROUPS, it does not address deleted rows or delta store row groups, so the space is not reclaimed.

How to eliminate wrong answers

Option A is wrong because ALTER INDEX REBUILD is an offline operation that requires exclusive locks and significant resources, causing major performance impact during business hours; it should be scheduled after hours. Option C is wrong because a standard ALTER INDEX REORGANIZE without COMPRESS_ALL_ROW_GROUPS only defragments compressed row groups but does not force compression of delta store row groups or remove deleted rows, so it may not reclaim the expected space. Option D is wrong because rebuilding the entire database is an extreme, unnecessary operation that causes prolonged downtime and data movement, far exceeding the minimal impact approach needed.

Practice this question →

101

MCQhard

You are configuring automatic tuning for an Azure SQL Database. The database has a heavy OLTP workload. You want to automatically correct query plan choice regressions without manual intervention. Which automatic tuning option should you enable?

A.DROP_INDEX

B.CREATE_INDEX

C.CORRECT_INDEX

D.FORCE_LAST_GOOD_PLAN

AnswerD

Identifies and forces the last good plan to avoid regressions.

Why this answer

FORCE_LAST_GOOD_PLAN, is the correct automatic tuning option for Azure SQL Database to automatically correct query plan choice regressions. When the database engine detects that a newly compiled query plan performs worse than the previously known good plan, it can automatically force the last known good plan without manual intervention, which is ideal for a heavy OLTP workload where performance stability is critical.

Exam trap

The trap here is that candidates often confuse index tuning options (CREATE_INDEX, DROP_INDEX) with query plan regression correction, mistakenly thinking that creating or dropping indexes will fix a plan choice regression, when in fact FORCE_LAST_GOOD_PLAN is the specific feature designed for that purpose.

How to eliminate wrong answers

Option A is wrong because DROP_INDEX is an automatic tuning option that identifies and drops unused or duplicate indexes to improve write performance and reduce storage, but it does not address query plan regressions. Option B is wrong because CREATE_INDEX automatically creates missing indexes that improve query performance based on the workload, but it does not correct query plan choice regressions. Option C is wrong because CORRECT_INDEX is not a valid automatic tuning option in Azure SQL Database; the valid index-related options are CREATE_INDEX and DROP_INDEX only.

Practice this question →

102

MCQmedium

You are reviewing the long-term retention (LTR) policy for an Azure SQL Database. The exhibit shows the current policy. You need to ensure that backups are retained for at least 10 years for compliance. What should you do?

A.Increase the yearly retention to P10Y.

B.Change the weekOfYear to 10.

C.Increase the monthly retention to P120M.

D.Increase the weekly retention to P10W.

AnswerA

Yearly retention covers the 10-year requirement.

Why this answer

The current yearly retention is P3Y (3 years), which is insufficient for the 10-year compliance requirement. Increasing it to P10Y retains yearly backups for 10 years. Option B is incorrect because weekOfYear specifies which week's backup is retained for yearly retention, not the retention period.

Option C is incorrect because monthly retention at P120M (120 months = 10 years) would retain 120 monthly backups, but the requirement is for yearly retention, and monthly retention doesn't cover full 10-year compliance on its own. Option D is incorrect because weekly retention at P10W (10 weeks) is far less than 10 years.

Practice this question →

103

MCQhard

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?

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

B.The login password has expired; update the password in the job step.

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

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

AnswerB

SQL Server password expiration can cause intermittent login failures.

Why this answer

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.

Exam trap

The trap here is that candidates may overlook password expiration as a cause for intermittent failures and instead focus on connectivity or agent issues, but the specific 'Login failed for user' error with SQL authentication points directly to credential expiration.

How to eliminate wrong answers

Option A is wrong because a failover group with a read-only secondary would cause connection failures when trying to write, but the error 'Login failed for user' is an authentication error, not a write-permission error; also, the job could be configured to connect to the primary listener. Option C is wrong because a schedule conflict would not produce a 'Login failed for user' error; it would typically result in a job being skipped or a concurrency error. Option D is wrong because if the SQL Server Agent were not running, the job would not run at all (not intermittently fail), and the error would be about the Agent service, not a login failure.

Practice this question →

104

MCQmedium

Your Azure SQL Database is experiencing deadlocks. You have enabled deadlock graphs in the extended events session. After capturing a deadlock, you need to analyze it to determine which queries are involved. What should you use?

A.Query sys.dm_exec_requests with a filter on blocking.

B.Open the deadlock graph file in SQL Server Management Studio (SSMS).

C.Azure Monitor for SQL and view deadlock metrics.

D.Query Store and review the regressed queries.

AnswerB

SSMS can display deadlock graphs captured via extended events.

Why this answer

To analyze a deadlock graph captured via extended events, you should open the .xdl file in SQL Server Management Studio (SSMS), which provides a graphical representation of the deadlock, showing the processes involved, the resources, and the queries. Option B is correct. Option A is incorrect because sys.dm_exec_requests with a blocking filter shows current blocking, not historical deadlock details.

Option C is incorrect because Azure Monitor for SQL provides metrics but not the detailed deadlock graph. Option D is incorrect because Query Store tracks query performance over time but does not capture deadlock events or graphs.

Practice this question →

105

MCQmedium

Refer to the exhibit. A user reports being unable to connect to the database. What is the most likely cause?

A.The user account is locked out due to too many failed login attempts.

B.The user does not have permission to access the database.

C.The server firewall is blocking the IP address.

D.The user is using an incorrect password.

AnswerD

The error 'Password did not match' directly indicates an incorrect password.

Why this answer

The error logs show multiple login failures for user 'appuser' from IP 192.168.1.100, with reasons indicating password issues. The combination of error messages suggests the password is incorrect or expired.

Practice this question →

106

MCQeasy

A company has an Azure SQL Database that is experiencing performance degradation during peak hours. The database is configured with the Standard tier (S2). Which action should you recommend to improve performance without changing the application code?

A.Scale up the database to a higher service objective (e.g., S3).

B.Enable Query Store and run the Performance Dashboard.

C.Enable read scale-out to offload read queries.

D.Create nonclustered indexes on all tables.

AnswerA

Increases DTU limit, providing more resources.

Why this answer

Scaling up to a higher service objective (e.g., S3) increases DTUs, providing more resources to handle peak loads without requiring any application code changes. Option B is wrong: Query Store aids in performance monitoring and troubleshooting but does not directly improve performance. Option C is wrong: read scale-out offloads read-only workloads to a readable secondary, but the issue is general performance degradation, not specifically read-heavy.

Option D is wrong: creating nonclustered indexes may improve query performance but often requires application or query adjustments, and does not guarantee improvement without code changes.

Practice this question →

107

MCQmedium

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?

A.sys.dm_exec_sessions

B.sys.query_store_query_text

C.sys.dm_exec_requests

D.The SQL Server error log, viewable using sp_readerrorlog.

AnswerD

Trace flag 1222 outputs deadlock graphs to the SQL Server error log, viewable via sp_readerrorlog or similar tools.

Why this answer

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.

Exam trap

Candidates may confuse trace flag 1222 with Extended Events or assume deadlock info is accessible via DMVs like sys.dm_exec_requests. However, trace flag 1222 outputs specifically to the error log.

Practice this question →

108

MCQhard

Refer to the exhibit. The SalesDB database is experiencing log space full errors. Based on the exhibit, what is the most likely reason?

A.The database storage is almost full, preventing log growth

B.The transaction log is not being truncated, possibly due to an active transaction or replication

C.The log rate limit is being throttled due to high log IO percentage

D.The database should be scaled to BusinessCritical tier for faster log writes

AnswerB

High used log space with high write rate suggests truncation issue.

Why this answer

The exhibit shows a transaction log with very low free space (0.01%) and a high log used percentage (99.99%), but the data file has ample free space. This indicates the transaction log is not being truncated, likely due to an active transaction preventing log reuse or a replication scenario that marks log records as needed. In Azure SQL Database, log space is managed automatically, but long-running transactions or replication can block log truncation, causing log space full errors even when storage is not full.

Exam trap

The trap here is that candidates see 'log space full' and immediately think of storage capacity issues (Option A) or performance throttling (Option C), but the exhibit clearly shows the data file has free space, directing the focus to log truncation failure as the root cause.

How to eliminate wrong answers

Option A is wrong because the exhibit shows the data file has 99.99% free space, so database storage is not almost full; the issue is specifically with the transaction log, not overall storage. Option C is wrong because the log rate limit is a performance throttle that slows log writes but does not cause log space full errors; the log used percentage is high due to lack of truncation, not throttling. Option D is wrong because scaling to BusinessCritical tier improves IO performance but does not resolve log truncation issues; the root cause is an active transaction or replication blocking log reuse, not insufficient write speed.

Practice this question →

109

MCQmedium

You have a SQL Managed Instance that hosts a critical OLTP database. You notice that the average query wait time has increased significantly over the past hour. You need to identify the top resource waits. What should you use?

A.sys.dm_exec_query_stats

B.Query Store Wait Stats in SSMS

C.sys.dm_os_wait_stats

D.sys.dm_db_index_usage_stats

AnswerC

Provides cumulative wait statistics for all sessions.

Why this answer

C is correct because sys.dm_os_wait_stats is the dynamic management view that aggregates wait statistics across all sessions in the SQL Server instance, including SQL Managed Instance. It provides cumulative wait times categorized by wait type (e.g., PAGEIOLATCH, LCK_M_S), making it the appropriate tool to identify top resource waits when average query wait time increases.

Exam trap

The trap here is that candidates confuse performance metrics DMVs (like sys.dm_exec_query_stats) with wait statistics DMVs, or they assume Query Store Wait Stats is the primary diagnostic tool for real-time wait analysis, when sys.dm_os_wait_stats is the direct and authoritative source for identifying top resource waits.

How to eliminate wrong answers

Option A is wrong because sys.dm_exec_query_stats returns aggregated performance statistics for cached query plans (e.g., CPU time, logical reads), not wait statistics; it cannot show resource waits. Option B is wrong because Query Store Wait Stats in SSMS is a feature that surfaces wait statistics from the Query Store, but it relies on the Query Store being enabled and configured, and it does not provide the comprehensive, instance-level wait statistics that sys.dm_os_wait_stats does for immediate diagnosis. Option D is wrong because sys.dm_db_index_usage_stats tracks index usage patterns (seeks, scans, updates), not wait times or resource contention.

Practice this question →

110

Multi-Selecteasy

You are monitoring an Azure SQL Database. You need to identify which two metrics are most important for detecting a memory pressure issue. Which TWO should you select?

Select 2 answers

A.Log IO percentage

B.Memory grants pending

C.Page life expectancy

D.CPU percentage

E.Data IO percentage

AnswersB, C

High pending grants indicate memory pressure.

Why this answer

Memory grants pending indicates queries that are waiting for memory grants, which is a direct sign of memory pressure. Page life expectancy (PLE) measures how long pages remain in the buffer pool; a low PLE suggests that pages are being evicted frequently due to memory pressure. Therefore, both B and C are the best metrics to detect memory pressure.

CPU percentage (D) indicates CPU pressure, and Data IO percentage (E) indicates I/O pressure, not memory pressure. Log IO percentage (A) relates to log write activity, not memory. Hence, the correct choices are B and C.

Practice this question →

111

MCQeasy

You are the database administrator for a company that uses Azure SQL Managed Instance. The instance hosts a mission-critical database that experiences periodic performance degradation. You need to set up a proactive monitoring solution that sends alerts when the average DTU usage exceeds 80% over a 5-minute period. The solution should minimize cost. What should you do?

A.Create an Azure Monitor metric alert on the 'dtu_consumption_percent' metric for the managed instance.

B.Create a SQL Server Agent job that checks sys.dm_db_resource_stats every 5 minutes and sends an email.

C.Use Elastic Database Jobs to run a query periodically and send alerts.

D.Stream diagnostic logs to a Log Analytics workspace and create a log alert rule.

AnswerA

Azure Monitor metric alerts are low-cost and can alert when average DTU usage exceeds threshold over a period.

Why this answer

Azure Monitor metric alerts on the 'dtu_consumption_percent' metric provide a cost-effective, proactive monitoring solution for Azure SQL Managed Instance. Metric alerts are charged per rule, and there is no additional ingestion cost, making them minimal cost. Option B is incorrect because SQL Server Agent jobs require a SQL Server Agent service and are not designed for real-time metric monitoring; they also incur overhead.

Option C is incorrect because Elastic Database Jobs are for executing T-SQL across databases, not for metric-based alerting. Option D is incorrect because streaming diagnostic logs to a Log Analytics workspace and creating a log alert rule incurs data ingestion costs, which is not minimal cost.

Practice this question →

112

Multi-Selectmedium

You are tuning an Azure SQL Database that has a heavy write workload. You need to reduce the number of log writes. Which TWO actions should you take? (Choose two.)

Select 2 answers

A.Change the database recovery model to SIMPLE

B.Use minimal logging for bulk operations

C.Rebuild indexes during peak hours

D.Use delayed durability for transactions

E.Increase batch size in INSERT operations

AnswersD, E

Delayed durability reduces log flush frequency.

Why this answer

Options D and E are correct. Option D: Using delayed durability (DELAYED_DURABILITY = ON for transactions) reduces the number of log flush operations, decreasing log writes. Option E: Increasing batch size in INSERT operations reduces the number of log records per row, thereby reducing total log writes.

Option A is incorrect because changing the recovery model to SIMPLE is not supported in Azure SQL Database, and even if it were, it would not reduce log writes for heavy write workloads; it only affects transaction log management. Option B is incorrect because minimal logging for bulk operations is not fully supported in Azure SQL Database (only available for specific operations like BULK INSERT against a heap with certain conditions), and it is not a general action to reduce log writes for a heavy write workload. Option C is incorrect because rebuilding indexes during peak hours actually increases log writes due to index rebuild operations.

Practice this question →

113

Multi-Selecteasy

Which TWO metrics in Azure SQL Database's Intelligent Insights can indicate a performance degradation due to increased resource consumption? (Choose two.)

Select 2 answers

A.Increased query duration.

B.High number of deadlocks.

C.Increased transaction log usage.

D.High DTU consumption.

E.Failed authentication attempts.

AnswersA, D

Longer query duration indicates performance degradation.

Why this answer

Options A and D are correct. In Azure SQL Database's Intelligent Insights, increased query duration and high DTU consumption are key metrics that indicate performance degradation due to increased resource consumption. Increased query duration suggests that queries are taking longer, often due to resource contention.

High DTU consumption reflects high usage of CPU, memory, and I/O, which can degrade performance. Option B (high number of deadlocks) indicates concurrency issues but does not directly measure resource consumption. Option C (increased transaction log usage) is related to write activity, not a primary performance metric in Intelligent Insights.

Option E (failed authentication attempts) is a security metric, unrelated to resource consumption.

Practice this question →

114

Multi-Selectmedium

Which TWO actions can reduce storage costs for an Azure SQL Database? (Select two.)

Select 2 answers

A.Increase the service tier to get more storage.

B.Enable row or page compression on large tables.

C.Reduce backup retention to 1 day.

D.Enable automatic tuning to optimize query plans.

E.Archive historical data to Azure Blob Storage using external tables.

AnswersB, E

Compression reduces storage footprint.

Why this answer

To reduce storage costs for an Azure SQL Database, you can enable row or page compression on large tables (option B) to decrease data size. Archiving historical data to Azure Blob Storage using external tables (option E) moves cold data out of the database, reducing storage consumption. Option A is incorrect because increasing the service tier typically increases storage costs.

Option C is wrong because reducing backup retention does not affect storage used for data files, only backup storage. Option D is incorrect because automatic tuning optimizes query performance, not storage costs. Therefore, the correct answers are B and E.

Practice this question →

115

MCQhard

Refer to the exhibit. You notice the database db1 is currently 80% full on storage, and the service objective is S3. Which action would best prevent storage full errors while minimizing cost?

A.Enable automatic storage growth on the database.

B.Change the service objective to S4.

C.Increase the maximum database size to 250 GB.

D.Scale the database to a higher service tier like Standard S4.

AnswerC

S3 supports up to 250 GB storage. Increasing max size to 250 GB provides headroom without changing the service tier, minimizing cost.

Why this answer

The exhibit shows the database is at 80% of its current maximum size. The S3 service objective allows a maximum database size of up to 250 GB. By increasing the maximum size to 250 GB, you provide sufficient storage headroom without changing the service tier, which would incur higher costs.

Option A (automatic storage growth) is not a user-configurable feature in Azure SQL Database. Options B and D involve scaling to a higher service tier (S4 or higher), which is unnecessary and more expensive.

Practice this question →

116

MCQhard

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

A.Blocking due to lock contention

B.CPU pressure from high-complexity queries

C.I/O subsystem bottleneck

D.Insufficient memory allocation for the database

AnswerC

PAGEIOLATCH_SH waits indicate I/O latency.

Why this answer

The exhibit shows PAGEIOLATCH_SH and WRITELOG waits dominating the wait statistics, which are classic indicators of I/O subsystem bottlenecks. PAGEIOLATCH_SH waits occur when a session is waiting for a data page to be read from disk into the buffer pool, while WRITELOG waits indicate delays in writing to the transaction log. These waits are not caused by CPU or memory pressure, but by slow disk I/O, making option C the correct root cause.

Exam trap

The trap here is that candidates see PAGEIOLATCH_SH and assume it is always caused by insufficient memory, but the combination with WRITELOG waits clearly points to an I/O bottleneck, not a memory issue.

How to eliminate wrong answers

Option A is wrong because blocking due to lock contention would manifest as LCK_M_* waits (e.g., LCK_M_S, LCK_M_X), not PAGEIOLATCH_SH or WRITELOG waits. Option B is wrong because CPU pressure from high-complexity queries would show SOS_SCHEDULER_YIELD or CXPACKET waits, not I/O-related waits. Option D is wrong because insufficient memory allocation would cause PAGEIOLATCH_SH waits only if memory pressure forces excessive physical I/O, but the presence of WRITELOG waits points directly to a log write bottleneck, not a memory shortage; memory pressure alone would not cause WRITELOG waits.

Practice this question →

117

MCQmedium

Refer to the exhibit. You are analyzing query performance using sys.dm_exec_query_stats. Based on the output, which query is the best candidate for optimization to reduce overall CPU usage?

A.Query 2 because it has the highest average CPU per execution.

B.Query 1 because it has the highest total_cpu_time.

C.All queries should be optimized equally.

D.Query 3 because it runs most frequently.

AnswerA

High average CPU suggests inefficiency; optimizing it can reduce overall CPU significantly.

Why this answer

Query 2 is the best candidate for optimization because it has the highest average CPU per execution, indicating that each run of this query consumes significantly more CPU resources than the others. Reducing the CPU cost per execution for this query will yield the greatest per-execution savings, making it the most efficient target for reducing overall CPU usage.

Exam trap

The trap here is that candidates often focus on total CPU time or execution frequency, but the key metric for optimization efficiency is average CPU per execution, which directly measures the cost of each individual query run.

How to eliminate wrong answers

Option B is wrong because total_cpu_time alone does not account for execution count; a query with high total CPU but many executions may have a low per-execution cost, and optimizing it might yield less benefit per change. Option C is wrong because not all queries have equal impact; focusing on the query with the highest average CPU per execution provides the most efficient optimization for reducing overall CPU usage. Option D is wrong because frequency of execution does not directly correlate with CPU impact; a frequently run query with low per-execution CPU may contribute less to total CPU than a less frequent but expensive query.

Practice this question →

118

Multi-Selecthard

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the Business Critical service tier. Which THREE configuration changes can improve query performance for reporting queries without significantly impacting OLTP operations?

Select 3 answers

A.Increase MAXDOP for the database to 8.

B.Create nonclustered columnstore indexes on large reporting tables.

C.Configure a read-scale replica and direct reporting queries to it.

D.Disable automatic tuning to prevent plan changes.

E.Enable result set caching for the database.

AnswersB, C, E

Columnstore indexes significantly improve aggregation and scan performance.

Why this answer

Creating nonclustered columnstore indexes (option B) improves aggregation and reporting query performance by using columnar storage and batch processing. Configuring a read-scale replica (option C) offloads reporting queries to a secondary replica, reducing contention on the primary for OLTP operations. Enabling result set caching (option E) caches query results in the Premium/Business Critical tiers, reducing repeated reads for static reporting data.

Option A (increasing MAXDOP to 8) can lead to parallel query contention and negatively impact OLTP performance, especially on smaller instances. Option D (disabling automatic tuning) removes beneficial plan corrections and is not recommended for improving performance.

Practice this question →

119

MCQhard

You have an Azure SQL Database with a heavy workload. You notice that the `PAGEIOLATCH_SH` wait is the top wait. Which performance issue does this indicate?

A.Blocking

B.CPU bottleneck

C.I/O subsystem bottleneck

D.Memory pressure

AnswerC

`PAGEIOLATCH_SH` indicates slow I/O for reading pages.

Why this answer

The `PAGEIOLATCH_SH` wait type indicates that a query is waiting for a data page to be read from disk into the buffer pool. Since this is the top wait, it points to an I/O subsystem bottleneck where the storage cannot keep up with the demand for reading pages, causing performance degradation.

Exam trap

The trap here is that candidates confuse `PAGEIOLATCH_SH` with memory pressure or blocking, but the key distinction is that this wait type specifically measures I/O latency for reading pages from disk, not memory availability or lock contention.

How to eliminate wrong answers

Option A is wrong because blocking is indicated by wait types like `LCK_M_*` (e.g., `LCK_M_S` or `LCK_M_X`), not by `PAGEIOLATCH_SH`. Option B is wrong because a CPU bottleneck typically manifests as high `SOS_SCHEDULER_YIELD` or `CXPACKET` waits, not I/O-related latches. Option D is wrong because memory pressure usually shows as `PAGEIOLATCH_EX` (for writes) or `RESOURCE_SEMAPHORE` waits, and while `PAGEIOLATCH_SH` can be exacerbated by insufficient memory, the primary indicator here is an I/O subsystem issue.

Practice this question →

120

Matchingmedium

Match each Azure SQL Database monitoring metric to its meaning.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Percentage of DTU or CPU used

Percentage of data I/O limit used

Percentage of log write limit used

Number of deadlocks occurring per minute

Why these pairings

These metrics are used to monitor resource usage and performance in Azure SQL Database.

Practice this question →

121

MCQhard

You have an Azure SQL Database that is part of an elastic pool. You notice that the pool's eDTU consumption is consistently high, and some databases are experiencing resource contention. You need to ensure that a critical database always gets a minimum amount of resources. What should you configure?

A.Configure per-database max eDTU for the critical database

B.Increase the eDTU of the elastic pool

C.Move the critical database to a dedicated service tier

D.Configure per-database min eDTU for the critical database

AnswerD

Min eDTU guarantees a minimum amount of resources.

Why this answer

Per-database min eDTU guarantees a minimum level of resources for a specific database within an elastic pool, ensuring that critical databases get the required resources even when the pool is under high utilization. Option A is incorrect because per-database max eDTU only limits the maximum resource usage, not guaranteeing any minimum. Option B is incorrect because increasing the pool's eDTU provides more total resources but does not guarantee that any single database receives a specific amount.

Option C is incorrect because while moving to a dedicated service tier would guarantee resources, it is more expensive and unnecessary if the critical database only needs a minimum resource guarantee within the existing pool.

Practice this question →

122

MCQmedium

You are managing an Azure SQL Database that is used by a real-time analytics application. The database uses the Hyperscale service tier. You notice that the transaction log rate is consistently high, causing performance degradation. You need to reduce the log generation rate without compromising data durability. What should you do?

A.Enable compression on transaction log backups.

B.Create additional nonclustered indexes on frequently updated tables.

C.Increase the service tier to Business Critical.

D.Increase the backup retention period.

AnswerC

Increasing the service tier to Business Critical provides better log write performance through local SSD storage, reducing the log generation rate. This option is correct.

Why this answer

Increasing the service tier to Business Critical does not reduce the transaction log generation rate; it only improves log write throughput. The log generation rate is workload‑dependent and is not changed by moving to a higher tier. None of the provided options achieve the goal of reducing the log generation rate while maintaining durability.

To reduce the rate, you would need to optimize the application (e.g., batching, reducing transactions), which is not offered among the choices.

Exam trap

A common trap is to think that compressing log backups reduces log generation, but it only reduces backup size. The actual solution is to upgrade to a higher tier with better log write performance.

Practice this question →

123

Multi-Selecthard

You are optimizing an Azure SQL Database that uses the Business Critical tier. Which TWO factors affect the maximum log rate?

Select 2 answers

A.Service level objective (SLO)

B.Number of vCores

C.Backup retention period

D.Number of log files

E.Page compression level

AnswersA, B

Correct. The service level objective (SLO) defines the resource limits, including the maximum log rate.

Why this answer

The maximum log rate in Azure SQL Database Business Critical tier is determined by the service level objective (SLO) which includes a preset log rate limit, and the number of vCores as part of that SLO. The number of log files does not affect the log rate; log write throughput is governed by the storage performance tied to the SLO. Backup retention period and page compression level have no impact on log rate.

Practice this question →

124

MCQmedium

You are monitoring an Azure SQL Database using the Automatic Tuning feature. The database has a workload that is read-intensive. You enable the CREATE INDEX and DROP INDEX options. After a week, you observe that the database has created several new indexes automatically. However, you notice that one of the new indexes is causing increased write latency for an application that performs frequent updates. What should you do to resolve the issue without losing the benefits of automatic tuning for other indexes?

A.Use the Azure portal to revert all automatic tuning recommendations for the past week.

B.Manually create the missing indexes that were dropped by automatic tuning.

C.Disable automatic tuning for the entire database.

D.Manually drop the problematic index using a DROP INDEX command.

AnswerD

You can manually revert a specific index while leaving automatic tuning active for other indexes.

Why this answer

Manually dropping the problematic index allows you to resolve the specific performance issue caused by increased write latency while retaining the benefits of automatic tuning for other indexes. The Automatic Tuning feature in Azure SQL Database can create indexes to improve read performance, but these indexes may introduce overhead on write operations. By issuing a DROP INDEX command, you surgically remove only the offending index without disabling the overall tuning mechanism.

Exam trap

The trap here is that candidates may think disabling automatic tuning entirely or reverting all recommendations is necessary, but the correct approach is to manually drop only the problematic index to preserve the benefits of automatic tuning for other indexes.

How to eliminate wrong answers

Option A is wrong because reverting all automatic tuning recommendations for the past week would undo all index changes, including beneficial ones, and does not target the specific problematic index. Option B is wrong because manually creating missing indexes that were dropped by automatic tuning is irrelevant; the issue is a newly created index causing write latency, not missing indexes. Option C is wrong because disabling automatic tuning for the entire database would stop all future tuning recommendations and lose the benefits of automatic index management for other queries, which is an overreaction to a single problematic index.

Practice this question →

125

MCQmedium

You are optimizing an Azure SQL Database that has a heavy workload of both reads and writes. The database has a clustered columnstore index on a large fact table. You notice that the index has high fragmentation and the performance of queries against this table is degrading. What should you do to improve performance?

A.Rebuild the clustered columnstore index

B.Increase the service tier to get more IOPS

C.Drop and recreate the clustered columnstore index

D.Reorganize the clustered columnstore index

AnswerA

Rebuild reduces fragmentation and improves scan performance.

Why this answer

Rebuilding the clustered columnstore index reduces fragmentation and improves query performance. Rebuilding is the recommended method to defragment a columnstore index. Option B is incorrect because increasing the service tier addresses performance issues related to IOPS but does not resolve fragmentation.

Option C is incorrect because dropping and recreating the index is more disruptive and unnecessary; a rebuild achieves the same result with less overhead. Option D is incorrect because reorganize is not supported for columnstore indexes; only rebuild is.

Practice this question →

126

Multi-Selecthard

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

Select 3 answers

A.Replication that has not delivered transactions to the subscriber

B.A memory-optimized table with a large number of rows

C.A long-running transaction that has not been committed

D.A missing index on a large table that causes excessive updates

E.Page compression enabled on tables

AnswersA, C, D

Log space held for replication.

Why this answer

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.

Practice this question →

127

MCQhard

You are configuring a private endpoint for an Azure SQL Database. The exhibit shows the current network ACLs. You need to ensure that only traffic from a specific subnet in VNet1 is allowed, and all other traffic is denied. What should you do?

A.No changes needed; the configuration already meets the requirement.

B.Add an IP rule to allow the subnet's IP range.

C.Set ignoreMissingVnetServiceEndpoint to true.

D.Change defaultAction to Allow.

AnswerA

Default deny with a VNet rule for the subnet allows only that subnet.

Why this answer

The exhibit shows that the private endpoint is configured with a network ACL that has a deny-all default action and an explicit allow rule for the specific subnet in VNet1. Since private endpoints use network policies (like NSG rules) to filter traffic, and the ACL already denies all traffic except the allowed subnet, no changes are needed. The configuration meets the requirement because the private endpoint's network ACLs are evaluated in order, and the explicit allow for the subnet overrides the default deny for all other traffic.

Exam trap

The trap here is that candidates may think they need to add an IP rule for the subnet's IP range (Option B) or change the default action to allow (Option D), not realizing that private endpoints use virtual network rules that already implicitly allow traffic from the subnet, and the default deny action is correct for restricting all other traffic.

How to eliminate wrong answers

Option B is wrong because adding an IP rule to allow the subnet's IP range is unnecessary; the private endpoint already uses the subnet's virtual network identifier, not a raw IP range, and the ACL already allows the subnet via a virtual network rule. Option C is wrong because 'ignoreMissingVnetServiceEndpoint' is a property for Azure SQL Database firewall rules when using service endpoints, not for private endpoint ACLs; it does not apply here. Option D is wrong because changing 'defaultAction' to 'Allow' would permit all traffic, including traffic from outside the specified subnet, which contradicts the requirement to deny all other traffic.

Practice this question →

128

MCQeasy

You are monitoring a critical production Azure SQL Database that is experiencing intermittent query timeouts. The database is configured with the General Purpose service tier. You need to identify the root cause of the timeouts with minimal overhead. What should you review first?

A.Implement automatic tuning to force plan regression fixes.

B.Increase the database service tier to Business Critical.

C.Use sys.dm_exec_query_stats to identify queries with high wait statistics.

D.Enable Query Store and review the Regressed Queries report.

AnswerC

Low-overhead way to find problematic queries and their wait types.

Why this answer

Sys.dm_exec_query_stats provides aggregated query performance data, including wait statistics, with minimal overhead. High wait stats often point to resource contention (e.g., CPU, IO) that can cause timeouts. Option A is incorrect: automatic tuning is for addressing plan regression after it's identified, not for initial troubleshooting.

Option B is incorrect: increasing the service tier is a reactive scaling measure, not a diagnostic step. Option D is incorrect: Query Store must be enabled to use the Regressed Queries report, and it may not be the first step if not already enabled; sys.dm_exec_query_stats is a lightweight dynamic management view available by default.

Practice this question →

129

MCQhard

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

A.Increase max degree of parallelism

B.Increase the log rate limit by scaling up the service level objective

C.Reduce backup retention period

D.Add more compute replicas

AnswerB

Higher SLOs provide higher log rate limits.

Why this answer

Increasing the log rate limit by scaling up the service level objective (SLO) allows more transactions per second, reducing log rate throttling. Option B is correct. Option A is wrong because increasing max degree of parallelism does not directly affect log rate; it may even increase log generation.

Option C is wrong because backup retention period does not influence log rate. Option D is wrong because adding compute replicas does not increase the log rate limit; log rate is tied to the primary replica's SLO.

Practice this question →

130

MCQmedium

You are managing an Azure SQL Database in the General Purpose service tier with 100 DTUs. The database supports an e-commerce application. Over the past week, you notice that CPU usage frequently reaches 100% during peak hours, causing query timeouts. You have identified that the most expensive query is a SELECT statement that joins five tables and returns aggregated sales data. You need to reduce CPU pressure without changing the service tier or adding indexes. What should you do?

A.Scale up to the Business Critical tier.

B.Increase the DTUs to 200.

C.Use Query Store to identify and force a more efficient execution plan.

D.Add a nonclustered index on the join columns.

AnswerC

Query Store can capture plan history and force a plan that uses less CPU without changing tier or indexes.

Why this answer

Using Query Store to identify and force a more efficient execution plan can reduce CPU usage without changing the service tier or adding indexes. Option A is incorrect because scaling to Business Critical changes the service tier, which is not allowed. Option B is incorrect because increasing DTUs changes the performance level and may increase cost, and it doesn't address the root cause of the expensive query.

Option D is incorrect because adding indexes is explicitly prohibited by the requirement.

Practice this question →

131

Multi-Selecthard

Which THREE factors should you consider when choosing between vCore and DTU purchase models for Azure SQL Database performance optimization?

Select 3 answers

A.Only vCore supports Azure Hybrid Benefit.

B.DTU is simpler for customers who want a bundled metric.

C.vCore allows reserved instance pricing for cost savings.

D.vCore provides more predictable performance for consistent workloads.

E.Only DTU supports elastic pools.

AnswersB, C, D

DTU combines compute, storage, and I/O.

Why this answer

The DTU (Database Transaction Unit) model bundles compute, storage, and I/O into a single, simple metric, making it easier for customers who want a straightforward, pre-configured performance tier without needing to manage individual resources. This contrasts with the vCore model, which requires separate configuration of vCores, memory, and storage, offering more granular control but greater complexity.

Exam trap

The trap here is that candidates often assume Azure Hybrid Benefit or elastic pools are exclusive to one model, when in fact both features are available across vCore and DTU, leading to incorrect elimination of correct options like B, C, and D.

Practice this question →

132

MCQhard

Refer to the exhibit. An Azure SQL Database in the Standard tier (S2: 50 DTU) is consistently showing high DTU consumption. Which action would most effectively reduce DTU usage?

A.Create an index on the tables accessed by Query 1234

B.Increase the log_write_percent by adjusting transaction log settings

C.Scale up to a higher service tier (e.g., S3)

D.Rebuild all indexes in the database

AnswerA

Reducing logical reads via indexing directly lowers DTU usage.

Why this answer

Query 1234 is likely the primary contributor to high DTU consumption, as indicated by the exhibit (not shown here but implied). Creating an index on the tables it accesses can reduce the number of logical reads and improve query performance, directly lowering DTU usage without additional cost. This is the most effective action because it addresses the root cause—poor query performance—rather than masking the symptom with more resources.

Exam trap

The trap here is that candidates often choose scaling up (Option C) as a quick fix, not realizing that it only increases resource limits without addressing the underlying inefficient query or missing index, leading to continued high DTU usage and unnecessary cost.

How to eliminate wrong answers

Option B is wrong because increasing log_write_percent is not a user-configurable setting; it is a metric that reflects the percentage of DTU used for log writes, and adjusting transaction log settings (e.g., log file size or growth increment) does not directly reduce DTU consumption. Option C is wrong because scaling up to a higher service tier (e.g., S3) would increase available DTUs but does not reduce actual DTU usage; it merely accommodates the high consumption, which is a costly workaround. Option D is wrong because rebuilding all indexes in the database is a heavy operation that temporarily increases DTU consumption and may not address the specific query causing the high usage; it is a blunt, resource-intensive approach that could worsen the problem.

Practice this question →

133

MCQmedium

You manage an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the average CPU usage is consistently above 90% during business hours. You have enabled Intelligent Insights, which recommends creating a missing index. What should you do first to validate the recommendation before implementing it?

A.Use Query Store to review query performance and missing index details.

B.Scale up the managed instance to a higher tier.

C.Enable automatic index tuning.

D.Create the recommended index immediately.

AnswerA

Using Query Store allows you to review query performance and missing index details to validate the recommendation before implementation.

Why this answer

Use Query Store to review query performance and missing index details. Intelligent Insights provides recommendations, but you should validate them using Query Store, which shows actual query performance and missing index details. This helps confirm the index will reduce CPU usage without negative side effects.

B is wrong because scaling up increases resources but doesn't address the root cause; it may be unnecessary. C is wrong because automatic index tuning would implement changes without validation, which could be risky. D is wrong because creating the index immediately without validation might cause performance issues or be unnecessary.

Practice this question →

134

MCQeasy

You need to configure Azure SQL Database to automatically adjust indexing based on workload patterns. Which feature should you enable?

A.Azure Advisor

B.Intelligent Insights

C.Automatic tuning

D.Query Store

AnswerC

Automatic tuning can automatically create and drop indexes.

Why this answer

Automatic tuning in Azure SQL Database continuously analyzes query execution plans and workload patterns, then automatically creates, drops, or rebuilds indexes to improve performance. It uses built-in intelligence to recommend and apply index changes without manual intervention, making it the correct feature for automatically adjusting indexing based on workload patterns.

Exam trap

A common mistake is to confuse features that provide recommendations (Azure Advisor, Intelligent Insights) or capture query performance data (Query Store) with Automatic tuning, which is the only feature that automatically applies index changes based on workload patterns without manual intervention.

How to eliminate wrong answers

Option A is wrong because Azure Advisor provides proactive recommendations for cost, security, reliability, and performance, but it does not automatically adjust indexing; it only suggests manual actions. Option B is wrong because Intelligent Insights uses built-in intelligence to monitor database performance and detect anomalies, but it does not automatically implement index changes; it delivers root cause analysis and recommendations. Option D is wrong because Query Store captures query execution statistics and plan history for troubleshooting and tuning, but it does not automatically adjust indexing; it requires manual analysis or integration with Automatic tuning to apply changes.

Practice this question →

135

MCQhard

You are reviewing an Azure SQL Database server's vulnerability assessment settings. The exhibit shows the current configuration. A recent security audit requires that vulnerability assessment scans be enabled and that results be retained for at least 90 days. What should you do?

A.Add additional email addresses to ensure notification.

B.Change retentionDays to 90 and keep the state as Disabled.

C.Change state to Enabled and set retentionDays to 90.

D.Remove the disabledAlerts entries to enable all alerts.

AnswerC

Enables the scan and meets the retention requirement.

Why this answer

The vulnerability assessment must be enabled (state: Enabled) and retentionDays must be set to at least 90 to meet the audit requirement. The exhibit shows state as Disabled and retentionDays as 30. Option A is wrong because adding email addresses only affects notifications, not the scan state or retention.

Option B is wrong because retentionDays of 90 with state Disabled means scans are not running. Option D is wrong because disabledAlerts control which alerts are suppressed; removing them does not enable the scan or change retention.

Practice this question →

136

MCQmedium

You are a database administrator for a large retail company. The company uses an Azure SQL Database in the Business Critical tier (8 vCores, 480 GB storage) to run its core transaction processing system. The database has automatic tuning enabled, including FORCE_LAST_GOOD_PLAN and CREATE_INDEX. You notice that the database is experiencing high CPU usage (90% average) during peak hours, and the Query Store shows that a specific query (Query ID 123) has regressed. The automatic tuning feature has forced a plan for this query, but the performance is still poor. You need to resolve the CPU issue and ensure the query runs efficiently. What should you do first?

A.Disable automatic tuning and manually create a plan guide for the query.

B.Use Query Store to compare the forced plan with the previous good plan and update statistics.

C.Modify the query to use query hints like OPTIMIZE FOR UNKNOWN.

D.Scale up the database to 16 vCores to handle the CPU load.

AnswerB

Plan regression often due to statistics; updating may let optimizer pick a better plan.

Why this answer

Reviewing the plan history in Query Store helps identify why the forced plan is not optimal, and perhaps the regression is due to parameter sniffing or outdated statistics. Option A is wrong because disabling automatic tuning may cause further regression and is not the first step. Option C is wrong because adding query hints like OPTIMIZE FOR UNKNOWN may not resolve the plan regression and could lead to suboptimal plans for other parameter values.

Option D is wrong because scaling up the database increases resources temporarily but does not address the underlying plan regression issue.

Practice this question →

137

MCQmedium

You are a database administrator for a medium-sized e-commerce company. The company runs its online transaction processing (OLTP) workload on an Azure SQL Database in the General Purpose service tier (DTU-based, S3). The database is used for order processing, inventory management, and customer data. Recently, during peak shopping hours (10 AM to 2 PM), users have reported that order entry forms take several seconds to submit, and inventory queries are timing out. Monitoring shows that DTU usage regularly hits 100% during these hours, with high PAGELATCH_IO waits. You need to resolve the performance issue with minimal cost increase. What should you do?

A.Increase the max storage size to 1 TB

B.Increase the service tier to S4 during peak hours

C.Create a read-only replica and offload reporting queries

D.Migrate to the vCore model with Hyperscale service tier

AnswerD

Hyperscale eliminates resource contention and handles high concurrency.

Why this answer

Migrate to the vCore model with Hyperscale service tier. The issue is high DTU usage with PAGELATCH_IO waits, indicating resource contention on I/O. Hyperscale architecture separates compute and storage, eliminating such bottlenecks and providing near-instant scaling for peak loads.

Option A (increase storage) does not address DTU limits. Option B (increase to S4) adds more DTUs but retains the same architecture limitations that cause PAGELATCH_IO. Option C (read replica) only offloads read queries, not write-heavy OLTP.

Practice this question →

138

Multi-Selecteasy

Which TWO metrics are available in Azure Monitor for an Azure SQL Database that can be used to set autoscale rules? (Select two.)

Select 2 answers

A.CPU percentage

B.Log write throughput

C.Deadlock count

D.DTU percentage

E.Query Store size

AnswersA, D

CPU percentage is a standard metric used for autoscaling Azure SQL Database.

Why this answer

Options A and D are correct because CPU percentage and DTU percentage are standard metrics for autoscaling Azure SQL Database. Option B is wrong because Log write throughput is not typically used for autoscale rules. Option C is wrong because Deadlock count is an event, not a continuous metric.

Option E is wrong because Query Store size is not a metric source for autoscaling.

Practice this question →

139

MCQeasy

You have an Azure SQL Database that uses the General Purpose service tier. You notice that the log write throughput is consistently near the limit. What should you do to improve log write performance?

A.Migrate to the Business Critical service tier.

B.Enable accelerated database recovery.

C.Migrate to the Hyperscale service tier.

D.Increase the DTU purchase model to a higher tier.

AnswerA

Business Critical provides higher log write throughput.

Why this answer

The General Purpose service tier in Azure SQL Database has a maximum log write throughput of 1.5 MB/s for the most common configurations. The Business Critical tier uses local SSD storage and a higher log I/O limit (up to 100 MB/s), which directly addresses log write throughput bottlenecks. Migrating to Business Critical is the correct action because it provides significantly higher log write throughput and lower latency for transaction log writes.

Exam trap

The trap here is that candidates often assume increasing DTUs or moving to Hyperscale will solve all performance issues, but they fail to recognize that log write throughput is a specific architectural limitation of the General Purpose tier that only the Business Critical tier resolves.

How to eliminate wrong answers

Option B is wrong because enabling accelerated database recovery (ADR) improves transaction rollback and recovery times, not log write throughput; it does not increase the log I/O capacity. Option C is wrong because the Hyperscale service tier is designed for large databases with fast scaling and high read throughput, but its log write throughput is still limited compared to Business Critical and is not the primary solution for a log write bottleneck. Option D is wrong because increasing the DTU purchase model to a higher tier (e.g., from S3 to S4) does not change the underlying architecture; General Purpose still uses remote storage with the same log write throughput limitations, regardless of DTU level.

Practice this question →

140

MCQhard

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

A.Immediately implement the DROP_INDEX recommendation to reduce overhead

B.Create the recommended index to improve performance

C.Revert the plan force because it is causing regression

D.Monitor the query performance to confirm the forced plan resolves the regression

AnswerD

The active recommendation should be monitored for effectiveness.

Why this answer

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.

Exam trap

Azure often tests the misconception that an automatic tuning recommendation should be immediately implemented or reverted without first monitoring its impact, leading candidates to choose premature actions like dropping indexes or reverting plans.

How to eliminate wrong answers

Option A is wrong because dropping an index based on a recommendation that is unrelated to the plan force could degrade performance if the index is still needed for other queries. Option B is wrong because creating a recommended index is not the immediate action when a plan force is active; the forced plan should be validated first to ensure it resolves the regression. Option C is wrong because reverting the plan force without monitoring its effect is premature; the forced plan may be the correct fix, and reverting could reintroduce the regression.

Practice this question →

141

MCQmedium

You are managing an Azure SQL Database that is experiencing intermittent performance degradation. Query Store shows that a specific query's execution plan changed, causing increased CPU usage. You need to ensure consistent performance without rewriting the application. What should you do?

A.Increase the DTU/service tier of the database

B.Create a missing index recommendation

C.Drop and recreate the index used by the query

D.Force the previous query plan using Query Store

AnswerD

Plan forcing enforces the known good plan for consistent performance.

Why this answer

Force the previous query plan using Query Store. This approach directly addresses the root cause by locking the query to a known good plan, ensuring consistent performance without application changes. Option A is incorrect because increasing the DTU/service tier may temporarily improve performance but does not fix the plan regression.

Option B is incorrect because creating a missing index recommendation may help but does not guarantee the query will use the previous plan. Option C is incorrect because dropping and recreating the index is disruptive and may not force the plan to revert.

Practice this question →

142

MCQhard

You administer a large Azure SQL Database that is used for a SaaS application. The database has a table with over 1 billion rows that is frequently queried by customer ID. The table currently has a clustered index on an identity column and a nonclustered index on customer ID. Queries that filter by customer ID are experiencing high IO and long execution times. You analyze the execution plan and see that the nonclustered index is used, but there are many key lookups. You need to optimize the query performance while minimizing storage overhead. What should you do?

A.Create a clustered columnstore index on the table

B.Create a filtered index on customer ID for frequent values

C.Partition the table by customer ID

D.Add all queried columns as included columns to the nonclustered index

AnswerD

Adding all queried columns as included columns to the nonclustered index makes it covering, eliminating key lookups and reducing IO with minimal storage overhead.

Why this answer

Adding all queried columns as included columns to the existing nonclustered index on customer ID creates a covering index. This eliminates the need for key lookups, reducing IO and improving query performance for point lookups by customer ID. The storage overhead is minimal since included columns are stored only at the leaf level.

Option A is wrong because a clustered columnstore index is designed for analytical workloads and can degrade point lookup performance. Option B is wrong because a filtered index on frequent values still may not cover all columns, leading to key lookups. Option C is wrong because partitioning does not eliminate key lookups and can add complexity without performance benefit for point queries.

Exam trap

The trap is that clustered columnstore indexes are often suggested for large tables to reduce storage and improve IO, but they are optimized for analytic workloads, not high-frequency point lookups. For point lookup queries, a covering nonclustered index is a better choice.

Practice this question →

143

MCQmedium

You are monitoring an Azure SQL Database using Query Performance Insight. You see a query with high duration and high CPU usage. The query plan shows a clustered index scan. What is the most likely cause and recommendation?

A.Fragmented clustered index; rebuild the clustered index.

B.Insufficient memory; increase the service tier.

C.Missing nonclustered index; create an index on the predicates.

D.Parameter sniffing; add OPTION (RECOMPILE).

AnswerC

An index seek would reduce CPU and duration.

Why this answer

Query Performance Insight shows a query with high duration and CPU usage, and the query plan reveals a clustered index scan. A clustered index scan reads all rows in the table, which is inefficient when only a subset of rows is needed. The most likely cause is a missing nonclustered index on the columns used in the WHERE clause (predicates), which would allow a seek operation instead of a full scan, reducing both CPU and duration.

Exam trap

The trap here is that candidates confuse a clustered index scan with fragmentation or parameter sniffing, but the scan is a symptom of a missing nonclustered index that would allow a seek, not a problem with the clustered index itself or plan caching.

How to eliminate wrong answers

Option A is wrong because a fragmented clustered index causes increased I/O and scan overhead, but the primary issue here is the scan itself, not fragmentation; rebuilding the index would not eliminate the scan if the query lacks a supporting index. Option B is wrong because insufficient memory would manifest as page life expectancy issues or disk spills, not a clustered index scan; increasing the service tier does not address the missing index. Option D is wrong because parameter sniffing leads to suboptimal cached plans for different parameter values, but the query plan shows a clustered index scan, which indicates a fundamental missing index issue, not a plan choice problem; adding OPTION (RECOMPILE) would not create the missing index.

Practice this question →

144

Multi-Selecteasy

You are troubleshooting a performance issue in an Azure SQL Database. You need to identify the queries that are consuming the most CPU over the last hour. Which two methods can you use? (Choose two.)

Select 2 answers

A.sys.dm_exec_query_stats

B.sys.dm_os_wait_stats

C.sys.dm_db_index_usage_stats

D.sys.dm_exec_requests

E.Query Store top resource consuming queries report

AnswersA, E

Provides cumulative CPU time for cached plans.

Why this answer

sys.dm_exec_query_stats (Option A) returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), which can be filtered by creation_time or last_execution_time to focus on the last hour. Query Store's top resource consuming queries report (Option E) provides a built-in, graphical view of queries ranked by CPU, duration, or other metrics over a configurable time window, making it ideal for identifying high-CPU queries in the last hour.

Exam trap

The trap here is that candidates often confuse sys.dm_exec_requests (current state) with sys.dm_exec_query_stats (historical aggregates), or assume wait stats directly identify CPU-heavy queries, when in fact they indicate what queries are waiting on, not what is consuming CPU.

Practice this question →

145

MCQeasy

You have an Azure SQL Database in the Hyperscale service tier. You need to ensure that read-only workloads are offloaded to a readable secondary. Which configuration should you set?

A.Set ReadOnlyRouting=1 on the database.

B.Add the database to a failover group.

C.Set ReadScale to 1 on the database.

D.Use ApplicationIntent=ReadOnly in the connection string.

AnswerD

This routes queries to a readable secondary.

Why this answer

Setting `ApplicationIntent=ReadOnly` in the connection string directs read-only workloads to a readable secondary replica in Azure SQL Database Hyperscale. This offloads read traffic from the primary, improving performance for write-heavy operations. The Hyperscale tier supports this feature without requiring a failover group or explicit read-scale configuration.

Exam trap

The trap here is that candidates confuse the `ReadScale` property (used in Premium tier) with the Hyperscale tier's always-on read-scale capability, or incorrectly think a failover group is required to enable read-only routing.

How to eliminate wrong answers

Option A is wrong because `ReadOnlyRouting=1` is not a valid Azure SQL Database setting; read-only routing is controlled via connection string intent, not a database-level property. Option B is wrong because adding the database to a failover group enables geo-failover and read-only routing for business continuity, but it is not required for offloading read workloads to a readable secondary in Hyperscale; the Hyperscale tier provides a built-in readable secondary without a failover group. Option C is wrong because `ReadScale` is a property for Azure SQL Database in the Premium tier (set to 1 to enable read-scale out), but in Hyperscale, read-scale is always enabled and does not need a separate configuration flag.

Practice this question →

146

MCQeasy

You are monitoring an Azure SQL Database and notice that the average CPU usage is 80% and the average data IO percentage is 70%. You need to identify the most likely cause of the high resource usage. What should you check first?

A.Check for long-running maintenance tasks

B.Check for connection pooling issues

C.Check for blocking and deadlocks

D.Use Query Store to identify top resource-consuming queries

AnswerD

Query Store helps find queries consuming CPU and IO.

Why this answer

High average CPU (80%) and data IO (70%) suggest that the database is under sustained load from inefficient or resource-intensive queries. Query Store captures query execution plans, runtime statistics, and resource consumption per query, making it the fastest way to pinpoint the top resource consumers. Checking Query Store first allows you to identify the specific queries driving CPU and IO, which is the most direct diagnostic step.

Exam trap

The trap here is that candidates often jump to 'blocking and deadlocks' (Option C) because they associate high resource usage with concurrency issues, but sustained CPU and IO are far more commonly driven by inefficient queries rather than blocking.

How to eliminate wrong answers

Option A is wrong because long-running maintenance tasks (e.g., index rebuilds, statistics updates) typically cause periodic spikes rather than sustained average usage at 80% CPU and 70% IO, and they would be visible in job history or sys.dm_os_wait_stats. Option B is wrong because connection pooling issues (e.g., orphaned connections, pool exhaustion) manifest as connection timeouts or login failures, not as sustained high CPU and IO percentages. Option C is wrong because blocking and deadlocks primarily cause waits and timeouts, not consistently high CPU and IO; they would show high wait stats for locks but not necessarily the resource consumption levels described.

Practice this question →

147

MCQhard

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?

B.Severity

C.Impact

D.Resource type

AnswerC

Shows cause: workload increase or query regression.

Why this answer

(Impact) is correct because the Impact dimension in Intelligent Insights indicates whether the high resource utilization is caused by an increase in user workload or a degradation in query performance. Option A (Metric) is incorrect because Metric shows the metric name (e.g., 'Resource utilization') but not the root cause. Option B (Severity) is incorrect because Severity indicates the alert level (e.g., high, medium) and does not provide insight into the cause.

Option D (Resource type) is incorrect because Resource type shows the Azure resource type (e.g., SQL Database) and is not relevant to the cause of the alert.

Practice this question →

148

MCQeasy

You have an Azure SQL Managed Instance with a database that is used for reporting. The reporting queries are read-only and can tolerate some latency. You want to offload the reporting workload from the primary instance to a secondary read-only replica. Which feature should you use?

A.SQL Server Integration Services (SSIS)

B.Readable secondary in a failover group

C.Transaction replication

D.Auto-failover group with read-write secondary

AnswerB

Allows read-only queries to be routed to the secondary.

Why this answer

The correct feature is a readable secondary in a failover group. This allows read-only queries to be routed to the secondary replica, offloading the reporting workload while maintaining high availability. SSIS (Option A) is an ETL tool, not for offloading read queries.

Transaction replication (Option C) requires complex configuration and is not native to Managed Instance. Option D is incorrect because an auto-failover group does not support a read-write secondary; the secondary is always read-only.

Practice this question →

149

Multi-Selecthard

Which THREE actions can help reduce the frequency of parameter-sensitive plan (PSP) problems in Azure SQL Database? (Choose three.)

Select 3 answers

A.Use the Optimize for ad hoc workloads setting (or OPTIMIZE FOR UNKNOWN).

B.Disable parameter sniffing by using the DISABLE_PARAMETER_SNIFFING hint.

C.Add the RECOMPILE query hint to problematic queries.

D.Create separate cached plans using forced parameterization.

E.Enable Query Store and use the Performance Dashboard.

AnswersA, C, D

Helps balance plan choice.

Why this answer

Options A, C, and D are correct. Option A (Optimize for ad hoc workloads or OPTIMIZE FOR UNKNOWN) helps by using average distribution instead of sniffing parameter values, reducing parameter-sensitive plan (PSP) problems. Option C (RECOMPILE hint) forces a new plan per execution, avoiding stale plans caused by parameter sniffing.

Option D (forced parameterization) creates multiple cached plans for different parameter values, directly addressing PSP. Option B (disabling parameter sniffing) is not a recommended approach and can lead to suboptimal plans. Option E (Query Store) is a monitoring tool that captures query data but does not directly reduce PSP frequency.

Practice this question →

150

MCQeasy

You manage an Azure SQL Database with the Premium service tier. You need to reduce storage costs by automatically deleting old audit logs that are older than 90 days. What should you configure?

A.Create an Extended Events session that filters and deletes old audit events.

B.Reduce the backup retention period to 90 days.

C.Set the audit retention policy to 90 days in the server's audit settings.

D.Configure a lifecycle management policy on the Azure Storage account that stores the audit logs.

AnswerD

Automatically deletes blobs older than 90 days.

Why this answer

Azure SQL Database audit logs are stored in Azure Storage. A lifecycle management policy on the storage account can automatically delete blobs older than a specified number of days, such as 90 days, reducing storage costs. Option A is incorrect because Extended Events are not designed for automated deletion of audit logs.

Option B is incorrect because backup retention is unrelated to audit logs. Option C is incorrect because the audit retention policy in Azure SQL Database's server audit settings only controls how long audit records are retained in the database, not the actual deletion of stored logs.

Practice this question →

← PreviousPage 2 of 3 · 211 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Monitor Optimize Db questions.

Start 20-question session