White Paper

EMC PERFORMANCE ACCELERATION FOR MICROSOFT SQL SERVER 2012 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012  SQL Server acceleration with XtremCache  Storage optimization with FAST VP

EMC Solutions Abstract This white paper describes the impact of EMC® XtremCache™ in a virtualized Microsoft SQL Server 2012 environment with Availability Groups on an EMC Symmetrix VMAX 10K system. The paper shows the performance improvements delivered by XtremCache, which caches the most frequently referenced data on a server-based PCIe card, eliminating the need to access the storage array. This paper also shows the integration of XtremCache with VMware vSphere 5 and the ease of maintenance in a virtualized environment. December 2013

Copyright © 2013 EMC Corporation. All Rights Reserved. EMC believes the information in this publication is accurate as of its publication date. The information is subject to change without notice. The information in this publication is provided “as is.” EMC Corporation makes no representations or warranties of any kind with respect to the information in this publication, and specifically disclaims implied warranties of merchantability or fitness for a particular purpose. Use, copying, and distribution of any EMC software described in this publication requires an applicable software license. For the most up-to-date listing of EMC product names, see EMC Corporation Trademarks on EMC.com. VMware, ESXi, VMware vCenter, and VMware vSphere are registered trademarks or trademarks of VMware, Inc. in the United States and/or other jurisdictions. Intel and Xeon are trademarks of Intel Corporation in the U.S. and/or other countries. All other trademarks used herein are the property of their respective owners. Part Number H10738.2

EMC Performance Acceleration for Microsoft SQL Server 2012 2 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Table of contents Executive summary............................................................................................................................... 5 Business case .................................................................................................................................. 5 Solution overview ............................................................................................................................ 5 Key results ....................................................................................................................................... 5 Introduction.......................................................................................................................................... 6 Purpose ........................................................................................................................................... 6 Scope .............................................................................................................................................. 6 Audience ......................................................................................................................................... 6 Terminology ..................................................................................................................................... 6 Technology overview ............................................................................................................................ 7 Introduction to the key components ................................................................................................. 7 EMC XtremCache .............................................................................................................................. 7 Server-side flash caching for maximum speed ............................................................................. 7 Write-through caching to the array for total protection ................................................................. 8 Application agnostic.................................................................................................................... 8 Integrated with vSphere .............................................................................................................. 8 Minimum impact on system resources ........................................................................................ 8 EMC Symmetrix VMAX 10K storage array .......................................................................................... 8 FAST VP on Symmetrix VMAX 10K ..................................................................................................... 9 EMC Virtual Storage Integrator ......................................................................................................... 9 EMC PowerPath/VE .......................................................................................................................... 9 VMware vSphere 5 ......................................................................................................................... 10 Microsoft SQL Server 2012 ............................................................................................................. 11 AlwaysOn .................................................................................................................................. 11 Solution configuration ........................................................................................................................ 12 Solution overview .......................................................................................................................... 12 Solution architecture...................................................................................................................... 12 SQL Server 2012 user profile and design........................................................................................ 13 Hardware resources ....................................................................................................................... 15 Software resources ........................................................................................................................ 16 Storage design ................................................................................................................................... 17 Overview ........................................................................................................................................ 17 Storage design layout .................................................................................................................... 17 FAST VP design considerations ...................................................................................................... 17 XtremCache management and configuration ...................................................................................... 19

EMC Performance Acceleration for Microsoft SQL Server 2012 3 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Overview ........................................................................................................................................ 19 XtremCache management .............................................................................................................. 19 XtremCache configuration .............................................................................................................. 20 Testing and validation ........................................................................................................................ 22 Test overview ................................................................................................................................. 22 About benchmark performance results .......................................................................................... 22 Test objectives ............................................................................................................................... 22 Test procedures ............................................................................................................................. 22 Test results .................................................................................................................................... 23 Results summary ....................................................................................................................... 23 Enabling XtremCache ................................................................................................................ 24 Effects of XtremCache on a two-tier system (FC and SATA) ......................................................... 25 Effects of XtremCache on a three-tier storage system (flash, FC, and SATA)................................ 29 XtremCache statistics ................................................................................................................ 31 VMware vSphere ESXi server performance ..................................................................................... 32 Conclusion ......................................................................................................................................... 34 Summary ....................................................................................................................................... 34 Findings ......................................................................................................................................... 34 References.......................................................................................................................................... 35 White papers ................................................................................................................................. 35 Product documentation.................................................................................................................. 35 Other documentation ..................................................................................................................... 35

EMC Performance Acceleration for Microsoft SQL Server 2012 4 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Executive summary Business case

In today’s increasingly cost-competitive environment, enterprises are being driven to optimize processes and improve service, while lowering IT costs. There are also increasing demands on IT infrastructure performance, driven by: 

Growing numbers of active users



Time-critical applications and escalating service-level agreements



Increased complexity of business processes and new analytic workloads



Multiple databases with high concurrent access

