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.
Use SQL Profiler to identify queries that experience CXPACKET waits. Add (OPTION
MAXDOP 8) to each query
B.
Use SQL Profiler to identify queries that experience CXPACKET waits. Add (OPTION
MAXDOP 1) to each query
C.
Execute the following command on the server: exec_sp_configure ‘maximum degree of
parallelism’, 8
D.
Execute the following command on the server: exec_sp_configure ‘maximum degree of
parallelism’, 1
Explanation: