Courseiva

CCNA Monitor Optimize Db Questions

75 of 214 questions · Page 1/3 · Monitor Optimize Db topic · Answers revealed

Practice these questionsExam hubAll 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.
AnswerD

A larger log file with proper auto-growth settings reduces the frequency of growth events and improves performance.

Why this answer

Option A is correct because increasing the log file size reduces auto-growth events and improves log throughput; the current size may be too small causing frequent growth and fragmentation. Option B is wrong because transactional replication adds overhead. Option C is wrong because enabling accelerated database recovery does not directly improve log write performance.

Option D is wrong because page compression reduces I/O but not log writes.

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

Option C is correct because 'Log write latency' directly measures the time to write to the log. Option A is wrong because 'Average IO latency' includes data and log. Option B is wrong because 'Log IO percent' measures log throughput, not latency.

Option D is wrong because 'Transaction log usage' measures log file space used.

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

High log write I/O suggests I/O bottleneck.

Why this answer

Options B and D are correct. CPU percentage directly indicates CPU usage, and log write bytes/sec indicates I/O activity for writes. Option A (deadlocks) is not a bottleneck.

Option C (session count) shows concurrency, not resource usage. Option E (storage space) is capacity, not performance.

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

High worker time with low logical reads indicates CPU-intensive operations like sorting or hash joins.

Why this answer

Option A is correct because high CPU consumption (worker time) with low I/O indicates CPU-bound queries, often due to inefficient joins, aggregations, or non-sargable predicates. Option B is wrong because low logical reads suggest not memory pressure. Option C is wrong because parameter sniffing would show varied plans.

Option D is wrong because high worker time with low I/O is not typical for transaction log writes.

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

Option A is correct because the metric shows log write IO usage, not CPU. Option B is wrong because it misinterprets the metric. Option C is wrong because it's not about storage size.

Option D is wrong because log write percent is about I/O, not transaction log space.

Practice this question →
6
MCQhard

You have an Azure SQL Managed Instance used for an e-commerce platform. During a flash sale, you experience a deadlock that causes transaction rollbacks. You need to minimize deadlock occurrences in the future. What should you implement?

A.Enable READ COMMITTED SNAPSHOT isolation level.
B.Configure deadlock graph in Extended Events.
C.Enable automatic tuning to force last good plan.
D.Increase the instance vCores to improve concurrency.
AnswerA

Row versioning reduces lock contention and deadlocks.

Why this answer

Option A is correct because enabling READ COMMITTED SNAPSHOT isolation reduces deadlocks by using row versioning. Option B is wrong because increasing instance size does not prevent deadlocks. Option C is wrong because automatic tuning does not handle deadlocks.

Option D is wrong because deadlock graph captures events but doesn't prevent them.

Practice this question →
7
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

Option A and D are correct. Option A identifies I/O bottlenecks by analyzing wait stats (PAGEIOLATCH). Option D reduces I/O by enabling compression.

Option B is wrong because it shows CPU/memory, not I/O. Option C is wrong because it shows resource usage, not per query I/O. Option E is wrong because scaling compute doesn't directly address I/O.

Practice this question →
8
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

Option D is correct because the serverless database auto-pauses after inactivity; disabling auto-pause keeps the database warm. Option A is incorrect because scaling vCores does not affect cold start delay. Option B is incorrect because Query Store does not speed up cold start.

Option C is incorrect because creating a job to ping the database is a workaround but not the recommended solution; disabling auto-pause is simpler.

Practice this question →
9
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

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

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

Practice this question →
10
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

Option C is correct because diagnostic settings can stream metrics to Log Analytics. Option A is wrong because Query Store does not send metrics to Log Analytics. Option B is wrong because DMVs do not stream automatically.

Option D is wrong because SQL Insights is a paid solution.

Practice this question →
11
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

Option B, C, D are correct. Approximate Count Distinct is part of Intelligent Query Processing that improves performance of COUNT(DISTINCT) queries. Adaptive Joins switch join strategies dynamically.