To address these challenges in Microsoft SQL Server 2012 environments, enterprises need to consider new approaches that can optimize performance while reducing costs and increasing data protection. EMC® XtremCache™ is a combination of hardware and software that uses Peripheral Component Interconnect Express (PCIe) flash technology and intelligent caching software to accelerate read operations by reducing latency and increasing throughput. A single XtremCache card can process up to 10,000 8 KB read requests, while a typical flash disk can process only a few thousand read requests. With both XtremCache and EMC Symmetrix® VMAX® 10K storage arrays, EMC provides a solution to optimize the performance of virtualized Microsoft SQL Server 2012 with minimal impact on system resources such as CPU and memory. Solution overview

The purpose of this solution is to show that XtremCache provides an efficient and intelligent I/O path for SQL Server 2012 on Symmetrix VMAX 10K in a virtualized environment. The environment is based on a multi-tier storage solution that is controlled and optimized by EMC Fully Automated Storage Tiering for Virtual Pools (FAST VP™).

Key results

This solution demonstrates the following results: 

XtremCache offloads the read workload of SQL Server OLTP transactions from the storage array to the server.



XtremCache improves SQL Server database transaction performance in a virtualized environment.



XtremCache has little impact on system resources such as CPU and memory.



Integration with the EMC Virtual Storage Integrator (VSI) plug-in for VMware vSphere vCenter makes XtremCache easy to manage and monitor in a virtualized environment.

EMC Performance Acceleration for Microsoft SQL Server 2012 5 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Introduction Purpose

This white paper is intended to show the ability of XtremCache to support I/O intensive SQL Server OLTP workloads.

Scope

This white paper: 

Showcases the ability of XtremCache to accelerate the performance of heavy SQL Server OLTP workloads with little impact on the system resources.



Demonstrates XtremCache’s easy management and configuration.

Audience

This white paper is intended for SQL Server 2012 database administrators and storage architects involved in planning, designing, or administering an environment with Symmetrix VMAX 10K and XtremCache.

Terminology

This white paper includes the following terminology. Table 1.

Terminology

Term

Definition

XtremCache

A server flash caching solution that reduces latency and accelerates throughput to dramatically improve application performance by using intelligent caching software and Peripheral Component Interconnect Express (PCIe) flash technology

FAST VP

Fully Automated Storage Tiering for Virtual Pools. A feature of Symmetrix storage arrays that automates the identification of data volumes for allocating or reallocating business application data across different performance and capacity tiers within the storage array.

OLTP

Online transaction processing. Typical applications of OLTP include data entry and retrieval transaction processing.

Symmetrix VMAX 10K

An enterprise-class disk array that provides the solution for providing the highest levels of reliability and availability in a scalable storage for medium-sized enterprises and larger organizations. With support for multiple disk technologies, EMC Virtual Provisioning™ storage allocation, and Fully Automated Storage Tiering (FAST), Symmetrix VMAX 10K provides an industry-leading solution for the market.

Warm-up time

Warm-up time consists mostly of promotion operations into XtremCache. This happens when the XtremCache has just been installed and is empty. This also happens when the working data set of the application has changed dramatically and the current XtremCache data is no longer being referenced.

EMC Performance Acceleration for Microsoft SQL Server 2012 6 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Technology overview Introduction to the key components

This section provides an overview of the technologies used in this solution: 

EMC XtremCache Note: Any mention of VFCache or XtremSW Cache in this white paper refers to XtremCache.

EMC XtremCache



EMC Symmetrix VMAX 10K storage array



FAST VP on Symmetrix VMAX 10K



EMC Virtual Storage Integrator



EMC PowerPath®/VE



VMware® vSphere® 5



Microsoft SQL Server 2012

EMC XtremCache is a server flash caching solution that reduces latency and increases throughput. By leveraging intelligent caching software and PCIe flash technology, XtremCache dramatically improves application performance. Server-side flash caching for maximum speed XtremCache software caches the most frequently referenced data on the server-based PCIe card, thereby putting the data closer to the application. The XtremCache caching optimization automatically adapts to changing workloads by determining which data is most frequently referenced and promoting it to the server flash cache. This means that the “hottest” data (most active data) is automatically cached on the PCIe card in the server for faster access. XtremCache offloads the read traffic from the storage array, which allows it to allocate greater processing power to other applications. While one application is accelerated with XtremCache, the array’s performance for other applications is maintained or even slightly enhanced. As shown in Figure 1, the XtremCache advanced architecture combines accelerated performance with data protection: 

Reads are serviced by XtremCache for performance.



Writes are passed through to the storage array for protection, in addition to retaining a copy in XtremCache.

EMC Performance Acceleration for Microsoft SQL Server 2012 7 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Figure 1.

Advanced architecture with EMC XtremCache

