Question 1.
You work as the database administrator at ITCertKeys.com. The ITCertKeys.com network consists of a single Active Directory domain named ITCertKeys.com. All servers on the ITCertKeys.com network run Windows Server 2003 and all client computers run Windows XP Professional.

The ITCertKeys.com network contains a SQL Server 2005 database server named ITCertKeys-DB01. ITCertKeys-DB01 hosts a database named ITK_Sales that stores sales data for the company. ITCertKeys-DB01 also hosts two financial applications. You need to optimize the performance of ITCertKeys-DB01 and increase the performance of the two financial applications. You need to modify the SQL Server configuration to accomplish this goal.

What should you do?

A. Configure the max worker threads server option.
B. Configure the min memory per query server option.
C. Configure the max server memory server option.
D. Configure the min server memory server option.

Answer: C

Explanation: 
The max server memory option is used to specify the maximum amount of system memory that SQL Server 2005 is allowed to consume. Setting this option will ensure that the SQL Server 2005 does not consume all the available memory and can be used to ensure that sufficient memory is remains available for the other applications to run.

Incorrect Answers:
A: The max worker threads option is used to specify the number of processor threads that are used to supported users connected to SQL Server. This does not assist in optimizing performance for other applications.
B: The min memory per query option is used to specify the minimum amount of memory that is allocated to the execution of a query. This is useful if there are several concurrent queries but it does not assist in optimizing performance for other applications. 
D: The min server memory option is used to ensure that SQL Server 2005 does not release memory below the min server memory value once it is reached. This ensures that SQL Server 2005 always has sufficient memory but it will not ensure that the other applications have sufficient memory.

Reference:
Microsoft SQL Server 2005 Books Online (2007), Index: max server memory option
Microsoft SQL Server 2005 Books Online (2007), Index: min server memory option
Microsoft SQL Server 2005 Books Online (2007), Index: min memory per query option
Microsoft SQL Server 2005 Books Online (2007), Index: max worker threads option

Question 2.
You work as the database administrator at ITCertKeys.com. The ITCertKeys.com network consists of a single Active Directory domain named ITCertKeys.com. All servers on the ITCertKeys.com network run Windows Server 2003 and all client computers run Windows XP Professional.
The ITCertKeys.com network contains a SQL Server 2005 database server named ITCertKeys-DB01. ITCertKeys-DB01 hosts a database named ITK_Sales that stores sales data for the company. ITCertKeys-DB01 also hosts two financial applications. Several ITCertKeys.com users in the Sales department complain that queries against the ITK_Sales database perform very poorly at times. You use System Monitor to monitor the performance of by using ITCertKeys-DB01 and discover that SQL Server 2005 does not have sufficient memory to run efficiently. You need to ensure that SQL Server 2005 always has sufficient memory to run efficiently.

What should you do?

A. Decrease the min memory per query server option.
B. Increase the min server memory server option.
C. Increase the max worker threads server option.
D. Decrease the max server memory server option.

Answer: B

Explanation:
The min server memory option is used to ensure that SQL Server 2005 does not release memory below the min server memory value once it is reached. This ensures that SQL Server 2005 always has sufficient memory. By default, min server memory is set to 0.

Incorrect Answers:
A: The min memory per query option is used to specify the minimum amount of memory that is allocated to the execution of a query. This is useful if there are several concurrent queries. Decreasing this option will increase the possibility that the SQL Server 2005 will perform poorly.
C: The max worker threads option is used to specify the number of processor threads that are used to supported users connected to SQL Server. Increasing this option will not address the memory problem.
D: The max server memory option is used to specify the maximum amount of system memory that SQL Server 2005 is allowed to consume. Decreasing this option will increase the possibility that the SQL Server 2005 will perform poorly.

Reference:
Microsoft SQL Server 2005 Books Online (2007), Index: min server memory option
Microsoft SQL Server 2005 Books Online (2007), Index: min memory per query option
Microsoft SQL Server 2005 Books Online (2007), Index: max worker threads option
Microsoft SQL Server 2005 Books Online (2007), Index: max server memory option

Question 3.
You work as the database administrator at ITCertKeys.com. The ITCertKeys.com network consists of a single Active Directory domain named ITCertKeys.com. All servers on the ITCertKeys.com network run Windows Server 2003 and all client computers run Windows XP Professional. The ITCertKeys.com network contains a SQL Server 2005 database server named ITCertKeys-DB01. ITCertKeys-DB01 hosts a database named ITK_Sales that stores sales data for the company. The tables in the ITK_Sales database are shown in the following database diagram. 
 

