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
Explanation:
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 it’s able to just stop where it is (it doesn’t have
a giant operation to rollback).
https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/