Write-through caching to the array for total protection XtremCache accelerates reads and protects data by using write-through caching to the storage to deliver persistent high availability, integrity, and disaster recovery. Application agnostic XtremCache is transparent to applications, so no rewriting, retesting, or recertification is required to deploy XtremCache in the environment. Integrated with vSphere XtremCache enhances both virtulized and physical environments. Integration with the VSI plug-in simplifies the management and mornitoring of XtremCache. Minimum impact on system resources XtremCache is designed to minimize CPU overhead in the server by offloading flash management operations from the host CPU to the PCIe card. XtremCache creates the most efficient and intelligent I/O path from the application to the datastore. This results in an infrastructure that is dynamically optimized for performance and protection in both physical and virtual environments. EMC Symmetrix VMAX 10K storage array

EMC Symmetrix VMAX 10K is a new enterprise storage system built to provide leading high-end virtual storage capabilities to a growing number of IT organizations and service providers with demanding storage requirements and limited resources. Symmetrix VMAX 10K is designed for easy installation, setup, and use. It is ideal for customers who need increased reliability and performance and is an ideal entry into a Symmetrix-based storage infrastructure. Symmetrix VMAX 10K includes preconfiguration for easy setup on the same day the array is received, with an installation that takes less than four hours. Using the Virtual Matrix Architecture, Symmetrix VMAX 10K provides enterprise-level reliability, availability, and serviceability. As a 100 percent virtually provisioned EMC Performance Acceleration for Microsoft SQL Server 2012 8 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

system, Symmetrix VMAX 10K gives a host, application, or file system the view that it has more storage than is physically provided1. Physical storage is allocated only when the data is written, rather than when the application is initially configured. This eliminates manual calculations and can also reduce power and cooling costs by decreasing the amount of idle storage capacity in the array. Adding FAST VP for fully-automated tiered storage minimizes or even eliminates the overhead of on-going application and storage performance-related maintenance tasks. Building on EMC’s VMware integration, Symmetrix VMAX 10K now offers even more efficient enterprise storage. EMC VSI for VMware simplifies the process of integrating EMC storage into a virtualized environment. FAST VP on Symmetrix VMAX 10K

Symmetrix VMAX 10K integrates simple self-tuning into an advanced enterprise-class storage system at a cost-effective price. By combining EMC sub-volume auto-tiering and Virtual Provisioning technologies, FAST VP enables storage administrators to implement automated policy-driven plans. FAST VP dynamically performs nondisruptive data movement between tiers to ensure that high-performance drives serve the hot spots of a volume or LUN and less expensive drives serve the inactive data. With FAST VP, customers can achieve:

EMC Virtual Storage Integrator



Better performance with lower cost, fewer drives, less power and cooling, and a smaller footprint



Maximum utilization of flash drives for high-performance workloads



Lower storage costs by placing the less accessed data on SATA drives



Radically simplified automated management in a tiered environment

EMC VSI is a VMware vCenter plug-in available to all VMware users with EMC storage in their environment. VSI simplifies the process of mapping vSphere datastores to LUNs and NFS shares on EMC storage. VSI also helps pinpoint the location of virtual machines and raw device mapping files on the array. The VMware administrator’s visibility into the storage layer can help troubleshoot storage performance issues and simplify communication among server, storage, and virtualization teams.

EMC PowerPath/VE In this solution, EMC PowerPath/VE software was used on a vSphere host in a VMware High Availability (HA) cluster. PowerPath enables the host to connect to a LUN through more than one host bus adapter (HBA) port. This offers multiple data access paths and is known as multipathing. PowerPath optimizes multipathed LUNs through load-balancing algorithms. Port load balancing equalizes the I/O workload across all available channels. Hosts connected to Symmetrix VMAX 10K benefit from multipathing.

1

The Symmetrix VMAX 10K storage array can be over provisioned at first.

EMC Performance Acceleration for Microsoft SQL Server 2012 9 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

The advantages of multipathing are:

VMware vSphere 5



Failover from port to port on the same storage processor, maintaining an even system load



Port load balancing across storage processor ports and HBAs



Higher bandwidth attachment from host to storage system

VMware vSphere 5 is a VMware virtual data center operating system. It continues to transform IT infrastructure into the most efficient, shared, and on-demand utility, with built-in availability, scalability, and security services for all applications and simple, proactive automated management. vSphere 5 has the following scalability and performance enhancements, which enable a virtual machine to leverage more resources from the hypervisor: 

Thirty-two-way Virtual Symmetrical Multiprocessing (SMP).



ESXi 5.0 supports virtual machines with up to 32 virtual CPUs, which lets you run larger CPU-intensive workloads such as SQL Server 2012 on the VMware ESXi operating system.



1 TB virtual machine RAM. You can assign up to 1 TB of RAM to ESXi 5.0 virtual machines.



Up to one million IOPS for each vSphere server.



More than 36 GB/s network bandwidth.

Building on EMC's VMware integration, Symmetrix VMAX 10K offers even more efficient enterprise storage through integration with the VMware vSphere 5 cloud infrastructure.

EMC Performance Acceleration for Microsoft SQL Server 2012 10 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Microsoft SQL Server 2012

SQL Server 2012 is the Microsoft database management and analysis system for eCommerce, line-of-business, and data warehousing solutions. AlwaysOn SQL Server AlwaysOn refers to the comprehensive high-availability and disaster recovery solution for SQL Server 2012. AlwaysOn presents new and enhanced capabilities for both specific databases and entire instances, providing flexibility to support various high availability configurations through: 

