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 Setting | Default Value |
| t3.medium/t3.large | MaxDOP | 2 |
| TempDb # Files | 2 | |
| t3.xlarge/m5.xlarge/c5.xlarge | MaxDOP | 4 |
| TempDb # Files | 4 | |
| t3.2xlarge/m5.2xlarge/c5.2xlarge | MaxDOP | 8 |
| TempDb # Files | 8 | |
| Anything bigger | MaxDOP | 8 |
| TempDb # Files | 8 | |
