Courseiva

CCNA Monitor, configure, and optimize database resources Questions

75 of 212 questions · Page 1/3 · Monitor, configure, and optimize database resources · Answers revealed

Practice these questionsDomain overviewAll questions
1
MCQhard

You are a database administrator for a financial services company that runs a critical application on Azure SQL Database in the Business Critical service tier. The database is named 'TransactionsDB' and has a size of 500 GB. The application experiences periodic performance degradation during end-of-month batch processing. Analysis shows that the degradation coincides with high log write activity and increased latency for write transactions. You have already verified that the log rate is within the service tier limits. The batch process performs a large number of INSERT, UPDATE, and DELETE operations on multiple tables. You need to optimize the transaction log performance without changing the application code or the service tier. The database uses the full recovery model and has a log backup every 5 minutes. What should you do?

A.Implement transactional replication to offload write operations to a secondary database.
B.Enable page compression on all tables to reduce the amount of data written to the log.
C.Enable accelerated database recovery to reduce the log space required for long-running transactions.
D.Increase the initial size of the transaction log file to 200 GB and set the auto-growth increment to a fixed size of 1 GB.
AnswerC

Accelerated Database Recovery reduces log space needed for long-running transactions, improving log write throughput during batch operations.

Why this answer

Enabling Accelerated Database Recovery (ADR) reduces the log space required for long-running transactions by using a persistent version store (PVS) to avoid writing undo logs. This helps mitigate log growth during batch operations, reducing the frequency of log autogrowth events and improving overall log write performance. Option A is incorrect because transactional replication adds overhead and does not directly optimize log performance.

Option B is incorrect because page compression reduces data storage but does not significantly reduce log writes (log records are row-level). Option D is incorrect because Azure SQL Database manages log file sizing automatically; you cannot configure initial size or auto-growth increments manually.

Practice this question →
2
MCQmedium

You administer an Azure SQL Database that uses the General Purpose tier. Users report that queries are slow during peak hours. You need to identify if the slow performance is due to log write latency. Which metric should you examine in Azure Monitor?

A.Log IO percent
B.Transaction log usage
C.Average IO latency
D.Log write latency
AnswerD

Measures log write time.

Why this answer

The correct metric to examine is Log write latency (option D), as it directly measures the time taken to write to the transaction log, which can indicate slow performance due to log write latency. Option A (Log IO percent) measures the percentage of log throughput used, not latency. Option B (Transaction log usage) measures log file space used, not performance.

Option C (Average IO latency) includes both data and log I/O, so it is not specific to log writes.

Practice this question →
3
Multi-Selecteasy

You are monitoring an Azure SQL Database that is experiencing high DTU usage. Which TWO metrics should you examine to determine whether the bottleneck is CPU or I/O?

Select 2 answers
A.Log write bytes per second.
B.Average CPU percentage.
C.Number of deadlocks per second.
D.Used storage space in GB.
E.Number of active sessions.
AnswersA, B

Correct. High log write bytes per second indicates significant I/O activity from transaction log writes, helping to identify an I/O bottleneck.

Why this answer

Average CPU percentage directly measures CPU utilization, indicating a CPU bottleneck when high. Log write bytes per second measures the throughput of transaction log writes, which is a key indicator of I/O activity and can reveal an I/O bottleneck. Therefore, both A and B are correct metrics to diagnose whether the high DTU usage is caused by CPU or I/O.

Practice this question →
4
MCQeasy

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You run a query against `sys.dm_exec_query_stats` to find the top 10 queries by total worker time. Several queries show high worker time but low logical reads. The database is not experiencing any blocking or deadlocks. What is the most likely cause of the high worker time?

A.The queries suffer from parameter sniffing leading to suboptimal plans.
B.The queries are experiencing memory pressure causing excessive lazy writes.
C.The queries are waiting on transaction log writes.
D.The queries are CPU-bound due to inefficient query plans.
AnswerD

Correct. High worker time with low logical reads is a classic sign of CPU-bound queries, often caused by inefficient query plans that perform heavy computations (e.g., complex joins, aggregations, or non-sargable predicates).

Why this answer

High worker time (CPU time) with low logical reads indicates that the queries are CPU-bound rather than I/O-bound. Inefficient query plans, such as those with large hash joins, sorts, or non-sargable predicates, can cause excessive CPU consumption without generating many logical reads. Option A is incorrect because parameter sniffing typically leads to varying plan quality, but the consistent high worker time across multiple queries suggests a systematic plan efficiency issue, not necessarily parameter sniffing.

Option B is incorrect because memory pressure would cause increased I/O activity (lazy writes) which is not observed with low logical reads. Option C is incorrect because transaction log writes are I/O operations and would not cause high worker time with low I/O.

Practice this question →
5
MCQmedium

You are monitoring an Azure SQL Database using the sys.dm_db_resource_stats DMV. The avg_log_write_percent column shows 95% for the last hour. What does this indicate, and what should you do?

A.The database is out of transaction log space; increase the max log size.
B.The database storage is running out; scale up storage.
C.The database is nearing its log write IOPS limit; consider scaling up or optimizing log writes.
D.The CPU is overloaded; scale up CPU.
AnswerC

avg_log_write_percent measures log IO percentage; high value indicates IO bottleneck.

Why this answer

The avg_log_write_percent metric in sys.dm_db_resource_stats measures the percentage of the log write IOPS limit used. At 95%, the database is nearing its log write IOPS limit, which can cause transaction delays and throttling. The appropriate response is to scale up the service tier (e.g., to a higher DTU or vCore level) or optimize log writes to reduce IOPS consumption.

Option A is incorrect because the metric does not indicate running out of log space; log space is separate. Option B is incorrect because storage scale (size) does not directly affect log write IOPS. Option D is incorrect because this metric is specifically about log I/O, not CPU.

Practice this question →
6
Multi-Selectmedium

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

Select 2 answers
A.Query sys.dm_exec_requests and filter on wait_type like PAGEIOLATCH.
B.Enable data compression on large tables to reduce I/O.
C.Increase the database service tier to add more compute resources.
D.Use sys.dm_db_resource_stats to see average I/O per minute.
E.Monitor sys.dm_os_performance_counters for CPU usage.
AnswersA, B

PAGEIOLATCH waits indicate I/O bottlenecks.

Why this answer

Options A and B are correct. A: Querying sys.dm_exec_requests with a filter on PAGEIOLATCH wait type identifies queries waiting for I/O, pinpointing I/O bottlenecks. B: Data compression reduces the size of data pages, decreasing I/O operations for large tables.

C: Increasing the service tier adds compute resources (CPU/memory) but does not directly address I/O bottlenecks; it may only mask the issue. D: sys.dm_db_resource_stats provides average I/O per minute at the database level, which is useful for monitoring but not for per-query I/O bottleneck identification. E: sys.dm_os_performance_counters for CPU usage tracks CPU performance, not I/O.

Practice this question →
7
MCQhard

You manage an Azure SQL Database that uses a Serverless compute tier. You notice that during idle periods, the database auto-pauses and then auto-resumes when a connection is made. However, users report that the first query after a pause is slow. You need to improve the performance of the first query. What should you do?

A.Increase the maximum vCores
B.Create a SQL Agent job to ping the database every hour
C.Disable auto-pause for the serverless database
D.Enable Query Store
AnswerC

Disabling auto-pause prevents the database from pausing, avoiding cold start delays.

Why this answer

The slow first query after auto-resume is caused by the cold-start latency of the serverless compute tier, which includes provisioning resources and warming the buffer pool. Disabling auto-pause ensures the database remains online and the buffer pool stays populated, eliminating the cold-start delay for the first query.

Exam trap

The trap here is that candidates may think increasing vCores or using a ping job solves the cold-start problem, but these options either do not address the root cause or are inefficient workarounds, while disabling auto-pause directly eliminates the latency by keeping the database always active.

How to eliminate wrong answers

Option A is wrong because increasing the maximum vCores does not prevent auto-pause or reduce cold-start latency; it only scales compute resources during active periods. Option B is wrong because a SQL Agent job that pings the database every hour would keep the database from auto-pausing only if the ping interval is shorter than the auto-pause delay (default 1 hour), but this is a workaround that does not address the root cause and can incur unnecessary compute costs. Option D is wrong because enabling Query Store captures query performance data but does not affect the auto-pause behavior or the cold-start latency of the first query after resume.

Practice this question →
8
MCQmedium

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in wait time for PAGEIOLATCH_SH. You need to identify the most likely cause. What should you investigate first?

A.Out-of-date statistics
B.Insufficient IOPS or throughput at the database level
C.Missing indexes
D.Blocking from long-running transactions
AnswerB

PAGEIOLATCH_SH waits indicate I/O subsystem pressure, often due to insufficient IOPS or throughput.

Why this answer

PAGEIOLATCH_SH waits indicate I/O subsystem pressure, often due to insufficient IOPS or throughput. Option A is incorrect because out-of-date statistics cause cardinality estimation errors, not I/O waits. Option C is incorrect because missing indexes typically cause table scans but not necessarily PAGEIOLATCH waits.

Option D is incorrect because blocking causes waits like LCK_M_*, not PAGEIOLATCH.

Practice this question →
9
MCQeasy

You are a database administrator for a company that uses Azure SQL Database. You need to configure a diagnostic setting to send database metrics to a Log Analytics workspace for long-term analysis. The solution should be cost-effective and include metrics like CPU percentage, data IO, and log IO. What should you do?

A.Enable Azure SQL Insights (preview) for the database.
B.Enable Query Store and configure it to export to Log Analytics.
C.In the Azure portal, add a diagnostic setting for the database to stream 'AllMetrics' to a Log Analytics workspace.
D.Create a T-SQL job that periodically inserts sys.dm_db_resource_stats into a table in Log Analytics.
AnswerC

Diagnostic settings can stream metrics to Log Analytics for cost-effective long-term analysis.

Why this answer