AlwaysOn failover cluster instances



AlwaysOn Availability Groups

Note: SQL Server AlwaysOn was not tested in this solution. For more details, refer to EMC

Mission Critical Infrastructure for Microsoft SQL Server 2012—EMC Symmetrix VMAXe, EMC FAST VP, SQL Server AlwaysOn Availability Groups, VMware vSphere.

EMC Performance Acceleration for Microsoft SQL Server 2012 11 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Solution configuration Solution overview

This solution includes a two-engine Symmetrix VMAX 10K storage array with optimized disk configuration managed and controlled by FAST VP policies. The storage array supports two SQL Server instances in a virtualized environment running SQL Server 2012. EMC XtremCache was installed on the ESXi host with the primary SQL Server virtual machine to boost the performance, offload the I/O processing from the storage array to the servers, and reduce the response time of the SQL Server.

Solution architecture

The solution design included the following physical components: 

Two vSphere ESXi servers, each hosting one SQL Server virtual machine



XtremCache enabled on the primary SQL virtual machine



Symmetrix VMAX 10K SAN storage



FAST VP-enabled storage tiers

EMC Performance Acceleration for Microsoft SQL Server 2012 12 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Figure 2 shows an overview of the solution architecture.

Figure 2.

SQL Server 2012 user profile and design

Solution architecture

The environment consisted of two SQL Server 2012 virtual machines on VMware vSphere 5 and Symmetrix VMAX 10K. Designed with 5 percent CPU overhead, this virtualized SQL Server 2012 environment provided sufficient performance and throughput with a reduced footprint. This resulted in greater server resource efficiency and reduced power and cooling costs compared to the physical environment. vSphere 5 provides a feature that supports up to 32 processors for virtual machines. This feature enables enterprise-level SQL Server 2012 to handle more transactions by providing more powerful processing capabilities.

EMC Performance Acceleration for Microsoft SQL Server 2012 13 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Table 2 shows the profile of the SQL Server configuration. Table 2.

SQL Server profile

Profile

Value

SQL Server database capacity

 4 user databases–1.8 TB in total  1 x 50 GB (5,000 user)  1 x 250 GB (25,000 user)  1 x 500 GB (50,000 user)  1 x 1 TB (100,000 user)

Number of SQL Server instances

2

Number of user databases for each virtual machine

4

Number of virtual machines

2

SQL Server virtual machine configuration

 Primary server: 32 Intel Xeon X5760 CPUs (2.27 GB/4x8 cores)/64 GB memory  Secondary server: 24 Intel Xeon X5760 CPUs (2.27 GB/4x8 cores)/64 GB memory

Concurrent users

Mixed to simulate hot, warm, and cold workloads across the databases

Figure 3 shows the SQL Server LUN design. In this solution, we used 278 GB of usable space on the 300 GB XtremCache card to support the caching needs for 16 database LUNs of over 3 TB (servicing 1.8 TB active data). The design follows these best practices: 

Distribute the data and log to different LUNs on the shared thin pools.



Use separate file groups for large databases (tpce2, tpce3, and tpce4).



Ensure configuration consistencies for the primary and secondary servers (data, log, and tempDB).



Create VMFS datastore on the cache device.



Create one virtual disk with 278 GB capacity from the VMFS datastore (the rest of the space is reserved for XtremCache) and add to the primary server.



Configure only primary database LUNs as XtremCache source LUNs.

EMC Performance Acceleration for Microsoft SQL Server 2012 14 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Figure 3.

Hardware resources

SQL Server LUN design

Table 3 shows the hardware resources used to validate this solution. Table 3.

Hardware resources

Equipment

Quantity

Configuration

Symmetrix VMAX 10K

1

 VMAX 10K 7.2K 2,000 GB (SATA)—Quantity: 64  VMAX 10K 15K 450 GB (FC)—Quantity: 120  VMAX 10K Flash 200 GB (flash)—Quantity: 20  Engines: 2  Cache: 96 GB (mirrored)  FAST VP enabled (Drive counts reflect what is available in the VMAX 10K array)

32-core Intel NehalemEX servers

2

32-core 196 GB RAM

EMC Performance Acceleration for Microsoft SQL Server 2012 15 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Equipment

Quantity

Configuration

Fibre Channel (FC) switches

2

8 GB FC switches

GbE network switch

1

Network switch–24 ports

XtremCache card (MLC)

1

Write-through policy, 300 GB caching space

Software resources Table 4 shows the software resources used in this solution. Table 4.

Software resources

Software

Quantity

Version

Purpose

Windows Server 2008 R2

4

2008 R2 x64 SP1

Source and target virtual machines Load virtual machines Domain controller VMware vCenter server

VMware ESXi 5

2

5.0.0 Update 1

Two-node ESXi cluster to host virtual machines

VMware vCenter 5

1

5.0.0

Virtual center management server

Microsoft SQL Server 2012

