The following best practices were used for the standard version of SQL Server 2019 VM configurations:
- Perform a current state analysis to identify workloads and sizing.
- Start with a proof of concept, and then test, optimize, iterate, and scale.
- Distribute databases and logfiles across multiple VMDKs.
- Distribute vDisks across four SCSI controllers.
- Use 64 KB NTFS allocation for database and log drives.
- Size for at least 20 percent free disk space on all drives.
- Put TempDB in NVMe persistent storage volumes.
- If NVMe storage is available, put TempDB into it.
- Create drives of slightly different sizes.
SQL Server datafiles
- Split each database into multiple files: one file per vCPU.
- Size database files equally.
- Do not shrink databases as it causes severe Index fragmentation..
SQL Server logfiles
- Under most circumstances, one log per database (including TempDB) should be enough.
- Log files fill sequentially, so extra files do not increase performance.
- Use multiple TempDB files, all the same size.
- Do not autogrow TempDB files.
- If cores < 8, the number of TempDB files = cores.
- If cores > 8, start with eight TempDB files and monitor for performance.
- Do not drop the TempDB database and size TempDB appropriately.
- One TempDB drive should be enough for most environments.
- More RAM can increase SQL database read performance.
- From the total Windows OS memory, reserve 4 GB to the OS itself. Configure SQL Server memory as per suggestion provided by Microsoft. See, Server Memory Configuration.
- Size each VM to fit within a NUMA node’s memory footprint.
- Do not over allocate vCPUs to VMs.
- At virtual level, 1 socket has 8 CPU cores.