SQL Server 2019 settings for AWS EC2 Instance Types

by Damien Dennehy

Trying to determine the best settings in SQL Server versions prior to 2017 could be extremely challenging. Not only did you need to be a SQL Admin to understand some of the setting values actually did, but some of the default settings were pretty dated – in fact some settings have been the same since SQL 2005 or SQL 2008.

Fortunately during installation of SQL 2017 and SQL 2019 the installation process analyses your server specifications and gives you recommendations to various settings.

The default value for each setting are listed here in case you change instance types and wish to use the default value of the new type. Just make everything 8 once you go to 2xlarge sizes.

Instance Type SQL SettingDefault Value
t3.medium/t3.large MaxDOP 2
TempDb # Files 2
t3.xlarge/m5.xlarge/c5.xlargeMaxDOP4
TempDb # Files 4
t3.2xlarge/m5.2xlarge/c5.2xlargeMaxDOP 8
TempDb # Files 8
Anything biggerMaxDOP 8
TempDb # Files 8

Damien Dennehy