ACCELERATE SQL SERVER 2014 WITH BUFFER POOL EXTENSION ON LSI NYTRO WARPDRIVE

EXECUTIVE SUMMARY

Microsoft® _{SQL Server}® _{2014 builds upon its mission-critical capabilities by providing breakthrough}

performance, availability and feature sets for your crucial database applications. One such feature, Buffer Pool Extension (BPE), provides database administrators with the option to increase transaction performance by utilizing non-volatile, NAND-flash based storage in order to extend the buffer pool size. BPE quickly increases the capacity to read pages for query execution while maintaining data integrity. Deployments that experience factors such as a high degree of read requests, those that are memory bound, and/or cannot accommodate additional host Dynamic Random Access Memory (DRAM), can benefit from this feature. The LSI® _Nytro™ _WarpDrive™ _{flash accelerator card is designed to provide the low}

latency and reliability features required in order to fully enable the benefits offered by BPE. This paper will delve into the Microsoft SQL Server 2014 BPE feature and the challenges it addresses when placed on a LSI Nytro WarpDrive flash accelerator card.

PROBLEM DEFINITION

Administrators are faced with decisions in relation to scaling transactions. Traditionally, this often involved a scale up approach by adding more memory or CPU to an individual server. In the case of DRAM, prices have decreased over the years. However, the growth of data sets that need to be read quickly are rapidly increasing to the point that it is not always cost-effective to add more memory to a server. Limitations such as DIMM size or the number of required DIMM slots can be costly depending on the DRAM type, server vendor requirements, and interoperability needs.

HIGH-LEVEL SOLUTION

Microsoft has created the SQL Server 2014 BPE feature to help address these limitations utilizing non-volatile NAND-flash storage. The BPE feature functions as a Level Two (L2) cache, with the main Buffer Pool functioning as a Level One (L1) cache. For workloads that consist primarily of frequently-accessed reads, SQL Server will automatically transfer these data pages to the (L2) cache as necessary. Only clean pages that are evicted from the Buffer Pool (L1) are transferred to retain data integrity.

A simple alter server configuration command is used to specify the location and size of the file for the BPE feature. By placing this file on a PCIe® _{flash Nytro WarpDrive flash acceleration card, transaction}

performance can be improved for read-heavy workloads when database working sets no longer fit into the main Buffer Pool (L1) cache that is backed by host DRAM.

ACCELERATE SQL SERVER 2014 WITH BUFFER

POOL EXTENSION ON LSI

®

_NYTRO

™

_WARPDRIVE

®

THE CHALLENGE

Meeting database performance demands. Increasing data that needs to be read quickly can be bottlenecked by the reluctance to add memory due to cost.

THE SOLUTION

The SQL Server 2014 BPE feature addresses this bottleneck. When used in conjunction with the LSI Nytro WarpDrive flash accelerator card, this feature can be fully enabled to maximize results.

THE RESULT

Decreased lazy writes, stolen server memory, a 30% improvement in transactions per second while page life expectancy increases1_.

SQL SERVER ENGINE

Buffer Manager Page Retrieval

Page Cached Buffer Pool L1 Cache (Hot Data) Write Page Read Page

Buffer Pool Extension L2 Cache (Lukewarm Data)

DRAM

LSI NYTRO WARPDRIVE CARD INTRODUCTION

The LSI Nytro WarpDrive flash accelerator card is a PCIe solid state primary storage solution for accelerating SQL Server workloads. The small PCIe footprint gives database administrators the option of easily transforming their storage subsystem into solid state storage. Traditional hard drives can put a strain on resources requiring more power, cooling, and space compared to only a single PCIe slot. Built with advanced solid state capabilities and technology that optimizes endurance and reliability, the LSI Nytro WarpDrive flash card offers an enterprise-ready solution. Using a host-offload

architecture, the card reduces server CPU and DRAM dependency, leaving these resources for the host and SQL Server Instance to utilize.

Nytro WarpDrive key features include:

• In-box drivers for Windows Server® 2012 R2 • All Microsoft drivers are WHQL qualified

- Installs as a single drive with no user configuration required

• SandForce® _DuraClass™ _{technology provides enhanced flash reliability, endurance and power}

efficiency

• RAISE™ – RAID-like data protection against NAND media errors • Dynamic overprovisioning

• Less than five second recovery from power failure

• PCIe 2.0 for HHHL (half height, half-length), PCIe 3.0 support for FHHL (full height, half-length) • Response times down to 50 microseconds

• High throughput (up to 4GB/s on PCIe 3.0 products) • Enterprise quality and reliability

• Global Service+ Program for 24x7 access to technical service for LSI Nytro products

SOLUTION DETAILS AND TEST CONFIGURATION

The objective of the following test configuration is to demonstrate the performance benefits of enabling the SQL Server 2014 BPE feature when placed on an LSI Nytro WarpDrive card. HARDWARE

WRITER

TECHNICAL REVIEWERS

SPECIAL

ACKNOWLEDGEMENT

ABOUT MICROSOFT

Microsoft employs over 90,000 employees and is the worldwide leader in software, services, and solutions that help people and businesses realize their full potential. SQL Server Database is a platform that gives customers the ability to scale data across cloud and on premise devices with tools providing business insight.

Source: http://www.microsoft.com/en-us/news/inside_ms.aspx

ABOUT LSI

LSI employs over 5,000 employees worldwide, designing semiconductors and software that accelerate storage and networking in datacenters, mobile networks and client computing. LSI Nytro WarpDrive application acceleration cards are designed to offer high performance with low latency and a low CPU burden for efficient application acceleration.

Item Quantity Version Details/Notes

Dell PowerEdge Server 1 T620

Intel Xeon Processor 2 E52650 • 2.00 GHz Physical 8 core • Total 32 cores w/hyper threading Memory 6 Hyundai • PC3-12800 16 GB

• Total 96GB Nytro WarpDrive 1 NWD-BLP4-1600 • SQL Server Data File

• SQL Server Log File • Size = 1.14TB • Interface = PCI-E Gen 2.0

• Firmware Package Version = 11.00.00.00 • Firmware Version = 111.00.00.00 • Driver = Widows Server 2012 R2 • Driver Version = 2.10.66.0

Item Quantity Version Details/Notes

Windows Server 2012 R2 1 6.3.9600 Build 9600 Data Center Edition

SQL Server 2014 1 12.0.2000.8 • Buffer Pool Extension File Size = 290GB • Database Size = 1TB

• Users = 300 LSI MegaRAID Storage Manager 1 13.08.50.03

SOFTWARE

BEST PRACTICES

The following best practices were used for this solution/configuration.

Operating System Parameter Configuration

Windows Updates Ensure Windows has the latest updates applied.

Drivers Ensure the drivers are up to date. Page File Set manually and maximum size when

possible. Avoiding Fragmentation Volume Formatting Ensure Windows volumes are formatted

with 64K allocation unit for SQL Server files. Power Management Configure for High Performance.

Server Parameter Configuration

BIOS Updates Ensure BIOS is up to date.

BIOS Configuration Disable CPU power management throttling features.

Storage Parameter Configuration

Firmware Ensure firmware is up to date.

SQL Server Parameter Configuration

Min/Max memory Configure based on operating system total. Configure buffer pool memory accordingly; leave 4GB for operating system.

Local Security Policy Lock pages in memory. Assign privilege to SQL Server service account.

Instant file initialization Perform volume maintenance tasks. Assign privilege to SQL Server service account.

Backup Compression Source backups were performed using the SQL Server backup compression option. Volume Assignment All SQL Server data and log files located on

one volume. NytroWarpDrive only. Volume Assignment BPE file assigned to dedicated volume. BPE file only.

Note: For more detailed information regarding the LSI Nytro WarpDrive card installation/ configuration please reference the LSI Nytro WarpDrive Card User Guide and ReadMe.txt files available online with the driver.

