Courseiva

CCNA Monitor, configure, and optimize database resources Questions

61 of 211 questions · Page 3/3 · Monitor, configure, and optimize database resources · Answers revealed

Practice these questionsDomain overviewAll questions
151
MCQeasy

You have an Azure SQL Database that is experiencing high wait times on RESOURCE_SEMAPHORE waits. You need to identify the root cause. What should you check?

A.Blocking and deadlocks
B.High CPU usage
C.Disk I/O bottlenecks
D.Queries with large memory grants
AnswerD

RESOURCE_SEMAPHORE waits indicate memory grant pressure.

Why this answer

RESOURCE_SEMAPHORE waits indicate memory grant pressure, typically caused by queries requesting large memory grants. Option D is correct. Option A is incorrect because blocking and deadlocks produce LCK_M_* waits, not RESOURCE_SEMAPHORE.

Option B is incorrect because high CPU usage leads to SOS_SCHEDULER_YIELD waits. Option C is incorrect because disk I/O bottlenecks cause PAGEIOLATCH waits.

Practice this question →
152
MCQeasy

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

A.Enable read scale-out
B.Wait for Automatic tuning to provide recommendations
C.Scale down to a lower service tier
D.Scale up to a higher service tier
AnswerD

Provides more CPU resources to handle the load.

Why this answer

Scaling up to a higher service tier (e.g., S4) provides more CPU resources, which directly addresses the high CPU usage. Option D is correct. Option A is incorrect because enabling read scale-out does not increase CPU capacity.

Option B is incorrect because automatic tuning may provide recommendations, but it is not the first action to take for immediate CPU pressure. Option C is incorrect because scaling down to a lower tier would reduce CPU resources, making the problem worse.

Practice this question →
153
MCQeasy

You need to monitor the long-running queries in an Azure SQL Database. Which dynamic management view should you query to see queries that have been running for more than 30 seconds?

A.sys.dm_exec_requests
B.sys.dm_db_resource_stats
C.sys.dm_exec_sessions
D.sys.dm_exec_query_stats
AnswerA

Includes start_time and total_elapsed_time for each request.

Why this answer

Sys.dm_exec_requests returns currently executing requests, including their start time and elapsed time, which can be used to identify queries running longer than 30 seconds. Option B (sys.dm_db_resource_stats) is incorrect because it provides resource consumption metrics (CPU, IO, memory) over time, not currently running queries. Option C (sys.dm_exec_sessions) is incorrect because it shows active sessions but does not include query execution details like elapsed time.

Option D (sys.dm_exec_query_stats) is incorrect because it contains aggregated query performance statistics, not currently running queries.

Practice this question →
154
MCQmedium

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that 'Query performance degradation' was detected. After reviewing the details, you find that a specific query now has a higher duration and is using a different execution plan. What is the recommended first step to troubleshoot?

A.Restart the database to clear the plan cache
B.Increase the service tier of the database
C.Query the Query Store to compare the previous and current plans
D.Run the Database Engine Tuning Advisor
AnswerC

Query Store allows plan comparison and identification of regression.

Why this answer

Query Store captures execution plans and runtime statistics, allowing you to compare the previous plan with the current plan to identify plan regression. Option A is wrong because restarting the database is disruptive and doesn't address the root cause. Option B is wrong because increasing the service tier may not fix plan regression and is not the first step.

Option D is wrong because Database Engine Tuning Advisor focuses on index and query design, not on comparing plan changes.

Practice this question →
155
Multi-Selecteasy

You need to configure monitoring for an Azure SQL Database to meet the following requirements: - Alert when average DTU consumption exceeds 90% for 10 minutes. - Track failed logins. - Analyze query performance over the last 30 days. Which THREE Azure services or features should you use? (Choose three.)

Select 3 answers
A.SQL auditing
B.Automatic tuning
C.Query Store
D.Azure Monitor metric alerts
E.Azure SQL Assessment
AnswersA, C, D

Audit logs capture failed logins.

Why this answer

(SQL auditing) is correct because it logs failed login attempts. Option C (Query Store) is correct as it retains query performance data for historical analysis (e.g., 30 days). Option D (Azure Monitor metric alerts) is correct because it can monitor metrics like DTU and trigger alerts when thresholds are exceeded.

Option B (Automatic tuning) is wrong because it optimizes query execution plans, not monitoring. Option E (Azure SQL Assessment) is wrong; it provides best-practice recommendations, not monitoring.

Exam trap

Candidates often confuse the purpose of SQL auditing (security logging) with Query Store (performance tracking) or metric alerts. Remember: auditing tracks logins, Query Store tracks query performance, and metric alerts monitor resource utilization.

Practice this question →
156
Multi-Selecthard

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

Select 3 answers
A.Whether to enable automatic plan guide creation.
B.Whether to allow automatic index dropping.
C.Whether to enable automatic statistics update.
D.Whether to force the last good plan for queries with plan regressions.
E.Whether to allow automatic index creation.
AnswersB, D, E

Automatic tuning can drop unused or duplicate indexes.

Why this answer

The correct answers are B, D, and E. Automatic tuning for Azure SQL Database includes the options 'CREATE INDEX' (option E), 'DROP INDEX' (option B), and 'FORCE LAST GOOD PLAN' (option D). Option A (automatically creating plan guides) is not part of automatic tuning; plan guides are a manual feature.

Option C (automatic statistics update) is a separate maintenance operation and not an automatic tuning option. Therefore, when configuring automatic tuning, you should consider enabling or disabling index creation, index dropping, and forcing the last good plan to address query plan regressions.

Practice this question →
157
MCQmedium

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is consistently high. You need to reduce the log write latency. What should you do?

A.Enable data compression on the tables
B.Increase the log rate limit in the Hyperscale service tier
C.Enable Accelerated Database Recovery
D.Change the service tier to General Purpose
AnswerB

Increasing log rate limit improves log write throughput.

Why this answer

In the Hyperscale service tier, log write latency is directly influenced by the log rate limit. Increasing this limit allocates more I/O bandwidth to the log write path, reducing latency under high log generation. This is the correct action because the log rate limit is a configurable parameter in Hyperscale that controls how fast log records can be written to the log service.

Exam trap

The trap here is that candidates often confuse log write latency with data I/O latency and incorrectly choose data compression or ADR, not realizing that the Hyperscale tier has a dedicated log rate limit setting that directly controls log write performance.

How to eliminate wrong answers

Option A is wrong because data compression reduces storage size and I/O for data pages, but it does not affect the log write path or log rate limit; log writes are sequential and compression does not reduce log write latency. Option C is wrong because Accelerated Database Recovery (ADR) improves database recovery time and reduces transaction log space usage by versioning, but it does not directly reduce log write latency under a consistently high log rate. Option D is wrong because changing to General Purpose would move the database to a different architecture with lower I/O throughput and higher latency for log writes, worsening the problem.

Practice this question →
158
Multi-Selectmedium

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

