EMC EXTREME PERFORMANCE AND EFFICIENCY FOR MICROSOFT SQL SERVER

White Paper
EMC EXTREME PERFORMANCE AND EFFICIENCY
FOR MICROSOFT SQL SERVER
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
 Optimize very high throughput for OLTP SQL Server workloads
 Virtualize and consolidate database instances
 Create multiple snapshot copies with no performance impact
 Minimize storage footprint for multiple database copies
EMC Solutions
Abstract
This white paper describes the operational advantages of virtualized Microsoft
SQL Server 2012 and 2014 databases deployed on an EMC® XtremIO™ all-flash
array, and how the solution enhances the capabilities of SQL Server dependent
environments.
June 2014
Copyright © 2014 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.
All trademarks used herein are the property of their respective owners.
Part Number H13163
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
2
Table of contents
Executive summary............................................................................................................................... 6
Business case .................................................................................................................................. 6
Solution overview ............................................................................................................................ 6
Key results ....................................................................................................................................... 7
Introduction.......................................................................................................................................... 8
Purpose ........................................................................................................................................... 8
Scope .............................................................................................................................................. 8
Audience ......................................................................................................................................... 8
Terminology ..................................................................................................................................... 8
Technology overview ............................................................................................................................ 9
Overview .......................................................................................................................................... 9
EMC XtremIO .................................................................................................................................... 9
Key advantages ........................................................................................................................... 9
Inline data reduction ................................................................................................................. 10
Writeable snapshots ................................................................................................................. 11
XtremIO Management Server ..................................................................................................... 13
VMware vSphere ............................................................................................................................ 13
Microsoft SQL Server ...................................................................................................................... 14
Microsoft SQL Server 2012 ........................................................................................................ 14
Microsoft SQL Server 2014 ........................................................................................................ 15
Solution architecture .......................................................................................................................... 17
Overview ........................................................................................................................................ 17
Architecture diagram ...................................................................................................................... 17
Hardware resources ....................................................................................................................... 18
Software resources ........................................................................................................................ 18
Storage layer: EMC XtremIO ............................................................................................................... 20
Overview ........................................................................................................................................ 20
Storage design ............................................................................................................................... 20
Database storage design considerations ................................................................................... 20
Storage design details ............................................................................................................... 22
Microsoft SQL Server database design ............................................................................................... 24
Overview ........................................................................................................................................ 24
OLTP database storage design ....................................................................................................... 24
OLTP database profile .................................................................................................................... 24
OLTP database design.................................................................................................................... 24
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
3
Network layer ..................................................................................................................................... 26
Overview ........................................................................................................................................ 26
SAN networking best practices ....................................................................................................... 26
IP network best practices ............................................................................................................... 26
VMware vSphere network best practices ........................................................................................ 26
Physical servers and virtualization layer ............................................................................................ 27
Overview ........................................................................................................................................ 27
Compute and storage resources ..................................................................................................... 27
Network virtualization .................................................................................................................... 28
Design considerations ........................................................................................................................ 29
Overview ........................................................................................................................................ 29
XtremIO configuration best practices ............................................................................................. 29
Fibre Channel switch configuration ............................................................................................ 29
Server configuration .................................................................................................................. 29
vSphere Native Multipathing configuration ................................................................................ 31
Performance testing and validation .................................................................................................... 33
Overview ........................................................................................................................................ 33
Notes on results ............................................................................................................................. 33
Test objectives ............................................................................................................................... 33
Test scenarios ................................................................................................................................ 34
OLTP workload performance test .................................................................................................... 34
Test methodology ...................................................................................................................... 34
Test procedure .......................................................................................................................... 34
Test results................................................................................................................................ 35
XtremIO system performance .................................................................................................... 36
SQL Server 2012 versus SQL Server 2014 performance ............................................................. 36
System with XtremIO snapshot performance test ........................................................................... 38
Test methodology ...................................................................................................................... 38
Test procedure .......................................................................................................................... 39
Test results................................................................................................................................ 39
XtremIO data reduction analysis .................................................................................................... 43
Cost-effective data reduction ..................................................................................................... 43
Deduplication rate..................................................................................................................... 44
Conclusion ......................................................................................................................................... 45
Summary ....................................................................................................................................... 45
Findings ......................................................................................................................................... 45
References.......................................................................................................................................... 46
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
4
EMC documentation ....................................................................................................................... 46
White papers ............................................................................................................................. 46
Product documentation ............................................................................................................. 46
EMC XtremIO .................................................................................................................................. 46
VMware documentation ................................................................................................................. 46
Microsoft SQL Server documentation ............................................................................................. 46
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
5
Executive summary
Business case
In today’s increasingly demanding business environments, enterprises are being
driven to optimize processes and improve service. There are also higher demands on
IT infrastructure performance and data availability, which are driven by:

High-transaction workloads

Time-critical applications and escalating service-level agreements

Turnkey and third-party applications with high sensitivity for I/O
responsiveness

Replication of application databases for use by supporting business processes
such as business intelligence (BI) reporting, testing, and development
functions

Need for highly available architectures
In most environments, enterprises need to create copies of production data with
minimal impact to the system, and safely repurpose those copies for business teams
within their organization to use the data. Typically, they must wait hours or days to
get access to copies of production data. This delay reduces their effectiveness for
tasks such as BI insight, testing and development (test/dev), data integrity,
validation, and auditing.
As enterprises attempt to improve data availability, issues occur when the technology
solution cannot meet expectations, such as:

Complex configuration for SQL Server environments for production, test/dev,
and analytics

Limited capabilities to maintain multiple copies of databases for read and write
purposes, without affecting production performance or incurring significant
costs for duplicated high-performance environments

Over-used operations staff and increased costs associated with third-party
tools due to unwieldy backup and recovery methods
Enterprises that rely on Microsoft SQL Server must consider new approaches to meet
continuing operational performance and capacity management challenges. Currently,
they must consider systems that provide higher levels of performance while
minimizing operational costs and complexity.
Solution overview
Working together, Microsoft and EMC supply the critical components to deliver highperformance, enterprise-class availability solutions for SQL Server environments.
With EMC® XtremIO™, EMC provides you with a storage solution that is optimized for
extreme online transactional processing (OLTP) database performance for SQL Server
and ensures that you can maximize the efficiencies of other system resources, such
as CPU and memory.
Provisioning storage for optimal database performance is traditionally a complex,
time-consuming process that requires expert knowledge of the database itself, not
only of storage systems. The XtremIO all-flash array responds to changing conditions
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
6
such as transaction processing spikes and complex queries, and supports test and
development environments with up-to-date copies of production databases.
Through XtremIO array-based snapshot capabilities, this solution not only provides
near-instant recovery technology to minimize downtime once a database issue (data
loss, logical corruption, and so on) occurs, but also enables faster, easier, and more
cost effective data accessibility that improves business intelligence and analytics.
XtremIO all-flash arrays resolve database storage challenges by:
Key results

Creating a volume with just a few clicks and enabling the entire database
structure to be put into it. No planning, provisioning, or tuning steps are
required.

Automatically employing all storage system resources—solid-state drives
(SSDs) and controllers—all the time.

Scaling out the XtremIO system and increasing performance if your
requirements exceed what a single XtremIO X-Brick delivers.

Eliminating complexities by using XtremIO snapshots to manage multiple
instances and copies of databases.
The solution shows that the EMC XtremIO all-flash storage array delivers:

Fast and simple setup with little to no storage tuning. XtremIO works as
seamlessly in virtualized SQL Server environments as in physical ones, and is
easy to manage and monitor.

