SQL Server Best Practice Settings - Server Settings
I always recommend generating and reusing a configuration file whenever installing a SQL Server. This ensures consistency and speeds the process up quite a lot.
I never recommend installing antivirus on a SQL Server. Any instance of antivirus will have a negative impact on primarily I/O traffic, as every I/O operation has to be filtered through the antivirus driver. This can lead to everything from a very small to a serious performance impact, often making it extremely difficult to troubleshoot as the I/O impact will be inconsistent. If antivirus still must be used, make sure to set up exclusions for the relevant files and processes, but be aware of that any I/O still have to hit the exclusion list before going to the disk.
Make sure to use all available resources. Most people think SQL Server Standard Edition has a limit of 64/128GB of RAM, but it isn’t quite that simple. In short, SQL Server can use more than this hard limit as the limit only applies to a subset of the SQL Server memory areas.
Also worth to note is that the maximum number of cores a Standard Edition can utilize is physical cores – not virtual ones (as long as we’re talking bare metal)
Make sure that hyperthreading is turned off.
Windows Server 2008R2 and later have a funny default setting for power plans – balanced power plan. I find it unlikely that anyone would ever want anything less than maximum performance out of their servers, so change that to maximum performance.
As different sites have different requirements, it’s difficult to give general guidance about instances. Some shops prefer huge servers with several instances and some prefer smaller servers with one instance per server. As long as there is ample memory for the instances either works fine, each with their respective pros and cons. Most of the time it comes down to licensing and SLAs.
A tip for using multiple instances on one server is to use a different IP for each instance. This enables the instance to run on the default port of 1433. This in turn negates the need to specify the instance name in the connection string. While it is quite possible to assign several IPs to a single NIC, I find it better to use a dedicated NIC per instance.
Group managed service accounts (gMSAs) are a great way keep security tight without having to bother with the hassle of changing passwords for the service accounts. I’d suggest you use them.