You administer a SQL Server 2008 instance.
You need to configure the instance to use a single thread for queries that have an estimated execution cost less than 3.
Which sp_configure configuration option should you set?

• A. cost threshold for parallelism
• B. priority boost
• C. precompute rank
• D. query governor cost limit
• E. max worker threads

You administer a Microsoft SQL Server database that hosts an order-processing application.
Employees in the order entry group perform real-time data entry for customers. Employees in the reporting group run historical reports.
Employees in the order entry group report of poor performance when the reporting group runs reports.
You need to limit the amount of CPU and RAM used by the reporting group.
What should you do?

• A. Use sp_dbmmonitoraddmonitoring to set up a monitoring process to kill the reporting group's queries that pass a given threshold.
• B. Implement Resource Governor by using a Resource Pool assigned to the users of the reporting group to limit the amount of CPU/RAM they can use.
• C. Implement Policy Based Management by using the Server Performance facet.
• D. Create a set of views in a new schema, and then alter the queries of the reporting group to use these views.

DRAG DROP
You administer a Microsoft SQL Server 2008 R2 database that contains customer invoice accounts.
Backups are performed according to the following schedule:
--
Full database backup at 00:00 hours daily.
Transaction log backups at 06:00 hours, 14:00 hours, and 20:00 hours daily.
You discover that the database failed at 11:59 hours. You also discover that the database is no longer accessible due to a failure of the hard disk drive that contains the database data file.
You need to restore the database.
You also need to ensure that data loss is minimal.
Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition:

The following DDL has been run on the database:
CREATE PARTITION FUNCTION FUNC_FG (INT)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
You need to create a partition scheme that will place all data to the SECONDARY filegroup.
What should you do?

• A. Run the following statement:
  EXECUTE sp_tableoption
  @TableNamePattern ='OrderItem3',
  @OptionName= 'PartltionByYear';
  @OptionValue= 'true';
• B. Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
• C. Execute the DBCC CLEANTABLEcommand on the OrderItems table.
• D. Create a new Filegroup.
  Create a new database File.
  Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
  Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
• E. Create a new table.
  Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
  Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE
  clause.
• F. Remove the clustered index from the table.
• G. Create a new filegroup.
  Create a new database file.
  Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
  Use ALTER INDEX REORGANIZE statement.
• H. Create a new partition function.
  Create a new partition scheme.
  Add a clustered index to place the data onto the partition scheme.
• I. Use the ALTER TABLEstatement to remove the COLLATEoption.
• J. Run the following statement:
  CREATE PARTITION SCHEME SEC_FG
  AS PARTITION FUNC_FG
  ALL TO (SECONDARY);

You are managing two SQL Server 2008 computers called SQLTEST1 and SQTEST2, which include a copy of a database named Sales. The database is replicated between SQL1 and SQL2 by using transactional replication. A full backup of each database is performed every night.
Transaction log backups are performed every hour. Replication latency is typically less than two minutes. One afternoon, the Sales database on SQLTEST1 becomes corrupted. You are unable to repair the database. The Sales database on SQLTEST2 is unaffected. You need to return the Sales database on SQLTEST1 to normal operation as quickly as possible.
You must ensure a minimum loss of data and minimal impact to users of either server.
What should you do?

• A. You should detach the Sales database on SQL2. Copy the database file to SQL1, and link the database on both servers.
• B. You should reserve the most recent full database backup and the whole transaction logs made since the past backup could be made.
• C. You should reserve a full database backup on SQL2. Reserve the backup to SQL1.
• D. You should reserve only the newest transaction log backup.