DP-900 Practice Question: Index rebuilds defragment and compact index pages.
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?
⚠ Common exam trap
Many exam-takers 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.
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
✓
Rebuild the index online
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.
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 online is the best approach because it eliminates fragmentation caused by page splits and logical ordering issues without locking the underlying table for the entire operation. Azure SQL Database supports the ONLINE option for both clustered and nonclustered index rebuilds, allowing concurrent user queries to continue during the rebuild. This minimizes downtime for the order management system while restoring the index's B-tree structure to a defragmented state.
- ✗
Drop and recreate the index
Why it's wrong here
Dropping and recreating the index is unnecessarily disruptive because the index becomes completely unavailable during the drop and rebuild, causing dependent queries to fall back to table scans and potentially blocking transactions. Unlike an online rebuild, this approach requires manual coordination and risks longer outages. Since an online rebuild provides the same defragmentation benefit while keeping the index available, this is an inferior maintenance strategy.
- ✗
Add a clustered index on OrderDate
Why it's wrong here
Adding a clustered index on OrderDate is incorrect because OrderDate is not unique, so SQL Server would need to add a uniqueifier to each row, increasing index size and maintenance overhead. Additionally, clustering on a date column can cause page splits as new orders are inserted at the end of the index, though that only occurs if the index is built on the clustering key. The existing clustered index, typically on the primary key, is already suitable; the best fix is to rebuild it, not add another clustered index.
- ✗
Change the index to a clustered columnstore index
Why it's wrong here
Changing the index to a clustered columnstore index is wrong for a transactional order management system, as columnstore indexes are optimized for large analytical queries that scan many rows, not for point lookups or updates on a single order date. In an OLTP workload, a columnstore index can lead to excessive memory and CPU overhead due to segment reads and delta store operations, degrading performance of the frequent INSERT/UPDATE statements. A rowstore index, such as the existing B-tree, is the appropriate choice.
Go deeper
Related to this question
Learn chapter
Azure SQL Services
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
Key term
Azure SQL Database
Azure SQL Database is a fully managed relational database-as-a-service (DBaaS) in Microsoft Azure, based on the SQL Server engine, that handles scaling, backups, patching, and high availability automatically.
About these practice questions
One of 820 original DP-900 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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-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.