The recent increase in database usage at the company has resulted in several ITCertKeys.com users complaining of timeouts when they try to retrieve sales orders from the ITK_Sales database. You need to determine whether partitioning the Orders table would improve database performance.

What should you do? (Each correct answer presents part of the solution. Choose TWO.)

A. Run Database Engine Tuning Advisor.
B. Create a performance log file to monitor SQL Server:Buffer Manager:Page reads/sec.
C. Run SQL Server Profiler to replay the trace file and the log file.
D. Use the SQL Server Profiler Tuning template to create a trace file.
E. Create a performance log file to monitor Logical Disk: Disk Read Bytes/sec

Answer: A, D

Explanation:
You can use the Database Engine Tuning Advisor to determine whether indexing and partitioning of a table would improve database performance. The Database Engine Tuning Advisor analyzes a workload file that you can create by running SQL Server Profiler and creating a trace based on the SQL Server Profiler Tuning template.

Incorrect Answers:
B: The SQL Server:Buffer Manager:Page reads/sec counter is used to monitor the read activity on an instance of SQL Server. It does not help you determine whether partitioning a table will improve database performance.
C: You can use the SQL Server Profiler to analyze a trace file and a log file to determine which queries are causing excessive resource utilization. However, this information does not help you determine whether partitioning a table will improve database performance.
E: The Logical Disk: Disk Read Bytes/sec counter is used to monitor the read activity on a logical disk. It does not help you determine whether partitioning a table will improve database performance.

Reference:
Microsoft SQL Server 2005 Books Online (2007), Index: Database Engine Tuning Advisor [SQL Server]
Microsoft SQL Server 2005 Books Online (2007), Index: SQL Server Profiler
Microsoft SQL Server 2005 Books Online (2007), Index: performance counters [SQL Server]
Microsoft SQL Server 2005 Books Online (2007), Index: max server memory option 

Question 4.
You work as the database administrator at ITCertKeys.com. The ITCertKeys.com network consists of a single Active Directory domain named ITCertKeys.com. All servers on the ITCertKeys.com network run Windows Server 2003 and all client computers run Windows XP Professional. The ITCertKeys.com network contains a SQL Server 2005 database server named
ITCertKeys-DB01. ITCertKeys-DB01 hosts a database named ITK_Sales that stores sales data for the company. The tables in the ITK_Sales database are shown in the following database diagram.
 

Several ITCertKeys.com users start complaining of slow response time when they run queries against the ITK_Sales database. You run the sys.dm_os_schedulers view on ITCertKeys-DB01 and discover that the runnable_tasks_count is consistently at or above ten. You run System Monitor on ITCertKeys-DB01 and receive the output as shown in the exhibit. 
 

You need to improve database performance.
What should you do?

A. Increase the Random Access Memory (RAM).
B. Add an additional processor.
C. Upgrade the disk subsystem.
D. Use the Database Engine Tuning Advisor to suggest new indexes.

Answer: B

Explanation: 
The Processor:% Processor Time counter in the exhibit indicates that the processor is often running at over 80%. This indicates that the processor is causing a bottleneck. Adding an additional processor to the system will improve overall system performance.

Incorrect Answers:
A: The Memory:Avg. Disk Queue Length counter in the exhibit is low. This indicates that the RAM is not causing a bottleneck. Therefore, adding additional RAM to the system will not improve overall system performance.
C: The PhysicalDisk:Pages/sec counter in the exhibit is low. This indicates that the disk subsystem is not causing a bottleneck. Therefore, upgrading the disk subsystem will not improve overall system performance.
D: The Processor:% Processor Time counter in the exhibit indicates that the processor is often running at over 80%. This indicates that the processor is causing a bottleneck. Indexing the tables will not reduce the load on the processor and will not improve overall system performance.

Reference:
Microsoft SQL Server 2005 Books Online (2007), Index: CPU [SQL Server], monitoring 
Microsoft SQL Server 2005 Books Online (2007), Index: Database Engine Tuning Advisor [SQL Server]

Question 5.
You work as the database administrator at ITCertKeys.com. The ITCertKeys.com network consists of a single Active Directory domain named ITCertKeys.com. All servers on the ITCertKeys.com network run Windows Server 2003 and all client computers run Windows XP Professional. You use a client computer named ITCertKeys-WS444. The ITCertKeys.com network contains a SQL Server 2005 database server named ITCertKeys-DB01. ITCertKeys-DB01 hosts a database named ITK_Products. The recent increase in database usage at the company has resulted in several ITCertKeys.com users complaining of poor query performance and query timeouts try to retrieve data from the ITK_Products database. You need to determine the cause of the poor performance. You need to minimize the effect of your monitoring strategy on system performance.

