Home > Workload Solutions > SQL Server > White Papers > Dell PowerMax 2500 and 8500 Best Practices for Mission Critical SQL Server Databases > Lab configuration used for the performance tests
The following table shows the server and storage configuration used for the performance tests. Each server has 32 physical cores and 512 GB RAM. The SQL Server buffer cache was set to 10 GB in each instance to limit database caching and allow for more I/Os to exercise the storage subsystem.
Host name | Operating system | Hardware | Purpose |
DSIB1001 | Red Hat Enterprise Linux 8.5
| PowerEdge R740xd, 2 x Intel Gold 6242 (32 cores @ 2.80 GHz), 512 GB RAM
| HammerDB Clients |
DSIB1004 | SQL Server Instance 1 | ||
DSIB1007 | SQL Server Instance 2 | ||
DSIB1010 | SQL Server Instance 3 | ||
PowerMax 8500 | PowerMaxOS 10 | 2-node (single engine) PowerMax 8500, 768 GB raw DRAM (1,024 GB NVDIMM for metadata) |
|
The following table shows the software releases used for the performance tests. All the servers are configured with Red Hat Enterprise Linux release 8.5. The command line uses HammerDB 4.4 software to automate the test using shell scripts.
Host name | Version |
SQL Server hosts operating system | Red Hat Enterprise Linux 8.5 |
HammerDB host operating system | Red Hat Enterprise Linux 8.5 |
HammerDB software | HammerDB 4.4 for Linux |
PowerMax 8500 | PowerMaxOS 10 |
SQL Server software | SQL Server 2019 (mssql-server-15.0.4223) |
PowerPath for Linux | Release 7.4 |
To accommodate the best practices for SQL Server described previously, each SQL Server instance uses a striped LVM formatted with the XFS type file system. The only XFS file system mount option used was noatime. Kernel parameters are set based on Microsoft SQL Server tuned-adm best practices.
Each instance uses a striped LVM of 16 x 200 GB PowerMax volumes for the datafiles, and a striped LVM of 8 x 30 GB PowerMax volumes for the transaction log, each in their own storage group, aggregated with a parent storage group. A single volume of 10 GB is created for System DBs and tempdb (tempdb was not busy during HammerDB OLTP tests. In production environments, tempdb can become very active and can be placed on a striped LVM).
Each server uses 2 x 2-port 32 Gb FC HBAs for a total of four initiators. Each initiator is zoned to two PowerMax front-end ports (targets), creating eight paths per volume (four initiators x two targets).
To make storage management easy, a UDEV rules file is created to set aliases to the storage volumes based on their WWN, and to set the SQL Server storage permissions.
Note: To find the PowerMax volumes WWN, either use Unisphere or the Solutions Enabler command line, for example, symdev list -wwn -sg sqlLinuxInst1_userdb1_data. This command lists the WWNs for the volumes in the sqlLinuxInst1_userdb1_data SG.
SQL Server RPM is installed on each of the SQL Server hosts, and the sa user password is updated. Then, the SQL Server system and user databases default location is updated to the appropriate XFS mount points. A user database is created in each instance to accommodate the HammerDB data.
HammerDB is used with a 2,000 warehouses OLTP database created in each of the SQL Server instances (2,000 WH x ~100 MB per WH = ~200 GB dataset per database).