Tag Archives: 70-450 Study Guide

70-450: PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008


QUESTION 1
You work as a database administrator at ABC.com. You are in the process of preparing the
deployment of a new database that will have 45 gigabytes storage space for the transaction log
file, and 280 gigabytes storage space for the database data file.
There are approximately six 120 GB disk drives available for the database in the storage array.
ABC.com contains a RAID controller that supports RAID levels 0, 1, 5 and 10. The disks are on
the RAID controller. You have received an instruction from the CIO to make sure that the
transaction log’s write performance runs at optimum. The CIO has also instructed you to make
sure that in the event of a drive failure, the database and transaction log files are protected.
To achieve this goal, you decide to configure a storage solution.
Which of the following actions should you take?

A. You should consider using a RAID 1 volume as well as a RAID 5 volume in your storage configuration.
B. You should consider using a RAID 1 volume as well as a RAID 10 volume in your storage configuration.
C. You should consider using a RAID 3 volume as well as a RAID 5 volume in your storage configuration.
D. You should consider using a RAID 1 volume as well as a RAID 3 volume in your storage configuration.

Answer: A

Explanation:


QUESTION 2
You work as a database administrator at ABC.com. ABC.com has a database server named ABCDB04
with a SQL Server 2008 instance that includes an extensive mission-critical database that is
constantly being used ABC-DB04 has a quad-core motherboard with four CPUs.
When it is reported that ABC-DB04 often encounters CPU pressure, you receive an instruction
from management to make sure that the accessible CPU cycles are not exhausted by online index rebuilds.
Which of the following actions should you take?

A. You should make use of the affinity I/O mask option.
B. You should make use of the optimize for ad hoc workloads option.
C. You should make use of the affinity mask option.
D. You should make use of the max degree of parallelism option.

Answer: D

Explanation:


QUESTION 3
You work as a database administrator at ABC.com. ABC.com has a database server named ABCDB01
with a SQL Server 2008 instance.
During routine monitoring on ABC-DB01, you discover that the amount of CXPACKET waits
experienced by the instance is low, while the amount of lazy writer waits is abundant.
You have been instructed to enhance the operation of the instance to ensure productivity.
Which of the following actions should you take?

A. You should consider setting up the Windows System Monitoring tool to better the performance.
B. You should consider setting up the Asynchronous database mirroring to better the performance.
C. You should consider using the SQLAGENT.OUT log to better the performance.
D. You should consider setting up the software non-uniform memory access (soft-NUMA) to better the performance.

Answer: D

Explanation:


QUESTION 4
You work as a database administrator at ABC.com. ABC.com has a database server named ABCDB01.
ABC-DB01 is configured with 4 quad-core processors, 80 gigabytes of RAM, and multiple
independent raid volumes.
You are in the process of using a transactional database on the instance. It is anticipated that the
transactional database will have a significant amount of INSERT, UPDATE, and DELETE
activities, which incorporates the creation of new tables.
You receive an instruction from management to minimize the contention in the storage allocation
structures so that database performance is optimized, and the disk bandwidth maximized.
Which of the following actions should you take?

A. You should consider enabling Server Auditing.
B. You should consider using multiple data files for the database.
C. You should consider using row-level compression.
D. You should consider using the checksum page verify option.

Answer: B

Explanation:


QUESTION 5
You work as a database administrator at ABC.com.
ABC.com has informed you that a new database, named ABCData, has to be installed on a SQL
Server 2008 instance. ABCData is made up of several schemas, of which one will host a
significant amount of read-only reference information. Information is regularly inserted and
updated on ABCData.
You have received instructions from the management to configure a physical database structure
that enhances the backup operation.
Which of the following actions should you take?

A. This can be accomplished by using multiple filegroups and a single log file to set up the database.
B. This can be accomplished by using caching on the multiple data files.
C. This can be accomplished by using multiple downstream servers to create the database.
D. This can be accomplished by using the Database Engine Tuning Advisor tool to create the database.

Answer: A

Explanation:


MCTS Training, MCITP Trainnig

Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com

70-450 Q & A / Study Guide

 

