• Description: Microsoft® SQL Server 2005 - Implementation
• Passing Score: 0%
• Practice Test - Number of questions: 98 questions
• Simulation Test - Number of questions: 98 questions
• Simulation Test Duration: 120 minutes
• Has explanations: Yes

You work as the database administrator at IT department .com. All servers on the IT department .com network run Windows Server 2003 and all database servers run SQL Server 2005. The IT department .com network contains a database server named IT department -DB01. IT department -DB01 hosts a database named CK_Sales that stores sales data for the company. The CK_Sales database is also used for online transaction processing (OLTP). Several tables in the CK_Sales database have more than one million rows. You have created a clustered index on each table in the CK_Sales database as well as a nonclustered index on each table that is used frequently when the database was deployed. These indexes were based on projected query requirements. IT department .com users complain that query performance has degraded over time. You want to optimize performance for the clustered and nonclustered indexes. You run the Index Tuning Wizard to generate index recommendations but no changes are recommended for the indexes. You suspect that the indexes are fragmented. You need to optimize the indexes and ensure that fragmentation of the indexes do not occur in the future. What should you do?

Answer(s)

1. Run ALTER INDEX statement with the REORGANIZE option and create a DatabaseConsistency Check scheduled maintenance task.
2. Run ALTER INDEX statement with the REORGANIZE option and create aReorganize Index scheduled maintenance task.
3. Run ALTER INDEX statement with the REBUILD option and create a Rebuild Indexscheduled maintenance task.
4. Run ALTER INDEX statement with the REBUILD option and create a DatabaseConsistency Check scheduled maintenance task.

Run ALTER INDEX statement with the REBUILD option and create a DatabaseConsistency Check scheduled maintenance task.