You are the database administrator for your company and manage all the company’s SQL Server 2008 databases.
The production database named Prod1 contains all the product and sales data for the company. Database users complain that the database performance has degraded.
You must diagnose the problem andtune the database by using the Database Engine Tuning Advisor.
You create a workload by using SQL Server Profiler and want to use this workload to tune the database using theDatabase Engine Tuning Advisor.
Which statement is true?
A.
The trace file and the trace table workload must exist on the local server if you want to use them to tune thedatabase using the Database Engine Tuning Advisor.
B.
The data generated as tuning recommendations is stored in the trace file used while tuning the database.
C.
The Database Engine Tuning Advisor will generate recommendations for all the databases on the server.
D.
The Database Engine Tuning Advisor cannot use the trace tables as a workload while trace events are being written to it.
Explanation:
The Database Engine Tuning Advisor cannot use the trace tables as a workload while trace events are beingwritten to it. The process of writing the trace events to the trace table should be finished before the trace table canbe used as a workload for tuning the database. The Database Engine Tuning Advisor does not support using atrace table to which trace events are still being written. The option stating that the trace file and trace table workload must exist on the local server if you want to usethem for tuning the database using the Database Engine Tuning Advisor is incorrect. The trace file to be used asa workload can also exist on the remote server. The Database Engine Tuning Advisor can use a trace file locatedeither on a local or on a remote server as a workload for tuning the database. If you are specifying a trace table tobe used as a workload, the trace table must exist on the local server only. The Database Engine Tuning Advisorcannot use a trace table located on a remote server as a workload. The option stating that the data generated as tuning recommendations is stored in the trace file used while tuningthe database is incorrect. The data generated as tuning recommendations is stored in the msdb database. The option stating that the Database Engine Tuning Advisor will generate recommendations for all the databaseson the server is incorrect. The Database Engine Tuning Advisor will generate recommendations only for thosedatabases or database objects that you have selected while performing the tuning operation.
Objective:
Optimizing SQL Server PerformanceSub-Objective:
Use the Database Engine Tuning Advisor.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Performance >Performance Monitoring and Tuning How-to Topics > Database Engine Tuning Advisor How-to Topics > How to:Tune a Database > How to: Tune a Database by Using Database Engine Tuning Advisor TechNet > TechNet Library > Server Products and Technologies > SQL Server >SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Performance > Monitoringand Tuning for Performance > Tuning the Physical Database Design > Using Database Engine Tuning Advisor >Using Session Monitor to Evaluate Tuning Recommendations