MCTS Training, MCITP Trainnig

Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com


QUESTION 1
You work as a database administrator at Certkingdom.com. You are in the process of preparing the
deployment of a new database that will have 45 gigabytes storage space for the transaction log
file, and 280 gigabytes storage space for the database data file.
There are approximately six 120 GB disk drives available for the database in the storage array.
Certkingdom.com contains a RAID controller that supports RAID levels 0, 1, 5 and 10. The disks are on
the RAID controller. You have received an instruction from the CIO to make sure that the
transaction log’s write performance runs at optimum. The CIO has also instructed you to make
sure that in the event of a drive failure, the database and transaction log files are protected.
To achieve this goal, you decide to configure a storage solution.
Which of the following actions should you take?

A. You should consider using a RAID 1 volume as well as a RAID 5 volume in your storage configuration.
B. You should consider using a RAID 1 volume as well as a RAID 10 volume in your storage configuration.
C. You should consider using a RAID 3 volume as well as a RAID 5 volume in your storage configuration.
D. You should consider using a RAID 1 volume as well as a RAID 3 volume in your storage configuration.

Answer: A

Explanation:


QUESTION 2
You work as a database administrator at Certkingdom.com. Certkingdom.com has a database server named ABCDB04
with a SQL Server 2008 instance that includes an extensive mission-critical database that is
constantly being used ABC-DB04 has a quad-core motherboard with four CPUs.
When it is reported that ABC-DB04 often encounters CPU pressure, you receive an instruction
from management to make sure that the accessible CPU cycles are not exhausted by online index rebuilds.
Which of the following actions should you take?

A. You should make use of the affinity I/O mask option.
B. You should make use of the optimize for ad hoc workloads option.
C. You should make use of the affinity mask option.
D. You should make use of the max degree of parallelism option.

Answer: D

Explanation:


QUESTION 3
You work as a database administrator at Certkingdom.com. Certkingdom.com has a database server named ABCDB01
with a SQL Server 2008 instance.
During routine monitoring on ABC-DB01, you discover that the amount of CXPACKET waits
experienced by the instance is low, while the amount of lazy writer waits is abundant.
You have been instructed to enhance the operation of the instance to ensure productivity.
Which of the following actions should you take?

A. You should consider setting up the Windows System Monitoring tool to better the performance.
B. You should consider setting up the Asynchronous database mirroring to better the performance.
C. You should consider using the SQLAGENT.OUT log to better the performance.
D. You should consider setting up the software non-uniform memory access (soft-NUMA) to better the performance.

Answer: D

Explanation:


QUESTION 4
You work as a database administrator at Certkingdom.com. Certkingdom.com has a database server named ABCDB01.
ABC-DB01 is configured with 4 quad-core processors, 80 gigabytes of RAM, and multiple
independent raid volumes.
You are in the process of using a transactional database on the instance. It is anticipated that the
transactional database will have a significant amount of INSERT, UPDATE, and DELETE
activities, which incorporates the creation of new tables.
You receive an instruction from management to minimize the contention in the storage allocation
structures so that database performance is optimized, and the disk bandwidth maximized.
Which of the following actions should you take?

A. You should consider enabling Server Auditing.
B. You should consider using multiple data files for the database.
C. You should consider using row-level compression.
D. You should consider using the checksum page verify option.

Answer: B

Explanation:


QUESTION 5
You work as a database administrator at Certkingdom.com.
Certkingdom.com has informed you that a new database, named ABCData, has to be installed on a SQL
Server 2008 instance. ABCData is made up of several schemas, of which one will host a
significant amount of read-only reference information. Information is regularly inserted and
updated on ABCData.
You have received instructions from the management to configure a physical database structure
that enhances the backup operation.
Which of the following actions should you take?

A. This can be accomplished by using multiple filegroups and a single log file to set up the database.
B. This can be accomplished by using caching on the multiple data files.
C. This can be accomplished by using multiple downstream servers to create the database.
D. This can be accomplished by using the Database Engine Tuning Advisor tool to create the database.

Answer: A

Explanation:

MCTS Training, MCITP Trainnig

Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com