You work as the database administrator at Domain.com. You are responsible for writing procedure documents for the company. All servers on the Domain.com network run Windows Server 2003. The Domain.com network contains a database server named Certkiller -DB01 B01. Certkiller -DB01 was recently upgraded from SQL Server 2000 to SQL Server 2005.
You need to review and update the procedure for reducing fragmentation on indexes. The current procedure document recommends the use of the DBCC SHOWCONTIG and DBCC INDEXDEFRAG statements. You need to alter the procedure document to recommend the use of commands and statements that will be supported in future versions of SQL Server.
What should you do?
A.
Recommend the use for the sys.dm_db_partition_stats and ALTER INDEX REORGANIZE statements.
B.
Recommend the use for the sys.dm_db_index_physical_stats and ALTER INDEX REORGANIZE statements.
C.
Recommend the use for the sys.dm_db_partition_stats and ALTER INDEX REBUILD statements.
D.
Recommend the use for the sys.dm_db_index_physical_stats and ALTER INDEX REBUILD statements.
Explanation:
The sys.dm_db_index_physical_stats and ALTER INDEX REORGANIZE statements are used to reduce fragmentation of indexes and will be supported in future versions of SQL Server and replaces the DBCC SHOWCONTIG and DBCC INDEXDEFRAG statements respectively.
Incorrect Answers:
A: The sys.dm_db_partition_stats does not replace either the DBCC SHOWCONTIG and DBCC INDEXDEFRAG statements.
C: The sys.dm_db_partition_stats and the ALTER INDEX REBUILD statements do not replace either the DBCC SHOWCONTIG and DBCC INDEXDEFRAG statements.
D: The ALTER INDEX REBUILD statement does not replace either the DBCC SHOWCONTIG and DBCC INDEXDEFRAG statements.
Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: DBCC SHOWCONTIG Microsoft SQL Server 2005 Books Online (2006), Index: DBCC INDEXDEFRAG Microsoft SQL Server 2005 Books Online (2006), Index: ALTER INDEX REBUILD Microsoft SQL Server 2005 Books Online (2006), Index:
sys.dm_db_index_physical_stats
Microsoft SQL Server 2005 Books Online (2006), Index: sys.dm_db_partition_stats