You work as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. You detect that all indexes of the Marketing table on the SQL Server 2005 database server are heavily fragmented.
You are required to decrease the fragmentation of all the indexes of the Marketing table to a minimum. This needs to be accomplished whilst keeping the Marketing table is accessible to all users.
What should you do?
A.
You need to execute the statement:
USE DB1;
GO
ALTER INDEX ALL ON marketing
REORGANIZE WITH (LOB_COMPACTION = ON);
GO
B.
You need to defragment the disk that contains the Marketing table.
C.
You need to execute the statement:
USE DB1;
GO
ALTER INDEX ALL ON marketing
REBUILD;
GO
D.
You need to execute the statement:
USE master;
GO
DBCC CLEANTABLE (‘DB1′,’ marketing’);
GO
Explanation:
Defragmenting the disk will not help, this might help if the files containing the database were fragmented but not if the index itself is fragmented. Executing a statement with USE master will not help as the sales table is contained in the DB1 database. You have to use ALTER INDEX on DB1 and use the REORGANIZE switch to minimize internal fragmentation while the tale is online. The REBUILD switch is used for external fragmentation and will drop and rebuild the index to reduce both external and internal fragmentation and therefore the table will not be available to users during rebuild.