SOLUTION BRIEF

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

Bu�er ManagerPage Retrieval

Page Cached

Bu�er PoolL1 Cache

(Hot Data)

Write Page

Read Page

Bu�er Pool ExtensionL2 Cache

(Lukewarm Data)

DRAM

Nytro WarpDrive

Image 1. Lukewarm data resides on L2 cache with the Nytro WarpDrive card with hot data residing on DRAM.

SOLUTION BRIEF

Accelerate SQL Server 2014 with Buffer Pool Extension on LSI Nytro WarpDrive | 2

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

WRITERMark Pokorny

TECHNICAL REVIEWERSGina Geisel

Jasraj Dange

SPECIAL ACKNOWLEDGEMENTThomas Hammond-Doel

Kimberly Leyenaar

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• Formatting = Performance Over Provisioning

Nytro WarpDrive 1 NWD-BLP4-400 • SQL Server BPE File• Size = 298GB• 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• Formatting = Performance Over Provisioning

SOLUTION BRIEF

Accelerate SQL Server 2014 with Buffer Pool Extension on LSI Nytro WarpDrive | 3

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

http://www.microsoft.com.

SOLUTION BRIEF

Accelerate SQL Server 2014 with Buffer Pool Extension on LSI Nytro WarpDrive | 4

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.

Bu�er Manger Lazy Writes/Sec

6,000

5,000

4,000

3,000

2,000

1,000

0

Lazy

Writ

es/S

ec

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

TPC-E Test Time

BPE on (Nytro WarpDrive) Non-BPE

Image 2: High buffer pool lazy write counts.

SOLUTION BRIEF

Accelerate SQL Server 2014 with Buffer Pool Extension on LSI Nytro WarpDrive | 5

Bu�er Manger Page Life Expectancy

Seco

nds

BPE on (Nytro WarpDrive) Non-BPE

TPC-E Test Time

300

250

200

150

100

50

012: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.

% Bu�er Pool Extension Usage

Perc

enta

ge %

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)

Mem

ory

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

0BPE 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

LSI and the LSI & Design logo are trademarks or registered trademarks of LSI Corporation in the United States and/or other countries. All other brand and product names may be trademarks of their respective companies.

LSI Corporation reserves the right to make changes to the product(s) or information disclosed herein at any time without notice. LSI Corporation does not assume any responsibility or liability arising out of the application or use of any product or service described herein, except as expressly agreed to in writing by LSI Corporation; nor does the purchase, lease, or use of a product or service from LSI Corporation convey a license under any patent rights, copyrights, trademark rights, or any other of the intellectual property rights of LSI Corporation or of third parties. LSI products are not intended for use in life-support appliances, devices, or systems. Use of any LSI product in such applications without written consent of the appropriate LSI officer is prohibited.

Copyright ©2014 by LSI Corporation. All rights reserved. > 0414 SB0041.01

North American HeadquartersSan Jose, CAT: +1.800.372.2447 (within U.S.)T: +1.800.372.2447 (outside U.S.)

LSI Europe Ltd.European HeadquartersUnited KingdomT: [+44] 1344.413200

LSI KK HeadquartersTokyo, JapanT: [+81] 3.5463.7165

SOLUTION BRIEF

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

Seco

nds

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% Improvement

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.