Diagnostic settings in the Azure portal allow streaming of platform metrics (including 'AllMetrics' for CPU percentage, data IO, log IO) to a Log Analytics workspace cost-effectively. Option A is incorrect because Azure SQL Insights is a paid, advanced monitoring solution that is not necessary for basic metric streaming. Option B is incorrect because Query Store captures query performance data, not resource metrics, and cannot export directly to Log Analytics.

Option D is incorrect because manually inserting from sys.dm_db_resource_stats via a T-SQL job is inefficient, not built-in, and lacks the integration of diagnostic settings.

Practice this question →
10
Multi-Selecthard

Which THREE actions can you take to optimize query performance in Azure SQL Database using Intelligent Query Processing?

Select 3 answers
A.Enable adaptive joins
B.Enable interleaved execution for MSTVFs
C.Enable Query Store
D.Enable columnstore indexes
E.Use approximate count distinct
AnswersA, B, E

Part of IQP for dynamic join strategy selection.

Why this answer

Adaptive joins (A), interleaved execution for multi-statement table-valued functions (B), and approximate count distinct (E) are all features of Intelligent Query Processing (IQP) in Azure SQL Database. They improve query performance by dynamically adjusting join strategies, avoiding suboptimal plans for MSTVFs, and efficiently handling COUNT(DISTINCT) queries. Query Store (C) is a monitoring and tuning feature but it is not part of IQP.

Columnstore indexes (D) are a storage and indexing technique, not part of IQP.

Practice this question →
11
MCQeasy

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

A.The query is experiencing I/O latency
B.The query is using a lot of CPU time
C.The query is using a lot of memory
D.The query is reading many pages from the buffer pool, possibly due to missing indexes
AnswerD

Logical reads are page reads from the buffer pool, high values indicate excessive data access.

Why this answer

High total_logical_reads typically indicates that the query is reading many pages from the buffer pool, which often points to missing or inefficient indexes. Option D is correct. Option A is incorrect because high logical reads relate to buffer pool access, not necessarily I/O latency (which is indicated by high physical reads).

Option B is incorrect because CPU time is measured by worker_time, not logical reads. Option C is incorrect because while logical reads can increase memory usage, the primary indicator of memory usage is the memory grant, not logical reads.

Practice this question →
12
MCQhard

You have an Azure SQL Database that is configured with automatic failover groups. During a planned failover, you notice that the failover takes longer than expected. You need to minimize downtime during future planned failovers. What should you do?

A.Increase the service tier of the secondary to match the primary.
B.Remove the failover group and use a different disaster recovery strategy.
C.Pre-seed the secondary replica by initiating a manual sync before the planned failover.
D.Use a forced failover with data loss to speed up the process.
AnswerC

Reduces synchronization time during failover.

Why this answer

Pre-seeding the secondary replica by initiating a manual sync before the planned failover ensures that the secondary is fully up to date, reducing the synchronization time during the actual failover and thereby minimizing downtime. Option A is incorrect because increasing the service tier of the secondary does not reduce failover time; it only improves performance. Option B is incorrect because removing the failover group would eliminate the failover capability, not improve planned failover speed.

Option D is incorrect because a forced failover with data loss is for unplanned scenarios and would cause data loss, which is not recommended for planned failovers.

Practice this question →
13
MCQmedium

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

A.Configure a SQL Agent alert
B.Configure Azure SQL Auditing
C.Use Azure Advisor recommendations
D.Create a metric alert in Azure Monitor
AnswerD

Azure Monitor supports metric alerts for DTU consumption.

Why this answer

Azure Monitor can create metric alerts based on DTU consumption. Option D is correct. Option A is wrong because SQL Agent cannot send alerts based on DTU.

Option B is wrong because Azure SQL Auditing is for compliance. Option C is wrong because Azure Advisor provides recommendations but not real-time alerts.

Practice this question →
14
Multi-Selectmedium

You are optimizing an Azure SQL Database that runs a reporting workload. The database is in the General Purpose tier. You notice that many queries are performing table scans on large tables. Which TWO actions would most likely improve query performance without increasing costs?

Select 2 answers
A.Update statistics on the tables.
B.Upgrade to Business Critical tier.
C.Increase MAXDOP to 8.
D.Enable automatic tuning.
E.Create nonclustered indexes on columns used in WHERE clauses.
AnswersA, E

Updated statistics help the optimizer choose better execution plans, potentially avoiding scans.

Why this answer

Updating statistics (A) helps the query optimizer generate more accurate execution plans, potentially avoiding table scans. Creating nonclustered indexes on columns used in WHERE clauses (E) can provide direct access paths, reducing the need for full table scans. Both actions improve performance without increasing costs.

Option B (upgrading to Business Critical) increases cost and may not directly address table scans. Option C (increasing MAXDOP) can cause parallelism issues and is not guaranteed to reduce scans. Option D (enabling automatic tuning) is a feature that can suggest index and plan changes but is not as direct as updating statistics or creating indexes.

Practice this question →
15
MCQeasy

A company has an Azure SQL Database that experiences periodic performance degradation. The database uses the General Purpose service tier. You need to identify the most common performance bottlenecks. You enable the Query Store and collect data for a week. Which Query Store view should you query to find queries that have the highest total resource consumption over time?

A.sys.query_store_query_stats
B.sys.query_store_plan
C.sys.dm_exec_query_stats
D.sys.dm_db_resource_stats
AnswerA

This view provides aggregated runtime statistics like total CPU, IO, and duration for each query.

Why this answer

A is correct because sys.query_store_query_stats aggregates runtime statistics per query across all plans and time intervals, making it the ideal view to identify queries with the highest total resource consumption (e.g., CPU, I/O, duration) over the collected week. Query Store captures historical execution data, and this view provides the cumulative metrics needed to pinpoint the most resource-intensive queries for performance bottleneck analysis.

Exam trap

The trap here is that candidates confuse sys.dm_exec_query_stats (a live, cache-dependent DMV) with the Query Store's historical views, assuming both provide the same aggregated data, but only Query Store views retain data across plan evictions and time intervals for long-term analysis.

How to eliminate wrong answers

Option B is wrong because sys.query_store_plan stores plan-level metadata (e.g., plan ID, compilation parameters) but does not contain aggregated runtime statistics like total CPU or duration, so it cannot show highest resource consumption. Option C is wrong because sys.dm_exec_query_stats is a dynamic management view that shows cached query execution statistics only for currently cached plans, not historical data over a week; it resets on plan eviction or service restart, making it unsuitable for long-term trend analysis. Option D is wrong because sys.dm_db_resource_stats provides per-minute resource usage metrics for the database (e.g., DTU, CPU, I/O) at the database level, not per-query, so it cannot identify specific queries with high resource consumption.

Practice this question →
16
Multi-Selectmedium

You are tuning a query in Azure SQL Database. Which TWO actions can reduce logical reads?

Select 2 answers
A.Add query hints to force index usage
B.Create a nonclustered index on the columns used in WHERE clause
C.Rewrite the query as a stored procedure
D.Increase the database max memory setting
E.Update statistics on the tables involved
AnswersB, E

Index can reduce the number of rows read.

Why this answer

Creating appropriate indexes, such as a nonclustered index on columns used in WHERE clauses, can reduce logical reads by enabling index seeks instead of full table scans. Updating statistics helps the query optimizer choose efficient execution plans, potentially reducing logical reads. Adding query hints may force an index but does not guarantee reduced reads and can be counterproductive.

Rewriting as a stored procedure does not directly reduce logical reads. Increasing database max memory does not reduce logical reads.

Practice this question →
17
Multi-Selecteasy

Which TWO metrics in Azure SQL Database indicate that the database might need to be scaled up?

Select 2 answers
A.Data IO percentage consistently below 20%
B.Session percent consistently below 10%
C.Log write percent consistently above 90%
D.Memory consumption consistently below 30%
E.DTU/CPU consumption consistently above 90%
AnswersC, E

High log write percent indicates the log rate is throttled.

Why this answer

High DTU/CPU consumption (E) and high log write percent (C) both indicate that the database is approaching or hitting resource limits, suggesting a need to scale up. Options A, B, and D are incorrect because consistently low metrics (data IO, session percent, memory) indicate underutilization, not a need to scale up.

Practice this question →
18
Multi-Selecthard

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

Select 3 answers
A.avg_io_stall_ms
B.avg_page_life_expectancy
C.log_bytes_used
D.avg_pending_disk_io
E.page_cache_hit_ratio
AnswersB, D, E

Lower page life expectancy indicates memory pressure.

Why this answer

B (avg_page_life_expectancy) is correct because it measures how long (in seconds) a data page stays in the buffer pool before being evicted. A low value (typically below 300 seconds) indicates that pages are being flushed quickly due to memory pressure, forcing more physical I/O. This is a direct indicator of insufficient memory for the buffer cache.

Exam trap

Candidates often mistakenly think that only avg_page_life_expectancy (PLE) and page_cache_hit_ratio indicate memory pressure. However, avg_pending_disk_io is also a key metric: high pending I/O can result from insufficient memory causing frequent buffer pool evictions and increased disk activity. The real trap is confusing avg_io_stall_ms (which measures disk latency) with memory pressure—avg_io_stall_ms is a storage performance metric, not a direct memory pressure indicator.

Practice this question →
19
MCQmedium

You manage an Azure SQL Database (General Purpose, S2) used by a reporting application. The database has a table `FactSales` with 500 million rows. Queries that aggregate sales by date are slow. The execution plan shows a clustered index scan on `FactSales`. The table has a clustered index on `SaleID` and a nonclustered index on `DateKey`. The queries filter by `DateKey` and `ProductKey`. You need to improve query performance without changing the service tier. Which action should you take?

A.Create a nonclustered index on ProductKey only.
B.Partition the table by DateKey.
C.Create a clustered columnstore index on the table.
D.Create a covering index on DateKey and ProductKey including the aggregated columns.
AnswerD

Covering index provides index seeks and avoids lookups.

