You are employed as a database administrator at Domain.com. Your duties at Domain.com include administrating a SQL Server 2005 database computer named Certkiller -DB04. None of the databases on Certkiller -DB04 contain any views. You want to tune a database named CK_Stats that is hosted on Certkiller -DB04. You to accomplish this task using the Database Engine Tuning Advisor (DTA). Certkiller -DB04 contains a workload file that is appropriate for DTA.
You need to identify any missing nonclustered indexes. You want the existing structures remain intact, and that newly recommend structures are partitioned for best performance.
What should you do? (Each correct answer presents part of the solution. Choose THREE.)
A.
Specify the use of the Nonclustered indexes physical design structure.
B.
Specify the use of the Indexes physical design structure.
C.
Specify Full partitioning as the partitioning strategy.
D.
Specify No partitioning as the partitioning strategy.
E.
Specify Aligned partitioning as the partitioning strategy.
F.
Specify Keep all existing physical design structures (PDS) in database option.
G.
Specify Do not keep all existing physical design structures (PDS) in database option.
Explanation:
You want to identify missing nonclustered indexes; therefore you must specify the Nonclustered indexes physical design structure. You also want recommended structures to be partitioned for best performance. This means that you need the Full partitioning strategy. Finally, you want existing structures to remain intact. Therefore you must specify Keep all existing PDS.
Incorrect Answers:
B: Specifying the Indexes physical design structure will cause the DTA to consider adding indexes only. We just want nonclustered indexes.
D: When No partitioning is specified, the DTA does not consider partitioning in its recommendations.
E: When Aligned partitioning is specified, the DTA only considers partitioning that is aligned to existing partitions.
G: The Do not keep all existing PDS option will cause the DTA to consider dropping all existing physical design structures.