Transcript Maintaining a Microsoft® SQL Server® 2008 Database

Exam
70-450
QUESTION
CertKiller.com has hired you as a database administrator for
their network. Your duties include administering the SQL
Server 2008 servers.
You receive an instruction from the CIO to implement page
compression on numerous partitions in order to save
space. You need to determine what effect implementing
compression will have on the SQL Server components.
What should you identify? (Choose all that apply.)
ANSWER
A. Restore operations will not be affected by implementing
page compression.
B. Backup operations will not be affected by implementing
page compression.
C. Bulk export operations will not be affected by
implementing page compression.
D. Bulk import operations will not be affected by
implementing page compression.
E. Existing query plans will not be affected by implementing
page compression.
F. Log shipping will not be affected by implementing page
compression.
Question
You work as a database administrator at CertKiller.com. You
are responsible for administering a SQL Server 2008
instance.
You are currently planning the deployment of a new
database. The database will have the subsequent capacity
requirements:45 GB for the transaction log file280 GB for
the database data file
There are approximately six 120 GB disk drives available for
the database in the storage array. CertKiller.com contains
a RAID controller that supports RAID levels 0, 1, 5 and 10.
Question - Continued
The disks are on the RAID controller. You receive an
instruction from the CIO that is as follows: The transaction
log's write performance should be maximized. In the event
of a drive failure, it is expected that the database and
transaction log files is protected.
You then decide to plan a storage system. You need to
determine the storage configuration that will accomplish
this.
What should you identify?
Answer
A. You should consider using a RAID 1 volume as well as a
RAID 5 volume.
B. You should consider using a RAID 1 volume as well as a
RAID 10 volume.
C. You should consider using a RAID 3 volume as well as a
RAID 5 volume.
D. You should consider using a RAID 1 volume as well as a
RAID 3 volume.
Question
You work as a database administrator at CertKiller.com. You
are responsible for administering the SQL Server 2008
infrastructure for CertKiller.com.
During routine monitoring you discover that the instance
experiences a low number of CXPACKET waits as well as a
large number of lazy writer waits. To ensure productivity
you need to better the performance of the instance.
What should you do?
Answer
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.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL
Server 2008 for hosting databases.
You receive an instruction from management to make sure
that your department is prepared in the event of a natural
disaster. You thus decide to create a disaster readiness
plan. You need to determine the appropriate actions that
should be included in your disaster readiness plan.
What should you do? (Choose all that apply.)
Answer
A. You should consider testing and using RAID 5 for the
volume that contains the database files.
B. You should consider testing and using RAID 1 for the
volume that has the operating system.
C. You should consider maintaining the system logs in a
secure manner.
D. You should consider performing the recovery of a
database(s) on another SQL server.
E. You should consider testing and using RAID 5 for the
volume that has the operating system.
F. You should consider testing and using RAID 1 for the
volume that contains the database files.
Question
CertKiller.com has employed you as a database
administrator. CertKiller.com currently makes use of
Microsoft SQL Server 2008 for hosting databases. You are
responsible for administering a database server named
CERTKILLER-DB01.
You are in the process of enabling FILESTREAM on a volume
on CERTKILLER-DB01. You have received instruction from
the CIO to ensure that the volume store documents in
compressed form. You notice that volume F:\ is not
formatted. You need to make sure that the volume will be
able to store documents in a compressed form.
What should you do?
Answer
• A. You should execute the format F: /FS:NTFS
/V:BLOBContainer/A:8192 /C command.
• B. You should execute the format F: /FS:NTFS
/V:BLOBContainer/A:8192 command.
• C. You should execute the format F: /FS:NTFS
/V:BLOBContainer/A:4096 command.
• D. You should execute the format F: /FS:NTFS
/V:BLOBContainer/A:4096 /C command.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL
Server 2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
with an instance that hosts a database named CK_DATA.
During the course of the day you receive instruction from
CertKiller.com to design a RAID solution for the CK_DATA
database which has high read/write activity. CertKiller.com
wants you to ensure that the I/O performance of
CERTKILLER-DB01 is improved whilst ensuring that there
is fault tolerance for the database. CertKiller.com wants
you to make use of the existing resources as funds are
tied to other projects.
What should you do?
Answer
A. You should consider making use of a RAID 5 solution.
B. You should consider making use of a RAID 10 solution.
C. You should consider making use of a RAID 1 solution.
D. You should consider making use of a RAID 0 solution.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of
Microsoft SQL Server 2008 for hosting databases. You are
responsible for administering a
database server named CERTKILLER-DB01 with an instance
that hosts a database named
CK_STAFF which is hosted on a RAID 5 volume. During the
course of the day you receive
instruction from CertKiller.com to change the default size of
the allocation unit which is set at 4096
bytes. CertKiller.com wants you to accomplish the task
which having the RAID 5 volume data
assigned to Drive E: to retain is existing volume name.
What should you do?
Answer
A. You should consider making use of the format E: /A:4096
/V:Data command.
B. You should consider making use of the format E: /A:4K
/V:Data command.
C. You should consider making use of the format E: /A: 64K
/V:Data command.
D. You should consider making use of the format E /A:65536
/V:Data command.
Question
• You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL Server
2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
with an instance that hosts a database named CK_DATA
which is used by an online portal for storing data. The
portal is expected to receive large numbers of queries
which will require high availability for the data.
CertKiller.com wants you to meet the company
requirements below: CertKiller.com wants to have the SQL
Server 2008 configuration optimized to ensure the
database remains intact even when a hard disk
fails.CertKiller.com wants to have the data recovery
method to be reliable and provide faster recovery if a hard
disk fails whilst ensuring database performance is
optimized.CertKiller.com wants to have the databases
available even when the operating system (OS) disk fails.
• What should you do?
Answer
A. You should consider making use of RAID 1 for the
operating system and Transaction Logs; and
RAID5 for the SQL Server files and filegroups.
B. You should consider making use of RAID 0 for the
operating system and Transaction Logs; and
RAID 5 for the SQL Server files and filegroups.
C. You should consider making use of RAID 0 for the
operating system; RAID 1 for the
Transaction Logs; and RAID 5 for the SQL Server files and
filegroups.
D. You should consider making use of the same RAID 1 for
the operating system and Transaction
Logs; and RAID 5 for the SQL Server files and filegroups.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL Server
2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
with an instance that hosts a database named CK_SALES.
During the course of the day you receive instruction from
CertKiller.com to reconfigure the CK_SALES database for
placement on the disk subsystem. CertKiller.com wants
you to place the database on the fastest I/O subsystem
whilst ensuring user data is not lost when a disk failure
occurs.
• What should you do?
Answer
A. You should consider placing the CK_SALES database on
the same RAID 1 disk as the
operating system.
B. You should consider placing the CK_SALES database on
the same RAID 0 disk as the
operating system.
C. You should consider placing the CK_SALES database on a
separate RAID 0 solution.
D. You should consider placing the CK_SALES database on a
separate RAID 5 solution.
Question
• You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL Server
2008 for hosting databases. You are responsible for
administering a Microsoft 70-450: Practice Exam "Pass Any
Exam. Any Time." - www.actualtests.com 9 ctualTests.com
Question continues
• database server named CERTKILLER-DB01 with an
instance that hosts a database named CK_SALES. During
the course of the day you receive instruction from
CertKiller.com to deploy a new CK_PRODUCTS database
which should meet the capacity requirements set below:
CERTKILLER-DB01 will require 175 GB for the database
data file.CERTKILLER-DB01 will require 60 GB for the
transaction log. The CERTKILLER-DB01 storage array has
six 200 GB disk drives available for the database which are
attached to a RAID controller supporting RAID levels 0, 1,
5 and 10. CertKiller.com wants to have the write
performance of the transaction log maximized whilst
having the database and transaction log protected from
drive failure by designing the storage system.
• What should you do?
Answer
• A. You should consider making use of a RAID 0 volume
and RAID 5 volume.
• B. You should consider making use of a RAID 1 volume
and RAID 5 volume.
• C. You should consider making use of a single RAID 5
volume.
• D. You should consider making use of a single RAID 10
volume.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL
Server 2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
with an instance that hosts a database named CK_SALES.
CERTKILLER-DB01 hardware configuration is shown below:
CERTKILLER-DB01 has eight quad-core
processors.CERTKILLER-DB01 has 128-GB RAM.
During the course of the day you discover that ERTKILLERDB01 is experiencing a low number of CXPACKET waits
whilst experiencing a large number of lazy writer waits.
CertKiller.com wants you to optimize the performance of
CERTKILLER-DB01.
• What should you do?
Answer
A. You should consider having the software non-uniform
memory access (soft-NUMA) configured.
B. You should consider having an increase configured in the
maximum degree of parallelism
option.
C. You should consider having the Resource Governor
configured.
D. You should consider having the Windows System
Resource Manager configured.
Question
You are employed as a database administrator at
CertKiller.com. You are responsible for administering a
SQL Server 2008 database server named CERTKILLERDB01 that consists of four processors.
CERTKILLER-DB01 contains numerous databases which
include the databases named CK_PRODUCTS and
CK_RESEARCH. The subsequent rules are applied
CERTKILLERDB01: Four processors should be used for
operations on CK_PRODUCTS.Two processors should be
used by the CK_App application for queries on the tables
in CK_RESEARCH.Four processors should be used for all
imports, exports, queries or operations on the other
databases located on CERTKILLER-DB01.
What should you do? (Choose all that apply)
Answer
• A. This can be accomplished using MAXDOP in order to set
the processor number to four.
• B. This can be accomplished using MAXDOP in order to set
the processor number to two.
• C. This can be accomplished using the max degree of
parallelism option and setting the value to 4.
• D. This can be accomplished using MAXDOP with the
default value.
• E. This can be accomplished using the max degree of
parallelism option and setting the value to 2.
Question
You are employed as a database administrator at
CertKiller.com. CertKiller.com currently makes use of
Microsoft SQL Server 2008 for hosting databases. You are
responsible for administering a database server named
CERTKILLER-DB01 hosts five databases.
CERTKILLER-DB01 contains two processors. You have
Received instruction from the CIO to make use of more than
one processor for parallelism. You thus set the Show
Advanced Options to 1. You then write the subsequent code:
sp_configure 'max degree of parallelism', 6;
GO
RECONFIGURE WITH OVERRIDE;
GO
You need to determine what the result will be when you
execute the code above.
What should you identify?
Answer
• A. It will result in four processors being used.
• B. It will result in three processors being used.
• C. It will result in two processors being used.
• D. It will result in one processor being used.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL
Server 2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
with an instance that hosts a database named CK_SALES.
CertKiller.com requires information about the system
resources like the amount of memory available and
consumed by CERTKILLER-DB01.
What should you do?
Answer
A. You should consider making use of the
sys.dm_os_memory_nodes dynamic management
view.
B. You should consider making use of the
sys.dm_os_sys_info dynamic management view.
C. You should consider making use of the sys.dm_os_stacks
dynamic management view.
D. You should consider making use of the
sys.dm_os_process_memory dynamic management
view.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL
Server 2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
with an instance that hosts a database named
CK_ORDERS. During the course of the day you receive
instruction from CertKiller.com to select mwhat the
recommended size of the allocation unit should be when
your database is expected to have a high number of
transactions.
What should you do?
Answer
• A. You should consider having the allocation unit size 64 K.
• B. You should consider having the allocation unit size 32 K.
• C. You should consider having the allocation unit size
4096.
• D. You should consider having the allocation unit size
8192.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL
Server 2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
containing four processors with an instance that hosts a
database named CK_SALES. During the course of the day
CertKiller.com decided to configure the MAX Degree of
Parallelism option set to 2. You have later created an index
making use of the MAXDOP option and set the number of
processors to 3. CertKiller.com wants you to determine
what the results would be?
Answer
• A. The current configuration would make use of one
processor only.
• B. The current configuration would make use of two
processors by the default setting of Max
• Degree of Parallelism option overriding the MAXDOP
option.
• C. The current configuration would make use of four
processors only.
• D. The current configuration would make use of three
processors only.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL
Server 2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
containing multiple processors and has an instance that
hosts a database named CK_SALES. During the course of
the day you receive instruction from CertKiller.com to
create a large volume of indexes and delete older indexes
whilst optimizing CPU performance on CERTKILLER-DB01.
What should you do?
Answer
A. You should consider making use of the MAXDOP index
option in the index statement.
B. You should consider making use of the MAXDOP index
option with the ALTER INDEX
REORGANIZE statement.
C. You should consider setting the Max Degree of
Parallelism option to 0.
D. You should consider setting the Max Degree of
Parallelism option to 1.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL
Server 2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
with an instance that hosts a database named CK_SALES.
CERTKILLER-DB01 has four quad-core processors and
regularly experiences CPU pressure. The users at
CertKiller.com use the large mission critical database
frequently, which resides on CERTKILLER-DB01. During
the course of the day you receive an instruction from the
CIO to make sure that the online index rebuilds does not
utilize all thye CPU cycles that are available.
What should you do?
Answer
A. You should consider having the max degree of
parallelism option configured.
B. You should consider running the Netsh utility.
C. You should consider using the Hyper-V feature on the
quad-core servers.
D. You should consider configuring the Background
Intelligent Transfer Service (BITS) server
extensions.
Question
You work as a database administrator at CertKiller.com.
Your duties include administrating a SQL Server 2008
instance. You are currently planning to deploy a new
database named CK_Data to the 2008 instance.
CK_Data will have multiple schemas as well as being
subjected to regular inserts and updates. A large amount
of read-only reference information will be hosted on one of
the schemas. You have received instructions from the
management to set up a physical database structure to
optimize the backup performance.
What should you do?
Answer
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.
Question
You are employed as the database administrator at
CertKiller.com. CertKiller.com currently makesuse of
Microsoft SQL Server 2008 for hosting databases. You are
responsible for administering adatabase server named
CERTKILLER-DB01.
CERTKILLER-DB01 is configured with 1024 MB of RAM, 80
GB system drive with SQL Serverinstalled as well as a
single Core2 Duo 1.6 GHz processor. CERTKILLER-DB01
hosts a database named CK_DATA. CK_DATA contains:
One primary data file Two secondary data files One
transaction log file.
During routine monitoring you discover that the hard drive
has only 2 GB of free space available. You notice that the
performance of CK_DATA has degraded. You have received
instruction from the CIO to optimize the performance of
CK_DATA.
What should you do?
Answer
A. Your best option would be to increase the memory to
2048 MB.
B. Your best option would be to add additional hard drives
as well as moving the database files
across the hard drives.
C. Your best option would be to compress the hard drive in
order to create more space.
D. Your best option would be to add a new processor to
CERTKILLER-DB01.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL
Server 2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
hosts a database named CK_DATA.
You are in the process of adding additional tables to
CK_DATA in order to support the new application. The
additional tables will BLOBs that is greater than 1 MB. You
need to maximize the read access. At present the
application makes use of a middle tier for application logic.
You need to determine the data type that needs to be
configured for CK_DATA.
What should you do?
Answer
A. You should consider configuring the varbinary(max) data
that is stored in CK_DATA.
B. You should consider configuring the varchar(max) data
using FILESTREAM storage on an
NTFS partition.
C. You should consider configuring the varchar(max) data
stored in CK_DATA.
D. You should consider configuring the varbinary(max) data
using FILESTREAM storage on an
NTFS partition.
Question
You work as a database administrator at CertKiller.com. You
are responsible for a Microsoft SQL Server 2008 server
named CERTKILLER-DB01. CERTKILLER-DB01 contains
two hard drives named Drive B and Drive C. Both these
drives have at least 50 GB of free space available.
CERTKILLER-DB01 has one transaction log file and three
data files that contain large indexes. You receive an
instruction from the CIO to optimize performance of the
database. The data files as well as the transaction files is
stored in currently stored on Drive A. Drive A also contains
the system drive where CERTKILLER-DB01 is installed.
Due to budgetary constraints you are unable to add
additional hard drives on CERTKILLER-DB01.
What should you do?
Answer
A. This can be accomplished by installing a new SQL Server
instance on CERTKILLER-DB01.
Thereafter the database should be moved to the new SQL
Server instance.
B. This can be accomplished by placing the transaction log
file on Drive B and keeping the
database files on the same location.
C. This can be accomplished by moving the largest data file
from Drive A to Drive B and leaving
the remaining files in the same location.
D. This can be accomplished by moving the files to Drive B.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL
Server 2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
with an instance that hosts a database named CK_SALES
which is accessed frequently by network users updating
the CK_SALES database records for online users.
CertKiller.com wants you to monitor the transaction log
activities being performed on CERTKILLER-DB01.
What should you do?
Answer
A. You should consider making use of the SQL Server: SQL
Statistics object.
B. You should consider making use of the SQL Server:
Workload Group Stats object.
C. You should consider making use of the SQL Server:
Databases object.
D. You should consider making use of the SQL Server:
General Statistics object.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL Server
2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
with an instance that hosts a database named CK_SALES
which is used for an online application generating large
volumes of transactions on a daily basis including update
and insert operations. During the course of the day you
receive instruction from CertKiller.com to make use of a
number of indexes for update and insert operations whilst
optimizing the indexing operations. CertKiller.com wants
you to additionally locate the indexes which are most
frequently used for performing the update and insert
operations.
• What should you do?
Answer
A. You should consider making use of the
sys.dm_db_index_physical_stats view or function.
B. You should consider making use of the
sys.dm_db_index_operational_stats view or function.
C. You should consider making use of the
sys.dm_fts_index_population view or function.
D. You should consider making use of the
sys.dm_db_index_usage_stats view or function.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL Server
2008 for hosting databases. You are responsible for
administering a database server named CERTKILLER-DB01
with an instance that hosts a database named CK_SALES.
During the course of the day you added four disk drives to
CERTKILLER-DB01 which configuration are shown below:
Disk Drive 1: 40 GB. System drive with SQL Server
installed.Disk Drive 2: 40 GB. System drive with SQL Server
installed.Disk Drive 3: 60 GB. Formatted and with 35 GB
free.Disk Drive 4: 60 GB. Formatted with 38 GB free.
CertKiller.com plans on creating an additional database
named CK_PRODUCTS which will have a primary data file
and secondary data file with one transaction log file which is
expected to have high utilization in the production
environment. CertKiller.com wants you to optimize the
performance of the database.
What should you do?
Answer
A. You should consider having the primary data files placed
on Drive 1 and the transaction log file
on Drive 2 with the secondary data file on Drive 3.
B. You should consider having the primary data files placed
on Drive 2 and the transaction log file
on Drive 3 with the secondary data file on Drive 4.
C. You should consider having the primary and secondary
data files placed on Drive 2 and the
transaction log file on Drive 3.
D. You should consider having the primary and transaction
log files placed on Drive 2 and the
secondary data file on Drive 3.
Question
You work as a database administrator at CertKiller.com.
CertKiller.com currently makes use of Microsoft SQL Server
2008 for hosting databases. You are responsible for
Administering a database server named CERTKILLER-DB01
with an instance that hosts a database named CK_SALES.
During the course of the day you create a partitioned data
fact table which is large requiring data to be stored across
several disks.
CertKiller.com is aware of the partitioning functions which
horizontally split tables based on the value in a specific
column. CertKiller.com wants you to implement a sliding
window scenario switching partitions into and out of the
table whilst archiving the oldest data to improve
performance.
What should you do?
Answer
A. You should make use of the statement: ALTER
PARTITION FUNCTION and MERGE RANGE.
B. You should make use of the statement: ALTER TABLE
and MERGE RANGE.
C. You should make use of the statement: ALTER
PARTITION FUNCTION and SPLIT RANGE.
D. You should make use of the statement: ALTER TABLE
and SPLIT PARTITION.