Why this answer

The query filters by DateKey and ProductKey and aggregates sales data. A covering index on DateKey and ProductKey that includes the aggregated columns (e.g., SUM(SalesAmount)) allows the query to be satisfied entirely from the index without touching the clustered index, eliminating the costly clustered index scan. This is the most direct and effective way to improve performance without changing the service tier.

Exam trap

The trap here is that candidates may choose partitioning (Option B) thinking it speeds up all queries by date, but without a covering index, partitioning alone does not eliminate the scan; it only reduces the data scanned to a single partition.

How to eliminate wrong answers

Option A is wrong because creating a nonclustered index on ProductKey only does not address the DateKey filter and would still require key lookups or scans to retrieve the aggregated data, failing to cover the query. Option B is wrong because partitioning the table by DateKey can improve manageability and partition elimination for range scans, but it does not eliminate the need for a covering index; the query would still scan the entire partition(s) unless a suitable index exists. Option C is wrong because a clustered columnstore index is optimized for large-scale data warehousing and analytics workloads, but it is not supported on the General Purpose S2 tier (columnstore requires S3 or higher or Premium tiers), and it would disrupt the existing clustered index on SaleID, potentially harming other workloads.

Practice this question →
20
MCQhard

You are the database administrator for a hybrid environment with on-premises SQL Server 2022 and Azure SQL Database. All databases use the AdventureWorks schema. You notice that a critical stored procedure runs slower on Azure SQL Database than on-premises. Both have identical indexes and statistics. What is the most likely cause?

A.The version of the cardinality estimator differs between environments.
B.Azure SQL Database uses a different resource governance model (DTU vs vCore).
C.The index fragmentation level is higher on Azure SQL Database due to automatic tuning.
D.Azure SQL Database has intelligent query processing features that are not available on-premises.
AnswerA

On-prem SQL Server 2022 may use legacy CE, while Azure SQL Database uses default CE, leading to different plans.

Why this answer

The cardinality estimator (CE) version can differ between on-premises SQL Server and Azure SQL Database, even with identical indexes and statistics. The CE version is determined by the database compatibility level, and Azure SQL Database may have a different default compatibility level than on-prem, leading to different query plans and performance. Option B is incorrect because DTU vs vCore is a purchasing model that does not inherently slow queries; it affects resource limits but not plan choices.

Option C is incorrect because automatic tuning in Azure SQL Database typically helps performance, and index fragmentation levels would be similar with identical indexes. Option D is incorrect because intelligent query processing features are available on both platforms and generally beneficial, not detrimental.

Practice this question →
21
MCQhard

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

A.The query will continue with the poor plan until you manually force a plan.
B.You will receive a recommendation to revert the plan change.
C.The database will automatically revert to the last good plan and log the event in sys.dm_db_tuning_recommendations.
D.The database will automatically revert to the last good plan without notification.
AnswerC

Auto-revert occurs and is logged in the tuning recommendations DMV.

Why this answer

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

Exam trap

The trap here is that candidates may confuse FORCE_LAST_GOOD_PLAN with the default automatic tuning recommendation mode, which only suggests changes without automatic reversion, leading them to pick Option B or D.

How to eliminate wrong answers

Option A is wrong because FORCE_LAST_GOOD_PLAN enables automatic plan regression correction, so the poor plan does not persist until manual action. Option B is wrong because the database does not just send a recommendation; it automatically reverts the plan when FORCE_LAST_GOOD_PLAN is enabled. Option D is wrong because the database does log the event in sys.dm_db_tuning_recommendations, providing notification through that system view.

Practice this question →
22
MCQhard

Your company uses Azure SQL Database with the Hyperscale service tier. You notice that index maintenance operations are taking longer than expected. What is the most likely reason for this performance issue?

A.The database is using the General Purpose tier
B.The index is using page compression
C.The log write throughput is insufficient
D.The transaction log is set to simple recovery
AnswerC

Hyperscale index rebuilds generate high log throughput; insufficient log throughput can cause delays.

Why this answer

In Azure SQL Database Hyperscale, index rebuild operations generate a large volume of log records. Although Hyperscale uses a distributed architecture with fast log commit, the log write throughput per service objective is finite and can become a bottleneck during intensive write operations like index rebuilds. This is the most common cause of slow index maintenance.

Option A (General Purpose tier) is irrelevant as the question specifies Hyperscale. Option B (page compression) does not significantly impact index rebuild duration. Option D (simple recovery) is not the cause; even with simple recovery, the log write throughput during rebuild can be insufficient.

Practice this question →
23
MCQhard

You are a database administrator for a SaaS company. You manage an Azure SQL Database that uses the Hyperscale service tier. The database supports a multi-tenant application with unpredictable workloads. You notice that the database's page server is experiencing high IOPS utilization, causing query performance degradation. You need to improve performance by optimizing the page server usage. What should you do?

A.Increase the number of secondary replicas to distribute read workload.
B.Enable accelerated database recovery (ADR) to reduce log IO.
C.Change the service tier to Business Critical to get faster IO.
D.Increase the MAXDOP setting for the database.
AnswerA

Correct. Adding secondary replicas in Hyperscale allows read workloads to be offloaded. Each replica has its own buffer pool, so pages can be served from cache instead of repeatedly requesting them from the page server, reducing IOPS utilization.

Why this answer

In Azure SQL Database Hyperscale, increasing the number of secondary replicas can help distribute read workloads. Secondary replicas have their own buffer pool caches, which can reduce page server IOPS utilization by serving read requests from cache rather than repeatedly fetching pages from the page server. Option B is incorrect because Accelerated Database Recovery (ADR) reduces undo log space requirements but does not affect page server IOPS.

Option C is incorrect because changing to Business Critical tier moves away from the Hyperscale architecture and does not directly address page server performance. Option D is incorrect because increasing MAXDOP may increase parallelism and potentially lead to higher IOPS, not reduce it.

Practice this question →
24
MCQhard

You manage an Azure 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 for the instance. Which PowerShell cmdlet should you run?

A.Set-AzSqlInstanceDatabase with -AutoTuningProperties
B.Set-AzSqlDatabase with -AutoTuningProperties
C.Set-AzSqlInstance with -DtcEnabled
D.Set-AzSqlServer with -AdministratorLogin
AnswerA

This is the correct cmdlet and parameter to enable auto-tuning on a Managed Instance database.

Why this answer

The correct cmdlet is Set-AzSqlInstanceDatabase because Azure SQL Managed Instance uses instance-level databases, and automatic tuning properties are configured at the database level within the instance. The -AutoTuningProperties parameter directly enables or configures automatic tuning recommendations for the specified database in the managed instance.

Exam trap

The trap here is that candidates confuse Azure SQL Database cmdlets (Set-AzSqlDatabase) with Azure SQL Managed Instance cmdlets (Set-AzSqlInstanceDatabase), leading them to select the wrong cmdlet for the managed instance context.

How to eliminate wrong answers

Option B is wrong because Set-AzSqlDatabase is used for Azure SQL Database (single database or elastic pool), not for Azure SQL Managed Instance databases. Option C is wrong because Set-AzSqlInstance with -DtcEnabled configures the Distributed Transaction Coordinator (DTC) setting for the managed instance, not automatic tuning. Option D is wrong because Set-AzSqlServer with -AdministratorLogin changes the administrator login for the logical server, which is irrelevant to automatic tuning on a managed instance.

Practice this question →
25
Multi-Selectmedium

Which TWO configurations can help improve the performance of an Azure SQL Database experiencing high `WRITELOG` waits?

Select 2 answers
A.Enable Transparent Data Encryption (TDE).
B.Use in-memory OLTP to reduce log writes.
C.Increase the service tier to a higher performance level.
D.Enable Query Store.
E.Increase the frequency of database backups.
AnswersB, C

In-memory OLTP reduces log generation.

Why this answer

High WRITELOG waits indicate that the transaction log is a bottleneck, often due to excessive log I/O. In-memory OLTP reduces log writes by logging only the delta changes for memory-optimized tables, rather than full row versions, which directly alleviates log pressure. This makes option B correct.

Exam trap

The trap here is that candidates often confuse WRITELOG waits with general I/O bottlenecks and select backup frequency or TDE, not realizing that only reducing log write volume (via in-memory OLTP) or increasing log write speed (via higher service tier) directly resolves the wait type.

Practice this question →
26
MCQhard

Refer to the exhibit. You observe DTU usage reaching 100% at 11:00 AM. Which recommendation would best resolve the performance issue?

A.Schedule the recurring job to run at a different time, such as midnight.
B.Enable read scale-out to offload reporting queries.
C.Increase the service tier to S3 to provide more DTUs.
D.Create an index on the tables used by the job.
AnswerA

Moving the job to off-peak hours can reduce contention and prevent DTU exhaustion.

Why this answer

The exhibit shows a pattern of increasing DTU usage. The recurring job runs from 10:00 to 10:30, but the peak at 11:00 suggests another process causing full usage. The best solution is to schedule resource-intensive operations during off-peak hours.

Increasing the service tier may help but is not the most targeted approach.

Practice this question →
27
MCQhard

You have an Azure SQL Database with automatic tuning enabled. You notice that a query that previously ran quickly is now running slower. Automatic tuning has implemented a plan correction. However, the new plan is performing worse. What should you do to revert to the previous behavior?

A.Drop and recreate the index used by the query
B.Wait for automatic tuning to revert the plan automatically
C.Disable automatic tuning for the database
D.Revert the automatic tuning plan correction using the Azure portal or T-SQL
AnswerD

You can revert the plan to the previous one manually.

Why this answer

When automatic tuning implements a plan correction that performs worse, you can manually revert the plan correction using the Azure portal or T-SQL commands (e.g., `ALTER DATABASE ... SET AUTOMATIC_TUNING ...` or querying `sys.dm_db_tuning_recommendations` and using the `revert` action). Option A is incorrect because dropping and recreating the index does not revert the plan correction; it may force a new plan but is not the direct revert method.