What should you do?

A. Run the SQL Server Profiler from ITCertKeys-WS444 and create a trace that monitors 
    ITCertKeys-DB01. Save the trace file to a table.
B. Run the Performance tool on ITCertKeys-DB01 to create a log that monitors ITCertKeys –
    DB01. Save the log file to a folder on ITCertKeys-DB01.
C. Run the SQL Server Profiler on ITCertKeys-DB01 and create a trace that monitors ITCertKeys 
    -DB01. Save the trace file to a table.
D. Run the Performance tool on ITCertKeys-WS444 to create a log that monitors ITCertKeys –
    DB01. Save the log file to a folder on ITCertKeys-WS444.

Answer: D

Explanation: 
You can use the Windows Performance tool on a client computer to monitor system performance on the database server without negatively affecting the performance of the database server. You can use the Windows Performance tool to create a log on the client computer that you can analyze to determine the cause of a resource bottleneck.

Incorrect Answers:
A, C: SQL Server Profiler is used to trace SQL Server events. It is not used to troubleshoot resource bottlenecks.
B: You should run the Windows Performance tool from you client computer and save the log file to your computer. This will reduce the impact of monitoring on the database server.

Reference:
Microsoft SQL Server 2005 Books Online (2007), Index: CPU [SQL Server], monitoring
Microsoft SQL Server 2005 Books Online (2007), Index: Database Engine Tuning Advisor [SQL Server]

Question 6.
You work as the database administrator at ITCertKeys.com. The ITCertKeys.com network consists of a single Active Directory domain named ITCertKeys.com. All servers on the ITCertKeys.com network run Windows Server 2003 and all client computers run Windows XP Professional. The ITCertKeys.com network contains a SQL Server 2005 database server named ITCertKeys-DB01. ITCertKeys-DB01 is running a default SQL Server instance and a named instance named ITK_Sales. ITCertKeys.com users complain that performance for both instances has deteriorated over the last six months. You want to determine the memory and processor resource requirements of the two instances as well as any resource conflicts between the two instances.

What should you do?

A. Use SQL Server Profiler to create a trace and capture typical activity for each instance with 
    both instances running.
B. Use Windows Performance to create counter log that captures performance counters for each 
    instance with both instances running.
C. Use Windows Performance to create counter log that captures performance counters for each 
    instance separately with each instance running on its own.
D. Use SQL Server Profiler to create a trace and capture typical activity for each instance 
    separately with each instance running on its own.

Answer: B

Explanation: 
You can use the Windows Performance to monitor system performance on the database server but you must capture the counters with both instances running at the same time to collect accurate data. 

Incorrect Answers:
A, D: SQL Server Profiler is used to trace SQL Server events. It is not used to troubleshoot memory and processor resource bottlenecks.
C: Multiple SQL Server instances share system resources and interact with each other; therefore you must capture the counters with both instances running at the same time to collect accurate data.

Question 7.
DRAG DROP
You work as the database administrator at ITCertKeys.com. The ITCertKeys.com network consists of a single Active Directory domain named ITCertKeys.com. All servers on the ITCertKeys.com network run Windows Server2003. The ITCertKeys.com network contains a SQL Server 2005 on a server named ITCertKeys-DB01. ITCertKeys-DB01 has five 120 GB hard disk drives. Two of the hard disks are configured as a mirrored volume that contains the operating system. The other three hard disks are configured as a RAID-5 volume. All database files are located on the RAID-5 volume while the transaction log files are located on the mirrored volume. ITCertKeys-DB01 is running a default SQL Server instance and a named instance named ITK_DB. The default instance hosts an Online Transaction Processing (OLTP) database named ITK_Sales while the named instance hosts historical data used for reporting. ITCertKeys.com users complain that database performance is deteriorating. You suspect that a disk problem might be causing the bottleneck. You want to determine which SQL Server instance is using the most disk time. 

What should you do? (To answer, select the appropriate performance counters that you should monitor in the left pane and drag them to the right pane.)
 

Answer:
 

