Exam 70-765 topic 3 question 48 discussion

Actual exam question from Microsoft's 70-765

Question #: 48
Topic #: 3

You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 Standard Edition. The server hosts a database named Orders.
Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD.
You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.
Which Transact-SQL command should you use?

A. ALTER INDEX IX_OrderDateON CustomerOrder DISABLE
B. ALTER INDEX IX_OrderDateON CustomerOrder ENABLE
C. ALTER INDEX IX_OrderDateON CustomerOrder REORGANIZE
D. ALTER INDEX IX OrderDateON CustomerOrder REBUILD

Show Suggested Answer

Suggested Answer: C 🗳️
Reorganize: This option is more lightweight compared to rebuild. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then its able to just stop where it is (it doesnt have a giant operation to rollback).
References:
https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

by t at Feb. 28, 2019, 8:51 p.m.

Comments

Submit Cancel

t

6 years, 4 months ago

If this is a heavily fragmented index, wouldn't the REBUILD option be a better choice?

upvoted 1 times

AlexandreVM

6 years, 1 month ago

Standard Edition... table needs to remain online...

upvoted 2 times

...