Option B is incorrect because automatic tuning does not automatically revert a plan correction that is performing worse; it may later adjust, but manual intervention is the reliable way. Option C is incorrect because disabling automatic tuning would only prevent future corrections but not revert the current one; the plan would remain until manually reverted or statistics change.

Practice this question →
28
Matchingmedium

Match each Azure SQL Database backup type to its description.

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

Concepts
Matches

Complete copy of the database

Changes since the last full backup

All log records since the last log backup

Why these pairings

These are the three types of backups used for point-in-time restore in Azure SQL Database.

Practice this question →
29
MCQmedium

You are a database administrator for an e-commerce company that uses Azure SQL Managed Instance. You have a database that experiences high blocking due to long-running transactions. You need to configure a solution that automatically terminates the blocking process after it has been waiting for more than 5 seconds. The solution should be set at the database level and should not require any application changes. What should you do?

A.Enable read committed snapshot isolation (RCSI) on the database.
B.Create a resource governor workload group with a maximum grant time of 5 seconds.
C.Set the DEADLOCK_PRIORITY to LOW on the blocking session and set LOCK_TIMEOUT to 5000.
D.Set the transaction isolation level to SNAPSHOT for all sessions.
AnswerC

Setting DEADLOCK_PRIORITY to LOW and LOCK_TIMEOUT to 5000 does not terminate the blocking transaction; LOCK_TIMEOUT kills the waiting query. Also, DEADLOCK_PRIORITY is a session-level setting, requiring application changes, which violates the requirement.

Why this answer

LOCK_TIMEOUT can be used to automatically terminate a query that has been waiting for a lock longer than a specified number of milliseconds. Setting LOCK_TIMEOUT to 5000 will kill the waiting (blocked) session after 5 seconds, which mitigates blocking. While LOCK_TIMEOUT is a session-level setting, it can be applied database-wide by using a logon trigger or by setting server-level defaults without modifying application code.

Resource Governor does not have a MAX_GRANT_TIME setting for T-SQL queries, so option B is invalid. RCSI and snapshot isolation reduce blocking but do not terminate long-running transactions, and setting the isolation level for all sessions requires application changes or connection settings.

Exam trap

A common trap is to assume LOCK_TIMEOUT terminates the blocking transaction, but it actually kills the waiting query. The blocker continues to hold locks.

Practice this question →
30
MCQmedium

Your Azure SQL Database is experiencing high CPU usage. You suspect a specific query is causing the issue. You have enabled Query Store. How can you identify the query that has consumed the most cumulative CPU time over the last hour?

A.Use sys.dm_exec_query_stats to sort by total_worker_time.
B.Use the Query Store Top Resource Consuming Queries report and filter by the last hour.
C.Use sys.dm_exec_requests to find queries with high CPU.
D.Use the Query Store Regressed Queries report.
AnswerB

This report is designed for this purpose, with time filter and cumulative metrics.

Why this answer

The Query Store Top Resource Consuming Queries report allows you to filter by time interval and shows cumulative CPU time, making it easy to identify the query consuming the most CPU over the last hour. Option A is wrong because sys.dm_exec_query_stats shows cached query plans and statistics since the last plan compilation, not cumulative over a specific time window. Option C is wrong because sys.dm_exec_requests shows currently running queries, not historical cumulative CPU usage.

Option D is wrong because the Regressed Queries report shows queries whose performance has changed due to plan changes, not simply high CPU consumption.

Practice this question →
31
MCQmedium

You are monitoring an Azure SQL Managed Instance using dynamic management views. You want to identify which queries are consuming the most IOPS. Which DMV should you query?

A.sys.dm_db_io_stats
B.sys.dm_os_performance_counters
C.sys.dm_exec_query_stats
D.sys.dm_db_index_usage_stats
AnswerC

Provides per-query execution statistics including I/O metrics like total_logical_reads and total_logical_writes, which can be used to identify queries consuming the most IOPS.

Why this answer

Sys.dm_exec_query_stats provides per-query execution statistics, including I/O metrics such as total_logical_reads and total_logical_writes, which can be used to identify queries consuming the most IOPS. Option A is incorrect because sys.dm_db_io_stats is not a valid DMV in Azure SQL Managed Instance and does not provide per-query I/O information. Option B is incorrect because sys.dm_os_performance_counters provides OS-level performance counters, not per-query I/O.

Option D is incorrect because sys.dm_db_index_usage_stats tracks index usage statistics, not query I/O.

Practice this question →
32
Multi-Selecthard

You are responsible for performance tuning of an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has several tables with millions of rows. Users report that a report query that joins four tables is slow. You examine the query execution plan and notice that the database engine is using an Index Spool (Lazy Spool) operator. Which TWO actions should you take to improve query performance? (Choose two.)

Select 2 answers
A.Disable parallelism for the query using the MAXDOP 1 hint.
B.Increase the DTU or vCore count of the database.
C.Create appropriate indexes on the columns used in joins and filters.
D.Rewrite the query using table hints to force a specific join order.
E.Update statistics on all tables involved in the query.
AnswersC, E

Proper indexing can allow the optimizer to use index seeks instead of scanning and spooling.

Why this answer

An Index Spool (Lazy Spool) operator in an execution plan indicates that the query engine is creating a temporary index on the fly to support join or filter operations, which is a sign of missing or inadequate permanent indexes. Creating appropriate indexes on the columns used in joins and filters (Option C) eliminates the need for the spool, reducing I/O and improving performance. Updating statistics (Option E) ensures the query optimizer has accurate distribution information to generate efficient plans, which can also help avoid spool operations.

Exam trap

The trap here is that candidates often assume an Index Spool is always a performance booster (like a regular index seek) or that increasing hardware resources (Option B) is the quick fix, when in fact the spool is a costly workaround for missing permanent indexes and stale statistics.

Practice this question →
33
Multi-Selectmedium

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

Select 2 answers
A.Rewrite queries to use sargable predicates.
B.Upgrade to a higher service tier.
C.Enable row-level security on sensitive tables.
D.Create missing indexes identified by the Database Engine Tuning Advisor.
E.Update statistics on tables with outdated statistics.
AnswersD, E

Index creation can improve query performance without code changes.

Why this answer

Creating missing indexes (option D) and updating statistics (option E) are performance optimization techniques that do not require changes to application code. Indexes recommended by the Database Engine Tuning Advisor can reduce query execution time by enabling efficient data access paths, while updating statistics helps the query optimizer generate better execution plans. Options A and B require code changes or direct query modifications, option C is a security feature that may impact performance but not optimize it, and upgrading the service tier (option B) is a scaling action that increases resources but is not a targeted query optimization.

Practice this question →
34
MCQhard

Your company plans to migrate a large on-premises SQL Server database to Azure SQL Managed Instance. The database uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. How should you manage the TDE protector after migration to ensure minimal administrative overhead and high availability?

A.Use Azure Key Vault to store the TDE protector
B.Use the service-managed TDE key provided by Azure
C.Bring your own certificate and store it in the managed instance
D.Disable TDE after migration and use Always Encrypted instead
AnswerA

Azure Key Vault provides centralized, secure, and highly available key management for TDE.

Why this answer

Using Azure Key Vault to store the TDE protector provides centralized management, automatic key rotation, and high availability without manual intervention. Option B is incorrect because service-managed keys offer less control and are not recommended for scenarios requiring bring your own key (BYOK) compliance. Option C is incorrect because SQL Managed Instance does not support storing TDE certificates directly in the instance; instead, you can use Azure Key Vault to bring your own key.

Option D is incorrect because TDE cannot be disabled after migration without decrypting the database, and Always Encrypted is a different feature for column-level encryption, not a replacement for TDE.

Practice this question →
35
MCQeasy

You are monitoring an Azure SQL Database that is running a mission-critical workload. You notice that the DTU consumption is consistently above 90% during peak hours. You need to recommend a solution to reduce the DTU consumption. What should you recommend?

A.Scale up to a higher service tier or increase DTUs
B.Scale down to a lower service tier
C.Enable geo-replication
D.Enable read scale-out
AnswerA

Scaling up provides more resources, reducing DTU consumption percentage.

Why this answer

When DTU consumption consistently exceeds 90%, the database is resource-constrained, leading to performance degradation. Scaling up to a higher service tier or increasing DTUs directly provides more CPU, memory, and I/O resources, alleviating the bottleneck and reducing DTU utilization percentage. This is the standard corrective action for sustained high DTU usage in Azure SQL Database.

Exam trap

The trap here is that candidates may confuse high DTU consumption with a need for high availability or read scaling, but the correct response is to increase resource capacity via scaling up.

How to eliminate wrong answers

Option B is wrong because scaling down reduces available resources, which would worsen the high DTU consumption and likely cause performance failures. Option C is wrong because geo-replication provides disaster recovery and read-only replicas, but does not reduce DTU consumption on the primary database. Option D is wrong because read scale-out offloads read-only workloads to a replica, but the primary database's DTU consumption remains unchanged for write operations and other workloads.

Practice this question →
36
MCQeasy

You have an Azure SQL Managed Instance and notice that automatic tuning is not enabled. You want to automatically force a plan that performed better than the existing plan. What should you enable?

A.Automatic index management (DROP INDEX)
B.Automatic index management (CREATE INDEX)
C.Automatic plan correction (FORCE_LAST_GOOD_PLAN)
D.Intelligent Insights
AnswerC

Detects regressions and forces previous good plan.

Why this answer

Automatic plan correction (FORCE_LAST_GOOD_PLAN) is the correct feature to automatically force a plan that performed better than the existing plan. Option A (DROP INDEX) and Option B (CREATE INDEX) are for index management, not plan forcing. Option D (Intelligent Insights) provides analysis but does not automatically force plans.

Practice this question →
37
Multi-Selecthard

You are optimizing an Azure SQL Database that uses the Hyperscale service tier. You need to identify which three actions can improve write performance. Which THREE should you select?

