You work as the database administrator at Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains a database server named Certkiller -DB01 B01. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. CK_Sales 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. You want to optimize performance for the clustered and nonclustered indexes. You want to generate index recommendations.
What should you do?
A.
Run the Index Tuning Wizard and save the recommended changes to a file.
B.
Run the Database Engine Tuning Advisor and save the data to a SQL script file.
C.
Run the SQL Server Profiler with the Tuning template and save the result to a file.
D.
Run the sys.dm_db_index_operational_stats statement and save the results to a file.
Explanation:
You should use SQL Server Profiler with the Tuning template to generate a workload file and then use the Database Engine Tuning Advisor to analyze the workload file.
Incorrect Answers:
A: The Index Tuning Wizard has been replaced in SQL Server 2005 by the Database Engine Tuning Advisor.
B: you should first use SQL Server Profiler with the Tuning template to generate a workload file and then use the Database Engine Tuning Advisor to analyze the workload file.
D: you can’t use the sys.dm_db_index_operational_stats statement to generate index recommendations.
Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: optimizing databases [SQL Server]
Microsoft SQL Server 2005 Books Online (2006), Index: performance [SQL Server], Database Engine Tuning Advisor
Microsoft SQL Server 2005 Books Online (2006), Index:
sys.dm_db_index_operational_stats