EMC VFCache Accelerates Microsoft SQL Server

W White Paper

EMC XTREM X MCACHE AC CCELEERATEES MICROSOFFT SQLL SERV VER EMC XtrremSF, EMC E Xtre emCache, EMC V VNX, Miicrosoft SQL Serrver 2008  Xtre emCache e dramatiically imp proves SQ QL perforrmance  VNX X protectts data

EMC Solutions S s Group p Abstract This white e paper descrribes how EM MC XtremCache with EMC VNX accelerrates online tran nsaction processing (OLTTP) performan nce in a Micrrosoft SQL Se erver 2008 enviironment. Novemberr 2013

Copyrightt © 2013 EMC Corporatio n. All Rights Reserved. EMC belie eves the information in th his publicatio on is accurate e as of its publicatio on date. The information is subject to change with hout notice. The inform mation in thiss publication n is provided “as is.” EMC C Corporation n makes no representations or warranties o of any kind w with respect to o the informa ation in s d disclaims imp plied warrantties of this publication, and specifically ness for a parrticular purpo ose. merchanttability or fitn Use, copyying, and disttribution of aany EMC softw ware describ bed in this publicatio on requires an a applicablee software liccense. For the mo ost up-to-datte listing of EEMC product names, see EMC Corpora ation Trademarrks on EMC.co om. All tradem marks used herein are thee property of their respecttive owners. Part Numb ber: H9539.3 3

EMC X XtremCache A Accelerates Microsoft SQ QL Server

EMC XttremSF, EMC XXtremCache, EEMC VNX, Micrrosoft SQL Serrver 2008

,

2

Table of contentss Executive e summary .................................................................................................................................. 4 Busine ess case ........................................................................................................................................ 4 The sto orage perform mance challe enge....................................................................................................... 4 The XtremCache so olution ....................................................................................................................... 5 Serrver-side Flassh caching for maximum speed s ................................................................................ 5 Write-through ca aching to the e array for tottal protection n .................................................................... 6 App plication agnostic.......................................................................................................................... 6 Sha areable and scalable s ..................................................................................................................... 7 Solution architecture a ............................................................................................................................... 8 Introdu uction ........................................................................................................................................... 8 Physiccal architectu ure .............................................................................................................................. 8 EMC VNX V ................................................................................................................................................ 8 XtremCacche technology with Micrrosoft SQL Se erver: testing g and validattion .......................................... 9 Introdu uction ........................................................................................................................................... 9 Worklo oad profile .................................................................................................................................... 9 Recom mmended con nfigurations................................................................................................................ 9 Perform mance chara acteristics ................................................................................................................... 9 Conclusio on ............................................................................................................................................ 12 Reference es ............................................................................................................................................ 13 White papers ....................................................................................................................................... 13 Producct documenta ation........................................................................................................................ 13

EMC X XtremCache A Accelerates M Microsoft SQ QL Server


3

Executive summa ary Business casse

In an a increasing gly competitivve environmeent, businessses are beingg driven to op ptimize bussiness processses and to improve serv ice, while low wering IT cossts. Meeting tthese new w requiremen nts has becom me critical to o the financiaal success off many compa anies. Con nsequently, operational o and a revenue-ggenerating aapplications a are experienccing dramatic deman nds on perforrmance, driveen by: 

Growth in the numberss of active ussers



ons and escaalating servicce level agree ements Time-criticcal applicatio



Increased d complexity of business p processes an nd new analyytic workload ds



Multiple databases d wiith high conccurrent accesss

order to Bussinesses need to consider new approaaches to perfformance challenges in o mee et these dem mands cost efffectively and d without saccrificing data a protection. TThe storage performance e challenge

The e latest servers with multi-core processsors represeent a potentia al performance bottleneck for th he storage su ubsystem. Ass processing capacity and d heavier workloads s syste em is challen nged to keep pace with th he growing I/O are added, the storage dem mands. While e CPU perform mance impro oves 100-fold d every decad de, magneticc disk rem mains relative ely flat, as shown in Figuree 1.

Figu ure 1.

CPU U performance e versus disk d drives

In a traditional architecture, a as shown in Figure 2: 

Reads and d writes are serviced s by t he storage array



Performan nce varies de epending on the back-end d array’s med dia, workload d, and network



,

4

Figu ure 2.

Trad ditional archittecture

What if you coulld double your applicatio on performan nce by decrea asing latencyy and nute? incrreasing IOPS and transacctions per min TThe XtremCa ache ssolution