Select 3 answers
A.Use smaller log writes
B.Increase the log rate limit
C.Increase the service tier to Business Critical
D.Enable read scale-out
E.Enable Accelerated Database Recovery
AnswersA, B, E

Smaller log writes reduce latency.

Why this answer

Using smaller log writes reduces log write latency, which improves write performance in Hyperscale. Option B is correct: Increasing the log rate limit allows higher log throughput, directly enhancing write performance. Option E is correct: Enabling Accelerated Database Recovery reduces log amplification and improves log write efficiency, benefiting write performance.

Option C is incorrect: The Hyperscale service tier is distinct from Business Critical; moving to Business Critical does not apply to Hyperscale. Option D is incorrect: Read scale-out improves read performance, not write performance.

Practice this question →
38
MCQmedium

You are a database administrator for a large e-commerce platform using Azure SQL Database. You notice that a specific query frequently causes high CPU usage during peak hours. The query is a SELECT with multiple JOINs and a WHERE clause on a non-clustered index. You have already updated statistics and rebuilt indexes. What should you do next to optimize performance?

A.Use Query Store to identify and force a better execution plan.
B.Enable automatic tuning to let Azure SQL Database handle the issue.
C.Add more indexes on the columns used in JOINs and WHERE clause.
D.Create a read replica and offload the query to it.
AnswerA

Query Store captures plan history and allows forced plan to stabilize performance.

Why this answer

Query Store can identify query performance regressions and provide plan forcing. Option B is wrong because automatic tuning might not address the specific query without Query Store data. Option C is wrong because index tuning may not help if the query plan is suboptimal.

Option D is wrong because read replicas offload read traffic but do not optimize CPU usage of a single query on the primary.

Practice this question →
39
MCQhard

You are optimizing a data warehouse workload on Azure SQL Database. The workload involves large batch inserts and nightly aggregations. You notice that the transaction log is growing excessively during the batch inserts, causing performance degradation. You need to reduce log growth without affecting data consistency. What should you do?

A.Change the database recovery model to Simple.
B.Use bulk insert operations with TABLOCK hint to enable minimal logging.
C.Create a partition function and scheme to spread the inserts.
D.Increase the maximum log size of the database.
AnswerB

Minimal logging reduces log space for large imports under full recovery model.

Why this answer

Using minimally logged operations (e.g., bulk insert with TABLOCK) reduces log space for large imports under the full recovery model, but requires specific conditions. Option A is wrong because simple recovery model is not supported in Azure SQL Database (only FULL, BULK_LOGGED is not available). Option C is wrong because partitioning does not reduce log growth.

Option D is wrong because increasing log size only accommodates growth, does not prevent it.

Practice this question →
40
MCQhard

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

A.Reduce storage to 512 GB.
B.Change tier to Hyperscale.
C.Enable serverless with auto-pause enabled.
D.Reduce capacity to 2 vCores.
AnswerC

Serverless auto-pause stops compute billing when idle.

Why this answer

Enabling serverless with auto-pause allows SalesDB to automatically pause during off-hours (6 PM to 8 AM) when not in use, significantly reducing compute costs for a database used only during business hours. Option D is incorrect because reducing vCores alone does not stop charges during idle time, while auto-pause eliminates compute costs entirely when paused. Option A is incorrect because storage reduction does not address the core issue of paying for idle compute.

Option B is incorrect because Hyperscale is designed for high-performance, large-scale workloads and is not cost-optimized for a schedule-based usage pattern.

Practice this question →
41
MCQmedium

You have an Azure SQL Database that is experiencing performance degradation. You suspect that parameter sniffing is causing suboptimal execution plans. What should you do to mitigate this issue without changing application code?

A.Add the OPTION (RECOMPILE) query hint to all queries.
B.Clear the plan cache using DBCC FREEPROCCACHE.
C.Enable the 'Optimize for Ad Hoc Workloads' setting.
D.Enable forced parameterization for the database.
AnswerD

Forces SQL Server to parameterize queries, reducing parameter sniffing impact.

Why this answer

Forced parameterization (option D) is the correct mitigation because it instructs the SQL Server query optimizer to treat all literal values in queries as parameters, which reduces the likelihood of parameter sniffing causing suboptimal plans for repeated queries. This is done at the database level without requiring any changes to application code, making it ideal for scenarios where code modification is not feasible.

Exam trap

The trap here is that candidates often confuse 'clearing the plan cache' (option B) as a permanent fix, when it is only a temporary workaround that does not address the root cause of parameter sniffing.

How to eliminate wrong answers

Option A is wrong because adding OPTION (RECOMPILE) to all queries would require modifying application code, which violates the constraint of not changing application code. Option B is wrong because clearing the plan cache with DBCC FREEPROCCACHE is a temporary fix that removes all cached plans but does not prevent parameter sniffing from reoccurring when queries are recompiled. Option C is wrong because enabling 'Optimize for Ad Hoc Workloads' only defers caching of single-use ad hoc query plans to reduce memory bloat; it does not address parameter sniffing or forced parameterization.

Practice this question →
42
Multi-Selectmedium

You are monitoring an Azure SQL Database that is experiencing high DTU consumption. You need to identify the queries that are causing high resource usage. Which two data sources can you use? (Choose two.)

Select 2 answers
A.sys.dm_os_wait_stats
B.Query Store
C.sys.dm_exec_query_stats
D.sys.dm_db_index_usage_stats
E.sys.dm_io_virtual_file_stats
AnswersB, C

Tracks query performance metrics.

Why this answer

Query Store (Option B) captures a history of query execution plans and runtime statistics, allowing you to identify queries with high CPU, I/O, or duration. sys.dm_exec_query_stats (Option C) returns aggregate performance statistics for cached query plans, including total CPU time and logical reads, which directly points to resource-intensive queries. Both are valid sources for diagnosing high DTU consumption in Azure SQL Database.

Exam trap

The trap here is that candidates often confuse wait statistics (sys.dm_os_wait_stats) with query-level performance data, but wait stats show system-wide bottlenecks, not the specific queries causing high DTU.

Practice this question →
43
MCQeasy

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

A.Enable Stretch Database to transparently stretch old data to Azure Blob Storage.
B.Implement table partitioning and move old partitions to a separate database with a lower service tier.
C.Use Elastic Query to query historical data stored in Azure Blob Storage.
D.Migrate to the Hyperscale service tier to take advantage of tiered storage.
AnswerB

Allows archiving old data in a cheaper database while keeping it queryable.

Why this answer

Implementing table partitioning allows you to efficiently manage large tables by splitting them into smaller, more manageable partitions. You can then move older partitions to a separate database with a lower service tier (e.g., from General Purpose to Basic), which reduces storage costs while keeping the archived data queryable when needed. Stretch Database (option A) is deprecated and no longer recommended.

Elastic Query (option C) is used to query remote databases, not to archive data. Hyperscale (option D) is designed for high scalability and performance, not for cost reduction on infrequently accessed data.

Practice this question →
44
Multi-Selecteasy

You have an Azure SQL Database that uses automatic tuning. Which TWO benefits does automatic tuning provide?

Select 2 answers
A.Automatically scale up the database service tier
B.Automatically identify and correct query plan regressions
C.Automatically create read replicas
D.Automatically update statistics
E.Automatically create missing indexes
AnswersB, E

Plan correction is a key feature.

Why this answer

Options B and E are correct. Automatic tuning can identify and correct query plan regressions (B) and automatically create missing indexes (E). Option A is wrong because automatic tuning does not automatically scale the service tier; that's auto-scale.

Option C is wrong because automatic tuning does not automatically create read replicas. Option D is wrong because automatic tuning does not automatically update statistics.

Exam trap

Automatic tuning in Azure SQL Database includes only index creation and plan regression correction. It does not include auto-scaling, read replicas, or statistics updates.

Practice this question →
45
MCQhard

You are the database administrator for a large e-commerce company. The production Azure SQL Database (Business Critical, 16 vCores) hosts the order processing system. Recently, users report that order submissions are slow during peak hours. You examine the wait statistics and find that `LCK_M_IX` waits are the top wait type. You also notice that the stored procedure `usp_PlaceOrder` performs an `UPDATE` on the `Orders` table, and there is a high volume of concurrent transactions. The table has a clustered index on `OrderID` and a nonclustered index on `CustomerID`. The procedure uses serializable isolation level. Which action will most effectively reduce the blocking?

A.Increase the vCore count to 24.
B.Convert the Orders table to a memory-optimized table.
C.Add a nonclustered index on the Status column of the Orders table.
D.Change the database to use read committed snapshot isolation (RCSI) and modify the procedure to use read committed.
AnswerD

RCSI uses row versioning to avoid locks.

Why this answer

The primary issue is blocking caused by `LCK_M_IX` waits under the serializable isolation level, which holds range locks and prevents concurrent updates. Changing to read committed snapshot isolation (RCSI) and using read committed eliminates these locks by providing statement-level row versioning, allowing concurrent transactions to read without blocking writers. This directly addresses the high volume of concurrent `UPDATE` operations without requiring schema or hardware changes.

Exam trap

The trap here is that candidates often assume scaling up hardware (Option A) or adding indexes (Option C) will fix blocking, when the root cause is the isolation level's locking behavior, which requires a concurrency model change like RCSI.

How to eliminate wrong answers

Option A is wrong because increasing vCores does not resolve logical blocking caused by lock contention; it only improves throughput for CPU-bound workloads, not concurrency issues. Option B is wrong because converting to a memory-optimized table would require significant application changes and does not directly address the isolation-level-induced blocking; memory-optimized tables use optimistic concurrency but still need isolation level adjustments. Option C is wrong because adding an index on the Status column does not reduce blocking from serializable isolation; it may help query performance but does not change lock behavior or contention on the Orders table.

Practice this question →
46
MCQhard

You are troubleshooting a performance issue on an Azure SQL Database. Query Store shows a significant increase in query duration for a specific query. The execution plan has changed from a hash join to a nested loops join. What is the most likely cause?

