Improving SQL Server Performance with Entry Level Storage System MSA 2042

A Demartek report shows that an HPE MSA 2042 system with solid-state drives and intelligent software can increase the efficiency of SQL Server at minimal cost.

The explosive growth of the flash-based array market continues. According to HPE reports, in the 3rd quarter of 2016, sales of the 3PAR array based on flash memory increased by 70% compared to last year ¹ . In the large enterprise sector, storage systems built entirely on SSDs are undergoing a massive implementation period, providing advantages in many workload scenarios, such as real-time transaction processing (OLTP), data warehousing, and VDI.

To make flash-based storage more affordable for small and medium businesses, HPE introduced the HPE MSA 2042 storage system, equipped with solid-state drives and intelligent software that improves application performance and improves business results with minimal cost.
')
Now let's see how using this entry-level storage system can improve the performance of SQL Server DBMS.

Why SQL Server 2016

For many generations, Microsoft SQL Server has been at the center of the enterprise IT environment, used for a wide variety of purposes, and has covered virtually all industry verticals. In October 2015, Gartner ranked SQL Server as the industry leader among database systems for transactional loads ² , and the Public Security Board of the National Institute of Standards and Technology named SQL Server the most secure database in the industry ³ .

Moreover, SQL Server 2016 offers the industry's best total cost of ownership ⁴ , which makes it a natural choice for entry-level HPE storage testing.

Demartek SQL Server Testing Report: A Tale of Two Useful MSA 2042 Technologies

Demartek recently evaluated the performance of SQL Server 2016 using an MSA 2042 storage system with two embedded solid-state drives (400 GB each) with a total capacity of 800 GB. Two software configurations are available that improve performance and do not require additional costs:

Read Cache on Solid State Drives — Two built-in solid-state drives were used as a read cache to improve the performance of random read operations.
Automatic distribution by performance levels (this feature is included in the Advanced Data Services software package) - MSA 2042 supports the allocation of auxiliary LUN levels (sub-LUN tiering) and automatic data transfer between these levels. Three separate levels are supported: high performance level — based on solid-state drives, standard level — based on enterprise-class SAS hard drives, and “archived” level — based on average SAS hard drives.

The MSA 2042 automated multi-level storage subsystem moves data between the available levels depending on the characteristics of the data obtained by evaluating them. Often used "pages" are transferred to the highest available level, ensuring maximum application performance.

This multi-tier storage system takes advantage of solid-state drives to speed up write and read operations.

Real-time transaction processing (OLTP) conditions and test results in SQL Server based on HPE MSA 2042

The main goal of Demartek testing was to measure the transactional aspects of storage performance for a real OLTP workload based on SQL Server 2016 by simulating a brokerage firm with customers who generate transactions related to trading, invoice requests and market research. In the context of business results, measured key workload indicators include:

the number of database transactions per second
decrease response time.

Traditional storage performance metrics were also examined: throughput and I / O operations per second (IOPS). Below in two tables are the results of independent testing of Demartek:

Using solid-state drives as a cache for reading

Number of database transactions per second: +35%
Reduced response time: 4.5 times

Use solid-state drives for automatic distribution by performance level

Number of database transactions per second: + 60%
Decrease response time: 11 times

Note. Demartek conducted a benchmark test for 24 hours to ensure that I / O operations were stable and there was enough time to completely warm up the cache for reading based on solid-state drives and to transfer hot data to the appropriate storage level.

Key points

The results of using configurations with solid-state drives as cache memory for reading and with distribution by performance levels showed that the number of database transactions performed per second increased, and the response time during I / O operations decreased by 4-10 times. This increase in productivity has led to improved user interaction with SQL databases.

Key Aspects of the Demartek Research Solution

HPE MSA 2042 complements the HPE entry-level flagship array with built-in solid-state drives with a total capacity of 800 GB and a software package that implements enterprise-class features previously available in more expensive systems. High performance, automated storage tiering and data protection have become standard array features, and the reduced price makes MSA 2042 the ideal choice for small and medium-sized companies looking to improve SQL Server performance.
HPE SN1100E Gen 5 16Gb , Emulex Fiber Channel adapter provides up to 1.2 million I / O operations per second (IOPS) through a single port, and the stated mean time between failures (MTBF) of 10 million hours guarantees reliability and trouble-free operation. The SN1100E also supports the virtual Fiber Channel (VFC) adapter in Microsoft Windows Server for virtualized SQL servers. (NOTE: The Demartek report used an older version of the HPE SN1000E 16Gb Fiber Channel adapter, which has now been replaced with the HPE SN1100E version) .
The HPE SN6000B Fiber Channel Switch (Brocade 6510) , designed to increase the flexibility of your storage infrastructure and protect your investment, allows you to increase the number of ports from 24 to 48 and supports data rates of 4, 8, 10, or 16 Gbps. All this is in one case with a height of 1U. In addition, it provides low-cost access to SAN technology and the ability to scale storage area network.

Why is this good news for small and medium businesses?

As large enterprises continue to implement storage on solid-state drives, the HPE MSA 2042 system is an attractive and affordable solution for small and medium-sized companies, offering built-in performance enhancements for business applications and enterprise-class data management services.

November 30, we hold a webinar dedicated to the next generation of storage systems Store Virtual 3200 and MSA 2042. To have the opportunity to ask questions to an expert, register for our free webinar .