Virtualizing SQL Server 2008 Using EMC VNX Series and VMware vsphere 4.1. Proven Solution Guide

(1)

Virtualizing SQL Server 2008 Using

EMC VNX Series and

VMware vSphere 4.1

(2)

EMC believes the information in this publication is accurate 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, ESX, VMware vCenter, and VMware vSphere are registered trademarks or trademarks of VMware, Inc. in the United States and/or other jurisdictions. All other trademarks used herein are the property of their respective owners.

Intel and Xeon are trademarks of Intel Corporation in the United States and/or other jurisdictions.

(3)

Table of Contents

Chapter 1: About this Document ... 5

Overview ... 5

Audience and purpose ... 6

Scope ... 6

Reference architecture ... 7

Introduction to the VNX Family for unified storage ... 8

Validation environment profile ... 9

Hardware and software resources ... 10

Prerequisites and supporting documentation ... 10

Terminology... 11

Chapter 2: Application Design ... 13

Overview ... 13

Microsoft SQL Server ... 14

Microsoft SQL Server best practices... 14

Chapter 3: Virtualization ... 15

Overview ... 15

Concepts ... 16

Advantages of virtualization ... 16

Virtualization best practices ... 16

Chapter 4: Network Design ... 17

Overview ... 17

Considerations ... 17

Implementation ... 17

Best practices ... 18

Chapter 5: Storage Design... 19

Overview ... 19

Design considerations ... 20

Storage design implementation... 21

Best practices ... 21

Chapter 6: Virtualization Performance – Testing and Validation ... 22

Overview ... 22

Testing tools ... 23

Methodology ... 23

Testing results summary ... 24

Result analysis ... 24

Chapter 7: FAST Cache Performance – Testing and Validation ... 28

Overview ... 28

Methodology ... 29

Chapter 8: LUN Provisioning Options – Testing and Validation ... 34

Overview ... 34

Methodology ... 35

Snapshot performance study – pool LUNs (Thick, Thin, and Compressed) as compared to RAID group LUNs ... 40

Chapter 9: VNX for Block Protocols (10 Gb) ... 46

Overview ... 46

(4)

(5)

Chapter 1: About this Document

Overview

Introduction EMC's commitment to consistently maintain and improve quality is led by the Total Customer Experience (TCE) program, which is driven by Six Sigma methodologies. As a result, EMC has built Customer Integration Labs in its Global Solutions Centers to reflect realworld deployments in which TCE use cases are developed and

executed. These use cases provide EMC with an insight into the challenges currently facing its customers.

This document summarizes a series of best practices that were discovered, validated, or otherwise encountered during the validation of the Virtualizing SQL Server 2008 Using EMC® VNX™ Series and VMware vSphere™ 4.1 solution using the following products:

EMC VNX series EMC PowerPath® VMware vSphere 4.1

VNX Fully Automated Storage Tiering (FAST) Cache

Use case definition

A use case reflects a defined set of tests that validates the reference architecture for a customer environment. This validated architecture can then be used as a reference point for a proven solution.

Contents This chapter contains the following topics:

Topic See Page

Overview 5

Audience and purpose 6

Scope 6

Reference architecture 7

Introduction to the VNX Family for unified storage 8

Validation environment profile 9

Hardware and software resources 10

Prerequisites and supporting documentation 11

(6)

Audience and purpose

Audience The intended audience for the Proven Solution Guide is: Customers

EMC partners

Internal EMC personnel

Purpose The purpose of this document is to build a unified storage solution using the EMC VNX storage platform for VMware virtual environments and demonstrate the benefits of EMC FAST Cache for Microsoft SQL Server 2008 in an online transaction

processing (OLTP) environment. This document validates the performance of all aspects of the solution and provides guidelines to build similar solutions.

Information in this document can be used as the basis for a solution build, white paper, best practices document, or training. It can also be used by other EMC organizations (for example, the technical services or sales organization) as the basis for producing documentation for a technical services or sales kit.

Scope

Scope This document contains the results of testing Microsoft SQL Server 2008 using EMC VNX5700™ and FAST Cache. The objectives of this testing are as follows:

Demonstrate the performance of Microsoft SQL Server 2008 using EMC VNX5700 in a VMware virtual environment.

Demonstrate the benefits of using EMC FAST Cache for Microsoft SQL Server 2008 databases in OLTP environments.

(7)

Reference architecture

Corresponding reference architecture

This Proven Solution Guide has a corresponding Reference Architecture document that is available on EMC Online Support and EMC.com. The Virtualizing SQL Server 2008 Using EMC VNX Series and VMware vSphere 4.1 — Reference Architecture provides more details.

If you do not have access to the document, contact your EMC representative.

Reference architecture logical diagram

(8)

Introduction to the VNX Family for unified storage

EMC VNX

family

The EMC VNX™ family is optimized for virtual applications delivering industry-leading innovation and enterprise capabilities for file, block, and object storage in a scalable, easy-to-use solution. This next-generation storage platform combines powerful and flexible hardware with advanced efficiency, management, and protection software to meet the demanding needs of today’s enterprises. All of this is available in a choice of systems ranging from affordable entry-level solutions to high-performance, petabyte-capacity configurations servicing the most demanding application requirements. The VNX family includes the VNXe™ series, purpose-built for the IT manager in smaller environments, and the VNX series designed to meet the high-performance, high-scalability requirements of midsize and large enterprises.

