DP-300 Practice Question: Monitor, configure, and optimize database resources
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?
⚠ Common exam trap
Many candidates 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.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Update all statistics on the table.
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.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Increase the service tier of the Azure SQL Database to provide more resources.
Why it's wrong here
Increasing the service tier adds more DTUs or vCores and memory, but it does not refine the pre-existing cardinality estimation error in the query plan. The optimizer still sees stale statistics and may insist on a clustered index scan because that plan was compiled with the current (incorrect) row estimates. A scan against a large table can still saturate the upgraded resources, so the root inefficiency remains and the workload may continue to experience high latency.
- ✓
Update all statistics on the table.
Why this is correct
Updating all statistics on the table refreshes the histograms and density information for every index and column, giving the query optimizer a current view of data distribution. With accurate cardinality estimates, the optimizer can determine that the predicate is selective and choose an index seek instead of a scan. This is the targeted fix when a plan becomes suboptimal due to stale statistics, and it is the only option here that directly addresses the optimizer's input.
- ✗
Rebuild the clustered index to reduce fragmentation.
Why it's wrong here
Rebuilding the clustered index improves page-level fragmentation and reduces the number of I/Os required to read an already chosen plan, but it does not change the optimizer's decision between a scan and a seek. A seek is only selected when the predicate's cardinality estimate indicates high selectivity; fragmentation is irrelevant to that estimate. Moreover, rebuilding the index does not update the statistics histograms unless data has changed, so the stale density values that caused the scan would remain intact.
- ✗
Update the statistics on the nonclustered index only.
Why it's wrong here
Updating statistics on the nonclustered index only affects the histogram for the key columns in that specific index, which may have little or no bearing on the predicate that is causing the scan. If the query filters on a column that is not a leading key column of that index, the optimizer will still rely on the outdated stats for that column and continue with the scan. The table may also have other indexes or column statistics that are stale, so updating only one nonclustered index leaves the underlying cardinality problem unaddressed.
Go deeper
Related to this question
Learn chapter
Managing Environment Configurations and Resource Governance
Key term
Azure SQL Performance Tuning
Azure SQL Performance Tuning is the process of optimizing the speed and efficiency of queries and database operations in Microsoft Azure SQL Database or SQL Managed Instance to reduce latency and improve throughput.
Key term
Azure SQL Indexes
Structures in Azure SQL Database that speed up data retrieval by providing quick access paths to rows, similar to a book index.
About these practice questions
Courseiva writes every DP-300 question from scratch — 574 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-300 exam.