白皮書

Caching Software Performance Test: Microsoft SQL Server Acceleration with FlashSoft Software 3.8 for Windows Server

Introduction

The objective of this paper is to present the performance gains that can be achieved on “bare metal” and virtualized computing environments through the use of a host-based solid-state storage cache enabled by the FlashSoft® software from SanDisk. Microsoft SQL Server 2014 was installed on a server running Microsoft Windows Server 2012 R2 and performance was measured from a client computer using HammerDB. The database for the test was contained on a storage array comprising SAS-attached hard disk drives (HDDs), which was attached directly to the host server.

 

System Under Test

The computers used for this test were configured as follows:

Hardware

  • Host Server: DellTM PowerEdgeTM R730xd
    • CPU: 2x Intel® Xeon® E5-2690 v3 @ 2.60GHz, 12 cores each
    • System Memory (RAM): 192GB DDR3
    • Storage Controller: Dell PERC H730 Mini RAID controller (embedded); cache size: 1024MB
      • HDD Storage: 12x 300GB SAS 6Gb/s 10K RPM, Seagate ST300MM0006
      • Flash Memory (SSD): 1x 1.3TB PCIe Fusion ioMemoryTM SX350 1300 (cache)
  • Direct Attached Storage Array: Dell PowerVaultTM MD3200
    • 10x 600GB SAS 6Gb/s 10K RPM, Toshiba AL13SEB600
    • The drives were formatted into two virtual disks: DB (2.8TB) and LOG (1.1TB) 
    • Directly connected to host server via SFF-8088 Mini SAS cable
  • Client Computer: Dell PowerEdge R720xd
    • CPU: 2x Intel Xeon E5-2603 @ 1.80GHz, 4 cores each
    • System Memory (RAM): 96GB DDR3
    • Storage Controller: Dell PERC H310 Mini RAID controller (embedded)
      • HDD Storage:
  • 2x 500GB SAS 6Gb/s 7.2K RPM, Seagate ST500NM0001
 

Figure 1: Diagram of hardware configuration for tests

 

Software

  • Host Server Software
    • Windows Server 2012 R2, Windows Server 2012 R2 Hyper-V
    • Microsoft SQL Server 2014
      • Microsoft SQL Server Management Studio: 12.0.4100.1
      • Microsoft Analysis Services Client Tools: 12.0.4100.1
      • Microsoft Data Access Components (MDAC): 6.3.9600.16384
      • Microsoft MSXML: 3.0.6.0
      • Microsoft .NET Framework: 4.0.30319.33440
      • Buffer pool size / RAM reservation for SQL Server: 16GB
  • Client Computer Software
    • Windows Server 2012 R2
    • HammerDB: 2.18
  • Caching Software
    • FlashSoft for Windows Server (version 3.8.0)
      • "fiodrv" Driver Version: 4.1.0.62685
    • Flash Memory Driver: Fusion ioMemory SX350-1300, VSL Driver 4.2.1
 

Test Procedure

The benchmark test was conducted multiple times to measure and compare performance of the non- accelerated HDD storage backend and the same HDD backend accelerated using a FlashSoft software write back cache. Two sets of tests were performed:

  • Workload Acceleration: Measure the increased number of Transactions Per Minute (TPM) possible for the same storage configuration using a FlashSoft write-back cache compared to the non-accelerated HDD baseline.
  • File Exclusion: Observe the increased number of Transactions Per Minute possible using the same FlashSoft accelerated workload on a virtual machine with certain files (operating system and logs) excluded from acceleration.

General Setup (for all tests)

  • 1) Install and configure Windows Server 2012 R2 and SQL Server 2014 on the host server.
  • 2) Install and configure flash memory (SSD) and FlashSoft software on the host server.
  • 3) Install and configure HammerDB on the client computer.
  • 4) Connect the host server to the backend storage array using a SAS cable.

 

Bare Metal Workload Acceleration Test Setup

