You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2 to
administrate and maintain its database.
You have received instruction from management to create a strategy that makes use of the data
collector is utilized to collect performance information. Your strategy should also allow for 15 day
old information to be removed, and also reduce the amount of effort required to administer the
performance information.
Which of the following actions should you take?

You work as a database administrator at ABC.com.ABC.com makes use of multiple SQL Server
2008 R2 instances. One of the SQL Server 2008 R2 instances includes a database named
ABC_DB.
You have been instructed to devise a data collection solution that makes use of the data collector
to gather performance information of all the instances. Your solution should allow for the gathered
performance information to be reserved in ABC_DB. Your solution should also allow for function
data to be gathered and loaded in the management data warehouse every 12 hours.
Which of the following actions should you take?

You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2
instance.
The SQL Server 2008 R2 instance includes a database named ABC_DB that makes use of the
bulk-logging recovery model. You have employed a WAN connection for log shipping ABC_DB.
You have also configured ABC_DB’s indexes to be reproduced by a scheduled job that results in
the log backups growing in size.
You have been instructed to make sure that the log backups employed by log shipping is reduced
in size.
Which of the following actions should you take?

You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2
instance that contains a database, named ABCBooks.
ABCBooks includes the BookTitle, EANNumber, SellingPrice and IssueDate columns. A clustered
index is configured for the BookTitle column and a non-clustered index, which includes the
EANNumber and SellingPrice columns, is configured for the IssueDate column.
A Transact-SQL query, which includes a query hint, searches the ABCBooks table for BookTitle,
EANNumber, SellingPrice, and IssueDate data. During monitoring you notice that the query’s
performance is slow. You then run the sys.dm_db_index_physical_stats dynamic management

function (DMF) and find that the avg_fragmentation_in_percent is in excess of 25%.
You have been instructed to find a solution to improve the query’s performance.
Which of the following actions should you take?

You work as a database administrator at ABC.com.ABC.com makes use of a SQL Server 2008 R2
instance.
ABC.com contains quite a few databases that are utilized by applications. You are planning to
make use of the Resource Governor to cut the result of searches. These searches are run via
SQL Server Management Studio.
You have been instructed to make sure that the CPU utilization for searches done by the SQL
Server Management Studio should not be more than 40 %. You also have to make sure that in the
event of the applications requiring all of the CPU resources, they are permitted to do so when
necessary.
Which of the following actions should you take FIRST?

You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2
instance.
After receiving a complaint from a ABC.com user regarding the sluggish execution of the
applications on the server, you decide not to make use of the table scans to obtain additional data
as a result of the poor execution.
You have to gather the necessary data in another way.
Which of the following actions should you take?

You work as a database administrator at ABC.com.ABC.com is making use of a SQL Server 2008
R2 instance.
ABC.com includes a database that is used by an application that is integral to daily operations.
You are then informed by a ABC.com user that the server is not running as it should be.
You need to make use of the Database Engine Tuning Advisor to enhance the application’s
execution, while preventing other users from being affected by server performance.
You start by setting up a test server, named ABCTEST-SR01 that has the same hardware setting
as the server with the performance issue.
Which of the following actions should you take NEXT? (Choose all that apply.)

You work as a database administrator at ABC.com.ABC.com is making use of a SQL Server 2008
R2 instance to manage and maintain its databases.
The SQL Server 2008 instance includes a database that has a table, named ABC_Tbl. You then
configure ABC_Tbl to include a Geography data type column, named ABC_Colmn.
You have been instructed to devise a solution that makes allowance for ABC_Colmn to include a
spatial index.
Which of the following actions should you take?

You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2
instance that contains a database named ABC_Prod.
You have been instructed to join two tables on a column called ABC_Common. You have to
ensure that the values in the columns being joined are compared using a comparison operator.
Which of the following actions should you take?

You work as a database administrator at ABC.com.ABC.com is using a SQL Server 2008 R2
instance.
The SQL Server 2008 R2 instance includes a database that has a table, named ABC_Tbl, which is
partitioned on the ProID column. The partitioning characteristics are as follows:
•Partition 1 includes integer values between 5 and 30,000.
•Partition 2 includes integer values in excess of 30,000.
Due to the growth in business operations, ABC.com foresees the need to have a third partition in
the table.
You receive instructions to create a third partition on the table. This partition must be able to
include the integer values in excess of 60,000.
You decide to make use of an ALTER PARTITION FUNCTION Transact-SQL statement.
Which of the following is TRUE with regards to this Transact-SQL statement? (Choose all that
apply.)