Interleaved Execution for multi-statement table-valued functions helps avoid suboptimal plans. Option A is wrong because Columnstore indexes are not part of Intelligent Query Processing. Option E is wrong because Query Store is a feature but not part of Intelligent Query Processing.

Practice this question →
12
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 logical reads often indicate that queries are reading more data than necessary, possibly due to missing indexes or inefficient queries. Option A is correct. Option B is wrong because CPU time is measured by worker_time.

Option C is wrong because high logical reads can increase memory usage. Option D is wrong because it does not indicate I/O directly.

Practice this question →
13
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

Option A is correct because seeding data to the secondary before failover reduces the time to synchronize. Option B is wrong because increasing the service tier does not directly reduce failover time. Option C is wrong because forced failover with data loss is not recommended for planned failovers.

Option D is wrong because removing the failover group would not help.

Practice this question →
14
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 B is correct. Option A is wrong because SQL Agent cannot send alerts based on DTU.

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

Practice this question →
15
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

Options B and D are correct. Creating appropriate indexes reduces table scans. Updating statistics helps the optimizer choose better plans.

Option A is wrong because increasing MAXDOP might not help and could cause issues. Option C is wrong because it increases tier cost. Option E is wrong because it does not address table scans.

Practice this question →
16
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 →
17
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 can reduce logical reads by enabling seeks instead of scans. Updating statistics helps the optimizer choose efficient plans. Option A and C are correct.

Option B is wrong because increasing memory does not reduce logical reads directly. Option D is wrong because rewriting as a stored procedure does not reduce logical reads.

Practice this question →
18
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 and high log write percent indicate the database is hitting resource limits. Option A and D are correct. Option B is wrong because low data IO is not an indicator.

Option C is wrong because low memory indicates underutilization. Option E is wrong because low session percent is not a scaling trigger.

Practice this question →
19
MCQhard

Refer to the exhibit. You have configured the automatic tuning policy as shown. After a week, you notice that an index has been dropped automatically, causing a critical query to run slowly. What should you do to prevent this in the future while still benefiting from automatic tuning?

A.Manually create the dropped index and mark it as a required index.
B.Enable Query Store to track index usage.
C.Set the dropIndex option state to Disabled in the tuning policy.
D.Disable automatic tuning entirely.
AnswerC

Prevents automatic index drops while retaining other tuning features.

Why this answer

Option C is correct because disabling the dropIndex option prevents automatic index drops while keeping forcePlan and createIndex enabled. Option A is wrong because disabling all automatic tuning removes all benefits. Option B is wrong because reverting by creating a manual index does not prevent future drops.

Option D is wrong because Query Store does not prevent index drops.

Practice this question →
20
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

The trap here is that candidates confuse I/O-related metrics (like avg_io_stall_ms or avg_pending_disk_io) with memory pressure, but those metrics indicate storage performance issues, not insufficient memory for the buffer pool.

Practice this question →
21
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 →
22
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

Option C is correct because differences in the cardinality estimation model (e.g., legacy CE vs. default CE) can cause different plan choices, even with identical schema and stats. Option A is wrong because DTU vs. vCore is a purchasing model, not a performance feature that inherently slows queries. Option B is wrong because if indexes are identical, fragmentation impact should be similar.

Option D is wrong because intelligent query processing is generally beneficial, not detrimental, and differences would be minimal.

Practice this question →
23
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

Option D is correct because FORCE_LAST_GOOD_PLAN will automatically revert to the last known good plan if the new plan causes regressions. Option A is wrong because automatic tuning does not show a popup. Option B is wrong because reverting is automatic, not manual.

Option C is wrong because reverting happens automatically.

Practice this question →
24
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

Option B is correct because in Hyperscale, index rebuild operations are logged and can cause significant log throughput, which is a common bottleneck. Option A is wrong because page compression is not directly related to index rebuild time. Option C is wrong because Hyperscale uses a distributed architecture with fast log commit.

Option D is wrong because Hyperscale does not have a fixed DTU limit; it scales compute independently.

Practice this question →
25
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.
AnswerB

ADR reduces log volume and IO, which decreases page server IOPS utilization.

Why this answer

