|
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
|