1) On the host server map to the MD storage array and create addressable storage volumes for the benchmark test:

  • Data ( F: ) 488GB (RAID 5)
  • Log ( G: ) 195GB (RAID 10)
  • Cache ( H: ) 135GB (managed by Fusion ioMemory VSL driver 4.2.1)
  • Any additional capacity should remain unallocated
 

Figure 2: Storage volume mappings on the host server

 

2) Configure HammerDB on the client computer using the following settings:

  • Database size: 409GB
  • ODBC Driver: ODBC Driver 11 for SQL Server
  • Total Transactions per User: 1,000,000,000
  • Ramp up Time: 150 minutes
  • Test Duration: 60 minutes

Figure 3: HammerDB client settings

 

  • 3) Run HammerDB to measure performance of the non-accelerated backend (baseline).
  • 4) Enable FlashSoft software to accelerate the DATA disk only (do not accelerate the OS or LOG disks).
  • 5) Run HammerDB to measure performance of the accelerated backend.
  • 6) Compare results.
  •  

File Exclusion Test Setup

Starting with the Workload Acceleration Test configuration described above:

1) Reconfigure storage volumes of the MD storage array as follows:

  • Data ( F: ) 2,048GB (RAID 5)
  • Log ( G: ) 195GB (RAID 10)
  • Cache ( H: ) 102GB (managed by Fusion ioMemory VSL driver 4.2.1)
  • Any additional capacity should remain unallocated
 

Figure 4: Storage volume mappings on the host server

 

2) Enable Hyper-V on the host server and create one virtual machine.

  • RAM: 39GB
  • Create the following VHDX storage volumes in the virtual machine. Their associated disk image files should be stored on volume F: of Disk 0 on the physical host server.
    • Data: ( F: ) 300GB (295GB) VHDX
    • Log: ( G: ) 100GB VHDX
    • OS: ( C: ) 100GB VHDX
 

Figure 5: Storage volumes of the virtual machine

 

Figure 6: Disk image files on the host server

 

  • Install and configure Windows Server 2012 R2 as the guest operating system on the virtual machine.
  • Install and configure SQL Server 2014 on the virtual machine.

3) Enable FlashSoft on the host server to accelerate the disk image files located on volume F: which in turn will accelerate performance of the Data, Log and OS VHXD volumes of the virtual machine.
4) Run HammerDB on the client computer to measure SQL performance of the accelerated virtual machine.
5) On the host server disable FlashSoft acceleration of the disk image files for Log and OS.
6) Run HammerDB on the client computer to measure SQL performance of the virtual machine with only the Data volume accelerated.
7) Compare results.

 

Workload Acceleration Test (Bare Metal)
Test Summary Score
(Transactions Per Minute)
Baseline
Storage backend not accelerated.
  • HammerDB benchmark
  • Database size: 409GB
  • Ramp up time: 150 minutes
  • Test time: 60 minutes
  • SQL buffer pool size: 16GB
93,042 TPM
Accelerated
Data files accelerated with FlashSoft write-back cache.
  • HammerDB benchmark
  • Database size: 409GB
  • Cache size: 135GB (33% size of workload)
  • Write-back cache
  • OS and log not accelerated
  • Ramp up time: 150 minutes
  • Test time: 60 minutes
  • SQL buffer pool size: 16GB
403,058 TPM
x4.38 Improvement over Baseline

Table 1: Workload acceleration test

 

Figure 7: Increased TPM performance with FlashSoft write-­‐back caching

 

Figure 8: Baseline SQL Transactions

 

Figure 9: Accelerated SQL Transactions

 

Figure 10: Baseline disk activity

 

Figure 11: Accelerated disk activity

 

File Exclusion Test Benchmark (Hyper-V)
Test Summary Score
(Transactions Per Minute)
Baseline
Storage backend not accelerated.
  • HammerDB benchmark
  • Database size: 295GB
  • Ramp up time: 150 minutes
  • Test time: 60 minutes
  • SQL buffer pool size: 16GB