Support for the most demanding transactional SQL Server 2012 and SQL Server
2014 workloads, with throughput that can easily exceed 200,000 IOPS for a
two X-Brick configuration while maintaining sub-millisecond latencies.

Substantial storage footprint savings by using XtremIO inline data reduction
and snapshots, which we observed in this configuration with a 16:1 overall
efficiency.

Close to real-time, high-performance copies of data using XtremIO snapshot
technology at no measurable cost, while providing near-instant recovery of
production data, even in TBs of data scale.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
7
Introduction
Purpose
This white paper describes a highly available and scalable solution for Microsoft SQL
Server deployed in a virtualized vSphere environment with XtremIO storage. The white
paper also demonstrates that XtremIO read/writeable snapshots provide highly
effective reporting or development environments with no impact to the consolidated
production server performance.
Scope
The white paper demonstrates how:

The solution improves and enhances the performance of SQL Server 2012 and
2014 versions by providing new capabilities and simplifying the configuration
of the environment

XtremIO read/writeable snapshots provides instant creation of multiple
database copies with minimal performance impact on production databases
Audience
The white paper is intended for Microsoft SQL Server database administrators (DBAs),
VMware administrators, storage administrators, IT architects, and technical managers
responsible for designing, creating, and managing Microsoft SQL Server databases,
infrastructure, and data centers.
Terminology
The white paper includes the following terminology.
Table 1.
Terminology
Term
Definition
Data synchronization
The process by which changes to a primary database are
reproduced on a secondary database.
OLTP
Typical applications of online transaction processing
(OLTP) include data entry and retrieval transaction
processing.
Round robin
Round robin uses an automatic path selection policy to
rotate through all available paths, enabling the distribution
of load across the configured paths. Round robin can
present one of the most performance-effective ways of path
selection. The next available I/O path in the list is selected
without any determining factor. If you had, for example, six
I/Os in the queue to storage, paths 1 to 6 would be used in
order.
VMDK
A virtual machine datafile for VMware.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
8
Technology overview
Overview
EMC XtremIO
The key technology components used in this white paper are:

EMC XtremIO

VMware vSphere

Microsoft SQL Server
The EMC XtremIO storage array is an all-flash system based on scale-out architecture.
The system uses building blocks, called X-Bricks, which can be clustered together to
grow performance and capacity as required. This solution uses two X-Bricks clustered
together as a single logical storage system.
Key advantages
XtremIO uses flash to deliver value across the following main dimensions:

Performance: Regardless of how busy the system is, and regardless of storage
capacity utilization, latency and throughput remain consistent, predictable, and
constant. Latency within the array for an I/O request is typically far less than
one millisecond (ms). Figure 1 shows an example of the XtremIO dashboard
used to monitor performance.
Figure 1.

XtremIO Storage Management Application dashboard
Scalability: Based on a scale-out architecture, the XtremIO storage system’s
single X-Brick is a building block. Multiple X-Bricks can be clustered together to
provide additional performance or capacity. Performance scales linearly,
ensuring that two X-Bricks supply twice the IOPS and four X-Bricks supply four
times the IOPS of the single X-Brick configuration, while latency remains
consistently low as the system scales out. XtremIO arrays scale out to any
required performance or capacity level, as shown in Figure 2.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
9
Figure 2.
X-Brick scaling units

Data reduction: The core XtremIO engine implements content-based inline data
reduction. XtremIO automatically reduces (deduplicates) data as it enters the
system. This reduces the amount of data written to flash, improving longevity of
the media and reducing cost. Volumes are always thin-provisioned without any
loss of performance, over-provisioning of capacity, or fragmentation.

Data protection: XtremIO uses a proprietary flash-optimized data protection
algorithm, XtremIO Data Protection (XDP), which provides superior data
protection, while enabling performance that surpasses any existing RAID
algorithms. Optimizations in XDP also result in fewer writes to flash media for
data protection purposes.

Functionality: XtremIO supports high performance and space-efficient
snapshots, inline data reduction, thin provisioning, and full vSphere VAAI,
integration with support for Fibre Channel (FC) and iSCSI protocols.

Simplicity: There is no need to choose the RAID type, create a RAID group, or
decide whether or not to enable thin provisioning or deduplication. These
functions are already built into the system. Provisioning storage with XtremIO is
as simple as deciding how large a LUN you want to create.
Inline data reduction
XtremIO inline data reduction provides a number of benefits including:

Cost-effective data reduction while increasing performance and reliability

Easy scale-out

Inline, global, and always on

Improved array performance

Extended flash endurance
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
10
Deduplication, data reduction, and scale-out
One of the key differentiators for XtremIO is the built-in deduplication, which is 100
percent optimized for flash drives (SSDs) and always on with no setup,
administration, or tuning required.
While flash performance is highly desirable, the cost can be prohibitive. With the
XtremIO real-time data-reduction technology, you can sustain a logical capacity that
often exceeds the physical flash capacity of the system by a large margin.
The effective cost of XtremIO can be lower than a traditional array for the same
amount of data, making it extremely attractive compared to other flash-based
solutions.
With data reduction, the XtremIO system’s capacities can expand beyond its physical
storage. The effective logical capacity of a single X-Brick can be much more than its
nominal flash capacity in environments that contain highly duplicate information.
Inline, global, and always on
Historically, data reduction techniques were relegated to secondary workloads such
as backup and archiving because they resulted in a severe negative performance
impact. In contrast, the XtremIO data reduction technology not only creates no
performance loss, but also accelerates data reduction.
Data reduction occurs between all logical volumes on the array and across all XBricks in a cluster. Data reduction ratios are greatly enhanced since the process is not
constrained within a single volume.
XtremIO built-in data reduction is always on and does not require any administration
work.
Extended flash lifetime
XtremIO data reduction extends the life of the flash. Writes are avoided by reducing
data in flight, and extending flash endurance by leaving flash write cycles available
for unique data.
Improved array performance
On XtremIO storage, the more data reduction that takes place, the faster the array
runs.
XtremIO inline data reduction reduces data in real time in the data path and does not
need post-processing operations, which improves performance, consistency, and
predictability while adding less I/Os to the flash drives.
Writeable snapshots
XtremIO elevates writeable snapshots beyond data protection as the key enabler of
huge productivity gains by:

Creating as many writeable copies of production volumes as you need with a
small storage footprint

Consolidating test/dev, data warehousing, business intelligence copies, and
application workloads

Managing the agile database lifecycle
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
11
XtremIO snapshots are equivalent to production volumes in terms of performance,
property, and functions, which means that a snapshot in XtremIO can be considered
the same as the production volume.
Figure 3 shows how XtremIO works in an environment with a demand for large
amounts of test/dev, quality assurance (QA) data from a writeable snapshot.
Figure 3.
XtremIO snapshots
XtremIO snapshots provide users not only with a clone-like image that can be used as
a test bed, but also lower the cost of creating and keeping many of them. This makes
it possible for many applications to use production data for development as needed,
and makes data available for QA or business intelligence needs.
Benefits of snapshots include:


Inherently writeable, not read-only

Built into metadata
Metadata is only needed for globally unique writes. Entire metadata copies
are not required as in other snapshot implementations.

Can be used as live production volumes without the need to create a
writeable or instantiate snapshot for read/write access
Space and metadata efficient

Each snapshot does not need full metadata structure
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
12



Common metadata is shared between production and snapshot

Space is only used for new unique data blocks and associated metadata

Deduplication and thin provisioning is always on

Enables affordable consolidation
Maximum performance, scalability, and economy

Instant creation of a complete snapshot

No impact on system performance

