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. There is at least one index on each table in the CK_Sales database. The tables in the CK_Sales database are shown in the following database diagram.
Domain.com users in the Finance department run several stored procedures against he CK_Sales database to create month end reports. You have recently altered some of the stored procedures. How Finance department users complain that the month-end reports take longer to create. You need to improve the performance of the stored procedures.
What should you do? (Each correct answer presents part of the solution. Choose two.)
A.
Use System Monitor to monitor the execution of the stored procedures and save the results to a trace file.
B.
Use SQL Server Profiler with the Tuning template to generate a trace file for the stored procedures.
C.
Use the Index Tuning Wizard to analyze the trace file.
D.
Run the stored procedures in the Query Editor and save the results to a trace file.
E.
Use the Database Engine Tuning Advisor to analyze the trace file.
Explanation:
You should use SQL Server Profiler with the Tuning template to generate a trace file for the stored procedures and then use the Database Engine Tuning Advisor to analyze the trace file and tune the database.
Incorrect Answers:
A: The Windows Server 2003 System Monitor cannot be used to monitor the execution of a stored procedure. System Monitor is used to monitor system resources such as processor, hard disk, and services, etc.
C: The Index Tuning Wizard has been replaced in SQL Server 2005 by the Database Engine Tuning Advisor.
D: The results from the Query Editor cannot be saved to a trace file and cannot be used by the Database Engine Tuning Advisor to analyze the trace file and tune the database.
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: performance [SQL Server], queries