71,744 TPM
Accelerated w/o File Exclusion
All files accelerated with FlashSoft write-back cache.
  • HammerDB benchmark
  • OS, log and database accelerated
  • Database size: 295GB
  • Cache size: 135GB (46% size of workload)
  • Write-back cache
  • Ramp up time: 150 minutes
  • Test time: 60 minutes
  • SQL buffer pool size: 16GB
206,852 TPM
x2.88 Improvement over Baseline
Accelerated w/ File Exclusion
Only database files accelerated with FlashSoft write-back cache. OS and log VHDx files excluded from acceleration.
  • HammerDB benchmark
  • Database accelerated only (log, OS excluded)
  • Database size: 295GB
  • Cache size: 135GB (46% size of workload)
  • Write-back cache
  • Ramp up time: 150 minutes
  • Test time: 60 minutes
  • SQL buffer pool size: 16GB
425,527 TPM
x5.93 Improvement over Baseline

Table 2: File exclusion test

 

Figure 12: Enhanced caching performance utilizing file-­‐exclusion feature

 

Analysis / Conclusion

Data measured in the tests illustrate how FlashSoft software can significantly improve performance of Microsoft SQL Server workloads on both bare metal servers and Hyper-V virtual machines compared to traditional all-HDD backend storage.

The tests were conducted directly on the bare metal server and virtualized with Hyper-V using a Microsoft SQL Server workload measured by the HammerDB benchmark tool. Although the tests were constructed to be comparable to the conditions typically encountered in real-world computing and to generate data that reveal representative application performance, the tests can only be considered a demonstration of the capability of FlashSoft software – the data should not be interpreted as the performance impact of FlashSoft software for all workload types and storage environments. The actual performance of any caching solution is highly dependent upon the workload and the computing environment in which it is used.

The application performance test conducted on the bare metal host server demonstrates a 4.38 times increase in transactions per second as measured by HammerDB. The cache was only 1/3 the size of the database (workload) and was configured in write-back cache mode. The system activity graphs (figures 8 through 12) show operation of the cache as it warms up and enhances performance, as shown by the increased levels and intensity of SQL transactions and disk activity.

The file exclusion test highlights operation of a unique FlashSoft feature that allows the administrator to exclude individual files that would not typically benefit from caching. This frees caching resources to be applied toward “cacheable” files such as the database instead of the log or operating system. Additionally, it eliminates “thrashing” of the cache that occurs as the log is regularly updated with non- cacheable data intermixed with data of the database. The file exclusion test was conducted within a virtual machine to allow the log and operating system VHDx files to be excluded from cache acceleration. The parameters of the database and virtual machine storage were adjusted for this test to operate in the virtual machine. Table 2 illustrates a 2.8 times increase of performance over baseline when the log and operating system files are accelerated together with the database. Excluding the log and operating system in order to accelerate the database only further doubled the accelerated performance, resulting in nearly 6 times increase in performance compared to the baseline.

Disclosures

Specifications are subject to change.

READY TO FLASH FORWARD?

Whether you’re a Fortune 500 or five person startup, SanDisk has solutions that will help you get the most out of your infrastructure.

VIA
EMAIL

Go ahead, ask us some questions and we'll get back to you with answers.

Let's Talk
800.578.6007

Don't wait, let's just talk now and start building the perfect flash solution.

Global Contact

Find contact information for offices all over the world.

SALES INQUIRIES

Whether you'd like to ask a few initial questions or are ready to discuss a SanDisk solution tailored to your organizations's needs, the SanDisk sales team is standing by to help.

We're happy to answer your questions, so please fill out the form below so we can get started. If you need to talk to the sales team immediately, please phone: 800.578.6007

欄位不能為空。
欄位不能為空。
請輸入有效的電子郵件地址。
欄位只能包含數字。
欄位不能為空。
欄位不能為空。
欄位不能為空。
欄位不能為空。
欄位不能為空。
欄位不能為空。

Please indicate your areas of interest:

你必須選擇一個選項。

Questions or comments:

你必須選擇一個選項。

Thank you. We have received your request.