Option A is correct because enabling accelerated database recovery reduces the log generation and IO on page servers. Option B is wrong because increasing the number of replicas does not reduce page server IO. Option C is wrong because changing to Business Critical might not address page server IO; Hyperscale has its own architecture.

Option D is wrong because increasing max degree of parallelism might increase IO.

Practice this question →
26
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 →
27
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 →
28
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

Option B is correct because you can manually revert the plan correction via the Azure portal or T-SQL. Option A is wrong because disabling automatic tuning would prevent future corrections but not revert the current one. Option C is wrong because Query Store retains plans but reverting requires explicit action.

Option D is wrong because index recreation is not related to plan correction.

Practice this question →
29
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 →
30
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

This combination causes the session to be killed if it cannot acquire a lock within 5 seconds.

Why this answer

Option A is correct because setting the DEADLOCK_PRIORITY to LOW and using a lock timeout of 5 seconds will cause the blocking session to be killed if it exceeds the timeout. Option B is wrong because resource governor is not available in Managed Instance at the database level. Option C is wrong because read committed snapshot isolation does not kill blocking processes.

Option D is wrong because changing transaction isolation level does not automatically terminate processes.

Practice this question →
31
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

Option D is correct because the Top Resource Consuming Queries report in Query Store shows cumulative CPU time, duration, and other metrics, allowing easy identification. Option A is wrong because sys.dm_exec_query_stats shows current cached plans but not cumulative over a specific time window. Option B is wrong because the Regressed Queries report shows plan changes, not CPU consumption.

Option C is wrong because sys.dm_exec_requests shows currently running queries, not historical cumulative use.

Practice this question →
32
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
AnswerA

Provides IO statistics per database.

Why this answer

Option A is correct because sys.dm_db_io_stats provides IO statistics per database. Option B is wrong because sys.dm_exec_query_stats provides query execution stats but not IOPS specifically. Option C is wrong because sys.dm_db_index_usage_stats provides index usage, not IOPS.

Option D is wrong because sys.dm_os_performance_counters provides OS-level counters, not per-query IOPS.

Practice this question →
33
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 →
34
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

Options A and B are correct. Creating missing indexes and updating statistics are common performance optimization tasks that do not require code changes. Option C requires code changes.

Option D is not a direct optimization. Option E is about hardware, but still no code change; however, it's not a typical DBA action for query performance.

Practice this question →
35
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

Option B is correct because using Azure Key Vault as the TDE protector is recommended for centralized management and high availability. Option A is wrong because SQL Managed Instance does not support TDE with a certificate stored in the instance; it must use Azure Key Vault or service-managed. Option C is wrong because service-managed keys have less control and are not recommended for production.

Option D is wrong because TDE cannot be disabled after migration; it must be managed.

Practice this question →
36
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

Option C is correct because scaling up to a higher service tier or increasing DTUs provides more resources, reducing pressure. Option A is incorrect because scaling down would worsen performance. Option B is incorrect because geo-replication does not reduce DTU consumption.

Option D is incorrect because read scale-out is for read replicas, not reducing DTUs.

Practice this question →
37
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

Option A is correct because the FORCE_LAST_GOOD_PLAN option in automatic tuning automatically forces a plan when a regression is detected. Option B is wrong because CREATE INDEX automates index creation, not plan forcing. Option C is wrong because DROP INDEX is for unused indexes.

Option D is wrong because Intelligent Insights provides analysis but does not automatically force plans.

Practice this question →
38
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

Option B is correct: Increasing log rate limit improves log throughput. Option C is correct: Using smaller log writes reduces latency. Option D is correct: Enabling Accelerated Database Recovery reduces log amplification.

Option A is incorrect: Increasing service tier to Business Critical does not apply to Hyperscale. Option E is incorrect: Read scale-out does not improve write performance.

Practice this question →
39
MCQeasy

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

A.Enables auto-pause with a delay of 1 minute.
B.Configures the database to pause after 1 second of inactivity.
C.Disables auto-pause for the database.
D.Sets auto-pause delay to 1 hour.
AnswerC

-1 means auto-pause is disabled.

Why this answer

