You work as the database administrator at Domain.com. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that runs on a Windows 2000 Server computer. Certkiller -DB01 hosts a database named CK_Sales. The tables in the CK_Sales database are shown in the following database diagram.
An index named PK_Customers_CustomerID has been created on the Customers table. The index is used extensively throughout the day. Over the last few months, several new pages have been added to the index. Domain.com users complain that the performance queries against the index is very slow. You suspect that fragmentation is affecting query performance. You want to reduce fragmentation. You need to ensure that the index remain online and that no completed work is lost if the operation is interrupted.
What should you do?
A.
Run the ALTER INDEX PK_Customers_CustomerID ON CK_Sales.Customers REBUILD WITH (ONLINE = ON) Transact-SQL statement.
B.
Run the DBCC INDEXDEFRAG Transact-SQL statement.
C.
Run the ALTER INDEX PK_Customers_CustomerID ON CK_Sales.Customers REORGANIZE Transact-SQL statement.
D.
Drop and recreate the PK_Customers_CustomerID index.
Explanation:
To ensure that no completed work is lost if the operation is interrupted, you must perform the operation on the index while it is online. The ALTER INDEX REORGANIZE statement is used to reduce fragmentation of an index while the index is online.
Incorrect Answers:
A: The ALTER INDEX REBUILD WITH ONLINE statement can be used to reduce fragmentation of an index while the index is online but data loss will occur should the operation be interrupted.
B: The DBCC INDEXDEFRAG can be used to defragment indexes on a table. However, this statement is supported for backward compatibility and is not recommended. The ALTER INDEX statement is preferred.
D: When you DROP an index, the index is offline. You need to ensure that the index remains online.
Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: ALTER INDEX statement Microsoft SQL Server 2005 Books Online (2006), Index: indexes [SQL Server], reorganizing