2

RTM

Database software

EMC PowerPath

2

5.5

Advanced multipathing for SQL production and secondary host HBAs

EMC VSI

1

5.1

Provisioning and viewing storage from vCenter to simplify storage management

EMC XtremCache driver

1

1.0

Driver software installed on the ESXi host server

EMC XtremCache software

1

1.0

Required to be installed on each virtual machine that needs to be accelerated using XtremCache

EMC XtremCache VSI plug-in

1

1.0

XtremCache management in a VMware vCenter client

EMC Performance Acceleration for Microsoft SQL Server 2012 16 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Storage design Overview

The solution team used Symmetrix Virtual Provisioning technology for storage provisioning. Thin pools were configured using pre-configured thin data devices. Because the storage resources were allocated across all data devices in the thin pool, I/Os were widely striped. As a result, application I/O workload against the thin devices was evenly distributed across all resources.

Storage design layout

The storage design was to achieve host data LUN latency of less than 20 ms and log LUN latency of less than 5 ms. Table 5 shows the disk group configuration. Table 5.

Disk group configuration Recommended maximum IOPS per drive

Total estimated IOPS

Drive type

RAID type

Number of drives required

Flash1

RAID 5 (3+1)

16

1,800

28,800

FC

RAID 1

112

150

16,800

SATA

RAID 6 (6+2)

56

50

3,000

1

This tier was excluded in the two-tier storage configuration to examine the XtremCache effect when the storage is under pressure.

We created two FAST VP thin pools (primary and secondary) across all three tiers (three virtual pools). These two thin pools served the SQL Server tempDB, user databases, and log LUNs on each site of the availability group:

FAST VP design considerations



Primary pool: For LUNs on the primary SQL Server



Secondary pool: For LUNs on the secondary SQL Server

The following are some general guidelines for FAST VP sizing: 

Flash drives provide the best performance for workloads with higher numbers of random reads. Because of the sequential write nature of the database log LUNs, enable FAST VP only for data LUNs to effectively use flash drives. Log LUNs and tempDB LUNs were pinned to the FC tier.



For large thin devices, use striped meta-devices for better performance.



Perform sizing according to FAST VP policy requirements: 

RAID 5 flash drives for best total cost of ownership (TCO)



RAID 6 SATA drives for best data protection



RAID 1 FC drives for best performance

EMC Performance Acceleration for Microsoft SQL Server 2012 17 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Table 6 shows the FAST VP tiers and policy used in this solution. Table 6.

FAST VP tiers and policy

Tier name

Drive technology

Three-tier FAST VP setting2

Two-tier FAST VP setting 3

MS_Flash

200 GB Flash drives

30%

N/A

MS_FibreChannel

450 GB 15K FC drives

60%

100%

MS_SATA

2 TB 7.2K SATA drives

10%

100%

2

Policy designed to limit the amount of data to tiers, especially the flash tier, while providing optimal performance.

3

Two-tier setting was designed to demonstrate how XtremCache can alleviate storage bottlenecks for the read-intensive workloads.

EMC Performance Acceleration for Microsoft SQL Server 2012 18 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

XtremCache management and configuration Overview

This section provides an overview of how the EMC solution team configured XtremCache in this solution.

XtremCache management

Figure 4 shows the use of the VSI plug-in in the VMware environment to manage and monitor XtremCache.

Figure 4.

VSI integrated management console

You can also use the XtremCache Command Line Interface (CLI) within the Windows virtual machine for the same purpose. Figure 5 displays the current statistics of all the LUNs in the XtremCache pools using the 278 GB volume. It shows the total read hit ratio (approximately 55 percent) and write hit ratio (5.97 percent) along with the read and write hit ratios for the individual source LUNs.

EMC Performance Acceleration for Microsoft SQL Server 2012 19 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Figure 5.

XtremCache configuration

Windows XtremCache CLI

In this solution, we configured one cache device with a usable size of 278 GB out of the 300 GB XtremCache card. All 16 source data devices were associated with the cache device, as shown in Figure 4 and Figure 5. The configuration was straightforward through use of the wizards of the VSI integrated plug-in. If preferred, you can also use the CLI from the Windows virtual machine. We followed these steps to configure the XtremCache for the LUNs in the virtual machine, as shown in Figure 4: 1.

Create a virtual machine file system (VMFS) datastore on the cache device from the vSphere vCenter.

2.

Add the XtremCache devices in the form of virtual disks to virtual machines through the VSI plug-in for XtremCache. The entire device can be added to one virtual machine, or can be partitioned into virtual disks and then used for different virtual machines. The XtremCache virtual disk is shown in the Disk Management wizard of the virtual machine as an Original Equipment Manufacturer (OEM) partition (shown in Figure 6).

3.

Add the source devices to the enabled XtremCache device to accelerate the source device.

Any source device can be stopped temporarily for the caching operation with XtremCache or removed from caching without affecting other source devices.

EMC Performance Acceleration for Microsoft SQL Server 2012 20 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Figure 6.

VMware disk management

