The correct answer is to rebuild the fragmented index online. This is because rebuilding the index online in Azure SQL Database reorganizes the B-tree structure, consolidating fragmented pages and eliminating page splits without locking the table, which is essential for maintaining query performance on the OrderDate column in a production order management system. On the Microsoft Azure Data Fundamentals DP-900 exam, this scenario tests your understanding of index maintenance and the difference between online and offline operations—a common trap is choosing an offline rebuild or a simple reorganize, but online rebuild is preferred when zero downtime is required. Remember the memory tip: "Online rebuild, no downtime grief"—if the scenario emphasizes concurrent user access, always pick the online option.
DP-900 Practice Question: Index rebuilds defragment and compact index pages.
This DP-900 practice question tests your understanding of identify considerations for relational data on azure. Match the stated requirement to the specific cloud service, access model, or configuration option — many options are valid in isolation but not for this scenario. A key principle to apply: index rebuilds defragment and compact index pages.. Once you have made your selection, read the full explanation to reinforce the concept and understand why each distractor is designed to mislead on exam day.
A company uses Azure SQL Database for an order management system. They have a table 'Orders' with columns: OrderID (PK), CustomerID, OrderDate, TotalAmount. Queries that filter on OrderDate are slow. They create a nonclustered index on OrderDate. However, after many inserts, the index becomes fragmented and page splits occur frequently. Which action should the DBA take to maintain query performance?
Rebuilding the index defragments the index and can be done online to avoid blocking queries.
B
Drop and recreate the index
Why wrong: Dropping and recreating is more disruptive and not necessary when an online rebuild is available.
C
Add a clustered index on OrderDate
Why wrong: OrderDate is not unique and not typically chosen as a clustered index; it may cause page splits as well. The primary key is usually the best clustered index.
D
Change the index to a clustered columnstore index
Why wrong: Columnstore indexes are designed for analytical workloads, not for transactional queries that filter on a single date column.
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Rebuild the index online
Option A is correct because rebuilding the index online eliminates fragmentation and page splits without blocking concurrent queries, which is critical for a production order management system. The ALTER INDEX REBUILD operation reorganizes the index B-tree structure, consolidating pages and reducing logical fragmentation, thereby restoring query performance on OrderDate filters.
Key principle: Index rebuilds defragment and compact index pages.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
✓
Rebuild the index online
Why this is correct
Rebuilding the index defragments the index and can be done online to avoid blocking queries.
Related concept
Index rebuilds defragment and compact index pages.
✗
Drop and recreate the index
Why it's wrong here
Dropping and recreating is more disruptive and not necessary when an online rebuild is available.
✗
Add a clustered index on OrderDate
Why it's wrong here
OrderDate is not unique and not typically chosen as a clustered index; it may cause page splits as well. The primary key is usually the best clustered index.
✗
Change the index to a clustered columnstore index
Why it's wrong here
Columnstore indexes are designed for analytical workloads, not for transactional queries that filter on a single date column.
Common exam traps
Common exam trap: answer the scenario, not the keyword
The trap here is that candidates often confuse index maintenance actions, thinking a drop/recreate is simpler, or they incorrectly assume a clustered index on the filtered column always improves performance, ignoring the impact on write-heavy OLTP workloads.
Detailed technical explanation
How to think about this question
Index fragmentation occurs when page splits leave logical pages out of order, causing extra I/O during range scans. The ALTER INDEX REBUILD command in Azure SQL Database can be run with the ONLINE option (requires Enterprise or Standard tier), which uses row-versioning to allow concurrent DML operations, making it suitable for high-availability systems. Fragmentation levels above 30% typically warrant a rebuild, while lower levels may benefit from a REORGANIZE operation.
KKey Concepts to Remember
Index rebuilds defragment and compact index pages.
Online index rebuilds allow concurrent access to the table.
Fragmentation reduces query performance by increasing I/O.
Azure SQL Database supports online index operations for high availability.
TExam Day Tips
→Watch for words such as best, first, most likely and least administrative effort.
→Review why wrong options are wrong, not only why the correct option is correct.
Key takeaway
Index rebuilds defragment and compact index pages.
Real-world example
How this comes up in practice
A cloud solutions architect for a retail company is evaluating services for a new workload. The correct answer here reflects best practice for the specific scenario described — not a general cloud recommendation. Index rebuilds defragment and compact index pages. Cloud exam questions reward reading the constraint carefully: the same technology can be right or wrong depending on the use case.
Related glossary terms
Concepts from this question explained
These glossary pages explain the core terms tested in this DP-900 question in full detail.
Identify considerations for relational data on Azure — This question tests Identify considerations for relational data on Azure — Index rebuilds defragment and compact index pages..
What is the correct answer to this question?
The correct answer is: Rebuild the index online — Option A is correct because rebuilding the index online eliminates fragmentation and page splits without blocking concurrent queries, which is critical for a production order management system. The ALTER INDEX REBUILD operation reorganizes the index B-tree structure, consolidating pages and reducing logical fragmentation, thereby restoring query performance on OrderDate filters.
What should I do if I get this DP-900 question wrong?
Review index rebuilds defragment and compact index pages., then practise related DP-900 questions on the same topic to reinforce the concept.
What is the key concept behind this question?
Index rebuilds defragment and compact index pages.
About these practice questions
Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
This DP-900 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-900 exam.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.