A few months ago I suggested that the following settings should be the default for most SQL Server instances:
- Set cost threshold for parallelism to 50
- Disable lightweight pooling if it is enabled
- Disable priority boost if it is enabled
- Set optimize for ad hoc workloads to enabled
- Set max server memory (MB) to a custom value consistent with Jonathan Kehayias's algorithm
- Set backup compression default to enabled
- Set the power saving settings on Windows to high performance if possible
- Provide an option to flush the plan cache as needed
Over the next few posts, I will dive into why. This week, we look at disabling priority boost.