EMC Performance Acceleration for Microsoft SQL Server 2012 21 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Testing and validation Test overview

This solution validated the XtremCache capability and functionality to accelerate OLTP performance in a multiuser SQL Server 2012 database with the AlwaysOn Availability Groups virtualized with the VMware environment on the Symmetrix VMAX 10K storage system. This section presents the results of this testing.

About benchmark performance results

Benchmark results are highly dependent on workload, specific application requirements, and system design and implementation. Relative system performance will vary as a result of these and other factors. Therefore, it is suggested not to use this workload as a substitute for a specific customer application benchmark when critical capacity planning and product evaluation decisions are contemplated. All performance data contained in this report was obtained in a rigorously controlled environment. Results obtained in other operating environments may vary significantly. EMC Corporation does not warrant or represent that a user can or will achieve similar performance expressed in transactions per minute.

Test objectives

Test procedures

The objectives of the testing were to: 

Enable XtremCache on the primary SQL Server virtual machine to observe the impact of XtremCache on the system and the initial “warm-up” time.



Measure the impact of adding XtremCache to boost SQL Server (OLTP workload) performance in two configurations: 

Two-tier configuration (FC and SATA only) controlled by FAST VP



Three-tier configuration (flash, FC, and SATA) controlled by FAST VP

The EMC solution team conducted a series of tests by running the same concurrent OLTP workloads against the target databases. The test procedure was as follows: 1.

2.

Configure FAST VP with two storage tiers and run the designed OLTP workload. a.

Measure baseline SQL Server performance.

b.

Enable XtremCache on the primary server and measure the SQL Server performance.

Configure FAST VP with three storage tiers and run the same load. a.

Measure SQL Server performance.

b.

Enable XtremCache on the primary server and measure SQL Server performance again.

Note: Workload profile parameters were consistent for all tests.

EMC Performance Acceleration for Microsoft SQL Server 2012 22 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Test results

Results summary XtremCache started to take effect as soon as it was enabled and the devices that needed performance boost was added to the cache pool. It took approximately 1 hour in this environment to reach the maximum performance boost. The system had an XtremCache device of 278 GB usable cache space configured for 16 database LUNs. XtremCache significantly reduced SQL Server response time for high response time transactions in both two-tier and three-tier configurations. XtremCache host driver had minimal impact on server/virtual machine system resources. The read latency reduced by approximately 50 to 70 percent after we enabled XtremCache. We observed the same with the transaction latency. The XtremCache also significantly lowered the response time of high-latency transactions. Without XtremCache, the two-tier configuration can support only 14,000 IOPS. With XtremCache, it can support 24,000 IOPS with a read/write ratio of 90:10. XtremCache significantly lowered the I/O activities on the storage array (about 10,000 IOPS) in the three-tier configuration, thus enabling the storage system to support more server I/O requests. For test results of FAST VP, refer to EMC Mission Critical Infrastructure for Microsoft

SQL Server 2012—EMC Symmetrix VMAXe, EMC FAST VP, SQL Server AlwaysOn Availability Groups, VMware vSphere.

EMC Performance Acceleration for Microsoft SQL Server 2012 23 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Table 7 shows the detailed test results for all the test scenarios in this solution. Table 7.

Performance data with OLTP load Three-tier storage

Performance

Two-tier storage

Without XtremCache

With XtremCache

Without XtremCache

With XtremCache

SQL Server virtual machine CPU

67.45%

67.85%

15.50%4

51.43%

ESXi CPU

77.80%

78.20%

24.63%4

65.57%

Client TPS

2,193

2,585

1,225

2,229

SQL Server virtual machine IOPS

23,938

23,916

14,123

23,602

VMAX 10K front-end IOPS

24,698

14,987

15,475

13,798

Latency (ms) (read/write/transfer)

4/1/4

2/2/2

11/1/10

4/3/4

4

CPU usage was lower because the storage bottleneck created in this test limited the client load that can be pushed to the system.

Enabling XtremCache After enabling XtremCache for the first time, the performance boost was visible immediately. It took approximately 1 hour to reach the steady state of the maximum performance boost. There were 16 data LUNs configured to a single XtremCache with 278 GB usable cache space. Figure 7 shows the read/write IOPS for the primary SQL Server before and after enabling XtremCache in a two-tier storage system.

EMC Performance Acceleration for Microsoft SQL Server 2012 24 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Steady state

Baseline | XtremCache Enabled

12 20,000 15,000 IOPS

8 6

10,000

4 5,000

2

IOPS

18:15

18:10

18:00

17:55

17:50

17:40

17:35

17:30

17:20

17:15

17:05

17:00

16:55

16:30

16:25

0 16:20

0

Figure 7.

latency (ms)

10

latency ( ms)

Performance boost after enabling XtremCache

After the system reached the steady state, the system performance was very stable during the 24-hour testing period. Effects of XtremCache on a two-tier system (FC and SATA) As expected from a two-tier system, even with fewer transactions, SQL Server response time was visibly high without XtremCache. Trade Lookup and Trade Update query response times decreased by 50 percent when XtremCache was enabled, as shown in Figure 8. In this test, adding XtremCache also increased the total amount of transactions per second (TPS) supported by the system.