Select 2 answers
A.Increase MAXDOP
B.Add multiple data files to the filegroup
C.Use a columnstore index
D.Configure a readable secondary replica
E.Use hash partitioning on a table
AnswersB, E

Distributes allocation pages.

Why this answer

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

Exam trap

The trap here is that candidates often confuse PAGELATCH_EX contention with PAGEIOLATCH_EX (I/O-related waits) and incorrectly assume that adding indexes or changing parallelism settings will resolve the issue, when in fact the solution involves reducing contention on in-memory allocation structures through filegroup or partitioning strategies.

Practice this question →
159
MCQhard

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

A.Insufficient log IOPS on the secondary database
B.Network latency between the primary and secondary regions
C.High CPU usage on the primary database
D.Excessive read workload on the secondary database
AnswerB

Geo-replication log shipping is affected by network latency.

Why this answer

Active geo-replication asynchronously replicates transaction log records from the primary to the secondary database. High log write latency on the secondary is most commonly caused by network latency between the regions. Option A is incorrect because the secondary database's log IOPS are not directly the issue; the log is applied from the primary.

Option C is incorrect because primary CPU usage does not directly impact secondary log write latency. Option D is incorrect because read workload on the secondary does not affect log write latency; the secondary applies log regardless of read activity.

Practice this question →
160
MCQmedium

You run the KQL query shown in the exhibit. The query returns a list of query IDs. What is the purpose of this query?

A.Identify queries consuming the most CPU.
B.Identify the top 10 queries by average duration.
C.Identify queries with the highest total wait time.
D.Identify queries with the highest execution count.
AnswerB

Averages avg_duration per query and returns top 10.

Why this answer

The KQL query in the exhibit uses `summarize` with `avg(duration)` and then `top 10 by avg_duration`, which explicitly calculates the average duration per query_id and returns the top 10 queries with the highest average duration. This directly matches the purpose of identifying the top 10 queries by average duration.

Exam trap

The trap here is that candidates often confuse 'average duration' with 'total CPU consumption' or 'total wait time,' but the query explicitly uses `avg(duration)` and not `sum(cpu_time)` or `sum(wait_time)`, making the correct answer solely about average duration.

How to eliminate wrong answers

Option A is wrong because the query does not include any CPU-related metrics such as `cpu_time` or `avg_cpu_time`; it only aggregates `duration`. Option C is wrong because the query does not reference `wait_time`, `wait_stats`, or any wait-related columns; it focuses solely on `duration`. Option D is wrong because the query uses `avg(duration)` and not `count()` or `dcount()` to measure execution count; it ranks by average duration, not by how often a query ran.

Practice this question →
161
MCQmedium

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

A.Compute node CPU
B.Log service throughput
C.Page server IO
D.Remote storage IOPS
AnswerB

Log service is responsible for redo; latency there causes redo lag.

Why this answer

In Hyperscale, the log service is a separate component that handles redo; high latency there directly affects redo speed. Option A is wrong because compute node CPU affects query processing, not redo latency specifically. Option C is wrong because page servers handle data reads, not redo.

Option D is wrong because storage IO is distributed and rarely the bottleneck for redo in Hyperscale.

Practice this question →
162
MCQhard

You manage a critical Azure SQL Database that supports a financial application. The database is configured with the Business Critical service tier (8 vCores) and uses Always On availability groups for high availability. Recently, the application team noticed that some transactions are experiencing high latency during writes. You investigate and find that the log write latency is averaging 10 ms, and there are occasional HADR_SYNC_COMMIT waits. The application requires durable writes with synchronous commit. You need to reduce write latency without compromising data durability. What should you do?

A.Switch to Premium SSD v2 storage
B.Disable automatic tuning to reduce overhead
C.Reduce the number of secondary replicas to minimize synchronization overhead
D.Increase the number of vCores to 16
AnswerD

More vCores increase log write throughput.

Why this answer

Increasing the number of vCores (Option D) provides additional CPU and memory resources for the log writer, which can process transactions faster, thereby reducing log write latency. In the Business Critical tier, the log write path is CPU-intensive, and scaling up directly improves throughput. Option A is incorrect because Premium SSD v2 is not applicable to Azure SQL Database; storage is managed by the service.

Option B is incorrect because disabling automatic tuning does not impact log write performance and may negatively affect overall query performance. Option C is incorrect because the number of secondary replicas is fixed at three in Business Critical, and reducing them is not possible; further, synchronous commit requires at least one secondary to ensure durability.

Practice this question →
163
MCQeasy

You are monitoring an Azure SQL Database using Intelligent Insights. The built-in intelligence detects a performance issue and suggests a specific index to create. The database is running the Business Critical service tier. You want to automatically implement this recommendation without manual intervention. What should you configure?

A.Enable automatic tuning for 'FORCE LAST GOOD PLAN' in the Azure portal.
B.Set up Query Store to capture the recommended index execution.
C.Configure Azure Advisor to email you the recommendation.
D.Enable automatic tuning for 'CREATE INDEX' in the Azure portal.
AnswerD

Automatic tuning can automatically create and drop indexes based on workload patterns.

Why this answer

Azure SQL Database's automatic tuning can automatically implement index recommendations when 'CREATE INDEX' is enabled. Option A is incorrect because 'FORCE LAST GOOD PLAN' is for plan correction, not index creation. Option B is incorrect because Query Store is a monitoring tool, not an automatic tuning feature.

Option C is incorrect because Azure Advisor only provides recommendations, not automatic implementation.

Practice this question →
164
MCQeasy

You have an Azure SQL Database that is used by a reporting application. Reports run slowly during the day. You notice that the database is at the S1 tier. You need to improve report performance without changing application code. What should you do?

A.Rebuild fragmented indexes.
B.Scale up the database to a higher service tier.
C.Implement table partitioning.
D.Enable read scale-out to offload reports.
AnswerB

Higher DTU allocation directly improves throughput.

Why this answer

Scaling up to a higher service tier (e.g., S2 or S3) provides more DTUs, which directly addresses the resource bottleneck and improves report performance without changing application code. Option A is incorrect because while rebuilding fragmented indexes can help query performance, it may not be sufficient if the underlying issue is insufficient resources. Option C is incorrect because table partitioning can improve manageability and partition elimination but does not guarantee performance improvement for reports and often requires query changes.

Option D is incorrect because read scale-out is only available on the Premium and Business Critical service tiers, not on the S1 tier.

Practice this question →
165
MCQhard

The database 'mydb' is experiencing performance issues during peak hours. Based on the exhibit, what is the most likely cause?

A.Zone redundancy is disabled, causing failover delays.
B.The service tier S3 is not sufficient for the workload.
C.Read scale is disabled, increasing load on primary.
D.The database is auto-pausing frequently due to autoPauseDelay.
AnswerB

S3 has limited DTU which can cause performance issues.

Why this answer

The exhibit indicates the database is using the S3 service tier, which provides limited DTUs (100 DTU). During peak hours, this low tier is insufficient for the workload, leading to resource contention and performance degradation. Option A is incorrect because zone redundancy is related to high availability and failover, not peak-hour performance.