No overhead from “brute-force” copies

No metadata bloat

Minimizes deletion penalty for data and metadata
Flexibility

Take and keep as many snapshots as needed

Take snapshots of snapshots at any level

Create any snapshot tree topology as needed

Remove snapshots or their parent volume as needed
XtremIO Management Server
XtremIO Management Server (XMS) is a standalone dedicated Linux-based server that
is used to control the XtremIO system operation. XMS can be either a physical or a
virtual server. The array continues operating if it is disconnected from XMS but cannot
be configured or monitored.
VMware vSphere
VMware vSphere is a complete and robust virtualization platform, virtualizing
business-critical applications with dynamic resource pools for unprecedented
flexibility and reliability. It transforms the physical resources of a computer by
virtualizing the CPU, RAM, hard disk, and network controller. This transformation
creates fully functional virtual machines that run isolated and encapsulated operating
systems and applications.
VMware vSphere 5.5 is the 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.5 has the following scalability and performance enhancements, which
enable a virtual machine to use more resources from the hypervisor:

Support for 62 TB virtual machine datafile (VMDK)

Microsoft Cluster Service (MSCS) updates: VMware introduced a number of
additional features to support MSCS, including:

Microsoft Windows 2012 cluster
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
13

“Round-robin” path policy for shared storage1

iSCSI protocol for shared storage

FC over Ethernet (FCoE) protocol for shared storage with regard to the
introduction of round-robin support

16 GB E2E support: VMware introduced 16 GB end-to-end FC support. Both the
HBAs and array controllers can run at16 GB as long as the FC switch between
the initiator and target supports it.

PDL AutoRemove: Introduced with vSphere 5.5, this feature automatically
removes a device from a host when the device enters a PDL state.

vSphere Replication Interoperability

vSphere Replication Multi-Point-in-Time Snapshot Retention

vSphere Flash Read Cache
XtremIO offers efficient enterprise storage working with VMware vSphere 5.5 cloud
infrastructure.
Microsoft SQL
Server
Microsoft SQL Server 2012
Microsoft 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 (DR) solution for SQL Server 2012. AlwaysOn presents enhanced capabilities
for both specific databases and entire instances, providing flexibility to support
various high availability configurations through:

AlwaysOn failover cluster instances (FCI)

AlwaysOn Availability Groups (AAG)
This solution explores AAG, with a focus on the transaction-level replication feature
that provides access to near-live readable secondary replicas of production
databases.
AlwaysOn Availability Groups
AAG is a high-availability and disaster recovery solution introduced in SQL Server
2012, which enables administrators to maximize availability for one or more user
databases. SQL Server instances are configured so that a single primary database or
a group of primary databases can have up to four secondary database copies residing
on Windows Server Failover Cluster (WSFC) nodes.
1
A number of changes were made in vSphere 5.5 concerning the SCSI locking mechanism
used by MSCS when a failover of services occurs. To facilitate this new path policy, changes
have been implemented that make it irrelevant which path is used to place the SCSI
reservation; any path can free the reservation.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
14
Readable Columnstore indexes
The Columnstore index introduced in SQL Server 2012 provides significantly
improved performance for data warehousing types of queries.
SQL Server 2012 Columnstore indexes cannot be dynamically updated.
Microsoft SQL Server 2014
Microsoft's release of SQL Server 2014 has several compelling features.
New In-Memory OLTP Engine
By moving selected tables and stored procedures into memory, SQL Server 2014 can
drastically reduce I/O and improve performance of the OLTP applications.
The in-memory OLTP engine is designed for high concurrency and uses an optimistic
concurrency control mechanism to eliminate locking delays. In-memory OLTP tables
are copied into memory and made durable by transaction log writes to disk.
Enhanced Windows Server 2012 Integration
SQL Server 2014 provides improved integration with Windows Server 2012 and
Windows Server 2012:

Scales up to 640 logical processors and 4 TB of memory in a physical
environment

Scales up to 64 virtual processors and 1 TB of memory when running on a
virtual machine

Supports Windows 2012 R2 Storage Spaces feature to create tiered storage
pools that improve performance

Takes advantage of Server Message Block (SMB) 3.0 enhancements to achieve
high-performance database storage on file shares
With the new SMB Direct feature, you can use the network interface card (NIC)
remote direct memory access (RDMA) feature to provide access speeds for SMB
file shares nearing the access speed for local resources.
Resource Governor Improvement
The SQL Server 2014 Resource Governor provides a new capability to manage
application storage I/O utilization. The Resource Governor can limit the physical I/Os
issued for user threads in a given resource pool, enabling more predictable
application performance. This can be used to limit the number of I/Os issued at the
SQL Server instance boundary.
Buffer pool extension
The buffer pool extension provides the seamless integration of SSDs as a high-speed,
nonvolatile random access memory (NVRAM) extension to the Database Engine
standard buffer pool to significantly improve I/O throughput. The new buffer pool
extensions can provide the best performance gains for read-heavy OLTP workloads
Enhancements to AlwaysOn Availability Groups
The SQL Server 2014 AAG has been enhanced with support for additional secondary
replicas and Windows Azure integration.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
15
Readable secondary replicas in SQL Server 2014 are available for read-only
workloads, even when the primary replica is unavailable.
Updateable Columnstore indexes
Columnstore indexes in SQL Server 2014 are updateable. You can make updates to
the underlying table without first needing to drop the Columnstore index. A SQL
Server 2014 Columnstore index must use all of the columns in the table, and it
cannot be combined with other indexes.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
16
Solution architecture
Overview
This section describes the solution architecture.
The solution provides an optimal cost-to-performance ratio for Microsoft SQL Server
mission-critical application environments. The SQL Server 2012 and 2014 databases
are deployed as virtualized databases on an XtremIO storage array consisting of two
X-Bricks. There are also virtualized test/dev SQL Server instances in the environment
that access the XtremIO snapshots of the production database for testing and
development purposes.
Architecture
diagram
Figure 4 shows the logical architecture of this solution.
Figure 4.
Solution architecture
The architecture is composed of the following:

Storage layer: Comprised of two X-Bricks in a single XtremIO cluster (12U,
XtremIO version 2.4) with 14.94 TB of usable physical capacity.

SQL Servers database layer: Comprised of both SQL Server 2012 and SQL
Server 2014 as production servers. SQL Server 2012 has six databases and a
total of about 7 TB data. SQL Server 2014 has three databases and total of
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
17
about 4 TB data. Snapshots can be mounted onto any of the mount hosts at
any time, when needed.

Network layer: Comprised of two IP switches and two director-class SAN
switches, which we2 configured to produce 108 GB/s active bandwidth. The
SAN switches are designed for deployment in storage networks supporting
virtualized data centers and enterprise clouds.

Physical servers and virtualization layer: Comprised of three servers that use a
total of 120 Intel E7 processor cores with 2.9 GHz processors and a total of 2 TB
RAM. The rack server enables a high-performing, consolidated, virtualized
approach to a Microsoft SQL Server infrastructure, resulting in deployment
flexibility without the need for application modification.
The servers are installed with vSphere 5.5 and configured as a VMware ESXi
cluster. The cluster is comprised of two enterprise-class production SQL Server
virtual machines (SQL Server 2012 and SQL Server 2014). There are also three
more standalone SQL Servers—two SQL Server 2012 and one SQL Server 2014
virtual machines. Each virtual machine is configured with 16 vCPUs and 32 GB
RAM.
We examined the performance by running OLTP workloads against the databases on
these SQL Servers.
Hardware
resources
Table 2 lists the hardware resources used in the solution.
Table 2.
Hardware resources
Hardware
Quantity
Configuration
Storage array
1
XtremIO consisting of two X-Bricks
Servers
3
20 cores, 2.9 GHz processors, 512 GB RAM, including:
 2 x 1 Gb quad Ethernet (GbE) NICs
 2 x 10 GbE NICs
 2 x 8 GB FC dual-port HBAs