EMC Performance Acceleration for Microsoft SQL Server 2012 25 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

0.80 0.70

without VFCache

0.70

with VFCache 0.63

0.60 second

0.50 0.40

0.39

0.34

0.30 0.20 0.10 0.00 Broker Customer Market Volume Position Feed

Figure 8.

Market Watch

Security Detail

Trade Lookup

Trade Order

Trade Result

Trade Status

Trade Update

Average SQL Server response time with two-tier storage configuration

With only FC and SATA tiers in the FAST VP pool and XtremCache enabled, the SQL Server virtual machine IOPS increased from around 14,000 to 24,000, while the Symmetrix VMAX 10K IOPS slightly decreased. The client TPS almost doubled from 1,200 to 2,200. Refer to Figure 9 for more details.

EMC Performance Acceleration for Microsoft SQL Server 2012 26 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

23,602

25000 20000 15000

15,475

14,123

13,798

10000 5000

1,225

2,229

0 SQL Server virtual machine IOPS

VMAX 10K front-end IOPS

Before enabling VFCache Figure 9.

TPS

After VFCache stabilized

XtremCache impact on two-tier storage configuration

The read latency decreased by approximately two-thirds while the write latency slightly increased. XtremCache is a “write-through” cache, therefore does not handle writes. Writes are passed to the Symmetrix VMAX 10K storage array directly. With almost doubled SQL Server transactions, using the same back-end disk configuration took a bit longer to react to write requests. In this case, both read and write latencies were under 5 ms, well below the 20 ms limit that Microsoft recommends. CPU usage increased because of the increased SQL Server utilization with transactional processing. XtremCache removed the storage bottleneck from the system; therefore, the system was able to handle more SQL Server TPS. Refer to Figure 10 for more details.

EMC Performance Acceleration for Microsoft SQL Server 2012 27 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Figure 10.

CPU usage before and after XtremCache is enabled with two-tier storage configuration

As shown in Figure 11, before enabling XtremCache, the SQL Server workload’s read/write ratio was consistent at 87/13. After enabling XtremCache: 



On the SQL Server virtual machine: 

Read/write ratio reached 90/10



Read I/Os on the SQL Server virtual machine increased by approximately 10,000



Write operations increased by 23 percent

On the Symmetrix VMAX 10K storage array, the read/write ratio decreased to 84/16, indicating that more read I/Os are offloaded to the server side.

EMC Performance Acceleration for Microsoft SQL Server 2012 28 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

90/10

25,000

2,223 20,000 15,000

87/13 1,807

87/13

84/16

1,938

2,234 21,379

10,000 5,000

12,316

13,537

SQL Server virtual machine IOPS without VFCache

VMAX 10K IOPS without VFCache

11,564

Read/write ratio Write Read

0

Figure 11.

SQL Server virtual machine IOPS with VFCache

VMAX 10K IOPS with VFCache

Read/write ratio with two-tier storage configuration

Effects of XtremCache on a three-tier storage system (flash, FC, and SATA) As expected with a well-designed system, SQL Server response time was very low even before introducing XtremCache. While XtremCache did not affect queries with low response time, the queries with higher response time were affected dramatically. In our OLTP test, “Trade Lookup” and “Trade Update” were the two queries with very high response times. After XtremCache was enabled, the response time of these two queries decreased by more than 30 percent, as shown in Figure 12.

EMC Performance Acceleration for Microsoft SQL Server 2012 29 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Average response time( sec)

0.40 without VFCache

with VFCache

0.30

0.25 0.21

0.20

0.15

0.13 0.10

0.00

Figure 12.

Average SQL Server response time on system with three-tier storage configuration before/after XtremCache

With a three-tier storage system, the array IOPS decreased from 25,000 to 15,000 after XtremCache was enabled. The host throughput (TPS) improved from approximately 2,200 to 2,600 TPS. Read latency decreased by 50 percent. Refer to Figure 13 for details.

30000 25000

23,938 23,916

24,698

20000 14,987 15000 10000 5000

2,193

2,585

0 SQL Server virtual VMAX 10K front-end TPS machine IOPS IOPS Before enable VF cache After VFCache stabilized Figure 13.

XtremCache impact on system with three-tier storage configuration

EMC Performance Acceleration for Microsoft SQL Server 2012 30 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Usage of system resources, such as CPU and memory, did not change significantly after XtremCache was enabled. Memory usage was near 100 percent because of the SQL Server design of maximizing utilization of RAM. CPU usage was slightly increased after XtremCache was enabled (0.4 percent in both the SQL Server virtual machine and the ESXi server). As shown in Figure 14, before enabling XtremCache, the read/write ratio was consistent on the SQL Server virtual machine and the array (approximately 90:10). After enabling XtremCache: 

While the SQL Server virtual machine read/write ratio did not change, the read/write ratio on the array dramatically decreased.