Option C is incorrect because read scale is used for offloading read-only workloads, not for mitigating primary performance issues. Option D is incorrect because auto-pause delay (3600 seconds) affects serverless databases in idle periods, not active peak-hour usage.

Practice this question →
166
MCQhard

Refer to the exhibit. Which action should you take to improve performance?

A.Enable automatic tuning for the query.
B.Increase the database's DTU/DTU level.
C.Force plan 2 using the query store.
D.Drop and recreate the query's indexes.
AnswerC

Plan 2 has better metrics and should be forced to improve performance.

Why this answer

The exhibit shows two plans for the same query. Plan 2 has lower CPU, reads, and duration. Plan 1 is being forced, causing poor performance.

The best action is to force plan 2.

Practice this question →
167
MCQeasy

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

A.Audit logs are written using the secondary storage key
B.Auditing is currently disabled
C.Audit logs are retained for 30 days
D.Audit logs are sent to Azure Monitor
AnswerD

isAzureMonitorTargetEnabled is true.

Why this answer

The JSON shows 'isAzureMonitorTargetEnabled' is true, indicating audit logs are sent to Azure Monitor. Option B is incorrect because 'state' is 'Enabled', meaning auditing is enabled. Option A is incorrect because 'isStorageSecondaryKeyInUse' is false.

Option C is incorrect because 'retentionDays' is 90, not 30.

Practice this question →
168
MCQmedium

You manage an Azure SQL Database that has automatic tuning enabled. You receive an alert that the database is experiencing plan regression. The automatic tuning has forced a plan, but performance is still poor. What should you do first?

A.Disable automatic tuning and create a plan guide.
B.Review the Query Store to identify the root cause of regression.
C.Manually revert to the previous plan using Query Store.
D.Scale up the database to reduce resource pressure.
AnswerB

Query Store can show plan history and regression details, helping analyze why the forced plan is not optimal.

Why this answer

Query Store can show plan history and regression details, helping analyze why the forced plan is not optimal. Option A is wrong because disabling automatic tuning and creating a plan guide may be premature without first identifying the root cause. Option C is wrong because manually reverting to a previous plan using Query Store should not be the first action; you need to understand the regression first.

Option D is wrong because scaling up the database does not directly address plan quality or regression.

Practice this question →
169
MCQeasy

You need to configure automatic tuning for an Azure SQL Database to automatically identify and fix performance issues. Which two tuning options can be enabled?

A.FORCE LAST GOOD PLAN
B.CREATE INDEX
C.DROP INDEX
D.FORCE LAST GOOD PLAN and DROP INDEX
AnswerA, B

This is a valid automatic tuning option.

Why this answer

Automatic tuning in Azure SQL Database includes two main options: FORCE LAST GOOD PLAN and CREATE INDEX. FORCE LAST GOOD PLAN automatically identifies and forces the last known good query plan when a regression is detected, while CREATE INDEX automatically creates indexes to improve query performance based on workload patterns. Both options help identify and fix performance issues without manual intervention.

Exam trap

The trap here is that candidates may assume DROP INDEX is a valid automatic tuning option because they think automatic tuning includes both creating and dropping indexes, but Azure SQL Database only supports creating indexes automatically to avoid accidental performance degradation.

How to eliminate wrong answers

Option C is wrong because DROP INDEX is not an automatic tuning option; automatic tuning only creates indexes, never drops them, as dropping indexes could degrade performance. Option D is wrong because it combines FORCE LAST GOOD PLAN with DROP INDEX, but DROP INDEX is not a valid automatic tuning option in Azure SQL Database.

Practice this question →
170
Multi-Selectmedium

Which THREE actions can you take to monitor and optimize database resources in Azure SQL Database? (Choose three.)

Select 3 answers
A.Enable Microsoft Defender for Azure SQL to detect vulnerabilities.
B.Use Query Store to track query performance over time.
C.Query dynamic management views to identify blocking and resource waits.
D.Enable automatic tuning to automatically implement performance improvements.
E.Configure Microsoft Sentinel to monitor database activity.
AnswersB, C, D

Query Store provides historical query performance data.

Why this answer

Options B, C, and D are correct. Query Store tracks query performance over time, dynamic management views help identify blocking and resource waits, and automatic tuning automatically implements performance improvements. Option A (Microsoft Defender for SQL) is a security feature for vulnerability detection, not directly for monitoring and optimizing database resources.

Option E (Microsoft Sentinel) is a security information and event management (SIEM) tool, not specifically for database monitoring and optimization.

Practice this question →
171
Multi-Selectmedium

Which TWO options are valid methods to optimize query performance in Azure SQL Managed Instance?

Select 2 answers
A.Use columnstore indexes on large tables
B.Set database compatibility level to 150
C.Increase the maximum storage size
D.Enable Query Store and monitor regressions
E.Enable Transparent Data Encryption (TDE)
AnswersA, D

Columnstore indexes improve analytics and large scan queries.

Why this answer

Options A and D are correct. Columnstore indexes (A) improve analytical query performance by reducing I/O and using batch processing. Query Store (D) helps identify regressions by tracking execution plans and performance metrics.

Option B is incorrect; setting database compatibility level to 150 may enable new features but is not a direct query optimization method. Option C is incorrect; increasing storage size does not improve query performance. Option E is incorrect; Transparent Data Encryption (TDE) secures data but does not enhance performance.

Practice this question →
172
MCQhard

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

A.The database can automatically increase storage.
B.Geo-redundant backup storage is enabled.
C.The database uses zone-redundant high availability.
D.The database is using the Hyperscale service tier.
AnswerA

The storage profile shows autogrow enabled, allowing the database to automatically increase storage when needed.

Why this answer

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

Option D is wrong because the service tier shown is General Purpose, not Hyperscale.

Practice this question →
173
Drag & Dropmedium

Drag and drop the steps to configure transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key in Azure Key Vault in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

First set up Key Vault with a key, grant permissions, then enable TDE with customer-managed key, select the key, and save.

Practice this question →
174
MCQeasy

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

A.Concurrent inserts into a table with a clustered index causing last-page contention.
B.Insufficient buffer pool size leading to frequent reads from disk.
C.High CPU usage due to inefficient queries.
D.Long-running transactions blocking other queries.
AnswerA

High PAGELATCH waits commonly result from hotspot contention on the last page of an index.

Why this answer

PAGELATCH waits indicate contention on data pages, often due to concurrent inserts to the same page, such as the last page of a clustered index. Option B is wrong because buffer pool insufficient leads to PAGEIOLATCH waits, not PAGELATCH. Option C is wrong because high CPU usage due to inefficient queries causes CPU pressure, not directly PAGELATCH waits.

Option D is wrong because long-running transactions blocking other queries results in lock waits, not page latch contention.

Practice this question →
175
MCQmedium

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

A.Ledger tables
B.Always On availability groups
C.Intelligent Insights
D.Always Encrypted with secure enclaves
AnswerC

isIntelligentInsightsOn is true.

Why this answer

