Configure and manage automation of tasks →hardMultiple ChoiceObjective-mapped

DP-300 Configure and manage automation of tasks Practice Question

Exhibit

Refer to the exhibit.

```
-- Output from sys.dm_db_index_physical_stats
OBJECT_NAME = 'Orders'
index_type_desc = 'CLUSTERED'
avg_fragmentation_in_percent = 65
page_count = 25000
```

Refer to the exhibit. You are monitoring index fragmentation in an Azure SQL Database. You need to automate the rebuild of this index when fragmentation exceeds 50%. The rebuild must be online to minimize downtime. Which T-SQL statement should you include in your automated maintenance job?

⚠ Common exam trap

Watch out — candidates often confuse `REORGANIZE` with `REBUILD` for high fragmentation, or they overlook the `ONLINE = ON` option, assuming all rebuilds are online by default in Azure SQL 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

✓

ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON);

It uses the `ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON)` statement, which rebuilds all indexes on the table online, allowing concurrent user access and minimizing downtime. This meets the requirement to automate the rebuild when fragmentation exceeds 50%, as online rebuilds are supported in Azure SQL Database for this purpose.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
ALTER INDEX Orders ON Orders REBUILD;
Why it's wrong here
Syntax is incorrect; should be ALTER INDEX ALL or specify index name.
✓
ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON);
Why this is correct
Online rebuild minimizes downtime.
✗
ALTER INDEX ALL ON Orders REORGANIZE;
Why it's wrong here
Reorganize does not reduce fragmentation sufficiently for high fragmentation.
✗
ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = OFF);
Why it's wrong here
Offline rebuild causes downtime.

Go deeper

DP-300 Configure and manage automation of tasks Practice Question

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question