Prasad Venkatachar – Sr Director Solutions|Product
If you have been working with SQL Server 2022, you’re likely already familiar with many of the Azure cloud-connected features, such as Azure SQL Managed Instance, Azure Synapse Link, and object storage integration; as well as the server hardware integration features for integrated acceleration and offloading to hardware devices.
We at Pliops built a hardware-accelerated data storage product Extreme Data Processor (XDP) that will enable enterprises to address the challenges of data growth, performance acceleration & increased availability of database applications. One of the core strengths of Pliops XDP is its ability to scale database capacity and the number of databases with no impact on performance. To demonstrate the benefits of Pliops XDP with SQL Server 2022, we embarked on solution design and performance scaling testing using the HammerDB TPROC-C benchmark.
More SQL server testing posts are planned and will be published that work soon. For this post, I want to share the 3 goals of this solution-testing exercise,
- Demonstrate Pliops XDP performance scaling with SQL Server databases and serve as database consolidation. platform.
- Extend the same Pliops accelerated volume and enable it for database backup and restore offload benefits for lowering RPO and RTO needs.
- Illustrate the benefits of reduced SSD write amplification for SQL Server database workloads and increased shelf life or endurance of SSDs
SQL Server: HammerDB Performance Testing
For this solution testing, we set up a SQL Server 2022 standard version on a Dell R750 server with 4 NVMe SSD drives directly managed by Pliops XDP. The number of cores and amount of memory was configured to support the SQL standard license requirements (complete benchmark configuration can be found in the solution brief. As shown in the figure above, Pliops XDP exposes high-performance data storage volume with built-in data protection to tolerate any SSD failures and maintain database uptime. Pliops XDP in-line data compression and capacity expansion benefits enabled the provision of 14TB fully protected data volume over 14.8 TB of raw storage, with no capacity overhead for redundancy.
If you are working on an SQL server for a long time standard best practice is to isolate the datafile and transaction log files to separate mount points backed by separate Raid groups and NVMe SSDs to minimize the IO bottleneck. It takes a balanced approach to minimize SSD cost, and capacity exposed for provisioning databases, with some performance compromise depending upon RAID 5 or RAID10 configuration. This effort is vastly simplified by Pliops XDP, which provides a single volume that intelligently manages IO traffic to ensure data and logfile performance and neither database capacity or database protection does not get compromised. This approach significantly reduces the cost of SSDs ($/TB) especially when you have a large number of SQL server database deployments.
To demonstrate the performance scaling of the databases, initially, a single database was loaded with 25K warehouses which translates to 2.5TB database capacity using HammerDB. As a next step carried out HammerDB T-PROC performance testing by varying multiple user threads. We captured the database metrics of Transactions per Minute, DB hit ratio, batch requests/sec, virtual file stats, and system metrics of CPU utilization, memory consumption, and system IO. With consistent performance metrics captured through multiple testing iterations, we moved on to test two databases with an identical capacity of 2.5TB and the same number of threads (DB users) executing the TPROC benchmark. HammerDB provides an excellent option to explore the behavior of the IO subsystem during extreme IO workloads by accessing all 25,000 warehouses of the TPROC database using the ‘all warehouses enabled’ option. Unchecking this option will simulate the normal behavior of accessing frequently used warehouses with an increased database cache hit ratio. Our objective was to demonstrate the performance scaling of the SQL server databases in both situations, enabling enterprises to leverage the system design for both normal and peak storage demands.
As shown in the performance charts above, during the ‘all warehouses disabled’ scenario, we obtained 1.95X performance scaling. This gets marginally reduced to 1.85X with the ‘all warehouses enabled’ option. Thus, we can conclude from the performance scaling benchmark that the SQL Server standard version of the database increased from one to two databases to support multiple applications/customers will not incur performance loss – even with limited hardware resources (system CPU and Memory for SQL buffers). It is critically important for enterprises to design and build an optimized infrastructure that provides higher database and user density per a given data center footprint while meeting or exceeding the desired database application service level objectives.
We wanted to pursue further performance scaling testing with additional databases by leveraging SQL server resource governor to show multi-workload, multi-user environments with different SLAs options (Platinum, Gold, Silver, Bronze) on performance, availability, etc. As I mentioned above, we have more SQL tests planned and will post them here on the blog to highlight these results.
Seeking to get a complete picture of the overall database solution, we moved on from testing performance to testing database backup offloading.
Database Backup Offloading with Pliops
SQL Server databases (and their backups) can get very, very large. They take up a lot of disk space, and backing up databases or moving them around can get time-consuming. Microsoft introduced backup compression with T-SQL backup with compression command in its SQL 2008 enterprise edition. Compressing backup data benefits enterprises, as they are then able to consume less storage for backup and potentially reduce the backup duration. While many enterprise customers use the current compression methods, they are not without challenges, as compression ratios are frequently too low to compensate for the additional CPU overhead required. Compressed Backup operations can put significant strain on I/O and CPU. This becomes even more limiting when SQL standard version is being employed with just 24 cores available for the system.
The SQL Server 2022 feature provides integrated compression/decompression capabilities. While we are exploring this integration option with the SQL server for a backup offload, we wanted to evaluate whether or not a Pliops protected volume with built-in data protection could serve both as data volume and as backup volume and demonstrate storage and CPU savings during SQL backup operations. The goal of this testing was to gather out-of-the-box metrics on backup and recovery operations for the SQL database, which is listed in the table below. These metrics can be enhanced through further optimization.
The test metrics of duration, storage IO, capacity & CPU utilization were captured during full database backup, differential backup, and restore operations. As shown in the table below, 2.5TB TPROC database is backed up within 6.1 minutes and the size of the backup file is reduced by 3.8X with 20% CPU utilization. The full database backup set can be restored in 7.58 minutes. Based on additional experiments, CPU utilization can be considerably reduced if Pliops XDP is exclusively utilized for offloading the SQL backup, and data volume is served from a traditional RAID10 configuration.
SSD Write Amplification Benefits
The primary challenges with SSDs managed by traditional hardware or software RAID setups (RAID10 or RAID 5) are the write and read amplification overhead that not only restricts the SQL server performance but also reduces SSD longevity. Write amplification introduced by traditional RAID deployment methods significantly increases the amount of data that’s written to SSDs compared to actual SQL server transactional data. Because SSDs have a limited number of write cycles, write amplification adversely affects SSDs durability.
Pliops XDP mitigates the SSD durability issue by providing the in-line compression of SQL server data and then packing and merging data by converting random writes to sequential writes. This process significantly minimizes write amplification, thereby increasing both SQL server performance and SSD durability.
As shown in the chart above, during SQL server transactional workload testing the Pliops XDP provided a high write throughput of 836 MB/s to the SQL Server. However, 4 SSDs incur an aggregate disk write of 360 MB/s – a reduction of 2.3X and the amount of disk writes per SSD is only 91 MB/s, meaning that disk writes are reduced by factor of 9X.. Performance scaling, capacity expansion, data protection and write amplification reduction are essential design components for high-performance databases like SQL Server applications.
Solution Benefits
SQL Server solution testing with Pliops XDP: Key Takeaways
- Performance Scaling: Knowing your SQL Server database solution can scale to meet performance & capacity needs provides peace of mind.
- Hardware optimization: Pliops XDP ensures hardware is fully optimized to drive higher performance and capacity, especially using SQL server standard scenarios where with server CPU and memory are confined to license requirements.
- Fault Tolerant: Pliops’ architecture allows the storage layer to be more elastic and distributed, making it better equipped to handle changing workloads, fault-tolerant for SSD failures, and highly available.
- Efficient Storage and Retrieval: In today’s data-driven world, efficient storage and retrieval of information are critical to the success of businesses and organizations
- Database Backup compression: Enables backup compression for transaction log backups. Compressed backups require less disk space and can reduce the time required for backup and restore operations.