The JSON output shows "isIntelligentInsightsOn": true, indicating that the Intelligent Insights feature is enabled for the Azure SQL Database. Option A is incorrect because "isLedgerOn" is false. Option B is incorrect because there is no mention of availability groups in the JSON.

Option D is incorrect because even though "preferredEnclaveType" is set to VBS, Always Encrypted with secure enclaves requires explicit configuration on columns, and the setting alone does not mean the feature is enabled.

Practice this question →
176
MCQmedium

You manage an Azure SQL Database that runs a customer-facing application. You notice that during peak hours, the DTU consumption reaches 100% and queries experience timeouts. You need to ensure consistent performance without manual intervention. What should you implement?

A.Enable Query Store to identify costly queries.
B.Implement read scale-out to offload read queries.
C.Configure automatic tuning to force plan regression fixes.
D.Scale up the database to a higher service tier (e.g., from S2 to S3).
AnswerD

Scaling up increases DTU limits, alleviating resource contention.

Why this answer

Scaling up to a higher service tier (e.g., from S2 to S3) increases DTU limits, providing more CPU, memory, and IO resources to handle peak demand, thus preventing timeouts without manual intervention. Option A is incorrect because Query Store helps monitor and troubleshoot performance but does not automatically resolve resource shortages. Option B is incorrect because read scale-out offloads read-only queries to a read-only replica, but the issue is DTU consumption reaching 100%, which affects all queries, not just reads.

Option C is incorrect because automatic tuning can fix plan regression but does not add resources; it only optimizes query plans.

Practice this question →
177
MCQmedium

You observe that the average of Maximum DTU consumption over the last hour is consistently above 90%. What should you do next?

A.Scale up the database to a higher service tier or increase DTU.
B.Enable Query Store to analyze top queries.
C.Rebuild all indexes in the database.
D.Do nothing; it's normal for DTU to be high.
AnswerA

High DTU consumption indicates need for more resources.

Why this answer

When average DTU consumption is consistently above 90%, it indicates high resource usage. Scaling up the database to a higher service tier or increasing DTUs directly addresses the resource pressure and improves performance. Option B is incorrect because enabling Query Store is a diagnostic tool to identify performance issues, but it does not provide immediate relief.

Option C is incorrect because rebuilding indexes can help with fragmentation but may not address the underlying resource limitation. Option D is incorrect because sustained high DTU can lead to performance degradation and should be addressed.

Practice this question →
178
Multi-Selectmedium

Which TWO metrics should you monitor in Azure SQL Database to detect a potential memory pressure issue?

Select 2 answers
A.Page Life Expectancy
B.Memory Grants Pending
C.Average CPU percent
D.Log IO
E.Data IO
AnswersA, B

Low PLE indicates memory pressure.

Why this answer

Page Life Expectancy (PLE) is a key metric that indicates how long a data page remains in the buffer pool before being evicted. A consistently low PLE (e.g., below 300 seconds) signals that pages are being flushed too quickly due to memory pressure, often from insufficient buffer pool memory. Memory Grants Pending tracks the number of queries waiting for a memory grant to execute; a non-zero value indicates that the server cannot allocate enough memory to satisfy query workspace requirements, directly pointing to memory pressure.

Exam trap

The trap here is that candidates often confuse high CPU or I/O metrics with memory pressure, but CPU and I/O metrics reflect different resource bottlenecks, while PLE and Memory Grants Pending are the direct indicators of memory contention in Azure SQL Database.

Practice this question →
179
MCQeasy

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

A.Log IO percent
B.DTU used
C.CPU percent
D.Data IO percent
AnswerA

Measures log generation rate.

Why this answer

'Log IO percent' in Azure SQL Database reflects the log generation rate, which is critical for monitoring in the Hyperscale tier. Option B is incorrect because 'DTU used' is specific to DTU-based tiers, not Hyperscale. Option C is incorrect because 'CPU percent' measures processor usage, not log rate.

Option D is incorrect because 'Data IO percent' measures data file I/O, not log I/O.

Practice this question →
180
Multi-Selecthard

Which THREE factors should you consider when choosing between a Gen5 and a Premium-series hardware configuration for an Azure SQL Database? (Select three.)

Select 3 answers
A.Number of vCores available.
B.Memory-to-vCore ratio.
C.IOPS and throughput limits.
D.Compute generation and CPU architecture.
E.Support for Business Critical tier.
AnswersB, C, D

Premium-series often has higher memory per vCore.

Why this answer

The correct answers are B, C, and D. Gen5 and Premium-series hardware configurations for Azure SQL Database differ in memory-to-vCore ratio (B), IOPS and throughput limits (C), and compute generation/CPU architecture (D). Option A is incorrect because the number of vCores available is not a differentiating factor; both series offer a range of vCore counts.

Option E is incorrect because both Gen5 and Premium-series support the Business Critical service tier.

Practice this question →
181
Multi-Selectmedium

Which TWO methods can you use to monitor Azure SQL Database wait statistics?

Select 2 answers
A.Query sys.dm_os_wait_stats
B.Azure Monitor Alerts
C.Query sys.dm_exec_query_stats
D.Dynamic Management Views
E.Query Store wait statistics reports
AnswersA, E

Direct wait stats DMV.

Why this answer

Options A and E are correct. Querying sys.dm_os_wait_stats (A) directly provides cumulative wait statistics at the instance level. Query Store wait statistics reports (E) offer a query-focused view of wait statistics over time.

Option B is incorrect because Azure Monitor Alerts are for setting notifications based on metrics or logs, not for monitoring wait statistics directly. Option C is incorrect because sys.dm_exec_query_stats shows execution statistics like CPU and duration, not wait types. Option D is incorrect because 'Dynamic Management Views' is a broad category, not a specific method; while some DMVs relate to waits, the generic term is not a valid answer.

Practice this question →
182
MCQhard

You are the database administrator for an Azure SQL Database used by a financial trading application. The database is in the Business Critical service tier with 16 vCores. The application executes thousands of small, high-frequency transactions per second. Recently, the application's response time has increased, and you observe high PAGELATCH_EX waits in sys.dm_os_waiting_tasks. The database is 500 GB with a single data file (tempdb.mdf) and a single log file (tempdb_log.ldf). TempDB is configured with the default settings. You need to reduce PAGELATCH_EX contention in TempDB. What should you do?

A.Add additional TempDB data files equal to the number of vCores (16).
B.Move TempDB to Azure Premium Storage for better IO latency.
C.Increase the database service tier to 24 vCores.
D.Increase the initial size of the TempDB data file to 100 GB.
AnswerA

Multiple data files reduce allocation contention on SGAM and PFS pages, which cause PAGELATCH_EX waits.

Why this answer

PAGELATCH_EX contention in TempDB is typically caused by allocation page contention when many concurrent transactions compete for the same system page (e.g., PFS, GAM, SGAM). In Azure SQL Database Business Critical tier, adding multiple TempDB data files equal to the number of vCores (16) reduces contention by distributing allocations across files, each with its own allocation structures. This is a proven best practice for high-concurrency workloads like financial trading applications.