Note: For more detailed information regarding installation/configuration of SQL Server 2014 features, and Windows Server 2012 R2 platforms please see the applicable documentation listed at

TEST ENVIRONMENT PERFORMANCE METRICS

Testing consisted of a standard OLTP (Online Transaction Processing) type workload of 90% reads with SQL Server 2014. The same workload was used for testing both with and without using the BPE feature on the Nytro WarpDrive card. To evaluate the effectiveness of the SQL Server 2014 BPE feature the following performance metrics were used to monitor the behavior of the buffer pool, its response to memory pressure, and the processing of transactions.

• Lazy writes: number of buffers written by the buffer manager’s lazy writer.

• Page life expectancy: number of seconds a page will stay in the buffer pool without references. • Stolen server memory: specifies the amount of memory the server is using for purposes other

than database pages.

• Extension in use as a percentage: Percentage of the buffer pool extension paging file occupied by buffer manager pages.

• Business application transactions/second: Number of transactions completed for the user application.

When SQL Server experiences memory pressure some early signs can be an increase in lazy writes, and stolen server memory while page life expectancy decreases. Eventually if the host server memory becomes the bottleneck and cannot accommodate the database working set size, transaction performance can suffer as a result.

SOLUTION PERFORMANCE RESULTS

The following performance results were documented based on the testing described above.

Buffer Manger Lazy Writes/Sec

BPE on (Nytro WarpDrive) Non-BPE Image 2: High buffer pool lazy write counts.

Buffer Manger Page Life Expectancy

Sec

onds

BPE on (Nytro WarpDrive) Non-BPE TPC-E Test Time

300 250 200 150 100 50 0 12:28:48 12:57:36 13:26:24 13:55:12 14:24:00 14:52:48 15:21:36 15:50:24 16:19:12

The LSI Nytro WarpDrive (orange) improves Page Life Expectancy

Image 3: Sustained higher page life expectancy with BPE file on Nytro WarpDrive card.

% Buffer Pool Extension Usage

Per

cen

tage %

BPE on (Nytro WarpDrive) TPC-E Test Time

12:00:00 12:28:48 12:57:36 13:26:24 13:55:12 14:24:00 14:52:48 15:21:36 15:50:24 16:19:12 16:48:00 80 60 40 20 0

Image 4: Buffer pool extension (BPE) usage: file located on Nytro WarpDrive card showing ramp time and consistency with a maximum of 69% usage.

Average Stolen Server Memory (KB)

M emor y KB 5,000,000 4,500,000 4,000,000 3,500,000 3,000,000 2,500,000 2,000,000 1,500,000 1,000,000 500,000 0

BPE on (Nytro WarpDrive) Non-BPE

4,314,398

0

Image 5. Stolen server memory: dram is not affected, instead being used for database pages with the BPE file located on Nytro WarpDrive card.

FOR MORE INFORMATION AND SALES OFFICE LOCATIONS, PLEASE VISIT THE LSI WEBSITE AT: lsi.com

Image 6. Transaction performance comparison: user applications improve without the negative effects of memory pressure when using the SQL Server 2014 buffer pool extension feature on Nytro WarpDrive card.

Business Application Transactions/Sec

Sec

onds

Business Application Transactions

0.00 50.00 100.00 150.00 200.00 250.00 300.00 350.00 400.00

BPE on (Nytro WarpDrive) Non-BPE

281.00 368.00 30% I mpr ovemen t

SUMMARY

SQL Server 2014 database administrators now have an option to increase transaction performance by taking advantage of the performance improvements and reliability of non-volatile flash based storage. The low latency, high performance and power efficiency of PCIe flash provides a compelling solution for increasing database speed.

When your workloads consist of frequently accessed read requests, the SQL Server instance shows signs of becoming memory bound, and adding more server DRAM is not an option, utilizing the Buffer Pool Extension with the LSI Nytro WarpDrive product offering can be a winning combination.