Consider the following best practices for Windows and SQL Server 2022 hosted on Azure:
- For high-performance and mission-critical databases it is recommended to spread the database files across four or more disk (APEX Block Storage volumes).
- Store database transaction logs on a separate volume to not mix its unique I/O profile with the data files random I/O profile. Transaction logs I/O profile are sequential in nature. This practice can also enable a possible usage of APEX Block Storage snapshot technology to create database backups where a volume restore does not over-write the latest transaction logs.
- Move the tempdb files from the default location to separate volume or NVMe persistent storage (if available) to avoid page contention. This practice will substantially improve the database latency performance when temp files are busy.
- It is recommended to increase the number of data files in tempdb to maximize the bandwidth and to reduce contention in allocating structures. Monitor the contention level and increase the number of data files by multiple of four, to the maximum number of available logical processor.
- It is recommended to enable the lock pages in the memory to prevent the system from paging the data to virtual memory. Make the necessary changes in the group edit policy to enable the lock pages. For more information, see Enable the Lock Pages in Memory Option.
- It is recommended to format all the database disks with an NTFS file system.
- Microsoft SQL Server default page size is configured as 8KB. Eight continuous pages make up an extent (for example, 64KB). Microsoft SQL Server uses extents to store the data. Therefore, it is recommended to use 64 K NTFS allocation unit size for all the volumes of the SQL Server database.
For more information about Azure SQL Server best practices, see the best practices for SQL Server on Azure VMs.