Exam trap

The trap here is that candidates often confuse PAGELATCH_EX (allocation page contention) with PAGEIOLATCH_EX (IO-related waits) and incorrectly choose storage or tier upgrades instead of the file-count solution.

How to eliminate wrong answers

Option B is wrong because moving TempDB to Azure Premium Storage improves IO latency but does not resolve allocation page latch contention, which is a logical contention issue, not a physical IO bottleneck. Option C is wrong because increasing the service tier to 24 vCores adds more CPU and memory but does not address the root cause of PAGELATCH_EX waits, which are related to TempDB file structure, not compute resources. Option D is wrong because increasing the initial size of the single TempDB data file to 100 GB does not reduce contention; it only reduces auto-growth events but still leaves all allocations contending on the same system pages within a single file.

Practice this question →
183
MCQhard

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that during peak hours, the log rate is throttled frequently. You need to reduce log write throttling. What should you do?

A.Enable accelerated database recovery to reduce log generation.
B.Reduce the size of individual transactions to commit more frequently.
C.Partition large tables to spread log writes across files.
D.Increase the service level objective (SLO) to a higher vCore count.
AnswerD

Higher SLO provides higher log rate limit.

Why this answer

In Azure SQL Database Hyperscale, log write throttling occurs when the log generation rate exceeds the throughput capacity of the current service level objective (SLO). Increasing the vCore count raises the log write throughput limit, directly alleviating throttling. This is the correct approach because Hyperscale log rate limits are tied to the SLO, and higher tiers provide higher log I/O bandwidth.

Exam trap

The trap here is that candidates often assume reducing transaction size (Option B) will reduce log rate, but in Hyperscale, frequent small commits increase log flush frequency and can actually trigger throttling due to the per-second log rate limit.

How to eliminate wrong answers

Option A is wrong because Accelerated Database Recovery (ADR) reduces transaction rollback and recovery time, not the volume of log writes; it does not address log rate throttling. Option B is wrong because committing more frequently with smaller transactions actually increases the number of log flushes and can worsen log rate throttling, not reduce it. Option C is wrong because partitioning large tables distributes data across filegroups but does not affect the log write rate to the single transaction log; log writes are serialized and not spread across files.

Practice this question →
184
MCQmedium

You need to monitor Azure SQL Database performance over time and receive alerts when CPU usage exceeds 80%. Which Azure service should you use?

A.Automatic tuning
B.Query Performance Insight
C.Azure Monitor Alerts
D.SQL Assessment
AnswerC

Azure Monitor Alerts can trigger on CPU metric thresholds.

Why this answer

Azure Monitor Alerts is the correct service because it allows you to create metric-based alert rules that trigger when the CPU percentage of an Azure SQL Database exceeds a defined threshold (e.g., 80%). It continuously monitors performance metrics over time and sends notifications (e.g., email, SMS, or webhook) when the condition is met, fulfilling the requirement for both monitoring and alerting.

Exam trap

The trap here is that candidates often confuse Query Performance Insight (which shows historical query performance data) with a monitoring/alerting tool, but it lacks the ability to set proactive threshold-based alerts like Azure Monitor Alerts provides.

How to eliminate wrong answers

Option A is wrong because Automatic tuning is a feature that automatically adjusts index creation, index dropping, and query plan choices to optimize performance; it does not provide monitoring or alerting capabilities. Option B is wrong because Query Performance Insight provides detailed analysis of query performance, including resource consumption and wait statistics, but it does not support proactive alerting based on CPU thresholds. Option D is wrong because SQL Assessment evaluates the configuration and best practices of Azure SQL Database (e.g., security, performance settings) and generates a report, but it does not monitor real-time performance or send alerts.

Practice this question →
185
MCQmedium

You deploy a new Azure SQL Database and need to ensure that all queries are logged for performance analysis. Which configuration should you enable?

A.Data classification
B.Server-level audit
C.Diagnostic settings for SQLInsights
D.Query Store
AnswerD

Query Store captures query runtime statistics and plans.

Why this answer

Query Store captures a history of query execution plans, runtime statistics, and wait statistics, enabling detailed performance analysis and troubleshooting. It is the correct choice because it is specifically designed to log query-level performance data for Azure SQL Database without requiring external storage or additional configuration.

Exam trap

The trap here is that candidates often confuse server-level audit or diagnostic settings with query-level logging, but Query Store is the only feature that natively logs query execution plans and runtime statistics for performance analysis in Azure SQL Database.

How to eliminate wrong answers

Option A is wrong because Data Classification is a security feature for identifying and labeling sensitive columns, not for logging query performance. Option B is wrong because Server-level audit logs database events for compliance and security auditing, not query execution details for performance analysis. Option C is wrong because Diagnostic settings for SQLInsights send telemetry to Azure Monitor for broader monitoring, but they do not capture per-query execution plans and runtime statistics like Query Store does.

Practice this question →
186
Multi-Selecthard

You have an Azure SQL Database that is configured with geo-replication. You need to optimize read performance for reporting queries that run on the secondary replica. Which three actions should you take? (Choose three.)

Select 3 answers
A.Enable automatic tuning on the secondary.
B.Configure the secondary as a readable secondary.
C.Create nonclustered indexes on the secondary replica.
D.Force query plans on the primary replica to benefit the secondary.
E.Use read-only routing to direct reporting queries to the secondary.
AnswersA, C, E

Can optimize query performance automatically.

Why this answer

Enabling automatic tuning on the secondary replica allows the database to automatically create, drop, and force index recommendations based on the workload running on that replica. This is particularly beneficial for reporting queries, as the secondary can independently optimize its own index strategy without affecting the primary. Automatic tuning on a readable secondary helps maintain query performance by adapting to the read-heavy workload patterns typical of reporting scenarios.

Exam trap

The trap here is that candidates often assume that enabling automatic tuning on the primary automatically benefits the secondary, or that forcing query plans on the primary will propagate to the secondary, when in fact each replica maintains its own query store and plan cache.

Practice this question →
187
MCQmedium

Your Azure SQL Database is hitting the DTU limit frequently during business hours. You need to identify which queries are consuming the most DTU. What should you do?

A.Query sys.dm_db_resource_stats to see DTU usage per minute.
B.Use Azure Metrics for SQL Database to view DTU consumption per database.
C.Enable Query Store and review the Wait Statistics report.
D.Query sys.dm_exec_query_stats and correlate with sys.dm_db_resource_stats.
AnswerD

Querying sys.dm_exec_query_stats and correlating with sys.dm_db_resource_stats enables per-query DTU analysis.

Why this answer

Querying sys.dm_exec_query_stats and correlating with sys.dm_db_resource_stats allows you to identify which specific queries are consuming the most DTU. sys.dm_db_resource_stats provides DTU consumption data at the database level, while sys.dm_exec_query_stats gives query-level performance metrics. By joining these two DMVs, you can pinpoint the top DTU-consuming queries during business hours.

Practice this question →
188
MCQmedium

