data-konzept

'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. This week we look at setting max server memory correctly.'...

https://bornsql.ca/blog/proposed-sql-server-defaults-max-server-memory/