sp_configure Settings #

The application can set two configurations for you automatically.

# -1 sets a value based on each server's memory
# Please see the documentation
resource "configuration" "max_memory" {
	name = "max server memory (MB)"
	value = -1
	ensure = "alter"
}

# -1 sets a value based on each server's core count
# Please see the documentation
resource "configuration" "maxdop" {
	name = "max degree of parallelism"
	value = -1
	ensure = "alter"
}

Maximum Memory #

By default, SQL Server will request all the memory it thinks it needs. If the OS is under memory pressure, it can tell SQL Server to relinquish memory or just not grant it. Setting a maximum amount of memory is common on production machines that need predictable, stable performance.

SQLDSC calculates the maximum memory using the following formula:

1. If physical memory is less than or equal to 4 GB, use physical memory / 2.
2. If physical memory is larger than 4 GB, calculate the amount of memory to leave for the operating system and give everything else to SQL Server.
   1. Leave 1 GB for the operating system
   2. Leave 25% of all memory up to 16 GB for the operating system
   3. Leave 12.5% of all memory above 16 GB for the operating system

This formula is conservative and is acceptable for most servers. It can be overridden for each role or server. I’ve seen this calculation in lots of places but I credit it to Jonathan Kehayias.

Maximum Degree of Parallelism (MAXDOP) #

SQL Server’s default MAXDOP is zero which uses all avaialble cores. The automatic setting uses the following rules:

1. If one NUMA node, set MAXDOP to the number of cores with a maximum of 8.
2. If multiple NUMA nodes, set MAXDOP the number of cores per node with a maximum of 8.