Explore the performance of SQL SERVER 2014 databases using PowerEdge R630 with HDD and SSD drives
The choice of server for applications is influenced by several main factors:
Those companies that need a spatially efficient platform that delivers excellent performance and mirroring fault tolerance should consider the new Dell PowerEdge R630 server, a 1U high-density server designed for processing complex and demanding enterprise applications, as a solution.
We tested the performance of the Dell PowerEdge R630 server based on Intel Xeon E5-2660 v3 processors in Principled Technologies labs. Two server configurations were tested:
Each configuration consisted of a pair of servers in a cluster with Windows Server 2012 R2 and SQL Server 2014 installed. Upgrading to SSDs in the same form factor increased the number of operations per minute (OPM) by 10.5 times than the configuration on the HDD, and also reduced the response time by 59%. The update also reduced the cost of each transaction by 71%.
Saving space, performance boost and reliability
The PowerEdge R630 is a compact, single-unit server developed by Dell to support complex and demanding enterprise applications. With multiple servers in a cluster, using databases, as in our case, SQL Server 2014, the Dell solution is able to provide database resiliency and high availability for the data center. We decided to test the database performance with these parameters and with different storage subsystem configurations.
')
To configure high availability servers, we created an elastic cluster of two nodes running Windows Server 2012 R2 and SQL Server 2014 using AlwaysOn technologies from Microsoft SQL Server. AlwaysOn technology provides high availability and ease of disaster recovery and is available in SQL Server 2014.
Performance testing was carried out with the Intel Xeon E5-2660 v3 processors installed, on which real-time transaction processing ( OLTP ) was started using the DVD Store 2.1 test. Then we repeated the same test, but already with the servers into which the 1.8 "SATA SSD was installed.
Testbed configuration:
Hard Disk Configuration
We tested the server's HDD configuration at medium and heavy OLTP load levels. First, we used 100 ms as the response time to show a moderate database load. We configured one SQL Server to host four databases and received 20,736 operations per minute. We then increased the load intensity by changing the parameters to reflect applications with a higher level of usage.
SSD Configuration
As can be seen in the following graph, after replacing conventional HDD with SSD drives, system performance has increased dramatically. If you compare the scenario with a delay time of 20 ms, then the configuration with SSD drives increased the number of transactions by 7.7 times. We ran several combinations of SQL Server instance and database counting to see which combinations would show the best performance. Our tests started with 8 databases and two instances of SQL Server - this gave an advantage of 10.1 times compared to HDD-drives, and in a configuration with 24 bases and four instances of SQL Server received an increase of 10.5 times.
We found that with multiple instances of SQL Server, when using configurations with multiple databases, we were able to achieve a higher OPM. Here you can also see that the disk system on the SSD has a fairly large performance margin, which will withstand further increase in load.
Another factor that is of great importance when working with the database is the time delay between the user's request and the receipt of the requested data. This delay is called wait time or response time, measured in milliseconds, and this is another useful indicator for displaying the difference in performance between SSD and HDD solutions. We used the average value in milliseconds, for which the rt_tot_avg_msec parameter, obtained in the DVD Store 2.1 test, is responsible. It serves as an indicator of the average delay for a test client request. In our testing, we found that in the solution based on SSD response time is 60% lower than on the basis of HDD.
In addition, when we compared the costs and performance of the configurations relative to each other, we found that the R630 hard disk-based configuration spent OPM $ 0.45 and $ 0.13 on the SSD-based OPM, which is 71% better.
Conclusion
While the server showed high performance in a standard HDD configuration, its performance improved significantly when we tested the SSD configuration. Depending on the number of databases in our test, SSD drives showed an advantage from 7.7 to 10.5 times in the total number of PMN, than HDD drives. At the same time, the response time and cost of each transaction decreases. The Dell PowerEdge R630 Server can accommodate greater performance in a limited rack space. In addition, when the Dell PowerEdge R630 is deployed within a cluster, its resiliency, combined with Microsoft technologies, allows businesses to easily secure the network for their critical data in a small form factor.
Configuration cost comparison
About Dell PowerEdge R630
The Dell PowerEdge R630 server is equipped with two Intel E5v3 processors to ensure high computing performance. Dell PowerEdge R630 server is positioned as a tool for cloud applications, virtual environments and high performance computing (HPC). The PowerEdge R630 has 24 DIMM slots with support for up to 1.5TB of memory, supports an additional GPU accelerator and can support up to two additional NVMe Express Flash PCIe SSDs to reduce bottlenecks. In addition to this, PowerEdge R630 allows the flexibility to choose a storage subsystem. In addition to the more traditional 2.5 "HDDs, Dell also offers a 1.8" SATA SSD. The size of SSD drives varies from 60 to 960GB. The availability of additional hot-swappable power supplies and two SD cards allows the Dell PowerEdge R630 to maintain high hardware availability. The PowerEdge R630 comes standard with the iDRAC7 with Lifecycle Controller and Dell OpenManage, which do all the management optimization.
About Microsoft SQL Server 2014
SQL Server 2014 includes the AlwaysOn features introduced in SQL Server 2012, along with a number of achievements over previous versions. A new in-memory OLTP processing engine dramatically improves transaction processing. According to Microsoft, “the in-memory OLTP technology in SQL Server 2014 provides the highest performance for mission-critical applications with minimal additional cost, requiring only hot tables to be stored in memory, rather than the entire database, like other products on the market.” When these hot tables are optimized in memory, they automatically transition to a new data structure that eliminates locks and commits, further increasing performance. SQL Server 2014 also determines which storage procedures can be recompiled to further improve throughput.
About DVD Store Version 2.1
To simulate a real OLTP load, we used the DVD Store Version 2.1 benchmark. This test simulates an online DVD store, where users log on, search for movies and make purchases. DVD Store creates reports on the number of orders per minute that the system can process to show what performance you can expect for your customers. DVD Store also performs other actions, such as adding new customers, performs a wide range of database functions, which are necessary if you have to manage an e-commerce environment.
- performance;
- response time;
- reliability;
- available rack space.
Those companies that need a spatially efficient platform that delivers excellent performance and mirroring fault tolerance should consider the new Dell PowerEdge R630 server, a 1U high-density server designed for processing complex and demanding enterprise applications, as a solution.
We tested the performance of the Dell PowerEdge R630 server based on Intel Xeon E5-2660 v3 processors in Principled Technologies labs. Two server configurations were tested:
- with 2.5 "SAS hard drives (HDD)
- with 1.8 "SATA solid state drives (SSD).
Each configuration consisted of a pair of servers in a cluster with Windows Server 2012 R2 and SQL Server 2014 installed. Upgrading to SSDs in the same form factor increased the number of operations per minute (OPM) by 10.5 times than the configuration on the HDD, and also reduced the response time by 59%. The update also reduced the cost of each transaction by 71%.
Saving space, performance boost and reliability
The PowerEdge R630 is a compact, single-unit server developed by Dell to support complex and demanding enterprise applications. With multiple servers in a cluster, using databases, as in our case, SQL Server 2014, the Dell solution is able to provide database resiliency and high availability for the data center. We decided to test the database performance with these parameters and with different storage subsystem configurations.
')
To configure high availability servers, we created an elastic cluster of two nodes running Windows Server 2012 R2 and SQL Server 2014 using AlwaysOn technologies from Microsoft SQL Server. AlwaysOn technology provides high availability and ease of disaster recovery and is available in SQL Server 2014.
Performance testing was carried out with the Intel Xeon E5-2660 v3 processors installed, on which real-time transaction processing ( OLTP ) was started using the DVD Store 2.1 test. Then we repeated the same test, but already with the servers into which the 1.8 "SATA SSD was installed.
Testbed configuration:
- Processor: two Intel Xeon E5-2660 v3
- Memory: 128Gb DDR4-2133 ECC
- RAID Controller: Dell PERC H730P Mini
- Operating system: Microsoft Windows Server 2012 R2 Datacenter
- SSD: LITEON IT EBE-400NAS 2 × 60, 22 × 400 (Firmware 110A / 11X7)
- HDD: Seagate ST300MM0006 8x300 (Firmware LS08)
Hard Disk Configuration
We tested the server's HDD configuration at medium and heavy OLTP load levels. First, we used 100 ms as the response time to show a moderate database load. We configured one SQL Server to host four databases and received 20,736 operations per minute. We then increased the load intensity by changing the parameters to reflect applications with a higher level of usage.
SSD Configuration
As can be seen in the following graph, after replacing conventional HDD with SSD drives, system performance has increased dramatically. If you compare the scenario with a delay time of 20 ms, then the configuration with SSD drives increased the number of transactions by 7.7 times. We ran several combinations of SQL Server instance and database counting to see which combinations would show the best performance. Our tests started with 8 databases and two instances of SQL Server - this gave an advantage of 10.1 times compared to HDD-drives, and in a configuration with 24 bases and four instances of SQL Server received an increase of 10.5 times.
We found that with multiple instances of SQL Server, when using configurations with multiple databases, we were able to achieve a higher OPM. Here you can also see that the disk system on the SSD has a fairly large performance margin, which will withstand further increase in load.
Another factor that is of great importance when working with the database is the time delay between the user's request and the receipt of the requested data. This delay is called wait time or response time, measured in milliseconds, and this is another useful indicator for displaying the difference in performance between SSD and HDD solutions. We used the average value in milliseconds, for which the rt_tot_avg_msec parameter, obtained in the DVD Store 2.1 test, is responsible. It serves as an indicator of the average delay for a test client request. In our testing, we found that in the solution based on SSD response time is 60% lower than on the basis of HDD.
In addition, when we compared the costs and performance of the configurations relative to each other, we found that the R630 hard disk-based configuration spent OPM $ 0.45 and $ 0.13 on the SSD-based OPM, which is 71% better.
Conclusion
While the server showed high performance in a standard HDD configuration, its performance improved significantly when we tested the SSD configuration. Depending on the number of databases in our test, SSD drives showed an advantage from 7.7 to 10.5 times in the total number of PMN, than HDD drives. At the same time, the response time and cost of each transaction decreases. The Dell PowerEdge R630 Server can accommodate greater performance in a limited rack space. In addition, when the Dell PowerEdge R630 is deployed within a cluster, its resiliency, combined with Microsoft technologies, allows businesses to easily secure the network for their critical data in a small form factor.
Configuration cost comparison
About Dell PowerEdge R630
The Dell PowerEdge R630 server is equipped with two Intel E5v3 processors to ensure high computing performance. Dell PowerEdge R630 server is positioned as a tool for cloud applications, virtual environments and high performance computing (HPC). The PowerEdge R630 has 24 DIMM slots with support for up to 1.5TB of memory, supports an additional GPU accelerator and can support up to two additional NVMe Express Flash PCIe SSDs to reduce bottlenecks. In addition to this, PowerEdge R630 allows the flexibility to choose a storage subsystem. In addition to the more traditional 2.5 "HDDs, Dell also offers a 1.8" SATA SSD. The size of SSD drives varies from 60 to 960GB. The availability of additional hot-swappable power supplies and two SD cards allows the Dell PowerEdge R630 to maintain high hardware availability. The PowerEdge R630 comes standard with the iDRAC7 with Lifecycle Controller and Dell OpenManage, which do all the management optimization.
About Microsoft SQL Server 2014
SQL Server 2014 includes the AlwaysOn features introduced in SQL Server 2012, along with a number of achievements over previous versions. A new in-memory OLTP processing engine dramatically improves transaction processing. According to Microsoft, “the in-memory OLTP technology in SQL Server 2014 provides the highest performance for mission-critical applications with minimal additional cost, requiring only hot tables to be stored in memory, rather than the entire database, like other products on the market.” When these hot tables are optimized in memory, they automatically transition to a new data structure that eliminates locks and commits, further increasing performance. SQL Server 2014 also determines which storage procedures can be recompiled to further improve throughput.
About DVD Store Version 2.1
To simulate a real OLTP load, we used the DVD Store Version 2.1 benchmark. This test simulates an online DVD store, where users log on, search for movies and make purchases. DVD Store creates reports on the number of orders per minute that the system can process to show what performance you can expect for your customers. DVD Store also performs other actions, such as adding new customers, performs a wide range of database functions, which are necessary if you have to manage an e-commerce environment.
Source: https://habr.com/ru/post/248485/
All Articles