Delivering Accelerated SQL Server Performance with OCZ’s ZD-XL SQL Accelerator
Performance Test Results for Analytical (OLAP) and Transactional (OLTP) SQL Server 2012 Loads
Allon Cohen, PhD
Eli Ben Namer
The OCZ white paper entitled, “Introducing ZD-XL SQL Accelerator” addresses the key elements required to efficiently accelerate SQL Server applications (via flash volumes, flash caching, cache policy optimization, and cache pre-warming) and how these elements are seamlessly designed into the ZD-XL SQL Accelerator architecture. This white paper presents application load testing results performed in analytical and transactional environments as a follow-up to the “Introducing ZD-XL SQL Accelerator” white paper. By introducing ZD-XL SQL Accelerator into a SQL Server 2012 environment, query completion times are greatly improved while processing times are dramatically reduced unleashing accelerated application performance.
The Microsoft SQL Server 2012 enterprise database management system (DBMS) includes features and enhancements that improve database availability, simplify the moving of databases between instances, employ more productive management and development tools, and provide significant enhancements in performance, programmability and security. These improvements require that data access latency and transactional input/output operations per second (IOPS) deliver optimal performance so that concurrent users can be serviced without contention in transactional environments heightening the user experience. Additionally, access rates and database read latencies can significantly impact the time it takes to complete analytical queries in data warehouse environments requiring an optimized and efficient solution that accelerates SQL Server workloads.
OCZ’s new ZD-XL SQL Accelerator is designed to provide optimized and efficient flash acceleration for SQL Server environments through the tight integration of innovative hardware and software elements. This plug-and-play solution has earned the Best of Interop® award in the Data Center & Storage category through a potent combination of lightning fast flash performance, a unique cache mechanism that makes advanced and statistically-optimized decisions on what data to cache, and wizard-based implementation software that enables database administrators to setup caching policies that optimize application performance based on SQL Server workloads.
Performance Testing: Analytical Workloads
To simulate a typical data warehouse application, the performance tests included a 1.7 TB database to run data warehouse queries based on the TPC-H query set (includes an SF-1000 DB with columnar indexes added). The performance test compared an MS SQL Server 2012 database running the standard benchmark queries before and after ZD-XL SQL Accelerator was added to the host, recording the completion time of the data warehouse queries. Two baseline reference environments were used for testing, one that included a SAN and one that did not. Each baseline reference environment (SAN-based and SAN-less) utilized a ‘before’ test configuration that did not include the ZD-XL SQL Accelerator and an ‘after’ test configuration that did.
SAN-based Reference Environment
The SAN-based reference environment deployed SQL Server 2012 on a Dell PowerEdge R810 host server (supports four 10-core E7-4850 2.0GHz processors equating to 40 total cores). In this test environment, the main database resided on a connected 10GbE Dell Equalogic SAN array and utilized a RAID5 LUN for the database and a RAID10 LUN for tempDB and log files. The host server connected to the SAN in this ‘before’ configuration did not include the ZD-XL SQL Accelerator. This ‘before’ configuration is depicted in Figure 1.
Figure 1: The ‘before’ SAN-based reference environment without OCZ’s ZD-XL SQL Accelerator
The ‘after’ configuration (or the flash accelerated environment) utilizes the same server/SAN configuration with the addition of ZD-XL SQL Accelerator. In this test environment, ZD-XL SQL Accelerator flash caching was used to accelerate read operations from the main database residing on the SAN while tempDB and log files were placed on ZD-XL SQL Accelerator flash volumes enabling these workloads to be efficiently distributed between all available flash resources. This ‘after’ configuration is depicted in Figure 2.
Figure 2: The ‘after’ SAN-based environment with the addition of OCZ’s ZD-XL SQL Accelerator
SAN-less Reference Environment
The second baseline reference environment used for performance testing was SAN-less in which the main database was stored on a RAID0 hard disk drive (HDD) configuration that used five (5) 10,000 rpm SAS drives as depicted in Figure 3. This ‘before’ SAN-less configuration represents a standalone implementation that used only internal drives to run SQL Server.
Figure 3: The ‘before’ SAN-less reference environment without OCZ’s ZD-XL SQL Accelerator
The ‘after’ configuration (or the flash accelerated environment) utilizes the same server/HDD configuration with the addition of ZD-XL SQL Accelerator. In this test environment, ZD-XL SQL Accelerator flash caching was used to accelerate read operations from the main database while tempDB and log files were placed on ZD-XL SQL Accelerator flash volumes enabling these workloads to be efficiently distributed between all available flash resources. This ‘after’ configuration is depicted in Figure 4.
Figure 4: The ‘after’ SAN-less environment with the addition of OCZ’s ZD-XL SQL Accelerator
PLEASE NOTE: Complete configuration details are outlined in Appendix A.
Test Results: Analytical Workloads
To simulate sustained data warehouse loads in a production SQL Server 2012 environment, a set of 22 benchmark analytical queries (TPC-H based) were run and the time it took to complete these queries were recorded for the ‘before’ and ‘after’ SAN-based and SAN-less configurations.
The 22 TPC-H based benchmark queries are outlined as follows:
The completion time for the 22 data warehouse queries tested in the SAN-based environment ‘before’ and ‘after’ flash-based acceleration is summarized in Graph 1. As recorded, the green bars represent the configuration ‘before’ ZD-XL SQL Acceleration was added and the blue bars represent the addition of ZD-XL SQL Acceleration. The immediate takeaway is that once ZD-XL SQL Accelerator was added to the server/SAN configuration, query completion times for all queries was reduced, and in some cases, the longest queries were completed in less than a third of the time to complete on the SAN.
Graph 1: Data Warehouse Query Completion times in a dedicated SAN environment ‘before’ (green bars) and ‘after’ (blue bars) the addition of ZD-XL SQL Accelerator
Similarly, the completion time for the 22 data warehouse queries tested in the SAN-less environment ‘before’ and ‘after’ flash-based acceleration is summarized in Graph 2. As recorded, the green bars represent the configuration ‘before’ ZD-XL SQL Acceleration was added and the blue bars represent the addition of ZD-XL SQL Acceleration. As seen in this case scenario, the completion time of all queries in a SAN-less environment was even more dramatically reduced, with the longest queries completing in small fraction of the time it took to complete on the SAS-based HDDs in RAID0. In many cases, query completion times were reduced from hours to minutes.
Graph 2: Data Warehouse Query Completion times in a SAN-less environment ‘before’ (green bars) and ‘after’ (blue bars) the addition of ZD-XL SQL Accelerator
It is also interesting to note that the accelerated configuration when ZD-XL SQL Accelerator is added to the HDD configuration, a considerably lower completion time for the total of 22 queries is achieved even when compared to a SAN environment with a server that contains twice the memory. In this case the total completion time for all 22 queries with ZD-XL SQL Accelerator in a SAN-less configuration was 67 minutes versus 123 minutes in a SAN-based environment. This exemplifies the fact that in many cases the addition of ZD-XL SQL Accelerator can negate the need for larger SANs or more server memory thereby considerably lowering total cost of ownership (TCO) while significantly improving performance.
Test Results: Transactional Workloads
The SAN-based and SAN-less configurations used to performance test analytical workloads were also used to test SQL Server 2012 sustained transactional data access performance utilizing OLTP loads in a simulated 5000 Warehouse TPC-C based environment. The performance tests featured transaction rates (such as new orders) for multiple concurrent user counts (such as 10, 20 and 50).
After recording the transactional performance tests for both the SAN-based and SAN-less baseline reference environments, the tests were then accelerated using ZD-XL SQL Accelerator caching for the main database tables while placing the tempDB and log files on ZD-XL SQL Accelerator flash volumes. Graphs 3 and 4 summarize the transactional rates before and after the addition of ZD-XL SQL Accelerator to these baseline reference environments.
Graph 3: OLTP new order transaction rate in a SAN-based environment ‘before’ (green line) and ‘after’ (blue line) the addition of ZD-XL SQL Accelerator
Graph 4: OLTP new order transaction rate in a SAN-less environment ‘before’ (green line) and ‘after’ (blue line) the addition of ZD-XL SQL Accelerator
Graph 5: New order transaction rate comparison depicts the highest performance achieved for SAN-less HDD configurations, SAN-based configurations and SAN-less configurations with ZD-XL SQL Accelerator added
As indicated by the results of the new order transactional performance for both SAN-based and SAN-less environments, the addition of the ZD-XL SQL Accelerator card to these test environments increased the rate count significantly. In the SAN-based environment, ZD-XL SQL Accelerator generated over 115,000 new orders for 50 users where the similar configuration without ZD-XL SQL Accelerator was only able to produce over 12,000 new orders. Similarly in the SAN-less environment, ZD-XL SQL Accelerator generated over 180,000 new orders for 50 users where the similar configuration without ZD-XL SQL Accelerator was only able to produce over 35,000 new orders. This comparison is summarized in Graph 5 which shows the highest achieved new order transaction rate performance of the SAN-less HDD configuration, the SAN-based configuration, and the SAN-less HDD configuration accelerated by ZD-XL SQL Accelerator. Similar to the results from the analytical load test results, the addition of ZD-XL SQL Accelerator not only significantly increased transactional performance, but also showed that internal HDDs accelerated with ZD-XL SQL Accelerator significantly outperformed the SAN environment. As a result, IT managers can accelerate internal server HDDs with ZD-XL SQL Accelerator eliminating the need for costly back-end SANs.
Virtualized Flash Volume Performance Testing
One of the key capabilities featured within ZD-XL SQL Accelerator is the ability to expose some of the flash resources as flash volumes while using the remaining flash for caching. Exposing the flash resources as flash volumes is ideal for tempDB and write log loads and for smaller user databases that fit completely on flash and do not require a SAN copy of the data.
To measure flash volume direct I/O performance, a SQLIO load generation tool was used comparing RAID0 HDDs versus the ZD-XL SQL Accelerator flash volumes. Utilizing 64KB pages (the most common command size for SQL Server 2012 under many loads) and 32 outstanding I/Os, the random read results showed a significant performance increase of over 16,000 IOPS and over 1,000 MB/s bandwidth on the ZD-XL SQL Accelerator flash volume. This provided an over 40x performance boost when compared against the RAID0 HDD configuration which was only able to achieve under 400 IOPS and approximately 24 MB/s bandwidth. These results are depicted in Graph 6.
Graph 6: Comparison of direct I/O performance of ZD-XL SQL Accelerator flash volumes versus a RAID0 HDD configuration
OCZ’s new ZD-XL SQL Accelerator is a plug-and-play, tightly integrated hardware/software enterprise solution that accelerates Microsoft SQL Server database performance and provides DBAs simple best practice wizards for quick and easy deployment. SQL Server OLTP and OLAP workloads benefit from accelerated flash performance via caching policies that ensure high hit ratios for the most relevant SQL data. This innovative cache mechanism also employs unique pre-warming cache logic which loads critical data to cache in advance of SQL I/O access to it. The tight integration of flash hardware, acceleration software, firmware and driver delivers an immediate performance boost, reduced TCO and improved productivity whether the environment is SAN-based or SAN-less.
Appendix A: Testing Configurations
SAN-based Analytical Load Test Environment
SAN-less Analytical Load Test Environment
SAN-based Transactional Load Test Environment
SAN-less Transactional Load Test Environment