You are analyzing the exhibit KQL query that queries Azure Diagnostics logs for Query Store runtime statistics. The query is intended to show average CPU time per hour for each database. However, the result shows no data for the last 24 hours, although Query Store is enabled on all databases. What is the most likely reason?

A.Query Store is not enabled on the databases.
B.The diagnostic settings are not configured to send QueryStoreRuntimeStatistics to Log Analytics.
C.The time range in the query is too narrow and excludes the last 24 hours.
D.The query syntax is incorrect and needs to use 'project' before 'summarize'.
AnswerB

Without that category, no data is collected.

Why this answer

Query Store runtime statistics are sent to Azure Diagnostics only if diagnostic settings are configured to collect that category. Option A is wrong because Query Store is enabled. Option C is wrong because the query is correct.

Option D is wrong because the KQL query syntax does not require 'project' before 'summarize'; placement does not affect results.

Practice this question →
189
MCQeasy

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

A.Query Store
B.Azure Monitor metrics
C.Extended Events
D.SQL Server Profiler
AnswerA

Query Store provides built-in, low-overhead query performance monitoring and analysis.

Why this answer

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

Option D is wrong because SQL Server Profiler is deprecated and has high overhead.

Practice this question →
190
MCQmedium

You are monitoring an Azure SQL Database that hosts a financial application. You notice that the average DTU consumption is 20%, but occasionally spikes to 95% for 5-minute intervals. Users report slow response times during these spikes. You need to ensure consistent performance without over-provisioning resources. What should you do?

A.Migrate the database to the Hyperscale service tier.
B.Scale the database to a higher service tier to absorb the spikes.
C.Enable Query Store and use the Regressed Queries feature to find slow queries.
D.Identify and optimize the queries running during the spike periods, possibly rescheduling a heavy ETL job.
AnswerD

Rescheduling or tuning the ETL job can eliminate the spikes, ensuring consistent performance.

Why this answer

The issue is not a consistent resource shortage but periodic spikes caused by specific queries, likely from a heavy ETL job. By identifying and optimizing those queries or rescheduling the job, you can eliminate the spikes without permanently scaling up resources, which would waste cost and capacity. This aligns with the DP-300 focus on performance tuning and resource optimization rather than blind scaling.

Exam trap

The trap here is that candidates assume spikes always require scaling up (Option B) or migrating to a higher tier (Option A), but the DP-300 exam emphasizes that optimization and scheduling are often more cost-effective than over-provisioning.

How to eliminate wrong answers

Option A is wrong because Hyperscale is designed for large, highly scalable databases with fast recovery and read scale-out, not for handling occasional DTU spikes; it would over-provision and increase cost unnecessarily. Option B is wrong because scaling to a higher service tier permanently increases DTU capacity to absorb spikes that occur only 5 minutes at a time, leading to over-provisioning and wasted cost for the 80% of time when DTU is at 20%. Option C is wrong because Query Store and Regressed Queries help identify performance regressions over time, but the question already indicates the spikes are caused by known periodic heavy workloads (e.g., ETL), so the immediate action is to optimize or reschedule those queries, not just monitor them.

Practice this question →
191
MCQhard

Your Azure SQL Database is experiencing high DTU consumption. You need to identify the top resource-consuming queries. What should you do?

A.Use the Query Store reports in the Azure portal
B.Use SQL Server Profiler
C.Create an Extended Events session to capture query events
D.Query sys.dm_exec_query_stats
AnswerA

Built-in, persistent, and easy to use.

Why this answer

Query Store in Azure SQL Database provides built-in reports to identify top resource-consuming queries by CPU, IO, and duration, making it the easiest and most direct method. Option B (SQL Server Profiler) is not supported in Azure SQL Database. Option C (Extended Events) is more complex and not the simplest approach for this task.

Option D (sys.dm_exec_query_stats) can be used but lacks persistent historical data and is more effort than using Query Store reports.

Practice this question →
192
Multi-Selecteasy

Which TWO database properties can be configured to optimize performance for a read-heavy workload in Azure SQL Database?

Select 2 answers
A.Query Store
B.Data masking
C.Long-term retention policy
D.Automatic tuning (CREATE INDEX)
E.Read scale-out
AnswersA, E

Monitors query performance.

Why this answer

For a read-heavy workload in Azure SQL Database, enabling Query Store (option A) helps monitor query performance, identify regressions, and optimize query plans. Read scale-out (option E) offloads read-only queries to a readable secondary replica, reducing load on the primary and improving performance. Options B (Data masking) is a security feature, C (Long-term retention policy) is for backup retention, and D (Automatic tuning - CREATE INDEX) creates indexes but is not specifically for read-heavy workloads; it can help but is not a primary property for read offload.

Practice this question →
193
Multi-Selecthard

You are troubleshooting a performance issue on an Azure SQL Database. The database is experiencing high PAGELATCH_EX waits. Which TWO measures can help reduce these waits?

Select 2 answers
A.Use a hash distribution or round-robin distribution in a table design
B.Increase MAXDOP for the queries
C.Partition the table to distribute inserts
D.Use OPTIMIZE_FOR_SEQUENTIAL_KEY index option
E.Enable snapshot isolation level
AnswersC, D

Correct. Partitioning a table spreads insert activity across multiple pages, reducing contention on the last page and decreasing PAGELATCH_EX waits.

Why this answer

PAGELATCH_EX waits are caused by contention on the last page of an index, often due to sequential inserts. Two effective measures in Azure SQL Database are partitioning the table (option C) to spread inserts across multiple pages and using OPTIMIZE_FOR_SEQUENTIAL_KEY index option (option D) to reduce page latch contention. Option A is incorrect because hash or round-robin distribution is a feature of Azure Synapse dedicated SQL pools, not Azure SQL Database.

Option B is incorrect because increasing MAXDOP can increase parallelism and potentially worsen latch contention. Option E is incorrect because snapshot isolation does not reduce page latch waits.

Practice this question →
194
MCQhard

The query returns a list of query hashes with high average duration. You need to identify which queries are most likely causing CPU pressure. What additional metric should you include?

A.Include wait_stats to see blocking.
B.Include count_executions to see frequency.
C.Include avg_logical_reads to see I/O consumption.
D.Include avg_cpu_time to measure CPU usage.
AnswerD

CPU time directly measures CPU consumption.

Why this answer

Avg_cpu_time directly measures CPU usage per query, helping correlate high duration with CPU pressure. Option A is incorrect because wait_stats show wait types (e.g., blocking), not CPU consumption. Option B is incorrect because execution count alone does not indicate CPU usage; a query could run frequently but use little CPU.

Option C is incorrect because avg_logical_reads measure I/O, not CPU pressure.

Practice this question →
195
MCQeasy

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that resource usage has exceeded 90% for the past hour. What is the recommended first step to troubleshoot?

A.Scale up the database to a higher service tier immediately.
B.Check sys.dm_os_wait_stats for high wait types.
C.Enable the Query Store and wait for data collection.
D.Review the Intelligent Insights root cause analysis report.
AnswerD

