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 the why. Last time, we started with cost threshold for parallelism. This week is a quick look at lightweight pooling.