Home > Workload Solutions > SQL Server > White Papers > SQL Server 2019 Best Practices on PowerEdge Servers and PowerStore T Model Arrays > PowerStore: Add Multiple LUNs
Storage configuration can directly influence database performance. This best practice validates the number of storage LUNs configured for SQL Server 2019 to optimize database performance.
Table 8. Best Practice Category
Category | PowerStore Storage |
Product | PowerStore 5000T |
Type of best practice | Performance Optimization |
Day and value | Day 1, Highly recommended |
Overview
To determine if a best practice provides value, the metrics were compared to a baseline configuration of the database to validate performance. In the baseline configuration for the database volumes, the SQL data, SQL log, temp data, temp log, and SQL backup file system each had one LUN. The below table shows the baseline PowerStore volume groups and LUNs configuration.
Table 9. Baseline storage group configuration
Volume Group | Volume Name | Number of LUNs | VMware Datastore | Volume size (GB) | Notes |
sql_vm1_os | sql-vm1-os | 1 | sql-vm1-os-ds | 300 | Operating System |
sql_vm1_db1 | sql-vm1-db1-data1 | 1 | sql-vm1-db1-data1-ds | 512 | Data |
sql-vm1-db1-log1 | 1 | sql-vm1-db1-log1-ds | 256 | Log | |
sql_vm1_db1_temp | sql-vm1-db1-temp-data1 | 1 | sql-vm1-db1-temp-data1-ds | 256 | TempData |
sql-vm1-db1-temp-log1 | 1 | sql-vm1-db1-temp-log1-ds | 128 | TempLog | |
sql_vm1_db1_backup | sql-vm1-db1-backup1 | 1 | sql-vm1-db1-backup1-ds | 1000 | Backup |
The baseline storage configuration also includes separating database files into dedicated volume groups. This allows the database administrator (DBA) to only snapshot the database, without including unnecessary temp files and backup files. This configuration also provides space savings if replication is used.
The volume groups remain intact, but additional LUNs were added for the SQL User DB data area. Using multiple LUNs for the most active portion of a SQL Server database can improve performance. The primary benefit of using multiple LUNs is that the operating system creates an I/O queue path per LUN. The following table shows the optimized PowerStore storage configuration for the SQL Server database. In the optimized storage configuration, the SQL User DB Data area has four LUNs. Compared to the baseline configuration, this configuration has four times the number of I/O queue paths.
Table 10. Optimized PowerStore storage configuration
Volume Group | Volume Name | VMware Datastore | Volume size (GB) | Notes |
sql_vm1_os | sql-vm1-os | sql-vm1-os-ds | 300 | Operating System |
sql_vm1_db1 | sql-vm1-db1-data1 | sql-vm1-db1-data1-ds | 160 | SQL Data/Log |
sql-vm1-db1-data2 | sql-vm1-db1-data2-ds | 160 | ||
sql-vm1-db1-data3 | sql-vm1-db1-data3-ds | 160 | ||
sql-vm1-db1-data4 | sql-vm1-db1-data4-ds | 160 | ||
sql-vm1-db1-log1 | sql-vm1-db1-log1-ds | 256 | ||
sql_vm1_db1_temp | sql-vm1-db1-temp-data1 | sql-vm1-db1-temp-data1-ds | 256 | Temp Data/Log |
sql-vm1-db1-temp-log1 | sql-vm1-db1-temp-log1-ds | 128 | ||
sql_vm1_db1_backup | sql-vm1-db1-backup1 | sql-vm1-db1-backup1-ds | 1000 | Backup |
Once the additional LUNs are created, these LUNS can be formatted with a file system and mounted. Multiple SQL Server datafile can now be spread across all four data mountpoints.
Recommendation
Based on our test results, the overall database performance increased by having multiple LUNs for the SQL Data file system. This best practice of increasing the number of LUNs is highly recommended and should be considered as a Day 1 practice, as part of initial data provisioning.
For more information about PowerStore best practices with SQL Server database, see SQL Server Database Best Practices on PowerStore.