Provides automated analysis of performance issues.

Why this answer

Intelligent Insights provides a root cause analysis report that can help identify the issue. Option A is wrong because scaling up should only be done after identifying the root cause. Option B is wrong because checking wait statistics is lower-level; Intelligent Insights already provides a diagnosis.

Option C is wrong because Query Store may not be enabled by default and is not the first step.

Practice this question →
196
MCQmedium

You are troubleshooting a performance degradation on an Azure SQL Database. You notice that the database is hitting the maximum DTU limit frequently. Which action should you take first to reduce DTU consumption?

A.Increase the log rate limit
B.Scale up the database to a higher service tier
C.Use Query Performance Insight to identify and optimize top resource-consuming queries
D.Rebuild all indexes in the database
AnswerC

Query Performance Insight helps identify and optimize top resource-consuming queries, directly reducing DTU usage.

Why this answer

Query Performance Insight helps identify the top resource-consuming queries. Optimizing those queries is the most direct way to reduce DTU consumption. Option A is wrong because increasing the log rate limit does not reduce DTU consumption.

Option B is wrong because scaling up increases cost and may not address the root cause. Option D is wrong because rebuilding all indexes can increase DTU temporarily.

Practice this question →
197
MCQeasy

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the General Purpose tier. You notice that many queries are scanning large tables. What is the best first action to improve performance?

A.Partition the large tables by date.
B.Analyze the missing index recommendations from Query Store.
C.Scale up to Business Critical tier.
D.Implement columnstore indexes on all large tables.
AnswerB

Identifies specific indexes to reduce scans.

Why this answer

The best first action is to analyze the missing index recommendations from Query Store. Query Store tracks query performance and provides actionable index recommendations based on actual workload patterns. Partitioning (A) can help manage large tables but does not directly address scan issues caused by missing indexes.

Scaling up to Business Critical (C) is costly and premature without first analyzing the workload. Columnstore indexes (D) are beneficial for analytical queries but should be implemented selectively based on query patterns, not as a blanket solution. Therefore, analyzing missing index recommendations is the optimal starting point for optimization.

Practice this question →
198
MCQmedium

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

A.Grant the SELECT permission on the 'Sales' schema to 'AppUser'.
B.Add 'AppUser' to the db_datareader role.
C.Create a database role, grant EXECUTE on the 'Sales' schema to the role, and add 'AppUser' to the role.
D.Grant the EXECUTE permission on each stored procedure individually to 'AppUser'.
AnswerC

Schema-level EXECUTE permission grants execute on all stored procedures in the schema, meeting the requirement.

Why this answer

Creating a database role with EXECUTE permission on the 'Sales' schema grants execute on all stored procedures in that schema, adhering to least privilege. Option A is wrong because granting SELECT on the schema would give read access to underlying tables, which is not allowed. Option B is wrong because adding AppUser to the db_datareader role provides read access to all tables in the database, exceeding the required permissions.

Option D is not wrong in terms of functionality but is more administrative work compared to using a schema-level grant; however, for the requirement of least privilege, granting EXECUTE on each stored procedure individually would still work but is less efficient than the schema-level approach.

Practice this question →
199
MCQmedium

You manage an Azure SQL Database that is experiencing higher than expected DTU consumption. You need to identify which queries are consuming the most resources. Which dynamic management view should you query?

A.Query sys.dm_exec_requests
B.Query sys.dm_os_wait_stats
C.Query sys.dm_exec_query_stats
D.Query sys.dm_db_resource_stats
AnswerC

Correct. sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans, allowing identification of queries with high CPU, I/O, or duration.

Why this answer

sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans, including CPU time, logical reads, and duration, making it ideal for identifying queries that consume the most resources in Azure SQL Database. Option A (sys.dm_exec_requests) shows only currently executing requests, not historical data. Option B (sys.dm_os_wait_stats) provides server-level wait statistics, not per-query resource usage.

Option D (sys.dm_db_resource_stats) shows database-level DTU consumption per minute, not per-query details.

Practice this question →
200
MCQmedium

Your organization runs a critical e-commerce application on Azure SQL Database. You notice that during peak hours, query performance degrades significantly. You have enabled Intelligent Insights and Query Store. Which diagnostic metric would you analyze first to identify the root cause of the performance degradation?

A.Query store wait statistics
B.Deadlock count
C.DTU/CPU consumption
D.Failed connections
AnswerC

DTU/CPU consumption is the primary metric to analyze because peak load directly increases resource usage, leading to performance degradation.

Why this answer

DTU/CPU consumption directly correlates with query performance during peak load. With Intelligent Insights and Query Store enabled, analyzing DTU/CPU consumption helps identify resource contention as the primary cause of degradation. Option A (Query store wait statistics) is useful for detailed analysis but is not the first metric to check.

Option B (Deadlock count) is not a typical cause of sustained degradation. Option D (Failed connections) indicates availability issues, not performance.

Practice this question →
201
MCQhard

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

A.Increase the max log size using ALTER DATABASE.
B.Batch transactions and reduce log writes.
C.Enable accelerated database recovery to reduce log I/O.
D.Move to Business Critical tier.
AnswerB

Batching reduces the number of log writes, staying within limits.

Why this answer

Batching transactions reduces the number of log write operations, lowering the log write throughput below the service tier limit and avoiding throttling. Option A is incorrect because increasing the max log size does not affect the log write rate; it only provides more storage. Option C is incorrect because accelerated database recovery (ADR) improves recovery time and may reduce log I/O for crash recovery, but it does not directly reduce the sustained log write throughput from transaction processing.

Option D is incorrect because the requirement explicitly states not to move to Business Critical.

Exam trap

Candidates may think that increasing log size or enabling ADR will solve throttling, but only reducing log writes (via batching or minimally logged operations) addresses the throughput limit.

Practice this question →
202
MCQmedium

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

A.Enable forced parameterization on the database to promote plan reuse.
B.Create a plan guide to force a specific execution plan for the stored procedure.
C.Implement Query Store hints to force the last known good plan for the stored procedure.
D.Enable automatic tuning in Azure SQL Database and rely on the 'FORCE LAST GOOD PLAN' feature.
AnswerA

Forced parameterization helps reduce plan cache bloat and improves plan stability for queries with literals.

Why this answer

Enabling forced parameterization causes the database to treat literal values in queries as parameters, which promotes plan reuse and reduces plan cache bloat. This is ideal for stored procedures like usp_getProducts that have multiple query variants with different plans. Option B is incorrect because plan guides target specific queries, not the entire stored procedure, and are complex to manage.

Option C is incorrect because Query Store hints are used to force a specific plan for a query, but they require manual intervention and don't address the root cause of plan variability. Option D is incorrect because automatic tuning with 'FORCE LAST GOOD PLAN' is reactive and may not provide consistent performance for a procedure with many variants.

Practice this question →
203
MCQmedium

Refer to the exhibit. An Azure SQL Database is receiving Intelligent Insights degradation alerts. Which action should be taken first?