A.Parameter sniffing caused the optimizer to choose a plan for atypical parameter values
B.An index was dropped, forcing a table scan
C.Forced parameterization was enabled
D.Stale statistics caused the optimizer to underestimate cardinality
AnswerD

Leads to nested loops instead of hash join due to underestimation.

Why this answer

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

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

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

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

Practice this question →
47
Multi-Selectmedium

You are configuring automatic tuning for an Azure SQL Database. Which THREE recommendations can be applied automatically without manual approval?

Select 3 answers
A.FORCE LAST GOOD PLAN
B.Modify statistics
C.CREATE INDEX
D.DROP INDEX
E.Enable database compression
AnswersA, C, D

This is a built-in automatic tuning option that can be applied automatically.

Why this answer

Options A, C, and D are correct. Automatic tuning for Azure SQL Database can automatically apply FORCE LAST GOOD PLAN, CREATE INDEX, and DROP INDEX recommendations. Option B is incorrect because modifying statistics is not an automatic tuning option; it requires manual execution.

Option E is incorrect because enabling database compression is not part of automatic tuning.

Practice this question →
48
Drag & Dropmedium

Drag and drop the steps to configure an Azure SQL Managed Instance link for disaster recovery 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

The link requires a secondary instance first, then establishing the link, configuring as readable, monitoring, and failing over when needed.

Practice this question →
49
Multi-Selectmedium

You manage an Azure SQL Managed Instance. You need to monitor storage space usage. Which TWO dynamic management views can you use?

Select 2 answers
A.sys.dm_db_partition_stats
B.sys.dm_exec_query_stats
C.sys.dm_db_file_space_usage
D.sys.dm_db_log_space_usage
E.sys.dm_os_performance_counters
AnswersC, D

Shows data file space usage.

Why this answer

Options C and D are correct. sys.dm_db_file_space_usage provides data file space usage, and sys.dm_db_log_space_usage provides transaction log space usage. Option A is incorrect because sys.dm_db_partition_stats shows row counts and partition-level information, not storage space. Option B is incorrect because sys.dm_exec_query_stats is for query performance metrics.

Option E is incorrect because sys.dm_os_performance_counters includes various performance counters but does not directly show per-database space usage.

Practice this question →
50
MCQmedium

Your Azure SQL Managed Instance is configured with a long-term backup retention policy of 10 years. You need to reduce storage costs while still meeting a compliance requirement to retain monthly backups for 7 years. What should you do?

A.Configure a new long-term retention policy that retains monthly backups for 7 years.
B.Disable long-term backup retention and rely solely on point-in-time restore backups.
C.Set the point-in-time restore retention period to 7 years.
D.Migrate the database to Azure SQL Database and use geo-redundant storage.
AnswerA

Correct. A new LTR policy with monthly backups for 7 years meets compliance and reduces costs by not retaining weekly/daily backups for the full 10 years.

Why this answer

Configuring a new long-term retention (LTR) policy that retains monthly backups for 7 years meets the compliance requirement while reducing storage costs compared to keeping weekly/daily backups for 10 years. LTR allows you to specify retention periods for weekly, monthly, and yearly backups; setting monthly backups to 7 years satisfies the requirement without extra cost. Option B is incorrect because disabling LTR entirely would lose the compliance capability.

Option C is incorrect because point-in-time restore (PITR) retention is limited to 35 days, not 7 years. Option D is incorrect because migrating to Azure SQL Database does not inherently reduce costs and may introduce other issues.

Practice this question →
51
Multi-Selecteasy

You are troubleshooting a performance issue on an Azure SQL Database. Which THREE Dynamic Management Views (DMVs) should you query to identify the most resource-intensive queries?

Select 3 answers
A.sys.dm_exec_requests
B.sys.dm_os_wait_stats
C.sys.dm_exec_query_plan
D.sys.dm_exec_sessions
E.sys.dm_exec_query_stats
AnswersA, C, E

This DMV shows currently executing requests and their resource consumption.

Why this answer

The correct answers are A, C, and E. Option A (sys.dm_exec_requests) shows currently executing requests, which helps identify active resource-intensive queries. Option C (sys.dm_exec_query_plan) provides the execution plan for cached queries, enabling analysis of query performance.

Option E (sys.dm_exec_query_stats) returns aggregated performance statistics (e.g., CPU time, logical reads) for cached query plans, making it ideal for identifying resource-intensive queries across all executions. Option B (sys.dm_os_wait_stats) is incorrect because it shows instance-level wait statistics, not query-specific resource usage. Option D (sys.dm_exec_sessions) is incorrect as it provides session-level information, not query-level resource metrics.

Practice this question →
52
Multi-Selectmedium

Which TWO metrics from sys.dm_db_resource_stats should you monitor to identify a disk IO bottleneck in an Azure SQL Database?

Select 2 answers
A.avg_cpu_percent
B.max_size_percent
C.avg_data_io_percent
D.avg_memory_usage_percent
E.avg_log_write_percent
AnswersC, E

Data IO utilization

Why this answer

Options C and E are correct. avg_data_io_percent measures data IO usage relative to the limit, and avg_log_write_percent measures log IO usage—both indicate disk IO bottlenecks. Option A (avg_cpu_percent) is CPU usage. Option B (max_size_percent) is storage space.

Option D (avg_memory_usage_percent) is memory.

Practice this question →
53
MCQmedium

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

A.Query Store with retention set to 90 days.
B.DMVs queried by a custom script.
C.Azure SQL Analytics (now integrated in Azure Monitor) and configure alerts.
D.Intelligent Insights and configure email notifications.
AnswerC

Correct because Azure SQL Analytics (now part of Azure Monitor) provides long-term retention and alerting capabilities.

Why this answer

Azure SQL Analytics (now part of Azure Monitor) provides long-term retention and alerting capabilities. Option A is wrong because Query Store default retention is limited. Option B is wrong because DMVs only show current state and are not designed for long-term retention and alerting.

Option D is wrong because Intelligent Insights provides analysis but not configurable retention.

Practice this question →
54
Multi-Selecthard

Your Azure SQL Database is experiencing high CPU utilization due to frequent recompilations of a stored procedure. Which TWO actions should you take to reduce recompilations?

Select 2 answers
A.Execute sp_refreshsqlmodule on the stored procedure
B.Use the KEEPFIXED PLAN query hint
C.Use the OPTIMIZE FOR UNKNOWN query hint
D.Use the RECOMPILE query hint
E.Ensure parameter data types match the column data types
AnswersB, E

KEEPFIXED PLAN prevents recompilation due to statistics changes, reducing recompilations.

Why this answer

Options B and E are correct. Option B: Using the KEEPFIXED PLAN query hint prevents recompilations caused by statistics changes, ensuring the query plan remains stable. Option E: Ensuring parameter data types match the column data types avoids type mismatches that trigger recompilations.

Option A (sp_refreshsqlmodule) updates metadata but doesn't reduce recompilations. Option C (OPTIMIZE FOR UNKNOWN) can lead to plan instability without reducing recompilations. Option D (RECOMPILE) forces recompilation on each execution, increasing recompilations.

Practice this question →
55
MCQeasy

You are monitoring an Azure SQL Database using sys.dm_db_wait_stats. You see a high percentage of WRITELOG waits. What is the most likely cause?

A.Tempdb has allocation contention.
B.The transaction log is on a slow I/O subsystem.
C.Queries are blocked by locks.
D.CPU is under pressure.
AnswerB

WRITELOG waits directly relate to log write performance.

Why this answer

A high percentage of WRITELOG waits indicates that transaction log writes are slow, often due to I/O latency on the log drive. Therefore, the most likely cause is a slow I/O subsystem for the transaction log (Option B). Option A is incorrect because tempdb allocation contention manifests as PAGELATCH waits.

Option C is incorrect because blocking is represented by LCK_M_* waits. Option D is incorrect because CPU pressure shows SOS_SCHEDULER_YIELD waits.

Practice this question →
56
MCQeasy

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You want to identify the top queries by total CPU time over the last hour. Which DMV should you query?

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

Returns aggregate performance statistics for cached query plans.

Why this answer

Sys.dm_exec_query_stats. This DMV returns aggregated performance statistics for cached query plans, including total_worker_time (which represents total CPU time). By filtering on last_execution_time, you can identify queries executed within the last hour. sys.dm_exec_requests (B) shows only currently executing requests, not historical data. sys.dm_db_index_usage_stats (C) tracks index seek/scan operations, not CPU usage. sys.dm_db_resource_stats (D) provides resource consumption at the database level (e.g., DTU, CPU percentage), not per query.

Practice this question →
57
MCQhard

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

A.The server name in the template does not match the actual server
B.The storage container URI is incorrectly formatted
C.The SAS token has expired or is invalid
D.The database is not in the same region as the storage account
AnswerC

Expired or invalid SAS token is a common cause of access errors.

Why this answer

The error indicates that the storage account is not accessible. In the template, a SAS token is used to authenticate access to the storage container for long-term backup retention. The most common reason for this error is that the SAS token has expired or is invalid, as SAS tokens have an expiration date.

Therefore, Option C is correct. Option A is incorrect because the server name mismatch would cause a different error. Option B is incorrect because the URI format may be valid, but the token is the issue.

Option D is incorrect because cross-region storage is supported for long-term retention.

Practice this question →
58
MCQhard

You are migrating an on-premises SQL Server database to Azure SQL Database. The database uses Service Broker for asynchronous messaging. After migration, you notice that performance is degraded. What should you do to optimize?

A.Enable Service Broker in Azure SQL Database by setting ENABLE_BROKER.
B.Scale up the database to a higher service tier to improve Service Broker performance.
C.Replace Service Broker with Azure Queue Storage or Event Grid.
D.Configure Service Broker to use external activation via Azure Functions.
AnswerC

Azure SQL Database does not support Service Broker; use PaaS messaging services.