LAN switches
2
10 GbE, 32-port non-blocking
SAN switches
2
FC director-class switches with 6 blades
Software resources Table 3 lists the software resources used in this solution.
Table 3.
Software resources
Software
Version
Notes
XtremIO
2.4
All-flash storage
VMware vSphere
5.5
Hypervisor hosting all virtual
machines
VMware vCenter
5.5
Management of vSphere
2
In this white paper, “we” refers to the EMC Solutions engineering team that validated the
solution.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
18
Software
Version
Notes
Microsoft Windows 2012
R2
Operating system for database
servers
Microsoft SQL Server 2012
SP1 Enterprise Edition
Database
Microsoft SQL Server 2014
RTM Enterprise Edition
Database
Microsoft BenchCraft TPC-E
Toolkit
1.12.0-1026
TPC-E-like OLTP benchmark
workload tool
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
19
Storage layer: EMC XtremIO
Overview
XtremIO uses its multi-controller scale-out design and RDMA fabric to maintain all
metadata in memory.
This makes XtremIO arrays impervious to changes in workload—it does not matter
what LUN sizes are used, whether there are random or sequential access patterns, or
if there is locality of reference or not. The performance is always consistent and
predictable.
The need for a careful, painstaking storage design for optimized performance is no
longer necessary. For example, disruptive tempdb database workloads can co-exist in
the same LUN with its write-intensive transaction logs and still provide excellent
performance. With built-in thin provisioning, storage is only allocated when it is
needed. This enables DBAs to create larger LUNs to accommodate future or
unexpected growth for databases, without wasting any physical space on storage.
Best of all, heavy metadata operations such as inline data reduction, thin
provisioning allocations, and internal array copy operations are conducted entirely in
memory, instantly, without impacting I/O.
Storage design
Database storage design considerations
Performance is the number one consideration for tier-1 database storage design, but
inherent in traditional performance storage designs are the complexities and high
costs.
Database storage design typically requires free space at all levels of the storage
stack, from actual data in databases, to space allocated to datafiles and log files.
If a database runs out of datafile space, the database instance stops committing any
new transactions, and an immediate manual remediation is required to avoid
crashing the database and losing data. It is critical that the line of business is not
affected. If database file autogrowth is enabled, SQL Server automatically allocates
additional chunks of storage on disk to avoid this database file-full situation, but that
operation usually affects database performance and, if used indiscriminately, could
cause repeated fragmentation of datafiles across disks, which can further impact the
performance.
EMC and Microsoft SQL Server best practices recommend that you configure SQL
Server datafile sizes to be 10 to 20 percent larger than the current or intended
database size. Doing this requires free space at the NTFS volume level, and results in
the underlying storage space being locked out without perceivable value until the
space is needed. A maintenance window and manual intervention is required if the
NTFS volume needs expansion.
It is difficult to balance how much free disk space to allocate at the design stage,
which will not have an immediate use, versus the amount of readily available free
space for growth.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
20
Figure 5 shows an example of wasted space in a 1 TB database. This issue occurs
multiple times in database environments with multiple databases and log files across
many SQL Server instances, which compounds the cost and management
complexities.
Figure 5.
Traditional storage capacity consumption planning
In this example, you have 1 TB of data, but need at least 1.58 TB of allocated storage
space, by adhering to the traditional storage planning best practices. This represents
about a 58 percent waste of physical storage allocation for free space purposes.
Figure 6 shows how a 1 TB database can easily use less than 1 TB of physical storage
allocation on XtremIO and still satisfy the logical free space required for storage
planning.
Figure 6.
XtremIO storage capacity consumption planning
With XtremIO, using thin provisioning (allocate-on-demand) and deduplication means
that a 1 TB database requires less than 1 TB of allocated physical space. The
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
21
operational complexities can be eliminated by allocating as much LUN space, virtual
file system space and, therefore, NTFS volume space, as required from the start
because storage is only allocated on demand.
Storage design details
For this solution, XtremIO is deployed in a two X-Brick cluster configured by default
with XtremIO XDP to provide a physical capacity of 14.94 TB, as shown in Figure 7.
Figure 7.
XtremIO Management Application dashboard storage panel
With XtremIO, both random and sequential I/O generated from the database are
treated equally as data is randomized and distributed in a balanced way throughout
the array. The storage design for the Microsoft SQL Server database can be simplified
compared to traditional provisioning techniques.
For this solution, the volume size is standardized for easy deployment as shown in
Table 4. If you take advantage of thin provisioning, larger volume allocation does not
waste any physical storage while leaving room for growth.
Table 4.
Microsoft SQL Server storage design on XtremIO
Volume name
Volume purpose
LUN size
SQL_OS
Microsoft Windows 2012 R2 OS and SQL Server software
installation volume, which is used for multiple virtual
machines such as VMDK on the same datastore
1 TB
SQL_DB
Microsoft SQL Server database datafile volumes
2 TB
SQL_log
Microsoft SQL Server database log file volumes
500 GB
Tempdb
Microsoft SQL Server tempdb volumes
1 TB
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
22
For the production databases, volumes are created and presented to the virtual
machine for use with the Microsoft SQL Server virtual machines, as shown in Table 5.
Table 5.
Volume/LUN assignment for OLTP Database
Volume
Volume size
Volume type
OS
120 GB
VMDK on OS LUN/VMFS volume
SQL Server installation
and systems databases
120 GB
VMDK on OS LUN/VMFS volume
SQL Server data
2 TB
RDM or VMDK
SQL Server log
500 GB
RDM or VMDK
Tempdb
1 TB
RDM or VMDK
Note: Performance and availability of either RDM or VMDK volumes is very similar, so either
choice is reasonable depending on individual design requirements. Certain technologies,
such as Windows Failover Clustering, require RDMs when running in-virtual-machine
clustering (to support SCSI-3 reservations).
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
23
Microsoft SQL Server database design
Overview
In this solution, two virtualized instances with transactional OLTP databases (one on
Microsoft SQL Server 2012 and one on Microsoft SQL Server 2014) were created on a
vSphere high availability (HA) cluster.
OLTP database
storage design
As Table 5 on page 23 shows, we used six 2 TB database volumes to store the
relevant database files, including datafiles, and transaction log files, and temporary
files for the SQL Server 2012 databases. We used three 2 TB database volumes to
store the relevant files for the SQL Server 2014 databases.
OLTP database
profile
Table 6 lists OLTP database profile for the solution.
Table 6.
Database profile for OLTP database
Property
SQL Server 2012
SQL Server 2014
Database type
OLTP (transactional)
OLTP (transactional)
Database size
Total: 5 TB
Total: 2.25 TB
Microsoft SQL
Server databases
1 x 2 TB, 1 x 1 TB, 1 x 750 GB,
2 x 500 GB, 1 x 250 GB
1 x 1 TB, 1 x 750 GB, 1 x 500 GB
Memory for SQL
Server
32 GB
32 GB
Workload profile
OLTP workload simulated by
Microsoft BenchCraft
OLTP workload simulated by
Microsoft BenchCraft
Read/write ratio: 90/10
Read/write ratio: 90/10
8 KB
8 KB
Average data block
size
OLTP database
design
Table 7 and Table 8 list the OLTP database actual LUN design for the solution.
Table 7.
OLTP database actual LUN design detail for SQL Server 2012
Detail
Databases
Database name
DB_01
DB_02
DB_03
DB_04
DB_05
DB_06
Tempdb
Actual database size
750 GB
500 GB
1 TB
2 TB
250 GB
1 TB
400 GB
LUN size
2 TB
2 TB
2 TB
2 x 2 TB
2 TB
2 TB
1 TB
Actual log size
350 GB
250 GB
320 GB
360 GB
175 GB
320 GB
80 GB
Log LUN size
500 GB
500 GB
500 GB
500 GB
500 GB
500 GB
n/a
Total data and log size
7.2 TB
Total LUN size
16 TB
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
24
Table 8.
OLTP database actual LUN design detail for SQL Server 2014
Detail
Databases
Database name
DB_01
DB_02
DB_03
Tempdb
Actual database size
750 GB
500 GB
1 TB
400 GB
LUN size
2 TB
2 TB
2 TB
1 TB
Actual log size
350 GB
250 GB
320 GB
80 GB
Log LUN size
500 GB
500 GB
500 GB
n/a
Total data and log size
3.7 TB
Total LUN size
8.5 TB
Note: This design is based on our test workload. In a production environment, database
size, especially log file and tempdb sizes, can vary, depending on the type of transactions
and queries that are running on those databases.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
25
Network layer
Overview
This section describes the network details used in this solution for SAN and IP
network configuration, and for ESXi Server networks. When deploying a virtualized
database solution, such as Microsoft SQL Server, EMC recommends that you ensure
both compute and network redundancy at all levels when designing networking fault
tolerance.
SAN networking
best practices
EMC recommends you use the following SAN network best practices:
IP network best
practices
VMware vSphere
network best
practices