Option C is correct because setting AutoPauseDelay to -1 disables auto-pause for serverless databases. Option A is wrong because it's not enabling auto-pause. Option B is wrong because it's not setting a specific delay.

Option D is wrong because it's not changing service tier.

Practice this question →
40
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

Option A is correct because 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 →
41
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

Option B is correct because 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 →
42
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 two changes 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

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

Option C is wrong because storage reduction is not as impactful.

Practice this question →
43
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

Option C is correct because enabling the Optimize for Ad Hoc Workloads setting reduces plan cache bloat but does not directly address parameter sniffing. However, the correct approach to mitigate parameter sniffing without code changes is to use the RECOMPILE query hint or enable forced parameterization. But among the options, C is the best because it reduces plan cache bloat and can help with parameter sniffing indirectly.

Actually, option A is more direct: enabling forced parameterization makes SQL Server parameterize queries, reducing the impact of parameter sniffing. But the question says 'without changing application code' – forced parameterization is a database setting. Option B is wrong because clearing the plan cache is temporary.

Option D is wrong because it requires code changes. So the correct answer is A.

Practice this question →
44
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

Options A and C are correct. Query Store tracks query execution statistics including CPU, duration, and I/O, and the sys.dm_exec_query_stats DMV provides similar data. Option B is wrong because sys.dm_os_wait_stats shows wait types, not query resource usage.

Option D is wrong because sys.dm_db_index_usage_stats shows index usage, not query resource consumption. Option E is wrong because sys.dm_io_virtual_file_stats shows I/O statistics at the file level, not per query.

Practice this question →
45
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

Option D is correct because Azure SQL Database does not natively support tiered storage, but you can move data to Azure SQL Database Hyperscale (which allows scaling storage independently) or to Azure SQL Data Warehouse (now Azure Synapse). However, the best approach is to implement data archiving by partitioning and moving old data to a separate database with lower service tier. But among the options, D is the most practical: use SQL Server Stretch Database, which is deprecated, but the current alternative is to use Hyperscale or archive to Azure Storage and query via PolyBase.

Actually, the correct answer is A: use partitioning and move old data to a cheaper database tier. Option B is wrong because Hyperscale is more expensive. Option C is wrong because Elastic Query is for querying remote databases.

So I'll adjust explanation.

Practice this question →
46
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 A and D are correct. Automatic tuning can create missing indexes and identify and correct query plan regressions. Option B is wrong because automatic tuning does not automatically scale compute; that's auto-scale.

Option C is wrong because it does not manage statistics automatically. Option E is wrong because it does not manage read replicas.

Practice this question →
47
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 →
48
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

Option B is correct because a plan regression often occurs when statistics are stale, causing the optimizer to choose a suboptimal plan. Option A is wrong because parameter sniffing can cause plan changes but usually the plan is based on initial parameter values, not all parameters. Option C is wrong because missing indexes would cause scans, not a change from hash to nested loops.

Option D is wrong because forced parameterization might stabilize plans but does not cause this change.

Practice this question →
49
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, B, and D are correct. Automatic tuning includes FORCE LAST GOOD PLAN, CREATE INDEX, and DROP INDEX. Option C is wrong because modify statistics is not an automatic tuning option.

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

Practice this question →
50
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

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 →
51
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 A and D are correct. sys.dm_db_log_space_usage shows transaction log space. sys.dm_db_file_space_usage shows data file space. Option B is wrong because sys.dm_db_partition_stats shows row counts, not space. Option C is wrong because sys.dm_exec_query_stats is for query performance.

Option E is wrong because sys.dm_os_performance_counters includes many counters but not direct space usage per database.

Practice this question →
52
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

LTR allows granular retention schedules; monthly for 7 years is compliant and cost-effective.

Why this answer

Option D is correct because configuring a long-term retention (LTR) policy with monthly backups for 7 years meets compliance while reducing cost by not retaining weekly/daily backups for the full 10 years. Option A is wrong because disabling LTR entirely loses compliance. Option B is wrong because moving to Azure SQL Database does not automatically reduce cost and may not be feasible.

