You administer a Microsoft SQL Server 2012 database that contains a table named
AccountTransaction.
You discover that query performance on the table is poor due to fragmentation on the
IDX_AccountTransaction_AccountCode non-clustered index.
You need to defragment the index. You also need to ensure that user queries are able to use the
index during the defragmenting process.
Which Transact-SQL batch should you use?
A.
ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode
REORGANIZE
B.
ALTER INDEX ALL ON AccountTransaction REBUILD
C.
ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode
REBUILD
D.
CREATE INDEX IDXAccountTransactionAccountCode ON AccountTransaction.AccountCode WITH
DROP EXISTING
A
A
https://msdn.microsoft.com/en-us/library/ms189858.aspx
Reorganize directs SQL defrag manager to defragment the tables and indexes online. This defragmentation can be stopped without losing progress. The amount of log space used varies with the amount of work completed. During reorganization, SQL defrag manager skips pages that are in use. In general, select this defragmentation type when the instance or database must be available during defragmentation or when you defragment a small index.
http://www.idera.com/help/sqldefrag/3-2/content/sql%20defrag%20manager%20help/optimize%20performance.htm