Use 8 Gb/s FC switches and HBA ports.

Use multiple HBAs on the ESXi servers and at least two SAN switches to provide
multiple redundant paths between the server and the XtremIO cluster.

Zone each FC port from the database servers to all ports on the XtremIO XBricks for high availability and performance.
EMC recommends that you use the following IP network best practices:

Use multiple network cards and switches for network redundancy.

Use 10 GbE for network connection, if available.

Use virtual local area networks (VLANs) to logically group devices that are on
different network segments or sub-networks.

Enable and configure jumbo frames3 throughout the physical or virtual stack for
10 GbE networks.
Networking in virtual environments requires more considerations for traffic
segmentation, availability, and throughput in addition to the best practices followed
in a physical environment.
This solution was designed to efficiently manage multiple networks and redundancy
of network adapters on ESXi hosts. The key best practice guidelines are to:

Separate infrastructure traffic from virtual machine traffic for security and
isolation.

Use the VMXNET3 family of paravirtualized network adapters.

Aggregate physical network cards for network redundancy and performance, for
example, use pairs of physical NICs per server/vSwitch, and uplink each
physical NIC to separate physical switches.
For more information on networking with vSphere, refer to the instructions in VMware
vSphere Networking.
3
Maximum Transfer Unit (MTU) sizes of greater than 1,500 bytes are referred to as jumbo
frames. Jumbo frames require Gigabit Ethernet across the entire network infrastructure,
including servers, switches, and database servers.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
26
Physical servers and virtualization layer
Overview
The choice of a server platform for a virtualized infrastructure is based on both the
supportability of the platform and the technical requirements of the environment. In
production environments, it is essential that the servers used have:

Sufficient processors and memory to support the required number and
workload of the virtual machines.

Sufficient connectivity, both Ethernet and FC, to enable redundant connectivity
to the IP and storage network switches.

Sufficient capacity to withstand a server failure and support failover of the
virtual machines.
In this test environment, three physical servers running vSphere ESXi 5.5 are
configured as a vSphere HA cluster. Five virtual machines are created on this vSphere
cluster, two of which are configured to create virtualized Microsoft SQL Server
database virtual machines. The other three virtual machines are created as test/dev
instances that can be used to mount various snapshots for repurposing (test/dev).
Compute and
storage resources
EMC recommends that you implement the following VMware compute resource best
practices as explained in the Microsoft SQL Server Databases on VMware Best
Practices Guide:

Use Non-Uniform Memory Access (NUMA) on the ESXi servers, a computer
architecture in which memory located closer to a particular processor is
accessed with less delay than memory located farther from that processor.

Allocate virtual machine memory (vRAM) in a virtual machine to be less than or
equal to the local memory accessed by the NUMA node (processor).

Install VMware Tools, including several utilities that enhance the performance
of the virtual machine's guest operating system and improve the ability to
manage the virtual machine.

Configure the virtual machine memory reservations to be, at a minimum, the
size of the Microsoft SQL Server and operating system overhead.

Microsoft SQL Server only supports RDM for clustering, so use RDM in ESXi
virtual machine for database and log files that needs to failover in an MSCS
clustering.

Configure multiple paravirtualized SCSI (PVSCSI) controllers for the database
volumes. Using multiple virtual SCSI controllers enables the execution of
several parallel I/O operations inside the guest operating system.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
27
Network
virtualization
On each ESXi server, we created two standard vSwitches with a common
configuration as listed in Table 9.
Table 9.
vSwitch configuration
Name
Purpose
vSwitch0
Management and public virtual machine traffic
vSwitch1
Fault tolerant configuration for Microsoft SQL Server Cluster interconnect
Each virtual machine was assigned two vNICs (1 GbE and 10 GbE) using the high
performance VMXNET3 driver. The 1 GbE vNIC was mapped to vSwitch0 to deliver
public traffic. The 10 GbE vNIC was mapped to vSwitch1 to deliver Microsoft SQL
Server interconnect traffic.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
28
Design considerations
Overview
XtremIO makes it possible to run extremely high I/O loads on a single storage system.
With the XtremIO balanced architecture combined with performance, inline data
reduction, and virtually provisioned storage, many of the fine-tuning and
configuration practices that a traditional storage array requires are no longer needed.
To take full advantage of the high throughput that XtremIO storage provides, the
entire connectivity stack needs to be configured properly to reach extreme
performance, from optimizing queue depths on hosts to the number of available FC
paths that must be considered so that enough I/O can be pushed towards the
XtremIO system.
XtremIO
configuration best
practices
Fibre Channel switch configuration
For an XtremIO dual X-Brick cluster, a host may have up to eight paths per device.
Figure 8 shows the logical connection schemes for eight paths.
Figure 8.
XtremIO dual X-Brick FC switch configuration
Note: You can use EMC Virtual Storage Integrator (VSI) Path Management to configure path
management across EMC platforms, including XtremIO. Refer to the EMC VSI Path
Management Product Guide for further information on using this VMware vSphere client
plug-in.
Server configuration
To optimize performance to extreme levels, hosts accessing the XtremIO storage array
must be configured to enable higher I/O throughout instead of using the default
settings.
UCS server configuration
Most server default HBA throttle settings are not optimized for the high throughput
that a flash array provides. Therefore, it is important to choose the highest value for
this setting for the server so it will not limit the I/O throttle.
To adjust HBA I/O throttle of the Cisco UCS HBA, use these steps:
1.
In UCSM navigation, under Server, select Inventory.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
29
2.
Select Cisco VIC Adapters.
3.
Navigate to vHBA Properties.
4.
Set I/O Throttle Count to “1024”, as shown in Figure 9.
Figure 9.
Change I/O Throttle Count for Cisco UCS Server
ESXI server configuration
To optimally configure the ESX host for XtremIO storage (for vSphere 5.5), use these
steps:
1.
On vSphere, adjust the HBA queue depth through the ESX command-line
interface (CLI). The queue depth setting controls the amount of outstanding
I/O requests per a single path.
For optimal operation with XtremIO storage, follow the HBA vendor and server
vendor recommendations. As a general rule, you should set the queue depth
to the highest allowed by the HBA manufacturer (for example, 256).
Note: For more information about adjusting HBA queue depth with ESX, refer to
VMware KB article 1267 on the VMware website.
2.
Set the SchedQuantum ( to 64) and DiskMaxIOSize (to 4096) parameters:
esxcfg-advcfg -s 64 /Disk/SchedQuantum
esxcfg-advcfg -s 4096 /Disk/DiskMaxIOSize
3.
Obtain the NAA for the XtremIO LUNs presented to the ESX host and locate the
NAA of the XtremIO volume:
esxcli storage nmp path list | grep XtremIO -B1
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
30
4.
Run the following command to set SchedNumReqOutstanding for the device
to its maximum value (256):
esxcli storage core device set -d naa.xxx -O 256
vSphere Native Multipathing configuration
XtremIO supports the VMware vSphere Native Multipathing (NMP) technology. For
best performance, EMC recommends that you configure the native vSphere
multipathing for XtremIO volumes using these steps:
1.
Set the native round-robin path selection policy on XtremIO volumes
presented to the ESXi host.
2.
Set the vSphere NMP round-robin path switching frequency to XtremIO
volumes from the default value (1,000 I/O packets) to 1.
These settings ensure optimal distribution and availability of load between I/O paths
to XtremIO storage.
Note: Use the ESX command line to adjust the path switching frequency of vSphere NMP
round robin.
To set the vSphere NMP round-robin configuration, use one of these options:

Per volume, using vSphere Client, for each host where the volume is presented

Per volume, using ESX command line, for each host where the volume is
presented

Per host for all XtremIO volumes, presented to the host using ESX command
line
If EMC PowerPath®/VE is used for ESXi, PowerPath/VE treats XtremIO devices as
generic. Enabling generic loadable array module (LAM) support enables
PowerPath/VE to recognize and manage XtremIO devices. You can also use EMC VSI
for XtremIO for the NMP round-robin configuration.
Round-robin path management enablement in vCenter GUI
On each virtual machine, the LUNs for database storage were added from the XtremIO
array as RDM and spread across four PVSCSI controllers to balance I/O. The LUNs for
OS and SQL Server software installations are configured as VMDK so that the low I/O
storage LUNs can share the same volume on XtremIO.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
31
The I/O intensive database LUNs need to be configured as Round Robin (VMware) in
path management, as shown in Figure 10, if they are not managed by PowerPath.
Figure 10.
Storage device path management configuration
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
32
Performance testing and validation
Overview
The purpose of this testing is not to showcase raw performance ceiling numbers of
any of the compute, switch, or storage elements of this solution. The purpose is to
showcase, through scaling, how enterprise workloads can continue to be easily
serviced by XtremIO while all elements, including storage, stay within the “green
zone”, that is, an area of utilization and latencies that is healthy and sustainable for
production workloads.
The OLTP workloads were generated using a Microsoft Partner toolkit that creates a
TPC-E-like workload. This toolkit, based on the BenchCraft TPC-E Toolkit, was used to
simulate realistic OLTP workloads in this solution.
The system I/O performance metrics (IOPS, transactions per second (TPS), and
latency) were collected at the server/database and storage levels.
All tests were run on a properly configured XtremIO system.
Notes on results
Test results are highly dependent upon workload, specific application requirements,
and system design and implementation. Relative system performance will vary as a
result of these and other factors. Therefore, this workload should not be used as a
substitute for a specific customer application benchmark when critical capacity
planning and/or 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 does not warrant or represent that a user can or will achieve similar performance
expressed in transactions per minute.
Note: The database metric TPS is described and used within our test results. As transactions
differ greatly between database environments, these figures should only be used as a
reference and for comparative purposes within these test results.
Test objectives
The overall test objectives were to demonstrate:

The high performance achieved when virtualized Microsoft SQL Server
databases were run on XtremIO.

How XtremIO significantly simplified storage operations for Microsoft SQL
Server.

Sustained storage array IOPS for OLTP workloads on Microsoft SQL Server OLTP
database.

Significant storage space savings with inline data reduction on XtremIO when
snapshots of production volumes are used for repurposing (test/dev, backup,
BI, and so on) within environments.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
33
Test scenarios
The following scenarios were tested and are described in more detail in subsequent
sections:

OLTP workload performance test

