Your SQL Server 2008 database contains two tables named Customer and Order inside the Sales schema.
The Sales schema is defined as follows:
It has a stored procedure named SetCustomerStatus that reads from Order and modifies Customer.
It is owned by a user named SalesUser.
Another user named marketingUser wants to be able to call SetCustomerStatus and change the status of Customer.
You need to assign the least permission necessary to MarketingUser.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Set only Take Ownership on ustomer and Order.
• B. Perform the following tasks:
  Set Select on Customer.
  Set Update on Order.
• C. Perform the following tasks:
  Set Execute on SetCustomerStatus.
  Set Select on Customer.
  Set Update on Order.
• D. Set only Execute on SetCustomerStatus.

You are designing a new SQL Server instance that will support a Web application.
The Web application is hosted on a Web farm that contains 32 nodes.
The server has 128 GB of memory and 16 processor cores. The application contains two databases and supports both online analytical processing (OLAP) and online transaction processing (OLTP) workloads.
When testing the application, you discover that some queries run extremely slow and some queries run very fast.
You need to ensure that the server processes database queries as fast as possible . What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Execute the following command on the server: exec_sp_configure 'maximum degree of parallelism', 8
• B. Use SQL Profiler to identify queries that experience CXPACKET waits. Add (OPTION MAXDOP 8) to each query
• C. Use SQL Profiler to identify queries that experience CXPACKET waits. Add (OPTION MAXDOP 1) to each query
• D. Execute the following command on the server: exec_sp_configure 'maximum degree of parallelism', 1

You administer a SQL Server 2008 infrastructure.
You plan to design a solution to obtain hardware configurations, such as the number of processors on a computer and the processor type of all SQL Server 2008 computers.
The solution must meet the following requirements:
It is hosted on the central computer.
It can verify hardware configurations for multiple servers.
You need to select a technology that meets the requirements by using the minimum amount of development effort.
What should you do?

• A. Define policies based on conditions by using the ExecuteSql function.
• B. Define policies based on conditions by using the ExecuteWQL function.
• C. Use the Windows Management Instrumentation (WMI) provider for the server events.
• D. Use the Invoke-Sqlcmd cmdlet in SQL Server PowerShell cmdlet.

You are a professional level SQL Sever 2008 Database Administrator.
A maintenance strategy should be designed for a mission-critical database, and a large table named Orders is contained by the database. Index maintenance operations are contained in the design plan. When you design the strategy, the facts listed below should be taken into consideration.
First, the users continuously access to the Orders table in the database.
Secondly, a column of the xml data type is contained by Orders table.
Thirdly, the new rows are regularly added to the Orders table.
Fourthly, the average fragmentation for the clustered index of the Orders table is no more than 2
percent.
A strategy should be designed to have the performance of the queries on the table optimized.
Which action will you perform?

• A. The clustered index of the Orders table should be dropped.
• B. The clustered index of the Orders table should be excluded from scheduled reorganizing or rebuilding operations.
• C. The clustered index of the Orders table should be reorganized by reducing the fill factor.
• D. The clustered index of the Orders table offline should be rebuilt once a month.