The VNX family includes two platform series:

The flash optimized VNX series, delivers leadership performance, efficiency, and simplicity for demanding virtual application environments that includes VNX7500, VNX5700, VNX5500, VNX5300, and VNX5100

The VNXe (entry) series with breakthrough simplicity for small and medium businesses that includes VNXe3300 and VNXe3100

Customers can benefit from new VNX features as follows:

Feature VNX

series

VNXe series

Next-generation unified storage, optimized for virtualized applications

 

Capacity optimization features including compression, deduplication, thin provisioning, and application-centric copies

 

High availability, designed to deliver five 9s availability   Automated tiering with FAST VP (Fully Automated Storage

Tiering for Virtual Pools) and FAST Cache that can be optimized for the highest system performance and lowest storage cost simultaneously

 

Multiprotocol support for file, block, and object with object access through Atmos™ Virtual Edition (Atmos VE)

 File and block only Simplified management with EMC Unisphere™ for a single

management interface for all NAS, SAN, and replication needs

 

Up to three times improvement in performance with the latest Intel®_Xeon®_{multicore processor technology, optimized for} Flash

 

Note: VNXe does not support block compression.

EMC provides a single, unified storage plug-in to view, provision, and manage storage resources from VMware vSphere across EMC Symmetrix®, VNX family,

(9)

VMware storage management tasks. EMC also provides a set of free Microsoft Management Console (MMC) plug-ins for Windows and SharePoint environments. EMC Storage Integrator for Windows and SharePoint provides application-aware storage provisioning and supports EMC Symmetrix, VNX family, and CLARiiON, storage systems with both block and file functionality.

The VNX family includes five new software suites and three new software packs, making it easier and simpler to attain the maximum overall benefits.

Software suites available

VNX FAST Suite — Automatically optimizes for the highest system performance and the lowest storage cost simultaneously (FAST VP is not part of the FAST Suite for the VNX5100™).

VNX Local Protection Suite — Practices safe data protection and repurposing. VNX Remote Protection Suite— Protects data against localized failures, outages, and disasters.

VNX Application Protection Suite — Automates application copies and proves compliance.

VNX Security and Compliance Suite — Keeps data safe from changes, deletions, and malicious activity.

Software packs available

VNX Total Efficiency Pack — Includes all five software suites (not available for the VNX5100).

VNX Total Protection Pack — Includes local, remote, and application protection suites.

VNX Total Value Pack — Includes all three protection software suites and the Security and Compliance Suite (the VNX5100 exclusively supports this package).

EMC VNX family is two to three times faster than its predecessor as it uses the Intel Xeon 5600 series processors. The VNX quad-core processor supports the demands of advanced storage capabilities such as virtual provisioning, compression, and deduplication. Also, the performance of the Xeon 5600 series enables EMC to realize its vision for FAST on the VNX, with optimized performance and capacity, without tradeoffs, in a fully automated fashion.

Validation environment profile

Profile

characteristics

The solution was validated with the following environment profile:

Profile characteristic Value

SQL database size 400 GB

Instances and databases Single instance and single database Number of database files Four files, each on a different LUN

Workload OLTP (TPC-C-like)

Storage connectivity for SQL database FC unless otherwise noted RAID type, physical drive size, and speed of

the production SQL 2008 databases

(10)

Hardware and software resources

Hardware The following table lists the hardware used to validate the solution:

Equipment Quantity Configuration Notes

Storage 1 EMC VNX5700 300 GB SAS drives (15k rpm) 4 x 100 GB EFDs Primary database storage Enterprise-class network infrastructure

1 Cisco Nexus 5020 with connectivity for:

Fibre Channel FCoE iSCSI

Production systems may require additional hardware for high-availability purposes SQL Server 1 Two quad-core Intel Xeon

X5550 with 2.67 GHz 64-bit processors 64 GB RAM Primary database server Load Generation Servers

4 Two quad-core Intel Xeon E5440 with 2.83 GHz 64-bit processors

Load generation for test workload

Software The following table lists the software used to validate the solution

Software Version

Microsoft Windows Server Windows 2008 x64 Enterprise Edition R2 Windows 2003 x32 Enterprise Edition R2 SP2 Microsoft SQL Server Enterprise Edition 2008 SP1

EMC VNX Operating Environment for block

05.31.000.3.071

EMC PowerPath 5.3 SP1

VMware vSphere 4.1

Prerequisites and supporting documentation

Technology It is assumed that the reader has a general knowledge of the following products: Microsoft SQL Server 2008

EMC VNX series

Supporting documents

The following documents, located on EMC Online Support, provide additional, relevant information. Access to these documents is based on your login credentials. If you do not have access to the following content, contact your EMC representative.

EMC Tiered Storage for Microsoft SQL Server 2008 - Enabled by EMC CLARiiON CX4 and Enterprise Flash Drives — A Detailed Review

(11)

Other documents

