Home > Workload Solutions > SQL Server > Guides > Implementation Guide—SQL Server 2019 Best Practices on PowerStore > PowerStore: Adding Multiple Storage LUNs
Storage configuration can directly influence database performance. This best practice validates the number of storage LUNs configured for SQL Server that optimizes database performance.
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. Table 10 shows the baseline PowerStore volume groups and LUNs configuration.
Volume Group | Volume Name | VMware Datastore | Volume size (GB) | Notes |
SQLVM1OS | SQLVM1OS | SQLVM1OS | 300 | Operating System |
SQLVM1 | SQLVM1Data1 | SQLVM1Data1 | 160 | SQL Data/Log |
SQLVM1Log1 | SQLVM1Log1 | 160 | ||
SQLVM1Temp | SQLVM1TempData1 | SQLVM1TempData1 | 256 | Temp Data/Log |
SQLVM1TempLog1 | SQLVM1TempLog1 | 128 | ||
SQLVM1Backup | SQLVM1Backup | SQLVM1Backup | 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 the Temp files and backup files which are not necessary for a functional database snapshot. This configuration also provides space savings if replication is used.
Volume Group | Volume Name | VMware Datastore | Volume size (GB) | Notes |
SQLVM1OS | SQLVM1OS | SQLVM1OS | 300 | Operating System |
SQLVM1 | SQLVM1Data1 | SQLVM1Data1 | 160 | SQL Data/Log |
SQLVM1Log1 | SQLVM1Log1 | 160 | ||
SQLVM1Temp | SQLVM1TempData1 | SQLVM1TempData1 | 256 | Temp Data/Log |
SQLVM1TempLog1 | SQLVM1TempLog1 | 128 | ||
SQLVM1Backup | SQLVM1Backup | SQLVM1Backup | 1000 | Backup |
Table 11 shows the optimized PowerStore storage configuration. 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, there are four times the number of I/O queue paths.
Volume Group | Volume Name | VMware Datastore | Volume size (GB) | Notes |
SQLVM1OS | SQLVM1OS | SQLVM1OS | 300 | Operating System |
SQLVM1 | SQLVM1Data1 | SQLVM1Data1 | 160 | SQL Data/Log |
SQLVM1Data2 | SQLVM1Data2 | 160 | ||
SQLVM1Data3 | SQLVM1Data3 | 160 | ||
SQLVM1Data4 | SQLVM1Data4 | 160 | ||
SQLVM1Log1 | SQLVM1Log1 | 160 | ||
SQLVM1Temp | SQLVM1TempData1 | SQLVM1TempData1 | 256 | Temp Data/Log |
SQLVM1TempLog1 | SQLVM1TempLog1 | 128 | ||
SQLVM1Backup | SQLVM1Backup | SQLVM1Backup | 1000 | Backup |
Once the additional LUNs are created, these LUNS can be formatted with a file system and mounted. Multiple SQL Server data files can now be spread across all four data mountpoints. For detailed PowerStore best practices for SQL Server, see Storage Best Practices.
Recommendation
Based on our test results, the overall database performance increased significantly by having multiple LUNs for the SQL Data file system. Therefore, the 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. The below metrics showed increases in:
Implementation steps
To create additional LUNs for the SQL Server database, use the PowerStore Manager and follow the instructions on how to create volumes from the PowerStore manual.
After the LUNs have been created, follow the below steps to create additional file systems for the SQL database.
Change ownership of the mounted volume.