Why this answer

Service Broker is not fully supported in Azure SQL Database; it has performance and reliability limitations. The recommended approach for asynchronous messaging is to use Azure-native services like Queue Storage or Event Grid. Therefore, the correct action is to replace Service Broker (Option C).

Option A is incorrect because while ENABLE_BROKER can be used, it is not recommended for production due to poor performance. Option B is incorrect because scaling up does not resolve the fundamental feature incompatibility. Option D is incorrect because external activation via Azure Functions does not address the root cause and adds complexity.

Practice this question →
59
MCQhard

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

A.Purchase reserved capacity for the pool to reduce costs.
B.Use Query Store to identify and kill the high-usage queries.
C.Move 'OrdersDB' to a higher service tier, such as a standalone database with a higher DTU limit.
D.Increase the eDTU limit of the elastic pool to accommodate the high usage.
AnswerC

Isolating the database ensures other databases are not affected, and you can choose appropriate resources for 'OrdersDB'.

Why this answer

Moving 'OrdersDB' to a standalone database with a higher DTU limit isolates its resource usage, preventing it from affecting other databases in the elastic pool. This approach minimizes administrative overhead as it's a straightforward configuration change. Option A is incorrect because purchasing reserved capacity is a billing benefit, not a resource isolation mechanism.

Option B is incorrect because Query Store can identify high-usage queries but cannot limit resource consumption. Option D is incorrect because increasing the pool's eDTU limit would increase costs, and the high-usage database could still consume resources and affect others within the pool.

Practice this question →
60
MCQmedium

You are reviewing the configuration of an Azure SQL Database. The database is currently 250 GB in size. You are concerned about storage capacity. What is the maximum size limit for this database?

A.1 TB
B.500 GB
C.250 GB
D.4 TB
AnswerC

The maxSizeBytes value is 250 GB.

Why this answer

The database is currently 250 GB in size, which matches its maximum size limit. Therefore, the maximum size limit is 250 GB (Option C). Option A (1 TB) is incorrect because the limit is not that high.

Option B (500 GB) is incorrect as it exceeds the actual limit. Option D (4 TB) is also incorrect for the same reason.

Practice this question →
61
MCQmedium

You are managing an Azure SQL Database that runs a critical line-of-business application. Users report that a specific query is running slower than usual. You identify that the query is performing a clustered index scan on a large table with over 10 million rows. The table has a clustered index on an identity column and a nonclustered index on a frequently filtered column. You need to minimize the query execution time without adding additional indexes. What should you do?

A.Increase the service tier of the Azure SQL Database to provide more resources.
B.Update all statistics on the table.
C.Rebuild the clustered index to reduce fragmentation.
D.Update the statistics on the nonclustered index only.
AnswerB

Updating statistics helps the query optimizer generate a more accurate cardinality estimate, which may lead to an index seek instead of a scan.

Why this answer

The query is performing a clustered index scan, which means SQL Server is reading all rows in the table. Outdated statistics can cause the optimizer to choose a scan instead of a more efficient seek. Updating all statistics on the table (option B) provides the optimizer with fresh distribution information, potentially allowing it to choose a better execution plan that avoids the scan, thereby reducing query execution time without adding indexes.

Exam trap

The trap here is that candidates often assume a scan is always due to fragmentation (option C) or resource constraints (option A), when in fact the most common cause is stale statistics leading to a poor execution plan choice.

How to eliminate wrong answers

Option A is wrong because increasing the service tier provides more resources (CPU, IO, memory) but does not address the root cause of a suboptimal execution plan; the query may still perform a scan, just faster, and this incurs additional cost. Option C is wrong because rebuilding the clustered index reduces fragmentation, but fragmentation is unlikely to cause a scan to be chosen over a seek; the issue is plan choice, not physical index structure. Option D is wrong because updating only the nonclustered index statistics does not help if the optimizer is considering the clustered index scan; the statistics on the clustered index (or the entire table) must be updated to influence the plan choice for that scan.

Practice this question →
62
MCQhard

You manage an Azure SQL Database that is part of an Always On availability group in Azure SQL Managed Instance. You notice that the secondary replica is experiencing high log send queue size. The primary replica has ample CPU and I/O resources. The network latency between replicas is low. What is the most likely cause of the high log send queue?

A.The secondary replica is configured with asynchronous commit.
B.The primary replica is experiencing log flush delays.
C.The secondary replica's log apply rate is slower than the log generation rate on the primary.
D.The network bandwidth between replicas is insufficient.
AnswerC

A slow secondary can cause backlog, even with fast network.

Why this answer

The most likely cause of high log send queue is that the secondary replica's log apply rate is slower than the log generation rate on the primary (Option C). This typically occurs when the secondary replica is under-provisioned or has high I/O contention, causing it to fall behind in applying received log records. Option A (asynchronous commit) is incorrect because asynchronous commit can actually reduce log send queue pressure by not waiting for the secondary, but it does not directly cause queue buildup.

Option B (log flush delays on primary) is unlikely because the primary has ample CPU and I/O resources. Option D (insufficient network bandwidth) is unlikely given that network latency is low and the primary resources are ample.

Practice this question →
63
MCQhard

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

A.Update statistics for the Orders table.
B.Create a columnstore index on the Orders table.
C.Re-create the partition function with a different boundary type (e.g., RIGHT instead of LEFT) to align with query patterns.
D.Create an indexed view that pre-aggregates sales by month.
AnswerC

Changing boundary type can improve partition elimination for date range queries.

Why this answer

The issue is that partition elimination is not occurring, meaning the query scans all partitions instead of only the relevant ones for the current year. Since the table is partitioned by OrderDate using a monthly partition function, the alignment of partition boundaries with the query predicate is critical. If the current partition function uses LEFT boundaries, the boundary values represent the start of each partition, but queries for the current year might include dates that cross boundaries in a way that prevents elimination.

Re-creating the partition function with RIGHT boundaries ensures that each boundary value is the first day of the next partition, which often aligns better with range queries like 'WHERE OrderDate >= '2025-01-01' AND OrderDate < '2025-12-31''. This can enable partition elimination without changing application code. Option A (update statistics) does not affect partition elimination.

Option B (columnstore index) improves compression and query speed but does not force partition elimination. Option D (indexed view) requires code changes and does not directly cause partition elimination. Therefore, option C is correct.

Practice this question →
64
MCQmedium

You manage a SQL Server on Azure VM. Users report slow queries during peak hours. You suspect memory pressure. Which metric should you monitor in Azure Monitor to confirm this?

A.Page Life Expectancy
B.Batch Requests/sec
C.Buffer Cache Hit Ratio
D.Target Server Memory (KB)
AnswerD

Target Server Memory (KB) shows the amount of memory SQL Server is trying to acquire; consistently high values relative to Total Server Memory indicate memory pressure.

Why this answer

Target Server Memory (KB) shows the amount of memory SQL Server is trying to acquire; consistently high values relative to Total Server Memory indicate memory pressure. Option A is wrong because Page Life Expectancy is affected by memory but is a secondary indicator. Option B is wrong because Batch Requests/sec measures throughput, not memory.

Option C is wrong because Buffer Cache Hit Ratio indicates cache efficiency but not directly memory pressure.

Practice this question →
65
MCQhard

Your Azure SQL Database is configured with Active Geo-Replication to a secondary region for disaster recovery. During a routine failover drill, you notice that after failover, the application cannot connect to the new primary because the login credentials fail. The logins are contained in the master database. What is the most likely cause?

A.The DNS name of the secondary server changed after failover.
B.The SQL logins in the master database are not replicated to the secondary server.
C.The firewall rules on the secondary server do not allow connections from the application IP.
D.The application uses contained database users, which are not replicated.
AnswerB

Active Geo-Replication replicates only user databases, not master database logins.

Why this answer

When Active Geo-Replication is configured for Azure SQL Database, the secondary server is a separate logical server in a different region. The master database, which contains server-level logins, is not replicated as part of geo-replication; only the user databases are replicated. Therefore, after a failover, the new primary server does not have the server-level logins from the original primary, causing authentication failures for applications using those logins.

Exam trap

The trap here is that candidates often assume all server-level configurations, including logins, are automatically replicated with geo-replication, but in reality, only user databases are replicated, not the master database.

How to eliminate wrong answers

Option A is wrong because the DNS name of the secondary server does not change after failover; the failover process updates the geo-replication listener or the application connection string must point to the secondary server's DNS name, but the DNS name itself remains static. Option C is wrong because firewall rules are replicated as part of the server-level configuration when using geo-replication, and the question specifically states the issue is login credentials failing, not network connectivity. Option D is wrong because contained database users are stored within the user database itself and are automatically replicated with geo-replication, so they would not cause a login failure after failover.

Practice this question →
66
MCQhard

You are a database administrator for a large financial services company. You manage an Azure SQL Database in the Business Critical tier with a failover group configured for disaster recovery. The database has a heavy OLTP workload. You notice that the secondary replica is experiencing high log write latency, impacting the primary's performance due to synchronous commit. You need to minimize the performance impact on the primary while maintaining disaster recovery capabilities. What should you do?

A.Change the backup storage redundancy of the secondary replica to locally-redundant storage (LRS).
B.Add an additional secondary replica to distribute the log write load.
C.Change the failover group to use asynchronous commit mode.
D.Decrease the service tier of the secondary replica to General Purpose.
AnswerC

Changing to asynchronous commit mode allows the primary to commit without waiting for the secondary to harden the log. This directly reduces performance impact on the primary while still maintaining disaster recovery capabilities, albeit with possible data loss.

Why this answer

Changing the failover group to asynchronous commit mode decouples the primary's transaction commit from the secondary's log write. In synchronous mode, the primary waits for the secondary to confirm log hardening, which causes performance impact when the secondary has high log write latency. Asynchronous commit allows the primary to commit without waiting, thus minimizing performance impact while still maintaining disaster recovery capabilities (the secondary will apply changes eventually, though with potential data loss if a failover occurs before sync).

