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.

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.

0 0.5 1 1.5 2 2.5 3

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)

EMC Extreme Performance and Efficiency for Microsoft SQL Server 41 EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014 Figure 17. 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.

0

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.

Figure 18. 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.

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

Storage size (TB)

physical storage(TB) volume used( TB) total volume(TB) thin saving(%) total efficiency

EMC Extreme Performance and Efficiency for Microsoft SQL Server 43 EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014 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.

Figure 19. 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.

0

No snapshot With 5 snapshots

TB

Physical storage used Total physical storage available Actual volume used Total volume allocated

XtremIO data reduction analysis

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 45 EMC XtremIO, VMware vSphere, SQL Server 2012, SQL Server 2014

Conclusion

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 non-database 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.

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 X-Brick 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.

Summary

Findings

References

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

For more information, see the XtremIO website.

For additional information, see the documents listed below and available on the VMware website.

 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

documentation

EMC XtremIO

VMware documentation

Microsoft SQL Server

documentation