You administer a SQL Server 2008 instance. The server hosts databases for several
mission-critical applications.
You plan to use the Resource Governor to limit the effect of queries that are executed by
Microsoft SQL Server Management Studio.
You need to ensure that that the following requirements are met:
-Queries initiated through SQL Server Management Studio do not exceed 20 percent of CPU
utilization. -Queries initiated by the mission-critical applications are allowed to consume 100
percent of CPU utilization when required.
What should you do?

You administer a SQL Server 2008 instance. Users report that applications that run on the
server perform poorly. You suspect that the performance issues are related to table scans.
You need to use an appropriate Windows System Monitor object to capture the appropriate
information.
Which performance object should you use?

You administer a SQL Server 2008 instance. The server contains a very large database that
is used by an application that is constantly available.
Users report that the server performance has degraded.
You need to use the Database Engine Tuning Advisor to improve the performance of the
application.
You also need to ensure that the performance of the production server is unaffected while
analyzing the workload.
What should you do?

You maintain a SQL Server 2008 instance that contains a database named OrderEntry. The
OrderEntry database contains a table named OrderDelivery.
You add a column named DeliveryPoint to the OrderDelivery table. The DeliveryPoint
column is of the Geography data type. The OrderDelivery table does not contain indexes.
You need to create a spatial index on the DeliveryPoint column.
What should you do first?

You are mastering the company database; there are 15 retail stores in your company. On its
own SQL Server 2008 computer, every retail store keeps transactions of point-of-sale in a
database table called Saling. The Saling table also includes sales information from other
stores to enable customer returns to any of the 15 retail stores. You could refresh the data in
the main office to every retail store every hour. You should use trigger called trg_Coupon to
produce sales coupons based on customer sales and buying patterns. Through using the
fewest number of steps, you should set replication between the server in every retail store
and a middle server in the mainoffice. in real time. You should not have the Replication.
Which is the correct answer?

You administer a SQL Server 2008 instance.
You join two tables on a column named CompanyName by using the following query:
SELECT s.*,i.*
FROM SensitiveTb1 AS s
INNER JOIN Insensitive Tb1 AS iON i. CompanyName = s.CompanyName
When you execute the query, the following error is returned:
“Msg 468, Level 16, State 9, Line 17
Cannot resolve the collation conflict between ,SQL_Latinl_General_CPl_CS_AS’ and
‘SQL_Latin1_General_CP1_CI_AS’ in the equal to operation.”
You need to modify the ON clause of the query to successfully perform a case-sensitive join.
What should you do?

You administer a SQL Server 2008 instance that contains a database named Sales. The
Sales database has a table named Products that stores information about all types of
products. Users frequently query the Products table based on the TelevisionSize column.
The TelevisionSize column has the NULL value for all products other than Televisions.
There is currently no index on the TelevisionSize column. You need to improve the query
performance by ensuring that the effect on the disk space is minimized. What should you
do?

You administer a SQL Server 2008 instance that contains a database named
AdventureWorks.
The AdventureWorks database contains a table named OrderDetail. You plan to export all
data from this table to a file.
You need to ensure that the following data export requirements are met:
A Microsoft Office Open XML document format is used The data export process is saved for
reuse.
What should you do?

You administer a SQL Server 2008 instance that contains a database named
AdventureWorks. The AdventureWorks database contains the OrderHistory table. The
OrderHistory table is partitioned on the OrderId column. The first partition contains integer
values between 1 and
100.000. The second partition contains integer values greater than 100.000. You need to
add a new partition that contains integer values greater than 200.000. What should you do?

You administer a SQL Server 2008 cluster. You plan to test the failover response of the
cluster.
You need to implement a manual failover on the cluster.

Which two actions should you perform? (Each correct answer presents a complete solution.
Choose two.)