Home > Storage > PowerFlex > White Papers > Dell APEX Block Storage for Azure: Microsoft SQL Server 2022 Deployment and Performance Best Practices > SQL Server files layout
To get the optimal database performance it is important to design the database layout carefully. Database team to collaborate with the development and product team to design and plan the capacity requirement for different databases according to different size, IOPS, and latency requirements.
High-performance SQL Server databases often require a storage layout that includes more than a single disk for the database files. Storing all the database files in one partition/drive may be sufficient for general purpose, low-performance databases, however, to fully leverage Dell APEX Block Storage for Azure cluster capabilities and its robust performance, especially when using local NVMe drives, data, log, and temp files should be distributed across different windows disks (APEX Block Storage volumes).
The following figure shows the SQL Server database file layout. Database files are divided across different disks for better I/O throughput. For more information about optimizing the performance of Azure virtual machines for SQL server database see Best practices for SQL Server on Azure VMs.
As shown in the above figure, the database files are shared across four different partitions and log files are stored separately on the other partition. Tempdb files are moved from their default location to a Dell APEX Block Storage for Azure storage volume to optimize the database throughput. Pre-allocating large space for data and log files on the disk reduces the operating system calls from the database, hence improving the database response time. These steps help for better I/O distribution across different block volumes, which in turn improves the overall database performance.
The following table shows the partition size and file system used for the database:
Volume name | Size (GB) | File system | Allocation Size | Purpose |
C | 126 | NTFS | 64 K | OS partition and SQL Server installation files |
E | 151 | NTFS | 64 K | Data files partition |
F | 151 | NTFS | 64 K | Data files partition |
G | 151 | NTFS | 64 K | Data files partition |
H | 151 | NTFS | 64 K | Data files partition |
I | 703 | NTFS | 64 K | Log files partition |
J | 8 | NTFS | 64 K | Tempdb data and log files |
SQL Server provides multiple ways to identify the location of its data files and transaction log files. These include folders based on Windows drive letters, folders based on Windows mount points, and Cluster Shared Volumes (CSV) in the case of Windows Failover Clusters (WFCS). This example uses Windows drive letters to identify the data and transaction log files.