You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run Windows Server 2003 and all client computers run Windows XP Professional. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that hosts a database named CK_Sales. The CK_Sales database stores sales data for the company. Domain.com users complain that the performance of the CK_Sales database has deteriorated over the last few weeks. You use System Monitor to monitor the performance of Certkiller -DB01 and receive the output as shown in the exhibit.
You also notice that the ratio of SQL Recompilations/sec to Batch Requests/sec is unusually high. You suspect that the number of recompiles is causing the high processor time value. You need to improve the performance of the CK_Sales database.
What should you do?
A.
Turn off automatic updates of statistics for all tables in the CK_Sales database.
B.
Install an additional processor on Certkiller -DB01.
C.
Run the Database Engine Tuning Advisor.
D.
Use SQL Server Profiler to identify the stored procedures being recompiled.
Explanation:
You can use SQL Server Profiler to identify the stored procedures that are being recompiled. SQL Profiler will indicate which stored procedures are being recompiled and why each recompilation is occurring.
Incorrect Answers:
A: Turning off automatic updates of statistics for the tables may improve performance but it does not address the problem caused by the recompiles.
B: The high processor usage could be caused by the high number of recompiles. You should first reduce the number of recompiles before installing extra hardware on the server.
C: You can use the Database Engine Tuning Advisor to determine whether indexing and partitioning of a table would improve database performance but you cannot use it to identify which stored procedures are being recompiled.