System with XtremIO snapshot performance test
We also completed an XtremIO data reduction analysis in this solution’s SQL Server
environment.
OLTP workload
performance test
This test was used to measure the performance of the whole environment with both
SQL Server 2012 and SQL Server 2014 database workloads.
This test also showed how an XtremIO system can deal with growing database
workloads and continue to show stable performance.
Test methodology
Microsoft BenchCraft was used to generate the OLTP workload to drive high physical
random I/O from a database platform.
We ran the fixed number of concurrent users for each database with the same set of
OLTP queries simultaneously against all SQL Server database in the environment,
then measured the performance statistics. During the test, the number of concurrent
users was controlled so that we could generate a specific level of IOPS.
Test procedure
The test began with a single database workload. We ran it for a period of time to
stabilize the workload, and then we added another database workload while the
previous workload was still running. Each database workload continued to drive
additional IOPS on the XtremIO storage system without the host experiencing
increased I/O latency.
We started testing with the full loads shown in Table 10, which were run
consecutively to test the differences between SQL Server 2012 and SQL Server 2014
(workload sequence 1st to 6th steps) and additional loads on SQL Server 2012 to
achieve a full system workload.
Table 10.
Test workload sequence for full system load
Workload
sequence
Database
name
Database
size
SQL Server
Workload (No. of
users/maximum
transaction rate)
1st
DB_01
750 GB
SQL Server 2014
10/200
2nd
DB_01
750 GB
SQL Server 2012
10/200
3rd
DB_02
500 GB
SQL Server 2014
15/200
4th
DB_02
500 GB
SQL Server 2012
15/200
5th
DB_03
1 TB
SQL Server 2014
20/200
6th
DB_03
1 TB
SQL Server 2012
20/200
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
34
Workload
sequence
Database
name
Database
size
SQL Server
Workload (No. of
users/maximum
transaction rate)
7th
DB_04
2 TB
SQL Server 2012
5/200
8th
DB_05
250 TB
SQL Server 2012
5/200
9th
DB_06
1 TB
SQL Server 2012
5/200
Test results
As shown in Figure 11, the XtremIO array is highly scalable with multiple concurrent
SQL Server enterprise-scale database workloads.
Overall, the average latency remained low for the XtremIO array, while the added SQL
Server database workloads generated more I/O into the system. The entire system
generated over 4,200 TPS with a total of 200,000 IOPS when all nine database
workloads were fully loaded, while array latency remained under 1 ms for the XtremIO
system. The host average disk latency ranged from under 1 ms to under 2.5 ms.
5000
XtremIO total IOPS
200,000
4000
150,000
3000
100,000
2000
50,000
1000
0
0
1000
2000
3000
4000
XtremIO average latency (µsec)
250,000
0
5000
Total SQL Server TPS
IOPS
Figure 11.
Avg-Latency (µsec)
SQL Server/XtremIO scalability test
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
35
XtremIO system performance
When we pushed the full system load, XtremIO provided extremely high IOPS and
throughput with very low latency and a high overall SQL Server transaction rate as
shown in Figure 12 and Figure 13.
Figure 12.
Latency and IOPS observed in XtremIO X-Bricks during full load run of SQL
Server 2012 and SQL Server 2014
Figure 13.
Bandwidth observed on XtremIO X-Bricks during full load run of SQL Server
2012 and SQL Server 2014
SQL Server 2012 versus SQL Server 2014 performance
As shown in Figure 14, with the same server processor, memory, and setup, SQL
Server 2014 delivered more TPS than SQL Server 2012. Microsoft has improved
memory usage for transactions in SQL Server 2014.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
36
70%
60%
1926
2000
Total TPS
1711
1500
50%
40%
1156
1070
30%
1000
20%
500
418
390
10%
0
SQL Server processor utilization
2500
0%
Workload on one DB
Figure 14.
Workload on two DBs
Workload on three DBs
Total TPS SQL Server 2014
Total TPS SQL Server 2012
Processor SQL Server 2014
Processor SQL Server 2012
SQL Server database transactional performance: 2012 versus 2014
The SQL Server 2012 and SQL Server 2014 servers were set up with the same
hardware (servers, RAM, and number of processors).
SQL Server transactional performance
In a low transaction level, SQL Server 2014 has a similar performance to SQL Server
2012 with around 400 TPS (7 percent higher). With a more demanding workload, the
high performance back-end XtremIO enables SQL Server 2014 to handle a higher
transaction rate. As shown in Figure 14, there were up to 12.5 percent more
transactions processed by SQL Server 2014 when the transactions were increased.
The higher cumulative CPU utilization for SQL server 2014 (60 percent versus 55
percent for SQL Server 2012 ) was a result of the 20 percent more transactions that
SQL Server 2014 was executing. That is a 9 percent CPU usage increase for a 12.5
percent higher transaction rate.
Overall, SQL Server 2014 provides efficient performance and can handle a hightransaction system much better with XtremIO to eliminate any possible storage
latency, as shown in our tests.
SQL Server disk I/O
The disk I/O performance was very similar for SQL Server 2012 and SQL Server 2014.
As shown in Figure 15, SQL Server 2014 has slightly better overall performance for
the datafile LUNs (slightly better IOPS and lower latency).
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
37
30,651
35,000
30,000
IOPS
25,000
2.50
34,496
2.00
23,277
23,044
1.50
21,223
19,651
20,000
1.00
15,000
10,000
0.50
5,000
125 65
125 87
130 82
65
DB_01 Log
DB_02 Log
DB_03 log
tempdb
9
0
0.00
DB_01
DB_02
DB_03
SQL 2014 IOPS
SQL 2012 IOPS
SQL Server 2014 avg. latency(ms)
SQL Server 2012 avg. latency(ms)
Figure 15.
SQL Server LUN average latency ( ms)
40,000
SQL Server disk I/O performance: 2012 versus 2014
Log file LUN disk IO latencies are similar in both version of SQL Server, with much
lower IOPS in SQL Server 2012 (due to less transactions). Tempdb, has significantly
less IOPS in SQL Server 2012 (indicates that SQL Server 2014 may have improved the
transaction performance by optimizing the execution plan and therefore more usage
of tempdb). The tempdb latency, however is also much lower in SQL 2014, making
SQL 2014 a much better choice for performance sensitive IO intensive SQL Server
databases.
System with
XtremIO snapshot
performance test
In this scenario, we used XtremIO snapshots to create multiple copies of the
production database that could be used for test/dev environment provisioning.
XtremIO snapshot creation was instant; the snapshot created was available
immediately. Taking XtremIO snapshots did not have a visible performance effect
during or after the creation of these snapshots; the database was always online with
the same performance characteristics observed before snapshot creation. The
physical space utilization was minimal, so more snapshots could be created without
impact on the production database than in a traditional array.
There was no performance difference between accessing the primary volumes versus
accessing the snapshot volumes.
The snapshots could also be used as writeable copies if needed without any impact
on the production database. An XtremIO writeable snapshot can be viewed as a
traditional array’s clone without the same space requirements. XtremIO snapshots
produce a much smaller footprint on the physical storage.
Test methodology
This test used BenchCraft creating the same workload as in performance testing while
measuring the performance of the environment.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
38
After a set of snapshots was created, we continued to run the workload for eight
hours to simulate a typical workday workload. After that, another snapshot set was
created to measure the impact that one day’s workload introduced into the
environment.
The deduplication ratio and thin provisioning savings of XtremIO storage were
monitored for their impact on the snapshots created in this test.
These crash-consistent snapshots were then mounted on separate mount hosts, and
recovered to simulate a test/development workload. Read-only and read/write
workloads were introduced to the snapshots.
Test procedure
We used the following steps in this test:
1.
Workload created for the whole environment of the production database,
captured the current deduplication ratios, used physical capacity, and thin
provisioning space savings as a baseline.
2.
Created the first snapshot of the SQL Server 2012 database LUNs.
3.
Created first snapshot of SQL Server 2014 database LUNs.
4.
Created five snapshots.
5.
Continued to run the workload on the production database for eight hours to
simulate the change to this environment during a workday. Continued to
monitor the performance of the environment.
6.
Created another snapshot of the production database SQL Server 2012 and
then SQL Server 2014, captured the deduplication ratios, used physical
capacity, and saved thin provisioning space on the XtremIO array
management console.
7.
Created snapshot of both SQL Server 2012 and 2014 in the same operation.
8.
Mounted one of the snapshots created earlier and recovered it on a separate
SQL server (on both SQL Server 2012 and 2014 databases).
9.
Added an additional workload on another mounted snapshot database, and
then checked the system performance.
10. Added an additional read/write workload on the mounted snapshot
database, and then checked the system performance.
Test results
Snapshot creation observation
During snapshot creation, all snapshots were completed instantly, without any delay.
All of the snapshots were available for read/writeable use immediately after creation.
Consistent XtremIO performance over a prolonged period of time
As shown in Figure 16, XtremIO provides SQL Server with very consistent performance
during a very long period of extremely heavy workload. The latency is kept flat at
about 1 ms more than 10 hours of extremely heavy workload with five snapshots
created for all the SQL Server production databases.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
39
3
2
1.5
1
0.5
0
18:23:36
18:40:51
18:58:06
19:15:21
19:32:36
19:49:51
20:07:06
20:24:21
20:41:36
20:58:51
21:16:07
21:33:22
21:50:37
22:07:52
22:25:07
22:42:22
22:59:37
23:16:41
23:34:07
23:51:22
00:08:37
00:25:52
00:43:07
01:00:22
01:17:37
01:34:52
01:52:07
02:09:22
02:26:37
02:43:52
03:01:07
03:18:22
03:35:37
03:52:52
04:10:07
04:27:23
04:44:38
05:01:53
Average latency (ms)
2.5
Figure 16.
Consistent XtremIO performance over prolonged heavy workload
An XtremIO array with 15 TB of usable physical flash capacity served 181 TB of
volume space to the hosts. This has a huge cost benefit compared to traditional
storage or other flash arrays that lack the XtremIO efficiency technology.
All databases have a flat consistent performance with close to 1 ms latency on the
server side and kept the same high transaction rate throughout the entire testing
period. The spikes between 1 ms and 1.5 ms are database checkpoint activities.
Other than that, the entire test run kept a very flat and consistent latency.
Performance impact of XtremIO snapshots
We captured the metrics shown in Figure 17 to show the performance impact of the
XtremIO snapshot.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
40
Additional IOPS from
workload on multiple
mounted snapshots
1.4
}
Total IOPS
200,000
150,000
1.2
1
0.8
0.6
100,000
Latency (ms)
250,000
0.4
50,000
0.2
0
0
total IOPS
Figure 17.
latency(ms)
XtremIO snapshot performance impact
Our test shows:

In a fully loaded database environment, creating an XtremIO snapshot does not
impact performance.

The snapshot is created instantly and immediately available for use.

The number of snapshots does not affect the performance of the production
database.

As shown later in our results, the storage use of these snapshots is also low.
You can create as many snapshots as you want without any impact on the
performance of the production database.
In this test, we mounted both SQL Server 2014 and SQL Server 2012 databases on
two different mount-host virtual machines and ran a full workload on three of the
databases. Figure 17 shows that after we introduced about a 50,000 IOPS workload
on the snapshot, XtremIO sustained about 212,000 IOPS, and still maintained a very
low latency.
The workload on snapshots can achieve the same level as that on production with the
same performance as long as the total IOPS capacity is within the limits of XtremIO.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
41
XtremIO snapshot storage efficiency
The use of physical storage for XtremIO snapshots is minimal. During our testing, as
shown in Figure 18, the physical storage use was unchanged after we created
snapshots for a 7.5 TB database system. A total of 40 TB of capacity based on
snapshots was available for read/write with zero cost.
Storage size (TB)
250.00
100%
200.00
80%
150.00
100.00
60%
50.00
0.00
total efficiency
40%
4.5:1
6.3:1
8.2:1
10:1
11:1
13:1
14:1
16:1
baseline
snap 1
snap 2
snap 3
snap4
snap5
snap6
snap7
physical storage(TB)
Figure 18.
volume used( TB)
total volume(TB)
thin saving(%)
Storage efficiency for XtremIO snapshot operation
During this test, before the last two snapshots were created, we also set up a full
workload running for eight hours to introduce about 10 percent change in the
database. Because of the additional production data, there was an increase of
physical storage use of about 500 GB (that is for about 750 GB of change with five
snapshots on top of it). For a traditional array, this might require much more physical
storage use. In this case, less than the actual change was materialized on the
XtremIO physical storage, largely due to its inline data reduction capability.
Figure 18 shows that as more snapshots of the production database were created,
the deduplication ratios stayed the same, while the increase of the array’s used
physical capacity was low and the volume capacity increased dramatically. The
overall storage efficiency increased and the thin provisioning savings also increased.
XtremIO has a highly efficient snapshot mechanism. Each snapshot of the database
occupied only minimal physical storage, even after the eight-hour full workload had
added 10 percent more changes into the production database. The affected LUNs
needed less than the actual change of physical storage. In a traditional array, that
would require at least doubling the amount of the physical storage.
The total volume expectancy increased to over 200 TB from the 14.9 TB physical
storage because of the because of the space efficiency gained from snapshots. The
storage efficiency increased from 4.5:1 to 16:1 after the seventh snapshot.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
42
The deduplication rate did not change after the snapshots because XtremIO
snapshots are 100 percent space efficient for both user data and metadata. This
means snapshots do not create any data that must be deduplicated. Only after the
eight-hour full workload run against the production databases that introduced the
change, did the deduplication rate dropped slightly from 1.9:1 to 1.8:1 due to the
added unique data in the system.
In this solution test, we observed the data reduction from XtremIO in relation to the
SQL Server OLTP database.
Cost-effective data reduction
With XtremIO, you can sustain a logical capacity that exceeds by a large margin the
physical flash capacity in the system, as shown in Figure 19.
The effective amount of volumes created on XtremIO in this test was about 181 TB
with five database snapshots. The SQL Server database data deduplication rate was
maintained at close to 2:1. The cumulative effect of data reduction was that we fit
that amount of volume into about 13.5 TB of physical storage. The effective volume
and physical storage ratio was about 13:1. The more snapshots you create, the
higher this ratio will be. With seven snapshots, the ratio is 16:1.
180
160
140
120
TB
XtremIO data
reduction analysis
100
80
60
40
20
0
No snapshot
Figure 19.
With 5 snapshots
Physical storage used
Total physical storage available
Actual volume used
Total volume allocated
XtremIO data reduction with deduplication and thin provisioning
The total volume is what the physical storage space requires in a traditional array if
all snapshots were made read/writeable. The physical storage in XtremIO as shown in
Figure 19 is less than one-tenth of that.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
43
Deduplication rate
With data reduction, the XtremIO effective system capacities can expand beyond the
physical capacity. In environments that contain highly duplicate information, the
effective logical capacity of XtremIO can be much more than its nominal flash
capacity.
SQL Server has a deduplication ratio close to 2:1. This ratio depends greatly on the
SQL Server database data, as shown in Figure 20. For an actual requirement of about
25 TB storage for the SQL Server database, administration, and operating system,
less than 14 TB physical storage is needed in flash on the XtremIO array. However, we
can allocate about 60 TB when we create LUNs.
Figure 20.
SQL Server deduplication ratio
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
44
Conclusion
Summary
This solution demonstrates the tremendous value of EMC XtremIO shared storage for
database management systems such as Microsoft SQL Server. XtremIO offers a
scalable, extremely efficient storage solution for a consolidated SQL Server
environment that can be used for various workloads, especially for OLTP.
Storage can keep pace with linear scaling on the host side. XtremIO N-way
active/active scale-out architecture linearly scales capacity, creates extremely high
IOPS, and maintains extremely low latency. When you add additional compute
resources, including CPUs, memory, HBA ports, and front-end ports from server side,
the system can provide higher IOPS and throughput for OLTP environments.
The solution can achieve efficient resource utilization through virtualization while
providing high database performance. The capacity and processing capability can
easily be increased. As business needs change, this solution stack can align with the
shifting demands from any level, such as applications, database software, and nondatabase software. In turn, multiple new workload approaches, such as realtime
analytics, are made possible, with the consolidation of production and reporting
instances.
Best of all, snapshots are created instantly and can be used for any purpose. Adding
or removing a snapshot costs next to nothing. Even running read/writeable workloads
on a snapshot does not have much impact on the production database performance.
Findings
This solution provides:

Fast and simple setup with little to no storage tuning. XtremIO works as
seamlessly in virtualized SQL Server environments as in physical ones, and is
easy to manage and monitor.

Support for the most demanding transactional SQL Server 2012 and SQL Server
2014 workloads, with throughput that can easily exceed 100,000 IOPS per XBrick with an almost flat 1 ms latency.

Substantial storage footprint savings by using XtremIO inline data reduction
and snapshots, which we observed in this configuration with a 16:1 overall
efficiency.

Close to real-time, high-performance copies of data using XtremIO snapshot
technology at no measurable cost, while providing near-instant recovery of
production data, even in TBs of data scale.
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
45
References
EMC
documentation
These documents are available from the EMC.com or EMC Online Support websites.
Access to online support depends on your login credentials. If you do not have access
to a document, contact your EMC representative.
White papers
For additional information, see the white paper listed below.

Introduction to the EMC XtremIO All-Flash Array
Product documentation
For additional information, see the product documents listed below.

EMC XtremIO System Specifications

EMC VSI Path Management Product Guide

EMC XtremIO Storage Array User Guide
EMC XtremIO
For more information, see the XtremIO website.
VMware
documentation
For additional information, see the documents listed below and available on the
VMware website.
Microsoft SQL
Server
documentation

Microsoft SQL Server Databases on VMware Best Practices Guide

VMware vSphere Networking

VMware ESX Scalable Storage Performance
For additional information, see the documents listed below and available on the
Microsoft website.

Pre-Configuration Database Optimizations

Microsoft SQL Server Best Practices
EMC Extreme Performance and Efficiency for Microsoft SQL Server
EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014
46