The following document is available on the Microsoft website (http://msdn.microsoft.com):

SQL Server Books Online

The following document is available on the VMware website (www.vmware.com): Timekeeping in VMware Virtual Machines

Terminology

Introduction This section defines the terms used in this document.

Term Definition

Enterprise Flash Drive (EFD)

The enterprise-class EMC EFDs supported by VNX systems are constructed with nonvolatile semiconductor NAND Flash memory. Due to their solid state design, these drives are especially well suited for latency-sensitive applications that require consistently low read/write response times. EFDs are packaged in a standard 3.5-inch disk drive form factor used by existing VNX disk drive array enclosures, making for simple integration with existing infrastructure. Since there are no moving parts, EFDs use far less energy when compared to drives with rotating media and weigh less as well.

Logical unit (LUN)

A LUN is a logical disk object presented from the storage array to a host. It is identified by a LUN number, which uniquely identifies the LUN for that host. The LUN number is not globally unique.

RAID group In a VNX storage system, a RAID group is a set of physical disks with a RAID type on which one or more LUNs are bound. Each RAID group supports only the RAID type of the first LUN bound on it; any other LUNs bound on it have that same RAID type. LUNs are distributed equally across all the disks in the RAID group.

RAID 1 RAID method that provides data integrity by mirroring (copying) data onto another disk. This RAID type provides the greatest assurance of data integrity at the greatest cost in disk space. RAID 10 RAID method that mirrors and stripes, with data being written to two disks simultaneously. Data transfer rates are higher than with RAID 5, but RAID 1/0 uses more disk space for mirroring. Redundant

Array of Inexpensive Disks (RAID)

A method for storing information where the data is stored on multiple disk drives to increase performance and storage capacities and to provide redundancy and fault tolerance. Storage

processor (SP)

On VNX storage, a circuit board with memory modules and control logic that manages the storage system I/O between the host's Fibre Channel adapter and the disk modules.

System database

A database that is installed as part of the installation of Microsoft SQL Server. The system databases include master, model, msdb, tempdb, and others.

(12)

VNX FAST Cache

(13)

Chapter 2: Application Design

Overview

Introduction The primary application in this solution is Microsoft SQL Server 2008. In addition, this solution contains the following supporting key components:

EMC VNX series VNX FAST Cache

Scope The application design layout instructions presented in this chapter apply to the specific components used during the development of this solution.

Overview 13

Microsoft SQL Server 14

Microsoft SQL Server best practices 14

(14)

Microsoft SQL Server

Considerations _{Microsoft SQL Server is a relational database management system (RDBMS) that is}

commonly used in many business-critical applications to store, retrieve, and manage application data. It is sometimes considered an application, but is more appropriately considered as an application environment. Each individual business application has a dedicated set of database tables and associated query patterns that comprise the workload on the system as seen from a SQL server.

Implementation A single instance, single database of Microsoft SQL Server 2008 was used to test this solution.

An OLTP workload similar to a TPC-C workload was used for this solution. This workload was designed to simulate a typical transaction processing system characterized by many small transactions from a large number of users, which in aggregate appeared random.

Microsoft SQL Server best practices

Plan for storage performance, not for capacity

The most common mistake made while planning the storage for Microsoft SQL Server is to design for storage capacity and not for performance or I/Os per second (IOPS). With advances in disk technology, the increase in storage capacity of a disk drive has outpaced the increase in IOPS by almost 1,000:1 for spinning media. With this effect, it is rare to find a system that, when planned for performance, does not meet the storage capacity requirements for the workload. Hence, the IOPS capacity is the standard to be used while planning Microsoft SQL Server storage

configurations. Plan the storage capacity (GB) only after considering the IOPS capacity of a configuration.

Enable SQL Server to keep pages in memory

Microsoft SQL Server dynamically allocates and de-allocates memory based on the current state of the server to prevent memory pressure and swapping. However, if a process suddenly attempts to grab a substantial amount of memory, SQL Server may not be able to react quickly enough and the operating system (OS) may swap some of SQL Server’s memory to the disk. Unfortunately, there is a good probability that the memory that was swapped to the disk contains part of what SQL Server will soon be deallocating to decrease its memory use in response to the newly created memory pressure.

(15)

Chapter 3: Virtualization

Overview

Introduction This chapter provides procedures and guidelines to configure the virtualization components that can be used in this solution. Virtualization enables users to turn their infrastructure into an efficient and flexible internal cloud, thereby enabling them to:

Decrease their capital and operating costs by over 50 percent. Run a greener data center and reduce energy costs.

Control application service levels with advanced availability and security features. Streamline IT operations and improve flexibility.

VMware vSphere 4.1 is the virtualization platform used for this solution. In a VMware virtualized environment, storage can be provisioned to the SQL Server database in two different ways–Virtual Machine File System (VMFS) and Raw Device Mapping (RDM).

Scope The virtualization guidelines presented in this chapter apply to the specific components used during the development of this solution.

Overview 15

Concepts 16

Advantages of virtualization 16

(16)

Concepts

Virtualization layer

The virtualization layer abstracts the processor, memory, storage, and network resources of a physical server into multiple virtual machines. This allows multiple operating systems to run simultaneously and independently on a single physical server.

VMFS The SQL database and log LUNs on the storage array are connected to VMware vSphere 4.1 and are formatted as VMFS. The VMFS volume is then presented to the guest OS as local drives.

RDM The SQL database and log LUNs on the storage array are connected to VMware vSphere 4.1 as a raw device. The raw devices are then presented to the guest OS as local drive.

Advantages of virtualization

Reduced costs One of the main challenges faced by the customer is to reduce costs by using the infrastructure effectively. Virtualization enables a reduction in the number of servers and related IT hardware in the data center.

Reduced downtime

A running virtual machine production database can be moved from one physical server to another physical server without any downtime.

Performance and scalability

In a scale-out context, virtualization can provide superior performance and scalability compared to physically booted configurations, even when identical hardware is used.

Ease of use The single user interface enables administrators to manage and monitor multiple virtual machines from one console. Therefore, with virtualization, administrators can manage virtual machines easily and conveniently compared to physical servers.

Virtualization best practices

Be aware of virtual machine time

considerations

(17)

Chapter 4: Network Design

Overview

Introduction This chapter describes the network architecture of the solution.

Scope The network design guidelines presented in this chapter apply to the specific components used during the development of this solution.

Overview 17 Considerations 18 Implementation 18 Best practices 18

Considerations

Physical design considerations

To ensure uninterrupted communication between systems and storage in the environment, plan the networks for high availability. This includes having redundant switches and paths and redundant network interface cards (NICs)/HBA ports or HBA cards.

These physical design considerations apply to the Ethernet network used by clients to access the server and the Fibre Channel (FC) network used for the server to access the storage.

Logical design considerations

For the Ethernet network, administrators must ensure to load balance traffic across multiple links and prevent a single saturated link from affecting the application. For FC networks, zoning partitions the FC fabric into smaller subsets to improve throughput, manageability, application separation, and to add security. For path failover, high availability, and load balancing, consider a path failover software that seamlessly handles any single point of failure in the network.

Implementation

Physical design implementation

The Brocade DS-5100B switch was used in the testing of the solution for fabric connections, while the Cisco Catalyst 6509 switches were used to support gigabit Ethernet (GbE) connections.

(18)

Redundant paths were used between the storage and the servers.

Logical design implementation

World Wide Name (soft) zones were created on the FC fabric such that each HBA on the server was able to view each port on the storage array.

EMC PowerPath was installed on SQL Server for high availability and path failover.

Best practices

Plan for network high availability

(19)

Chapter 5: Storage Design

Overview

Introduction Storage design is an important element to ensure the successful deployment of the solution.

Scope The storage design layout instructions presented in this chapter apply to the specific components used during the development of this solution.

Overview 19

Design considerations 20

Storage design implementation 21

(20)

Design considerations

Overview Most people think of disks in terms of the data that can be stored on them. This refers to the storage capacity of the disk. The growth of data in gigabytes (GB) and terabytes (TB) is a measurement of the required storage capacity of the underlying disk system. However, another constraint in the design of storage systems is how quickly the data can be accessed. This refers to the performance capacity of the disk. Because the performance capacity of the disk directly translates into user experience, this constraint often exhibits challenges in planning and maintaining a production system.

Storage for databases

Traditionally, storage administrators add both performance and storage capacities to a system by adding disk spindles and various volume management techniques to harness the limits of the system. This method, while functional, is inefficient. It often leads to ―short-stroking,‖ where only a small portion of the storage capacity of the drive is used, while drawing its entire performance capacity. This leaves the additional storage capacity on the drive essentially inaccessible without negatively affecting the performance of the database residing on it.

EFDs – Changing the rules

The introduction of EFDs has skewed the relationship between performance capacity and storage capacity. A single EFD can replace many short-stroked drives by its ability to provide very high transaction rates. This reduces the total number of drives needed for the application and decreases the power consumption because of the fewer number of spinning disks. EFDs provide very low latency. This will benefit database applications where the response times are critical and where all the data cannot be kept at the host or the SP cache.

VNX FAST Cache

Placing the entire SQL database on EFDs will dramatically improve the performance. However, it would be a very costly solution. Alternatively, parts of the database (such as tables and indexes) that are accessed frequently can be moved to the EFDs. This exercise requires careful analysis and expertise and continuous monitoring to determine if the selected parts of the database are still valid to be stored on the EFDs because the access patterns may change over a period of time. If the storage system is shared by multiple SQL servers or databases, this task will be more complex.

The dynamic RAM (DRAM) cache that is traditionally available in storage arrays is too small to maintain large amounts of frequently accessed (hot) data for long periods.

(21)

Storage design implementation

Overview This document presents the performance studies using FAST Cache with a SQL OLTP workload. Each of these studies requires a different disk configuration. For this reason, most of the configuration information is located with the test data that it produces. Some elements of the design are common and are described in this section.

Common elements

In all cases, the primary storage for one or many databases was provided by a RAID 10 group of 15k rpm SAS disk spindles on a VNX5700 storage array running VNX Operating Environment for block. This is a standard for OLTP databases that require a high number of random I/Os to be serviced as quickly as possible.

In all cases, multiple LUNs were created against the RAID group consistent with the best practices, and set up in the database file group on SQL Server. There were no non-default configuration settings used unless explicitly stated in the test results section. The transaction log had a dedicated LUN on dedicated spindles.

Database working set

The database size was selected such that it was larger than the FAST Cache. For non-FAST Cache scenarios, the same database working set size was used to maintain consistency. The database size in all the cases was more than 400 GB.

Best practices

Do not allow database and log files to share physical spindles

It is highly recommended to ensure that the database data files and log files do not share the same physical spindles. This improves the performance and also helps to prevent the loss of data due to the loss of multiple drives.

Enable FAST Cache on appropriate LUNs

FAST Cache can show significant benefits for some workloads and almost no benefits on others. All workloads should be evaluated separately before FAST Cache is enabled. In the test cases for this solution, it was determined that there was no benefit by enabling FAST Cache on the transaction log. Configuring FAST Cache for the log actually hampers the overall system performance because FAST Cache pages are used to cache log records that are not needed.

Allow sufficient time for the cache to stabilize

(22)

Chapter 6: Virtualization Performance – Testing and Validation

Overview

Introduction This chapter examines the performance of Microsoft SQL Server 2008 using EMC VNX storage in a VMware virtual environment as compared to a physical

environment.

Overview 22

Testing tools 23

Methodology 23

Test results summary 24

Results analysis 24

(23)

Testing tools

Introduction To test an MS SQL Server 2008 environment, the Quest Benchmark Factory tool, designed by Quest, was used.

Quest Benchmark Factory

Quest Benchmark Factory was used to conduct benchmark testing by simulating users performing TPC-C OLTP database transactions. These transactions are considered TPCC-like since the TPC and Microsoft did not audit the results. All SQL Server parameters were set to the default values. No benchmark specific tunings were applied.

The following table provides the Transaction Types and Distributions that were used.

Transaction type Percentage

Stock level transaction 4

Delivery transaction 4

Order-status transaction 4

Payment transaction 43

New order transaction 45

The TPC-C workload simulates an online transaction processing (OLTP) environment suitable for many types of database applications. The workload is, however, artificial and the user counts and transactions per second (TPS) metrics reported are only a representation of the system response to this workload. By default, Quest Benchmark Factory TPC-C users generate approximately 0.05 TPS per user. By altering the timings for various test scenarios, 0.1 TPS per user was simulated. This ability becomes important as Microsoft SQL Server cannot accept more than 32,767 user connections in parallel. For simplicity all results are expressed in terms of TPS regardless of the user profile that was simulated.

Methodology

Introduction This section explains the testing methodology used.

Testing methodology

This testing used a single database with 5,000 TPC-C warehouses. This resulted in a database with approximately 400 GB of active data. Virtual users were added to this environment in periodic increments. The testing was stopped after the average transaction response time breached the gating metric of 2 seconds.

(24)

Unless otherwise noted, the scaling run was repeated at least twice with only minor differences observed between runs.

Testing results summary

Objective Determine the overall performance of Microsoft SQL Server 2008 in a VMware setup for an OLTP environment.

Setup In this study, the performance of EMC VNX is ascertained. The configuration used in this test scenario was 20 SAS drives (16 RAID 10 for database and four RAID 10 for log).

Result analysis

Introduction This section explains the result analysis for the different test scenarios.

Configuration The configuration used in this test was 20 SAS drives (16 RAID 10 for database

and 4 RAID 10 for log). The following two connection methods were used for the VMware environment:

RDM – The SQL database and log LUNs on the storage array were connected to VMware vSphere 4.1 as a raw device. The raw devices were then presented to the guest OS as local drives. The RDM can be connected to the ESX server in either physical or virtual compatibility mode. The physical compatibility mode allowed the guest OS to access the storage hardware directly.

VMFS – The SQL database and log LUNs on the storage array were

(25)

Setup The following figure shows the server configuration that is common across all the listed scenarios.

The following figure shows the storage configuration that is specific to this test scenario:

The configuration used 300 GB 15k rpm SAS spindles.

(26)

Testing results

The VMware RDM connection method achieved a maximum of around 1,337 TPS before becoming saturated based on the gating metric.

The VMware VMFS connection method achieved a maximum of around 1,332 TPS before becoming saturated based on the gating metric. There was almost no impact on the performance.

(27)

The following table provides the supported TPS and average response times. In this test scenario, virtualizing the system using RDM produced only a 4.9 percent decline in performance, while virtualizing the system using VMFS caused an 8 percent decline. Connection method TPS Average response time (s) Percent decline in TPS Physical 1,448 1.8 -- VMware RDM 1,377 1.7 4.9% VMware VMFS 1,332 1.3 8%

(28)

Chapter 7: FAST Cache Performance – Testing and Validation

Overview

Introduction This test examines the impact of adding FAST Cache to an existing SQL Server workload.

Overview 28

Testing tools 29

Methodology 29

Results analysis 30

(29)

Testing tools

Introduction To test the MS SQL Server 2008 environment, the Quest Benchmark Factory tool, designed by Quest, was used.

The ―Quest Benchmark Factory‖ section on page 23 provides more information on Quest Benchmark Factory.

Methodology

This testing used a single database with 5,000 TPC-C warehouses. This resulted in a database with approximately 400 GB of active data. Virtual users were added to this environment in periodic increments, allowing sufficient time for the FAST Cache to stabilize before sampling for that load level. The testing was stopped after the average transaction response time breached the gating metric of 2 seconds. For each load level, a series of virtual users was started in the test environment. Based on the user parameters, each user was expected to generate a certain TPS load under ideal circumstances. In the graphs, this is denoted on the horizontal axis as the Projected TPS for the load. It was expected that the achieved TPS will be below this level for any significant load on the system.

In most cases, 20 minutes was deemed sufficient for the cache to warm up for a specific workload.

Unless otherwise noted, the scaling run was repeated at least twice with only minor differences observed between the runs.

Testing results summary

Objectives Determine the potential benefit of adding FAST Cache to an OLTP environment.

Setup In this study, the performance of the systems before and after adding FAST Cache was compared. The configuration used in this testing was 20 SAS drives (16 RAID 10 for database and four RAID 10 for log).

(30)

Result analysis

Introduction This section analyzes the results of the different test scenarios.

(31)

The following diagram shows the storage configuration specific to this test scenario:

The storage configuration used 300 GB 15k rpm SAS spindles and 100 GB EFD devices.

NOTE: The shelf positioning in the diagram is for illustration purposes only. Components in this solution do not require a specific location within the DAE or cabinet. The drives must be positioned based on the published best practices as they apply to your environment

Testing results _{The configuration without FAST Cache achieved a maximum of around 1,448 TPS}

(32)

After FAST Cache was added, the performance improved dramatically, achieving a maximum of 5,838 TPS before becoming saturated.

A comparison of these two results shows that the inclusion of FAST Cache yields a dramatic increase in the number of TPS that the system can process.

The supported TPS and response times are given in the following table:

Configuration TPS Average response time (s)

20 SAS drives 1,448 1.8

20 SAS drives + 4 EFD 5,838 1.9

(33)

As the number of IOPS increases, it is interesting to observe the change in latency of those operations. Due to FAST Cache, the database disk latency is not very high.

The latency of I/O operations to the database disk decreased in the FAST Cache scenario, which is the opposite of the behavior observed in the baseline scenario. It is an accepted relationship that the database LUN latency can drive user response time. In this case, the two metrics do not map. This behavior gives rise to the

question as to what is driving the increase in response time. This behavior depends largely on the environment and requires a thorough analysis of the environment. However, this aspect is not covered in this document because at the time of publication, the analysis was not complete and the bottleneck may vary depending on the environment.

(34)

Chapter 8: LUN Provisioning Options – Testing and Validation

Overview

Introduction The EMC VNX series storage platform provides the following LUN provisioning options:

Pool LUNs

o Thick LUNs (Fully provisioned pool LUNs) o Thin LUNs

o Compressed LUNs RAID group LUNs

There are several ways to build an FC LUN on an EMC VNX series array. The traditional way used for the CX4 series of storage arrays required a user to specify a RAID group and then allocate all the space required for a LUN immediately. This is called a RAID group LUN. The CX4 series of arrays combined with FLARE® 30 introduced the concept of a storage pool, and by extension, a pool LUN. Pool LUNs can be fully provisioned, like a RAID group LUN, with all the space reserved

immediately. They can also provision space as needed. This is called a thin pool LUN. The equivalent of a fully provisioned pool LUN is commonly referred to as a thick pool LUN.

Pool LUNs enable you to take advantage of several features that were introduced with FLARE 30 like compression and FAST. For the VNX series of storage, the default recommendation is to provision LUNs as thick pool LUNs.

Compression is an attribute that can be enabled or disabled on each LUN. It can reduce storage costs significantly and is ideal for datasets that have low

performance requirements. Compression is also flexible because it is implemented at the LUN level, which can be applied to any data type that resides on the array. This chapter compares the performance of Microsoft SQL Server 2008 when using each of these LUN types. The chapter also examines the snapshot performance for pool LUNs and RAID group LUNs.

Overview 34

Testing tools 35

Methodology 35

Results analysis 36

(35)

Testing tools

Introduction To test the SQL Server 2008 environment, the Quest Benchmark Factory tool, designed by Quest, was used.

The ―Quest Benchmark Factory‖ section on page 23 provides more information on Quest Benchmark Factory.

Methodology

Introduction This section explains the testing methodology used. This testing used a single database with 5,000 TPC-C warehouses. This resulted in a database with approximately 400 GB of active data.

Testing

methodology 1

This methodology was used for the performance scaling test. Virtual users were added to this environment in periodic increments. The testing was stopped after the average transaction response time breached the gating metric of 2 seconds. For each load level, a series of virtual users was started in the test environment. Based on the user parameters, each user was expected to generate a certain TPS load under ideal circumstances. In the graphs, this is denoted on the horizontal axis as the Projected TPS for the load. It was expected that the achieved TPS will be below this level for any significant load on the system.

Unless otherwise noted, the scaling run was repeated at least twice with only minor differences observed between the runs.

Testing

methodology 2

This methodology was used for the snapshot performance test. A constant user load (approximately 25 percent of the saturation user load) was run. During the test run, the snapshot operation was triggered so that the impact of the operation on the production could be seen. In this solution, four snaps were taken at an interval of one hour duration. The duration of the constant user load test was selected such that it accommodated the completion of the snapshot operation in its window.

Testing results summary

Results summary

The following table summarizes the test results. The performance of different LUN types was compared with the performance of thick pool LUNs.

Method Impact

Thick pool LUNs baseline

Thin pool LUNs <1%

RAID group LUNs (+) 16.12%

(36)

NOTE: CX4-480 RAID group LUNs were almost 8 percent slower than the VNX5700 RAID group LUNs.

Result analysis

Introduction This section provides the result analysis for the following test scenarios: Thick pool LUNs

Thin pool LUNs RAID group LUNs Compressed pool LUNs

Configuration The configuration for all the scenarios used in this test was 20 SAS drives (16

RAID 10 for database and four RAID 10 for log).

Setup The ―Setup‖ section on page 25 provides the server configuration.

The following figure shows the storage configuration that is specific to this test scenario:

The configuration used 300 GB 15k rpm SAS spindles.

NOTE: The shelf positioning in the diagram is for illustration purposes only. Components in the presented solution do not require a specific location within the DAE or cabinet. The drive positioning must be based on published best practices as they apply to your individual environment.

Testing results Performance study – LUN provisioning options

This section presents the performance results achieved with each type of LUN along with the details of the actual storage space consumption on the array.

(37)

Thick pool LUNs

When thick pool LUNs were provisioned, the consumed capacity of the database pool was nearly 800 GB. The consumed capacity of the database changes with the provisioning options. This is discussed in the following sections.

Thick pool LUNs achieved a maximum of 1,247 TPS before becoming saturated based on the gating metric.

Thin pool LUNs

When thin LUNs were provisioned, the consumed capacity of the database pool reduced to 552 GB, giving 248 GB of space to the pool. However, the host still viewed 200 GB of space on all four LUNs.

Thin pool LUNs achieved a maximum of 1,243 TPS before becoming saturated based on the gating metric.

(38)

Compressed LUNs

When compression was enabled on thin LUNs, the consumed capacity of the database pool reduced from 552 GB to 492 GB, saving 60 GB of space with a compression ratio of 0.89.

Compressed pool LUNs achieved a maximum of 1,190 TPS before becoming saturated based on the gating metric. The degradation was less than 5 percent compared to thick pool LUNs, which is trivial considering the compression overhead.

RAID group LUNs

RAID group LUNs consume the same amount of space as the user capacity seen by the host server.

RAID group LUNs achieved a maximum of 1,448 TPS before becoming saturated based on the gating metric. RAID group LUNs performed nearly 16 percent better than thick pool LUNs.

(39)

The supported TPS and response times are given in the following table: Scenario Max TPS Avg Response Time (s) Percent TPS improvement / decline when compared to

thin LUNs

Thick pool LUNs 1,247 0.9 --

Thin pool LUNs 1,243 0.9 (-) 0.32

RAID group LUNs

(VNX5700) 1,448 1.8 (+) 16.12

RAID group LUNs

(CX4-480) 1,331 1.7 (+) 6.73

Compressed LUNs 1,190 1.7 (-) 4.57

In the tested environment, thin pool LUNs showed very minor performance decrement compared to thick pool LUNs, while compressed LUNs showed a marginal 4.5 percent decline.

(40)

Conclusion Thick pool LUNs perform well in SQL OLTP environments and are a good choice to start with. These can be converted to thin LUNs at a later point of time with minimal impact, if more efficient space management is required. If further space savings is required, compressing the LUNs is a good option. But this comes at the cost of reduced performance.

However, if there are stringent performance requirements that outweigh the

functional benefits of pool LUNs, it is recommended to provision RAID group LUNs in the environment.

Snapshot performance study – pool LUNs (Thick, Thin, and Compressed) as

compared to RAID group LUNs

Testing results _{This section examines the snapshot performance of different LUN types. The impact}

due to copy on first write (COFW) operation is observed and the time taken for each LUN type to settle back to the baseline value is noted.

Thick pool LUNs

(41)

Thin pool LUNs

(42)

Compressed LUNs

(43)

VNX5700 RAID group LUNs

(44)

CX4-480 RAID group LUNs

After the snapshot was initiated, the database disk latency shot up almost 10 times the normal value and then gradually came down. It took around 4 hours and 30 minutes to come back to the baseline value from the last snapshot.

The following table compares the snapshot performance of different LUN types.

LUN type Avg DB disk latency before the snap

Impact of snapshot on baseline (approx)

Settling time (approx)

Thick pool LUNs 7 ms 10x 150 min

Thin pool LUNs 10 ms 10x 210 min

RAID group LUNs

(VNX5700) 11.5 ms 10x 75 min

RAID group LUNs

(CX4-480) 12 ms 10x 270 min

Compressed LUNs 30.5 ms 15x 210 min

(45)

The settling times for all the LUN types of VNX5700 are better than those of the CX4-480 RAID group LUNs.

The impact of the snapshot due to COFW operation is almost the same in all scenarios except for the compressed LUNs. In the compressed LUN scenario, there are large spikes in the disk latency, which is expected due to the extra processing required for compressing/uncompressing the data.

Conclusion Thick pool LUNs perform well with snapshots in a SQL OLTP environment and are a good choice to start with. These can be converted to thin LUNs at a later point of time with minimal impact, if there is a need for more efficient space management. If further space savings is required, compressing the LUNs is a good option. But this comes at the cost of reduced performance.

However, if there are stringent performance requirements that outweigh the

(46)

Chapter 9: VNX for Block Protocols (10 Gb)

Overview

Introduction This study examines the performance characteristics of the following block protocols that VNX platforms support in a 10 Gb environment:

FCoE iSCSI

In a 10 Gb FCoE environment, there are two ways to connect to the storage array. FCoE capable switches generally include native Fibre Channel ports. They can be connected to traditional Fibre Channel SANs, or directly to EMC FCoE interfaces on the VNX array. Both methods are examined in this chapter.

Overview 46

Testing tools 47

Methodology 47

(47)

Testing tools

Introduction For protocol testing it is not required to examine the complete SQL Server

environment because the workload characteristics are constant until they reach the storage layer. Hence, the Microsoft SQLIO load generation tool was used instead of a database transaction load.

SQLIO SQLIO is a disk subsystem benchmark tool provided by Microsoft. This tool can be used to determine the I/O performance of a given configuration. The purpose of SQLIO is to test a variety of I/O types and sizes, and then determine the capabilities of an I/O subsystem. SQLIO is not used to simulate the I/O pattern of SQL Server. The tool generates results in terms of I/Os per second (IOPS), bandwidth (MB/s), and latency (ms).

Methodology

The following three connection methods with 10 Gb Ethernet links on the Cisco Nexus 5020 switch were examined:

10 Gb iSCSI

10 Gb FCoE (FCoE SLIC on VNX)

10 Gb FCoE from host to switch and 8 Gb FC from switch to VNX Two test loads were defined—one to examine the bandwidth and the other to achieve the maximum throughput on the link. In all cases, the dataset was defined so that it remained in the SP cache and was limited by the network interconnect.

Maximum bandwidth test

Sequential read I/Os of 64 KB was used to determine the maximum bandwidth (MB/s) achieved with the link. From a SQL Server perspective, this type of workload helps to manage large table scans or backup activity.

Maximum throughput test

Random read I/Os of 8 KB was used to determine the maximum throughput (IOPS) with the link. In a disk-bound scenario, higher IOPS is achieved by creating

(48)

Test results summary

Storage Setup Twenty six SAS drives (15k rpm) configured in a (13+13) RAID10 storage pool were used for all the three scenarios. Two fully provisioned LUNs were created (one LUN for each SP), and were made available to the host through the test network.

The VNX5700 has a factory setting of 512 MB read cache for each SP. The test file size for each LUN was 400 MB to fit the entire workload within the cache.

Network Setup The server and storage configuration was common for all scenarios. They differed in the storage network configuration.

The following figure shows a test scenario with a 10 Gb iSCSI network configuration.

(49)

The following figure shows a test scenario with a 10 Gb FCoE (FCoE SLIC on VNX) network configuration.

Result analysis

Testing results The results clearly show that further testing needs to be done to fully understand the performance maximums for this environment. The results are achieved with the default options in Windows and the storage array.

The following graph shows the maximum throughput results of the three different connection methods that supported IOPS loads.

(50)

For the bandwidth tests, a similar pattern was observed. The following graph shows that maximum bandwidth is achieved for all three connections.

However, the maximum network utilization was different in each case. The following graph shows the network utilization for each connection.

The initial iSCSI test scenario yielded very poor results. The test achieved only 26 percent network utilization. After investigating this behavior, it was possible to improve the network utilization to 93 percent by modifying the network parameters: 1. The initial configuration used two active ports and normal sized frames.

However, by enabling Jumbo Frames the network utilization was improved to 42 percent.

(51)

indicates that the second link was active.

3. A hardware iSCSI was used to achieve 89 percent network utilization for a 10 GbE environment. However, the same type of hardware in 1 GbE context showed no significant value.

4. Finally, Jumbo Frames were used on the hardware iSCSI to achieve 93 percent utilization.

The following graph shows the network utilization for all the iSCSI scenarios mentioned earlier.

Based on these test results, there are changes in best practices between 1 GbE and 10 GbE environments.

Conclusions All three protocols can generate good performance

The test scenarios documented here are specifically designed to achieve the highest performance from a connection. All three protocols are capable of generating good performance in a wide variety of workloads; the differences discussed occur at the edge of the performance envelope.

Enable Jumbo Frames for high-bandwidth applications

The results indicate that Jumbo Frames enhance the link utilization. Jumbo Frames must be enabled at every connection level—servers, switches, and arrays to have an appropriate impact.

Use iSCSI hardware initiators for maximum performance

The use of iSCSI hardware initiators yielded significant performance improvement for 10 GbE networks environment when compared to a 1 GbE environment.