REAL QUESTIONS SUBMIT MATERIAL ADVERTISE
Braindumps

Microsoft

Cisco

Citrix

CIW

CompTia

CWNA

Apple

Adobe

HP

Legato

Exin

Filemaker

Brocade

Ericsson

TIA

Veritas

ISEB

SCP

IISFA

ISM

OMG

Apc

Mile2

Foundry

Huawei

McData

Symantec

TeraData

RedHat

Solar Winds

Blue Coat

Riverbed

 

 
 
Click on name of dumper to view the dump
 
Adrial
 
 

70-444
PRO: Optimizing and Maintaining a
Database Administration Solution by Using
Microsoft SQL Server 2005

 

Thanx to www.exams.ws for providing helpful material 

 

QUESTION 1:
You work as the database administrator at Abc .com. The Abc .com network consists of a single Active Directory domain named Abc .com. All servers on the Abc .com network run Windows Server 2003 and all client computers run Windows XP Professional. The Abc .com network contains a SQL Server 2005 database server named Abc -DB01. Abc -DB01 hosts a database named Abc_Sales that stores sales data for the company. Abc -DB01 also hosts two financial applications. You need to optimize the performance of Abc -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 Abc .com. The Abc .com network consists of a single Active Directory domain named Abc .com. All servers on the Abc .com network run Windows Server 2003 and all client computers run Windows XP Professional. The Abc .com network contains a SQL Server 2005 database server named Abc -DB01. Abc -DB01 hosts a database named Abc_Sales that stores sales data for the company. Abc -DB01 also hosts two financial applications. Several Abc .com users in the Sales department complain that queries against the Abc_Sales database perform very poorly at times. You use System Monitor to monitor the performance of by using Abc -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 Abc .com. The Abc .com network consists of a single Active Directory domain named Abc .com. All servers on the Abc .com network run Windows Server 2003 and all client computers run Windows XP Professional. The Abc .com network contains a SQL Server 2005 database server named Abc -DB01. Abc -DB01 hosts a database named Abc_Sales that stores sales data for the company. The tables in the Abc_Sales database are shown in the following database diagram  
The recent increase in database usage at the company has resulted in several Abc .com users complaining of timeouts when they try to retrieve sales orders from the Abc_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 Abc .com. The Abc .com network consists of a single Active Directory domain named Abc .com. All servers on the Abc .com network run Windows Server 2003 and all client computers run Windows XP Professional. The Abc .com network contains a SQL Server 2005 database server named Abc -DB01. Abc -DB01 hosts a database named Abc_Sales that stores sales data for the company. The tables in the Abc_Sales database are shown in the following database diagram.
Several Abc .com users start complaining of slow response time when they run queries against the Abc_Sales database. You run the sys.dm_os_schedulers view on Abc -DB01 and discover that the runnable_tasks_count is consistently at or above ten. You run System Monitor on Abc -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 Abc .com. The Abc .com network consists of a single Active Directory domain named Abc .com. All servers on the Abc .com network run Windows Server 2003 and all client computers run Windows XP Professional. You use a client computer named Abc -WS444. The Abc .com network contains a SQL Server 2005 database server named Abc -DB01. Abc -DB01 hosts a database named Abc_Products. The recent increase in database usage at the company has resulted in several Abc .com users complaining of poor query performance and query timeouts try to retrieve data from the Abc_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 Abc -WS444 and create a trace that monitors Abc -DB01. Save the trace file to a table.
B. Run the Performance tool on Abc -DB01 to create a log that monitors Abc -DB01. Save the log file to a folder on Abc -DB01.
C. Run the SQL Server Profiler on Abc -DB01 and create a trace that monitors Abc -DB01. Save the trace file to a table.
D. Run the Performance tool on Abc -WS444 to create a log that monitors Abc -DB01. Save the log file to a folder on Abc -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 Abc .com. The Abc .com network consists of a single Active Directory domain named Abc .com. All servers on the Abc .com network run Windows Server 2003 and all client computers run Windows XP Professional. The Abc .com network contains a SQL Server 2005 database server named Abc -DB01. Abc -DB01 is running a default SQL Server instance and a named instance named Abc_Sales. Abc .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:
You work as the database administrator at Abc .com. The Abc .com network consists of a single Active Directory domain named Abc .com. All servers on the Abc .com network run Windows Server 2003 and all client computers run Windows XP Professional. The Abc .com network contains a SQL Server 2005 database server named Abc -DB01 that hosts a database named Abc_Sales. The Abc_Sales database stores sales data for the company. Abc .com users complain that the performance of the Abc_Sales database has deteriorated over the last few weeks. You use System Monitor to monitor the performance of Abc -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 Abc_Sales database. What should you do?
A. Turn off automatic updates of statistics for all tables in the Abc_Sales database.
B. Install an additional processor on Abc -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 8:
You work as the database administrator at Abc .com. The Abc .com network consists of a single Active Directory domain named Abc .com. All servers on the Abc .com network run Windows Server 2003 and all client computers run Windows XP Professional. The Abc .com network contains a SQL Server 2005 database server named Abc -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. Abc -DB01 hosts a database named Abc_Sales that stores sales data for the company. Abc .com users complain that the performance of the Abc_Sales database has deteriorated over the last six months. You use System Monitor to monitor the performance of Abc -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 9:
You work as the database administrator at Abc .com. The Abc .com network consists of a single Active Directory domain named Abc .com. All servers on the Abc .com network run Windows Server 2003 and all client computers run Windows XP Professional. The Abc .com network contains a SQL Server 2005 database server named Abc -DB01 that hosts a database named Abc_Sales. The Abc_Sales database stores sales data for the company. Abc .com users complain that the performance of the Abc_Sales database has deteriorated over the last six months. You use System Monitor to monitor the performance of Abc -DB01 and compare it to a baseline that was prepared six months ago. You notice that SQL Server:Buffer Manager:Buffer Cache Hit Ratio counter is consistently lower than the original baseline. You need to improve server performance. What should you do?
A. Add an additional processor.
B. Install a second hard disk.
C. Add more Random Access Memory (RAM).
D. Upgrade the disk controllers.
Answer: C
Explanation:
A lower value for the SQL Server:Buffer Manager:Buffer Cache Hit Ratio counter indicates that the system requires more RAM. This counter should be about 90 or higher. A lower value indicates that you need more RAM.
Incorrect Answers:
A: The processor and disk subsystem has no effect on the SQL Server:Buffer Manager:Buffer Cache Hit Ratio counter. Therefore a lower SQL Server:Buffer Manager:Buffer Cache Hit Ratio count is not related to a processor or disk subsystem problem.


70-444

 

 

Braindumps Real exam questions and verified answers - 100% passing guarantee - cheap prices.

 

Free brain dumps Braindumps, notes, books for free

 

Braindumps and Exams - Instant download real exam questions - Passing guarantee.

Follow us on FaceBook
Braindumps on Facebook
 
 
 
 
 

CheckPoint

Linux

Novell

DB/2

Network Appliance

EC-Council

Nortel

McAfee

Juniper

ISACA

PMI

Sybase

EMC

HDI

SNIA

ISC

Sair

IBM

Lotus

Exam Express

3COM

BICSI

DeLL

Enterasys

Extreme Networks

Guidance Software

Computer Associates

Network General

SAS Institute

Alcatel Lucent

SeeBeyond

TruSecure

Polycom

Hyperion

Hitachi

Nokia

Fortinet

Vmware

Fujitsu

Tibco

Intel

PostgreSQLCE

BusinessObjects

RESSoftware

BlackBerry

AccessData

ICDL

Isilon

SAP

The Open Group

ACSM

Altiris

Avaya

Cognos

F5

Genesys

SDI

ACI

ASQ

Google

H3C

HIPAA

HRCI

SOA

IIBA

Zend