The total Symmetrix VMAX 10K back-end IOPS decreased by 10,000 (all read I/Os).

30,000 25,000

92/8

92/8

92/8

1,974

2,269

1,986

20,000

81/19 15,000 10,000

2,578 21,964

22,429

Read/write ratio Write

21,930

Read 12,409

5,000 0

SQL Server virtual machine IOPS without VFCache Figure 14.

VMAX 10K IOPS without VFCache

SQL Server virtual machine IOPS with VFCache

VMAX 10K IOPS with VFCache

Read/write ratio on system with three-tier storage configuration

XtremCache statistics With a 278 GB XtremCache LUN, the 16 database LUNs totaling 3 TB had an average cache hit ratio of 50 to 60 percent, as shown in Figure 15.

EMC Performance Acceleration for Microsoft SQL Server 2012 31 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Figure 15.

VMware vSphere ESXi server performance

XtremCache statistics

Both VMware ESXi servers, which hosted the SQL Server virtual machines, demonstrated a consistent performance. Figure 16 shows the CPU usage of the ESXi server that served the primary copy of the SQL database virtual machine. CPU usage was approximately 65 percent during the testing, which was lower than when the system handled the same client workload without XtremCache (77 percent). This indicates that XtremCache had minimum impact on system resources such as CPU usage in a virtualized environment.

EMC Performance Acceleration for Microsoft SQL Server 2012 32 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Figure 16.

CPU usage for ESXi server with the primary SQL virtual machine

EMC Performance Acceleration for Microsoft SQL Server 2012 33 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

Conclusion Summary

EMC XtremCache has proven to be highly scalable and reliable. It can relieve I/O processing pressure from the storage system and boost the disk read operations driven by the host, even in a virtual ESXi based environment. XtremCache increases the overall transaction rate of SQL Server and significantly reduces disk latencies with minimal impact on system resources. 

Managing and monitoring XtremCache in a vSphere environment is easy. After being configured, XtremCache requires no user intervention and continuously adjusts to meet the application workload needs.



XtremCache can reduce SQL Server response time.



XtremCache host driver has minimal impact on server/virtual machine system resources. During testing, the system resources were mostly consumed by SQL Server workload. XtremCache driver overhead was negligible (0.4 percent CPU usage for the VMware environment.)

Findings



With a highly optimized, multi-tier storage system, XtremCache can offload read I/O processing from the storage array while reducing disk latencies, which enables higher transactional throughput.



With less optimized, two-tier storage configurations, XtremCache can significantly boost SQL Server transactions and lower overall host disk latency. It can address “hot spots” in the data center and alleviate possible storage bottlenecks.



The initial warm-up period for XtremCache with OLTP workloads varies for each environment. In this solution, we observed the effect of XtremCache immediately after it was enabled. It reached a steady state in approximately 1 hour for all 16 LUNs hosting a 3 TB database file.

The key findings of testing of this proven solution are as follows: 

Performance boost was observed immediately after the LUNs are added to the XtremCache pool.



XtremCache is a server-based cache. Introducing XtremCache to a storage environment does not require any changes to the application or storage system layouts.



Because XtremCache is a caching solution rather than a storage solution, there is no need to move data. Therefore, data is not at risk of being inaccessible if the server or the PCIe card fails.



XtremCache is designed to minimize CPU overhead in the server by offloading flash management operations from the host CPU onto the PCIe card.

EMC Performance Acceleration for Microsoft SQL Server 2012 34 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012

References White papers

Product documentation

Other documentation

For more information, refer to these white papers: 

EMC Mission Critical Infrastructure for Microsoft SQL Server 2012—EMC Symmetrix VMAXe, EMC FAST VP, SQL Server AlwaysOn Availability Groups, VMware vSphere



EMC XtremCache Accelerates Microsoft SQL Server—EMC XtremSF, EMC XtremCache, EMC Symmetrix VNX, Microsoft SQL Server 2008



EMC XtremCache Accelerates Virtualized Oracle—EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX and VMAXe, VMware vSphere, Oracle



Provisioning EMC Symmetrix VMAXe Storage for VMware vSphere Environments



Configuration Best Practices for Microsoft SQL Server and EMC Symmetrix VMAXe



Implementing Fully Automated Storage Tiering for Virtual Pools (FAST VP) for EMC Symmetrix VMAX Series Arrays



EMC Storage Optimization and High Availability for Microsoft SQL Server 2008 R2

For more information, refer to these product documents: 

Introduction to XtremCache



EMC XtremCache VMware VSI plug-In Administration Guide



Changing the Queue Depth for QLogic and Emulex HBAs



VMware ESX Scalable Storage Performance

For more information about Microsoft SQL Server 2012 and AlwaysOn, refer to these documents: 

AlwaysOn Availability Groups



Pre-Configuration Database Optimizations



SQL Server Best Practices Article

EMC Performance Acceleration for Microsoft SQL Server 2012 35 EMC XtremSF, EMC XtremCache, EMC Symmetrix VMAX 10K, EMC FAST VP, VMware vSphere, Microsoft SQL Server 2012