Serrver-side Flassh caching fo or maximum speed EMC C XtremCache™ is a servver flash cach hing solution that uses intelligent caching softtware and PC CIe flash tech hnology to red ut, which duce latencyy and increasse throughpu dramatically imp proves appliccation perforrmance. emCache sofftware cachess the most freequently useed data on th he server-bassed PCIe Xtre card d, thereby pu utting the datta closer to t he applicatio on, as shown n in Figure 3. This reduces the nee ed to access data d across tthe network ffrom the storrage array, w which me and increaases perform mance. both decreases response tim

Figu ure 3.

Xtre emCache acce elerates I/O peerformance wiithin the serveer



5

The e XtremCache e caching opttimization au utomatically adapts to changing workkloads by determining which data is most m frequenttly referenced d and promo oting it to the server PCIe flassh cache. This means thatt the “hottesst” data automatically ressides on the P card d in the serve er for faster access. a As shown s in Figu ure 4, the XtrremCache ad dvanced arch hitecture com mbines accele erated performance witth data prote ection: 

Reads are e serviced by XtremCachee for performaance



Writes are e passed thro ough to the sstorage array for protectio on

Figu ure 4.

Advvanced archite ecture with EM MC XtremCache

Xtre emCache pro ovides better performancee for read-inttensive applications. In th his test, Xtre emCache dem monstrated a throughput improvemen nt, measured d in transactions per min nute, of 260 percent p and an a 87 percen nt reduction iin read latency. XtremCacche worrks with appllications as diverse d as daatabases, anaalytics, enterrprise applica ation servvers, email, and a web servvers to give th hem the perfformance boost they need d. Xtre emCache enh hances both virtualized aand bare-mettal applicatio ons so you ca an smo oothly migratte your data center to a p private cloud at a pace tha at makes sen nse for you ur business1. Write-through caching c to the array for tootal protectio on emCache pro otects data byy using a writte-through algorithm, which means th hat Xtre writtes persist to o the back-en nd storage arrray. EMC trussted networkked storage, such as the EMC® Symm metrix VMAX™ ™ and the EM MC VNX™ fam mily of storagge arrays, pro otect data with advan nced data serrvices, which h include high h availabilityy, data integrrity, relia ability, and disaster d recovvery. App plication agn nostic Xtre emCache is transparent to o application ns, so no rew writing, retestting, or recerttification is re equired to de eploy XtremC Cache in yourr environmen nt. While this white e paper focusses on Microssoft SQL Servver 2008, the e XtremCache hitecture can n directly enh hance the pe rformance off many other applicationss and arch

1

XtrremCache is not n supported in shared diskk environmentts or active/acctive clusters.



,

6

indirectly enhan nce write-inte ensive appliccations that n now have gre eater access to SAN ources. reso Sha areable and scalable s While directly en nhancing the e performancce of read-inttensive applications, Xtre emCache n indirectly en nhance write-intensive ap pplications th hat now have e greater acccess to can SAN N resources. Xtre emCache offlloads much of o the read trraffic from thee storage arrray, which alllows it to allo ocate greater processing power p to oth er applicatio ons. While on ne application is accelerated with h XtremCache e, the array’ss performancce for other a applications iis e A s XtremCach he is installed d on more servers in maiintained or even slightly enhanced. the environment, the result is i a highly sccalable I/O processing mo odel. The envvironment as a whole, including the seervers and th he storage syystem, is capable of processing incre easingly morre IOPS.

on to EMC For more information about EMC XtremCaache, see thee Introductio

XtreemCache White Paper.



7

Solution architeccture Introduction

Thiss section pro ovides an ove erview of the physical architecture of tthis solution.

Physical a architecture

Figu ure 5 shows the t physical architecture for this solution.

Figu ure 5.

EMC C XtremCache with Microsooft SQL Server and VNX5300 0

The e solution arcchitecture con nsists of a M Microsoft SQLL Server 2008 8 R2, Cisco se erver, and d a VNX stora age array. The e solution ca n be configu ured with variious storage arrays, succh as EMC Symmetrix VMA AXe™ or VNX X. In this solu ution, we use ed VNX5300.. The servver used for this t test conffiguration waas Cisco UCS C-460/M1 rack mount w with four Xeo on X7560 pro ocessors and a total of 32 2 cores. The sserver configguration inclu uded the Xtre emCache servver-based Fla ash cache an nd two 8 Gb//s connection ns to the SAN N switches. EMC VNX

The e EMC VNX family deliverss industry-leaading innovaation and enterprise capabilities for file, f block, an nd object sto orage in a scaalable, easy--to-use solutiion. This storrage plattform combin nes powerfull and flexiblee hardware w with advanced d efficiency, man nagement, and protection n software to o meet the deemanding ne eeds of todayy's bussinesses. The e VNX series is i designed to t meet the h high-performance, high-scalability requirements off midsize and d large busin esses. X and EMC VN NXe™ are su upported by X XtremCache w when used fo or block storage VNX access.



