Some considerations when configure SQL Server on Azure VM. This also applicable to AWS VM
For storage:
- Separate volume for data and log file
- Enable read caching on data file volume
- Disable any caching on log file volume
- Use D: drive (local SSD that will be cleared each time with server reboot) for TempDB
- Enable instant file initialization
- Move trace file and error log directories to data volume
To evaluate disk performance when migrate from on-premises to cloud, capture following counters in Performance Monitor:
- average disk seconds/read
- average disk seconds/write
- SQL Server: Resource Pool Stats Disk Read and Write IO/sec (This is to see how much IOPs SQL Server is serving at its peak)
If need large amount of memory but not default allocated number of CPUs, consider using constrained vCPU count while still get the full amount of memory, storage and I/O bandwidth. This will help to reduce SQL Server licensing cost as SQL Server license is core based.
When creating mulitple data or log files, creat all data files or log files be the same size, so SQL Server an evenly distribute the write load, as SQL Server is doing proportional fill on writing. Prior SQL2016, DBA can also use trace flag 1117 to force multiple data files database to grow at the same rate and use trace flag 1118 to handle contention on accessing system pages of tempdb. Since SQL2016, the behaviors of trace flag 1117/1118 are built in for tempdb.