Option A is incorrect because backup storage redundancy (LRS vs GRS) only affects backup storage, not live log write latency for replication. Option B is incorrect because adding another secondary does not reduce latency on the existing secondary; it could even increase overhead. Option D is incorrect because decreasing the secondary's service tier to General Purpose would reduce its I/O capacity and likely worsen log write latency.

Practice this question →
67
MCQmedium

A production Azure SQL Database is experiencing high CPU usage during peak hours. The database uses the S3 service tier. You need to reduce CPU usage without changing the service tier. Which action should you take?

A.Increase the maximum number of concurrent workers.
B.Identify and create missing indexes.
C.Reduce MAXDOP to 1.
D.Increase MAXDOP to 8.
AnswerB

Missing indexes cause table scans, increasing CPU usage; adding indexes reduces CPU.

Why this answer

High CPU usage in an S3 Azure SQL Database often stems from inefficient query plans caused by missing indexes. Creating appropriate indexes reduces the number of rows scanned and the CPU cycles needed for operations like key lookups and sorting, directly lowering CPU consumption without changing the service tier.

Exam trap

The trap here is that candidates often assume reducing MAXDOP or increasing workers will fix CPU issues, but without addressing the root cause (poor query plans from missing indexes), these changes either exacerbate resource contention or fail to reduce CPU usage.

How to eliminate wrong answers

Option A is wrong because increasing the maximum number of concurrent workers (MAX_WORKERS) would allow more parallel queries to run, likely increasing CPU contention and worsening the problem. Option C is wrong because reducing MAXDOP to 1 forces all queries to run serially, which can increase CPU time per query due to lack of parallelism and may degrade performance for complex queries. Option D is wrong because increasing MAXDOP to 8 on an S3 tier (which has limited resources) can lead to excessive parallelism, causing CPU thrashing and inefficient resource utilization.

Practice this question →
68
Multi-Selecthard

You are monitoring an Azure SQL Database and notice that the 'tempdb' database is experiencing contention. Which THREE actions can reduce tempdb contention? (Choose three.)

Select 3 answers
A.Add multiple tempdb data files
B.Optimize queries that use temporary tables and table variables
C.Increase the size of tempdb
D.Change the collation of tempdb
E.Use multiple tempdb files equal to the number of CPU cores
AnswersA, B, E

Multiple files reduce allocation contention.

Why this answer

Adding multiple tempdb data files reduces allocation contention on system pages like PFS (Page Free Space), GAM (Global Allocation Map), and SGAM (Shared Global Allocation Map). When multiple sessions concurrently allocate pages in tempdb, a single data file becomes a bottleneck; additional files spread the allocation load across separate file system objects, reducing latch contention.

Exam trap

The trap here is that candidates often confuse 'increasing size' (Option C) with 'adding files' (Option A), mistakenly thinking that more space alone resolves contention, when in fact only multiple files reduce allocation page contention.

Practice this question →
69
MCQeasy

You are a database administrator for a large financial services company. You need to ensure that all queries that read sensitive customer data use an optimized execution plan. What feature should you enable to automatically identify and fix regressed query plans?

A.Query Store
B.Automatic Tuning
C.Intelligent Insights
D.Database Advisor for SQL Database
AnswerB

Automatic Tuning continuously monitors and automatically identifies and fixes regressed query plans.

Why this answer

Automatic Tuning can automatically identify and fix regressed query plans. Option A is wrong because Query Store captures performance data but does not automatically fix plans. Option C is wrong because Intelligent Insights provides monitoring, not automatic fixing.

Option D is wrong because Database Advisor for SQL Database gives recommendations but requires manual action.

Practice this question →
70
MCQeasy

You need to monitor the performance of a set of Azure SQL Databases from a single dashboard. Which Azure service should you use?

A.Azure Monitor Workbooks
B.Query Performance Insight
C.Azure SQL Analytics
D.Azure Data Studio
AnswerC

Azure SQL Analytics provides a unified monitoring solution for multiple Azure SQL databases.

Why this answer

Azure SQL Analytics provides a unified monitoring solution for multiple Azure SQL databases. Option C is correct. Option A (Azure Monitor Workbooks) is wrong because although it can be used, it requires manual configuration.

Option B (Query Performance Insight) is wrong because it is per-database. Option D (Azure Data Studio) is wrong because it is a client tool.

Practice this question →
71
MCQmedium

A company runs a critical SQL Server database on Azure Virtual Machines. The database experiences intermittent performance degradation during peak hours. The database administrator notices high I/O latency on the data drive. The current disk configuration uses Standard HDDs. What is the most cost-effective action to reduce I/O latency without changing the virtual machine size?

A.Enable instant file initialization in SQL Server.
B.Increase the virtual machine size to a series with higher I/O limits.
C.Migrate the data files to Premium SSD managed disks.
D.Migrate the data files to Standard SSD managed disks.
AnswerC

Premium SSD provides lower latency and is cost-effective without changing VM size.

Why this answer

Migrating the data files to Premium SSD managed disks is the most cost-effective action to reduce I/O latency without changing the virtual machine size. Premium SSDs provide consistent low-latency performance and higher IOPS/throughput compared to Standard HDDs, directly addressing the high I/O latency during peak hours. This change does not require resizing the VM, keeping compute costs unchanged while improving storage performance.

Exam trap

The trap here is that candidates may choose Standard SSD (Option D) thinking it is a sufficient upgrade, but they overlook that Premium SSD is required to achieve the low latency needed for critical databases, and that Standard SSD still uses HDD-like latency profiles under sustained load.

How to eliminate wrong answers

Option A is wrong because enabling instant file initialization only speeds up data file growth operations by skipping zeroing, but it does not reduce I/O latency for ongoing read/write operations during peak hours. Option B is wrong because increasing the virtual machine size to a series with higher I/O limits would increase compute costs and is explicitly excluded by the requirement to not change the VM size. Option D is wrong because Standard SSD managed disks offer better performance than Standard HDDs but still have higher latency and lower IOPS than Premium SSDs, making them insufficient to resolve high I/O latency in a critical database scenario.

Practice this question →
72
MCQeasy

You are responsible for a set of Azure SQL Databases that are used by different departments in your organization. The databases are deployed in an elastic pool with Standard tier (eDTU 200). Usage patterns show that the marketing database uses high CPU during the day, while the sales database uses high IO at night. You want to optimize costs while ensuring each database gets the resources it needs. What should you do?

A.Configure minimum and maximum eDTU per database in the pool
B.Migrate the pool to a vCore-based elastic pool
C.Add more databases to the pool to spread the load
D.Move each database to a standalone DTU tier
AnswerA

Guarantees resources per database while sharing.

Why this answer

Configuring minimum and maximum eDTU per database within the elastic pool guarantees that each database gets the resources it needs during peak usage (e.g., high CPU for marketing during the day, high IO for sales at night) while still sharing the pool's overall eDTU, optimizing costs. Option B is incorrect because moving to a vCore-based pool does not inherently address resource contention; it's a different purchasing model. Option C is incorrect because adding more databases to the pool increases contention and may degrade performance.

Option D is incorrect because moving each database to a standalone DTU tier would likely increase costs and eliminate the benefits of pooling.

Practice this question →
73
MCQhard

Your Azure SQL Database is configured with the Hyperscale service tier. You observe that log write latency is consistently high, affecting transaction throughput. What is the most likely cause and the recommended mitigation?

A.The log IOPS is limited by the disk performance; increase the provisioned IOPS.
B.The compute replica is undersized; scale up the compute to increase log throughput.
C.High log generation rate is causing log rate governance throttling; reduce the log generation rate by batching transactions.
D.The log write latency is due to network congestion; move the database to a different region.
AnswerC

Log rate governance throttles to protect secondary replicas; reducing log generation mitigates.

Why this answer

In Hyperscale, log rate is governed to protect secondary replicas; high latency indicates throttling. Option A is wrong because Hyperscale uses local SSD for log, not PIOPS. Option B is wrong because log rate governance affects all log writes.

Option D is wrong because scaling up compute doesn't increase log throughput limits.

Practice this question →
74
MCQeasy

You need to configure a long-term retention policy for backups of an Azure SQL Database that must retain weekly full backups for 5 years and monthly full backups for 10 years. Which backup retention feature should you use?

A.Geo-restore feature
B.Long-Term Retention (LTR) policy
C.Automated backups retention period
D.Point-In-Time Restore (PITR) retention
AnswerB

LTR allows you to retain backups for up to 10 years with configurable weekly, monthly, yearly schedules.

Why this answer

Long-Term Retention (LTR) policy allows you to retain backups beyond the default retention period, with weekly, monthly, and yearly options. Option A is wrong because geo-restore is for disaster recovery, not long-term retention. Option C is wrong because automated backups have a maximum retention of 35 days.

Option D is wrong because Point-In-Time Restore (PITR) retention only goes up to 35 days and does not support long-term retention.

Practice this question →
75
MCQmedium

You are optimizing an Azure SQL Database that has a large table with a clustered columnstore index. You notice that the index has been fragmented over time. You need to improve query performance. What should you do?

A.Drop and recreate the columnstore index
B.Reorganize the columnstore index
C.Update statistics on the table
D.Rebuild the columnstore index
AnswerB

REORGANIZE removes rows from deleted bitmaps and is the recommended maintenance.

Why this answer

REORGANIZE is the recommended maintenance operation for clustered columnstore indexes to defragment them by removing rows from deleted bitmaps without requiring a full rebuild. Option A (drop and recreate) is incorrect because it is disruptive and unnecessary for resolving fragmentation. Option C (update statistics) does not address index fragmentation directly.

Option D (rebuild) is more resource-intensive and typically reserved for indexes with high fragmentation, whereas reorganize is optimal for regular maintenance.

Practice this question →
Page 1 of 3 · 212 questions totalNext →

Ready to test yourself?

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

Start 20-question session