What are standard value of max degree for parallelism setting, cost threshold for parallelism configuration properties when I wish to reduce cxpacket wait time in SQL Server database. And what are good and bad effect of above setting.
The default setting for Cost Threshold is awful, it is something tested on hardware back in the 90s, so I Always tenfold that number (50 instead of 5). Max Degree Of Parallelism is a bit trickier. It depends on a lot of things. When I have more than one NUMA node, I keep MAXDOP to no more than the number of cores per NUMA node. If I have more than eight CPUs in each NUMA node, I keep MAXDOP to eight or lower. When I have absolutely no idea what the server will be used for (that happens sometimes with "Segregation of duties" in large companies and I just get a task to configure a server using "Best Practice"), I set MAXDOP to: - When there's a single NUMA node - half the number of cores. - When there's more than one NUMA node and each NUMA node has eight or less cores - MAXDOP=number of cores per NUMA node. - When each NUMA node has more than eight cores - MAXDOP=half the number of cores per NUMA node. But this is just _my_ "standard" configuration. If your server is performing 5000 1ms batches per second, each committing in less than 10ms or if your server is performing 5 half minute batches per second will clearly have an impact on choice of MAXDOP-configuration.