DP-300 Practice Question: Monitor, configure, and optimize database resources
Your Azure SQL Database has a recurring job that rebuilds indexes weekly. After a recent change, the job is taking much longer to complete. You suspect that the index fragmentation is higher than usual. What is the most efficient way to check index fragmentation across the database?
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
✓
Use the sys.dm_db_index_physical_stats dynamic management function.
Sys.dm_db_index_physical_stats. This DMV provides fragmentation details (e.g., avg_fragmentation_percent) for all indexes in a database with minimal overhead when using limited scanning. Option A (sys.indexes) does not include fragmentation statistics. Option C (SET SHOWPLAN_XML ON) shows query execution plans, not index fragmentation. Option D (sys.dm_db_missing_index_details) suggests new indexes to improve performance, but does not report existing index fragmentation.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Query sys.indexes to check the fragmentation percentage.
Why it's wrong here
sys.indexes does not contain fragmentation data.
- ✓
Use the sys.dm_db_index_physical_stats dynamic management function.
Why this is correct
This DMF returns fragmentation details efficiently.
- ✗
Use SET SHOWPLAN_XML ON and run sample queries.
Why it's wrong here
Shows query plans, not index fragmentation.
- ✗
Use the sys.dm_db_missing_index_details DMV.
Why it's wrong here
Shows missing indexes, not fragmentation.
Go deeper
Related to this question
Learn chapter
Overview of Azure Data Platform Options
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.
About these practice questions
Courseiva writes every DP-300 question from scratch — 906 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.