DP-300 Practice Question: Monitor, configure, and optimize database resources
You are the database administrator for an Azure SQL Managed Instance hosting a data warehouse workload. You notice that the storage space consumed by the database is significantly larger than expected. The database has multiple large tables with clustered columnstore indexes. You suspect that the columnstore indexes have become fragmented and that deleted rows are consuming space. You need to reclaim storage space with minimal impact on query performance during business hours. What should you do?
⚠ Common exam trap
Watch out — candidates often assume any REORGANIZE is sufficient, but without COMPRESS_ALL_ROW_GROUPS, it does not address deleted rows or delta store row groups, so the space is not reclaimed.
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
✓
Perform an ALTER INDEX REORGANIZE with the COMPRESS_ALL_ROW_GROUPS option on the affected columnstore indexes.
REORGANIZE with COMPRESS_ALL_ROW_GROUPS compresses all row groups, including those in the delta store, and removes deleted rows from columnstore indexes. This operation is online and can be performed during business hours with minimal impact on query performance, unlike a rebuild which is offline and resource-intensive. It directly addresses the fragmentation and deleted row space consumption in columnstore 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.
- ✗
Perform an ALTER INDEX REBUILD on the affected columnstore indexes after business hours.
Why it's wrong here
REBUILD is an offline operation that locks the table, impacting availability.
- ✓
Perform an ALTER INDEX REORGANIZE with the COMPRESS_ALL_ROW_GROUPS option on the affected columnstore indexes.
Why this is correct
This online operation reorganizes and compresses all row groups, reclaiming space from deleted rows with minimal impact.
- ✗
Perform an ALTER INDEX REORGANIZE on the affected columnstore indexes.
Why it's wrong here
REORGANIZE alone does not compress delta row groups and may not fully reclaim space from deleted rows.
- ✗
Rebuild the entire database by creating a new database and copying data.
Why it's wrong here
This is excessive and causes significant downtime.
Go deeper
Related to this question
Learn chapter
Overview of Azure Data Platform Options
Key term
Azure SQL Managed Instance
Azure SQL Managed Instance is a fully managed cloud database service that gives you nearly all the features of Microsoft SQL Server on your own server, without you having to manage the hardware or operating system.
About these practice questions
One of 906 original DP-300 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-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.