Home » Microsoft » 70-462 v.2 » Which two settings should you modify?
You have a default installation of SQL Server that hosts an Online Transaction Processing (OLTP) application.
Users report that they experience poor overall query performance for the application.
You query the wait statistics and discover that the two top waits are CXPACKET and SOS_SCHEDULER_YIELD.
You need to modify the SQL Server settings to resolve the issue causing the poor query performance.
Which two settings should you modify? Each correct answer presents part of the solution.
A. max degree of parallelism (MAXDOP)
B. Minimum Memory
C. optimize for ad hoc workloads
D. Boost SQL Server priority
E. cost threshold for parallelism
Correct Answer: AE
Explanation/Reference:
A: Lower the MAXDOP.
When high CXPACKET values are encountered, a possible issue, even in case when parallelism is evenly distributed, is when the cost of creating the parallel plan is higher than the cost of the serialized thread. This is often something that is overlooked and by the rule of thumb of reaching for altering of the Max Degree of Parallelism (MAXDOP), by setting it to 1 (each and every query will be processed by the single CPU core). Configuring MAXDOP settings to 1 should be the last resource used in troubleshooting excessive CXPACKET wait times.
When a high CXPACKET value is accompanied with a LATCH_XX and with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD, it is an indicator that slow/ inefficient parallelism itself is the actual root cause of the performance issues. And in such a scenario if the LATCH_XX waits is ACCESS_METHODS_DATASET_PARENT or ACCESS_METHODS_SCAN_RANGE_GENERATOR class, then it is highly possible that the parallelism level is the bottleneck and the actual root cause of the query performance issue. This is a typical example when MAXDOP should be reduced.
E: The Cost Threshold for Parallelism (CTFP) value is in seconds and it means that for every query for which SQL Server estimates that running time will be longer than 5 seconds, a parallel plan will be created.
To prevent unwanted parallelism, the CTFP number could be increased and by the aforementioned rule of thumb, a minimum value of 25. Recent analysis indicates that 50 should be the optimal minimal number for modern computers.
References: https://www.sqlshack.com/troubleshooting-the-cxpacket-wait-type-in-sql-server/