Option C is wrong because point-in-time restore (PITR) retention is limited to 35 days, not 7 years.

Practice this question →
53
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

Options A, C, and D are correct. sys.dm_exec_query_stats returns aggregated performance statistics for cached query plans. sys.dm_exec_requests shows currently executing requests. sys.dm_exec_query_plan shows the execution plan for cached queries. Option B is wrong because sys.dm_exec_sessions provides session-level information, not query-specific resource usage. Option E is wrong because sys.dm_os_wait_stats shows wait statistics, not query-specific resource use.

Practice this question →
54
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 B and D are correct. avg_data_io_percent measures data IO usage relative to limit; avg_log_write_percent measures log IO usage. Option A is CPU. Option C is memory.

Option E is storage space.

Practice this question →
55
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

Azure Monitor allows long-term log retention and metric alerts.

Why this answer

Option C is correct because Azure SQL Analytics (now part of Azure Monitor) provides long-term retention and alerting capabilities. Option A is wrong because Query Store default retention is limited. Option B is wrong because Intelligent Insights provides analysis but not configurable retention.

Option D is wrong because DMVs only show current state.

Practice this question →
56
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 D are correct. B is correct because using RECOMPILE hint forces recompilation only for that execution, but the question asks to reduce recompilations, so that's wrong. Wait, the correct answers are A and C.

Option A is correct because using KEEPFIXED PLAN prevents recompilations due to statistics changes. Option C is correct because keeping parameter data types consistent avoids recompilations due to type mismatch. Option B is wrong because RECOMPILE hint increases recompilations.

Option D is wrong because OPTIMIZE FOR UNKNOWN can cause plan instability but not reduce recompilations. Option E is wrong because sp_refreshsqlmodule does not reduce recompilations.

Practice this question →
57
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

Option A is correct because WRITELOG waits indicate that transaction log writes are slower than expected, often due to log I/O latency. Option B is wrong because tempdb contention shows PAGELATCH waits. Option C is wrong because blocking shows LCK_M_* waits.

Option D is wrong because CPU pressure shows SOS_SCHEDULER_YIELD.

Practice this question →
58
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

Option B is correct because sys.dm_exec_query_stats provides aggregated performance statistics including CPU time. Option A is wrong because it shows currently executing queries, not historical. Option C is wrong because it shows resource governance stats.

Option D is wrong because it shows index usage.

Practice this question →
59
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 exhibit shows a SAS token in the template. The most common issue is that the SAS token has expired or is invalid. Option B is correct.

Option A is wrong because the storage container URI is valid. Option C is wrong because the location is valid. Option D is wrong because the SAS token is used for authentication.

Practice this question →
60
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

Option B is correct because Service Broker is not fully supported in Azure SQL Database; you should use Azure Queue Storage or Event Grid. Option A is wrong because Service Broker cannot be enabled in Azure SQL Database. Option C is wrong because Service Broker uses internal activation, not external.

Option D is wrong because scaling up does not solve the feature incompatibility.

Practice this question →
61
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

Option D is correct because moving the database to a higher service tier or a separate pool isolates its resource usage and prevents impact on others. Option A is wrong because scaling the pool affects all databases. Option B is wrong because query store does not limit resources.

Option C is wrong because reserved capacity is a billing benefit, not a resource isolation mechanism.

Practice this question →
62
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 exhibit shows maxSizeBytes: 268435456000, which is 250 GB. Option D is correct. Option A is wrong because 4 TB is not the limit shown.

Option B is wrong because 1 TB is incorrect. Option C is wrong because 500 GB is incorrect.

Practice this question →
63
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 →
64
MCQeasy

You are configuring Azure SQL Database automatic tuning. You want to enable the feature that automatically creates and drops indexes based on workload patterns. Which option should you enable?

A.CREATE INDEX
B.Force Parameterization
C.Create Index and Drop Index
D.Force Last Good Plan
AnswerC

Automatic index management based on workload.

Why this answer

Option D is correct because the automatic tuning option for index management is called 'Create Index' and 'Drop Index'. Option A is wrong because 'Force Last Good Plan' is for plan regression. Option B is wrong because 'Force Parameterization' is a different tuning option.