Explanation:
The SQL Server:Buffer Manager object is used to monitor resource usage for each SQL Server instance. You need to monitor the SQL Server:Buffer Manager:Page reads/sec counter to determine the number of page reads performed by each instance and the SQL Server:Buffer Manager:Page writes/sec counter to determine the number of page writes performed by each instance.

Incorrect Answers:
The Logical Disk:Disk Write Bytes/sec and Logical Disk:Disk Read Bytes/sec counters can be used to monitor the total number of disk reads or writes per second for a volume. Neither can be used to determine instance is using the most disk time. The PhysicalDisk counters can be used to monitor a RAID volume but this cannot used to determine instance is using the most disk time.

Question 8.
You work as the database administrator at ITCertKeys.com. The ITCertKeys.com network consists of a single Active Directory domain named ITCertKeys.com. All servers on the ITCertKeys.com network run Windows Server 2003 and all client computers run Windows XP Professional. The ITCertKeys.com network contains a SQL Server 2005 database server named ITCertKeys-DB01 that hosts a database named ITK_Sales. The ITK_Sales database stores sales data for the company. ITCertKeys.com users complain that the performance of the ITK_Sales database has deteriorated over the last few weeks. You use System Monitor to monitor the performance of ITCertKeys-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 ITK_Sales database.

What should you do?

A. Turn off automatic updates of statistics for all tables in the ITK_Sales database.
B. Install an additional processor on ITCertKeys-DB01.
C. Run the Database Engine Tuning Advisor.
D. Use SQL Server Profiler to identify the stored procedures being recompiled.

Answer: D

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.

Question 9.
You work as the database administrator at ITCertKeys.com. The ITCertKeys.com network consists of a single Active Directory domain named ITCertKeys.com. All servers on the ITCertKeys.com network run Windows Server 2003 and all client computers run Windows XP Professional. The ITCertKeys.com network contains a  SQL Server 2005 database server named ITCertKeys-DB01 that has a single 120 GB hard disk, 1 GB of random access memory (RAM), and has a single 2.8 GHz Pentium 4 processor. ITCertKeys-DB01 hosts a database named ITK_Sales that stores sales data for the company. ITCertKeys.com users complain that the performance of the ITK_Sales database has deteriorated over the last six months. You use System Monitor to monitor the performance of ITCertKeys-DB01 and receive the output as shown in the exhibit. 
 

You need to improve server performance.

What should you do?

A. Add an additional processor.
B. Add more Random Access Memory (RAM).
C. Upgrade the disk subsystem.
D. Install a second hard disk and split data between the disks.

Answer: B

Explanation: 
A value of 62.8 for the SQL Server: Buffer Manager: Buffer Cache Hit Ratio counter indicates that the system requires more RAM. This counter should be above 90. A lower value indicates that you need more RAM.

Incorrect Answers:
A: A value of 43.8% for the Processor:% Processor Time and System:%Total Processor Time counters are well below the 80% threshold that indicates a processor problem.
C, D: A value of 1.4 for the PhysicalDisk:Avg. Disk Queue Length is close to indicating a bottleneck, but this is probably caused by insufficient memory than by hard disk performance. Disk activity is likely to drop when you add more memory.

Question 10.
DRAG DROP
You work as the database administrator at ITCertKeys.com. The ITCertKeys.com network consists of a single Active Directory domain named ITCertKeys.com. All servers on the ITCertKeys.com network run Windows Server 2003 and all client computers run Windows XP Professional. The ITCertKeys.com network contains a SQL Server 2005 database server named ITCertKeys-DB01 that has a RAID-5 disk array, 2 GB of random access memory (RAM), and has a single 3.2 GHz Xeon processor. ITCertKeys-DB01 hosts a database named ITK_Sales that stores sales data for the company. ITCertKeys.com users complain that the performance of the ITK_Sales database becomes sluggish at certain times of the day. You use System Monitor to monitor the performance of ITCertKeys-DB01 during a typical day and compare them to baseline results collected shortly after the database was deployed. You identify times during the day when the hardware resources on ITCertKeys-DB01 become a performance bottleneck. You need to determine which database activities are causing the hardware bottlenecks. You want to accomplish this using minimal amount of administrator effort and do not want to consume any more system resources than necessary. 

What should you do? (To answers, select the appropriate actions from the pane on the left and place them in the pane on the right.) 
 

Answer:
 

Explanation:
You should capture a SQL Profiler trace and Windows counter log over the same time period. You can open the trace in SQL Server Profiler and the counter log in the Windows Performance utility at the same time and compare the two.

	Web www.certsbraindumps.com