,

8

X XtremCa ache technology with w Micrrosoft SQ QL Serverr: testing g and vvalidatio on Introduction

EMC C tested the capabilities of XtremCach he to accelerrate online trransaction processing (OLTTP) performan nce in a Micrrosoft SQL Seerver 2008 en nvironment. This section presentss the results of the testin g. Notte

Benchm mark results are a highly dep pendent upo on workload, specific app plication requirem ments, and syystem design n and implem mentation. Re elative system performance will varry as a result of these and d other factorrs. Therefore, this workload should nott be used as a substitute for a specificc customer ark when crittical capacityy planning an nd/or producct applicattion benchma evaluation decisionss are contem plated.

W Workload profile

EMC C testing use ed a standard d TPC-E-like O OLTP workloaad, with a 750 GB databa ase and a 90//10 percent read/write r mix. EMC tookk a performan nce baseline to validate the performance characteristics of the enviro onment. EMC C then enable ed XtremCache and took measurements of transactions per ssecond and aapplication-level transacttion ency to demo onstrate the performance p contribution n of XtremCacche to the late app plication.

Recommended configurations

Xtre emCache is enabled e on all of the dataa LUNs. Howeever, XtremCa ache was nott ena abled on the log LUNs beccause they arre dominated d by writes. No specific tuning was requiired for Micro osoft SQL Server. With this configuration, XtremCache use s its cachingg algorithms tto automatically mmediate access. maiintain a copyy of the hottest data for im

Performance e characteristiics

Figu ure 6 comparres the overa all system thrroughput (traansactions pe er second) off the basseline and XtremCache-en nabled enviro onments. Thee availabilityy of the hot data in the server’s Xtre emCache resulted in a 26 60 percent im mprovement in transaction ns per second.



9

Relative Transactions Per Second l i i S d

4.0 3.5

3.6

3.0 2.5 2.0 1.5 1.0 1.0 0 0.5 0.0 XtremCache

Baseliine Figu ure 6.

Rela ative OLTP tra ansactions perr second (TPS)) improvemen nt

As TPS T performa ance improve ed by a facto r of 3.6, therre was an 87 percent redu uction in read d latency, as shown in Fig gure 7. 1.2

Relative Read Latency l i d

1.0 1.0 0 0.8 0.6 0.4 0.2 0.13 0.0 Baseliine Figu ure 7.

XtremCache

Imp pact on applica ation-level lattency

Xtre emCache cop pies and storres the hottesst read data on the PCIe ccard within th he servver. This data a can be repe eatedly read by the appliccation without sending th he I/O request to the back-end b storrage. In a wo rkload with a 90/10 perccent read/wriite mix, thiss results in an n overall average transacction latency decrease of 87 percent. It iss important to o note that in ndividual cusstomers migh ht see differe ent results. Imp provements in application n performancce depend on n a variety off factors, including: 

I/O read to write ratio



s of the workload d Inherent scalability



,

10



Existing constraints within the storrage subsysttem, before d deploying XtremCache



Tuning of the database



Sharing of XtremCache e with other aapplications



11

Conclusiion Ourr testing with a Microsoft SQL Server O OLTP workloaad compared d a system eq quipped with h XtremCache against a baseline b conffiguration witthout XtremC Cache. The Xtre emCache-equ uipped serve er demonstra ted the follow wing perform mance advantages: 

System th hroughput, measured m in t ransactions per second, was 3.6 time es the throughpu ut of the baseline withoutt any changees to applicattions.



Overall tra ansaction lattency was 87 7 percent lesss than the ba aseline.



XtremCache maintaine ed the integrrity and proteection of the data.



,

12

Referencces W White papers

Product documentatiion

For additional in nformation, see s the whitee papers listeed below: 

Introductiion to EMC XtremCache Xt



EMC Xtrem mCache Acceelerates Oraccle—EMC XtreemSF, EMC X XtremCache, EEMC VNX, EMC C Fast Suite, Oracle O Databbase 11g



EMC Xtrem mCache Acceelerates Oraccle—EMC XtreemSF, EMC X XtremCache, EEMC Symmetrix ix VMAX and VMAXe, Oraccle Databasee 11g



EMC Xtrem mCache Acceelerates Virtuualized Oracle le—EMC Xtrem mSF, EMC XtremCacche, EMC Sym mmetrix VMAX AX and VMAXe Xe, VMware vS Sphere, Oraccle Databasee 11g

nformation, see s the produ uct documen nts listed belo ow: For additional in 

EMC Xtrem mCache Dataa Sheet



EMC VNX X Family Data Sheet



13