Option C is wrong because 'CREATE INDEX' is a manual command, not an automatic tuning option.

Practice this question →
65
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

Option A is correct because if the secondary replica is not able to apply log records quickly enough, the log send queue grows. This can happen if the secondary has insufficient resources or is under load. Option B is wrong because direct network latency is low.

Option C is wrong because the primary has ample resources. Option D is wrong because log flush on the primary is not the issue.

Practice this question →
66
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

Option A is correct because partition elimination requires the predicate to be on the partitioning column. In this case, the queries likely do not filter on OrderDate, or the predicate is not sargable. Adding a clustered index on OrderDate would help if the queries filter on OrderDate, but the stem says the queries aggregate by month for the current year, so they likely have a WHERE clause on OrderDate.

If partition elimination is not happening, the most common reason is that the partition function is not aligned with the query predicate. Re-creating the partition function with a different boundary may help if the current boundaries are not matching. However, the best action is to ensure the queries use the partition column in the WHERE clause.

Since we cannot change the code, we can modify the partition function to align with the typical query pattern. Option B is wrong because creating views does not force partition elimination. Option C is wrong because columnstore indexes do not directly cause partition elimination.

Option D is wrong because updating statistics does not fix partition elimination.

Practice this question →
67
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

High target vs actual indicates memory pressure.

Why this answer

Option B is correct because 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 Buffer Cache Hit Ratio indicates cache efficiency but not directly memory pressure. Option C is wrong because Page Life Expectancy is affected by memory but is a secondary indicator.

Option D is wrong because Batch Requests/sec measures throughput, not memory.

Practice this question →
68
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

Option D is correct because contained database users are replicated automatically, but non-contained logins in master are not replicated with Active Geo-Replication. Since logins are in master, they are not present on the secondary after failover. Option A is wrong because Firewall rules for Azure services are enabled by default.

Option B is wrong because contained users are replicated. Option C is wrong because DNS changes are not required for connectivity after failover if using the same connection string with failover group.

Practice this question →
69
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.
AnswerA

This reduces log write latency on the secondary replica because LRS has lower write latency than GRS, while still maintaining disaster recovery.

Why this answer

Option C is correct because changing the secondary replica's backup storage redundancy from locally-redundant storage (LRS) to geo-redundant storage (GRS) might cause increased latency, but here the issue is high log write latency. Actually, the correct answer is to change the secondary's backup storage redundancy to LRS (if it is GRS) to reduce latency. However, the options are as given.

Option A is wrong because decreasing the secondary replica's service tier reduces latency but also reduces redundancy. Option B is wrong because asynchronous commit reduces protection. Option D is wrong because adding more replicas increases cost and complexity.

The best answer is C, as it addresses the storage latency issue without compromising data protection.

Practice this question →
70
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 →
71
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

Option A correct because adding files reduces allocation contention. Option C correct because using multiple tempdb files distributes contention. Option D correct because optimizing queries that use tempdb reduces load.

Option B wrong because increasing size does not reduce contention. Option E wrong because changing collation is unrelated.

Practice this question →
72
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

Option C is correct because 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 B is wrong because Intelligent Insights provides monitoring, not automatic fixing.

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

Practice this question →
73
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

Provides a pre-built monitoring dashboard for multiple databases.

Why this answer

Azure SQL Analytics provides a unified monitoring solution for multiple Azure SQL databases. Option C is correct. Option A is wrong because Query Performance Insight is per-database.

Option B is wrong because Azure Monitor Workbooks can be used but require manual configuration. Option D is wrong because Azure Data Studio is a client tool.

Practice this question →
74
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 →
75
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

Option B is correct because configuring minimum and maximum eDTU per database ensures that each database gets guaranteed resources when needed, while still sharing the pool. Option A is wrong because moving to separate standalone databases would increase cost. Option C is wrong because using a vCore pool instead of DTU does not solve the issue inherently.

Option D is wrong because adding more databases to the pool increases contention.

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

Ready to test yourself?

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

Start 20-question session