A.Increase the maximum storage size
B.Change the service tier to BusinessCritical
C.Scale up to Standard S3 (100 DTU)
D.Implement automatic tuning recommendations
AnswerD

Automatic tuning can optimize queries to reduce resource usage.

Why this answer

The database is hitting DTU limits (100% CPU and data IO). The alert recommends scaling up or optimizing queries. Since CPU and data IO are both high, query optimization is the better first step to avoid cost increase.

Practice this question →
204
MCQeasy

You need to monitor the storage space usage of an Azure SQL Database over time. Which tool should you use?

A.Intelligent Insights
B.Azure SQL Analytics (Azure Monitor)
C.Query Store
D.SQL Server Management Studio (SSMS)
AnswerB

Provides historical metrics including storage.

Why this answer

Azure SQL Analytics in Azure Monitor provides historical storage metrics. Option A is wrong because Intelligent Insights is for proactive diagnostics, not storage monitoring. Option C is wrong because Query Store focuses on query performance.

Option D is wrong because SSMS does not provide historical monitoring.

Practice this question →
205
MCQhard

Your organization runs a critical application on Azure SQL Managed Instance. You notice that the `tempdb` database is experiencing contention, leading to PAGELATCH_EX waits. The managed instance has 16 vCores and is using the General Purpose service tier. You need to reduce `tempdb` contention without altering the application code or changing the service tier. What should you do?

A.Enable memory-optimized tempdb metadata to reduce allocation contention.
B.Add 4 additional tempdb data files of equal size.
C.Add 16 tempdb data files of equal size, matching the number of vCores.
D.Enable accelerated database recovery to reduce transaction log overhead.
AnswerC

Distributing tempdb data across multiple files reduces allocation contention; best practice is to have one file per vCore.

Why this answer

Adding tempdb data files equal to the number of vCores (16) is the recommended best practice to reduce allocation contention. With 16 vCores, 16 files minimize PAGELATCH_EX waits. Option A is incorrect because memory-optimized tempdb metadata helps only with system metadata contention, not data page allocation.

Option B is incorrect because adding only 4 files is insufficient; the recommended number is equal to the number of vCores. Option D is incorrect because accelerated database recovery does not address tempdb contention.

Practice this question →
206
MCQeasy

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

A.Reorganize indexes regularly.
B.Use Query Store to force a good plan.
C.Add OPTIMIZE FOR UNKNOWN query hint to all queries.
D.Set the database to FORCE_PARAMETERIZATION = ON.
AnswerD

Forces parameterization to reduce sniffing effects.

Why this answer

FORCE_PARAMETERIZATION = ON is a database-wide setting that forces parameterization of all ad-hoc queries, helping to mitigate parameter sniffing issues without requiring code changes. Option A (reorganizing indexes) addresses fragmentation, not parameter sniffing. Option B (Query Store plan forcing) is used to pin a specific plan after a regression, not to prevent sniffing proactively.

Option C (OPTIMIZE FOR UNKNOWN) is a query hint that requires modifying query code, not a code-less solution.

Practice this question →
207
MCQeasy

You manage an Azure SQL Database that experiences periodic performance degradation. You need to identify the top queries by CPU consumption over the last hour. Which dynamic management view should you query?

A.sys.dm_exec_sessions
B.sys.dm_exec_query_plan
C.sys.dm_exec_query_stats
D.sys.dm_exec_requests
AnswerC

Provides aggregated CPU statistics for cached query plans.

Why this answer

(sys.dm_exec_query_stats) is correct because this DMV returns aggregated performance statistics for cached query plans, including total CPU time. It can be filtered to look at queries over the last hour. Option A (sys.dm_exec_sessions) shows current session-level information, not query-level CPU.

Option B (sys.dm_exec_query_plan) returns the actual execution plan, not performance statistics. Option D (sys.dm_exec_requests) shows currently executing requests, not historical CPU consumption.

Practice this question →
208
MCQmedium

Your Azure SQL Database is experiencing a sudden increase in wait time due to PAGEIOLATCH_SH waits. What should you do to reduce these waits?

A.Increase the database max memory
B.Add appropriate indexes to reduce table scans
C.Enable page compression on large tables
D.Force parameterization of queries
AnswerB

Adding indexes reduces the number of pages read, directly reducing PAGEIOLATCH_SH waits.

Why this answer

PAGEIOLATCH_SH waits indicate I/O bottlenecks caused by excessive page reads from disk. Adding appropriate indexes reduces the number of pages read by enabling more efficient data access (e.g., index seeks instead of table scans), directly reducing I/O. Option A is incorrect because increasing max memory does not address the underlying query inefficiency driving I/O.

Option C is incorrect because page compression reduces storage but may increase CPU and does not primarily reduce I/O waits. Option D is incorrect because forcing parameterization improves plan reuse but does not target I/O reduction.

Practice this question →
209
MCQmedium

Refer to the exhibit. You are managing db1, which is currently in Standard tier. What is the maximum size you can set for db1 without changing the service objective?

A.107,374,182,400 bytes (100 GB)
B.1,073,741,824,000 bytes (1 TB)
C.268,435,456,000 bytes (250 GB)
D.536,870,912,000 bytes (500 GB)
AnswerC

Standard S2 supports up to 250 GB. The current max is 100 GB, so you can increase to 250 GB.

Why this answer

The first command shows that Standard S2 supports a max size of 268435456000 bytes (250 GB). The second command shows the current max size is 107374182400 bytes (100 GB). Since db1 is already on S2, you can increase the max size up to 250 GB within the same service objective.

Practice this question →
210
MCQeasy

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

A.All successful and failed login attempts and all schema changes
B.All database-level actions
C.Only successful and failed database authentication attempts
D.All successful and failed logins and all data modification statements
AnswerC

Only the two groups specified.

Why this answer

The configuration only includes SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP. Option A is wrong because it includes actions not in the config. Option B is wrong because it includes data modification actions.

Option D is wrong because it includes all actions.

Practice this question →
211
Multi-Selecthard

You are configuring performance optimization for an Azure SQL Database that uses the Hyperscale service tier. The database has heavy read-write workloads. Which THREE actions should you take to optimize performance?

Select 3 answers
A.Create nonclustered columnstore indexes on large fact tables.
B.Configure resource governor to limit CPU usage.
C.Enable accelerated database recovery (ADR) to reduce transaction log IO.
D.Configure read-scale replicas to offload reporting queries.
E.Increase the service tier to Business Critical.
AnswersA, C, D

Columnstore indexes improve performance for large analytical queries.

Why this answer

Options A, C, and D are correct. Hyperscale benefits from nonclustered indexes on read-intensive tables, accelerated database recovery reduces log IO, and read-scale replicas distribute read load. Option B is wrong because Hyperscale does not support resource governor.

Option E is wrong because increasing service tier is not an optimization action.

Practice this question →
← PreviousPage 3 of 3 · 211 questions total

Ready to test yourself?

Try a timed practice session using only Monitor, configure, and optimize database resources questions.

Start 20-question session