Performance Testing of MSSQL using HammerDB

In this bonus lab you will install HammerDB tool and use it to benchmark MSSQL database performance on a given VM

Lab Agenda

1. Test database performance on MS SQL database configured following Best Practices

2. Test database performance on MS SQL database configured without following Best Practices - here all the database files are located in a single OS drive

   Note

   This bonus lab takes between 60 and 90 minutes depending on your familiarity with MS SQL databases.

Install HammerDB

1. Select your Initials-MSSQL-Manual VM and click Actions > Power On.

2. Log in to the VM using Remote Desktop Client/Console using the Administrator password you configured in Manual VM Deployment in Deploying MS SQL lab.

3. Download the HammerDB setup binaries on your VM from here. (Copy link address)

4. Go to the downloaded location: righ-click on the file and click on Advanced.

5. Install HammerDB using the instuctions here and make sure to install HammerDB in C: drive (default).

   Note

   If the installation URL doesn’t work. Install the exe file as you would install any normal windows package. It is as simple as clicking Next.

6. Once installed, Close HammerDB window (if you chose to Launch HammerDB).

   

Database Test 1 (with best practices on SQL)

In this section you will create a sample database (tpcc) following MS SQL Best Practices for database files on different drives. Once created, you will populate the database with data using HammerDB tool and run IO tests on it.

This will give you an opportunity to learn to use HammerDB tool and prepare you for the real world DB performance testing situations.

1. Open SQL Server Management Studio from you VM’s windows menu.

2. Enter user name: administrator and Nutanix/4u password and click on connect.

3. Go to Windows Explorer and create the the following folders:

   E:\data
   F:\data
   G:\data
   I:\logs
   

4. Right click on databases and select New database.

   

5. Give the name of the database as tpcc.

6. In the Database files table, scroll and select the path for tpcc and tpcc_log folder.

7. For tpcc, set path to E:\data.

8. For tpcc_log, set path to I:\logs.

9. Click on Add button twice to create two more database files.

10. Name them as tpcc_f and tpcc_g.

11. Set the path for two files as F:\data and G:\data.

    

12. To make sure the tables are created properly, right click on tpcc DB and select properties. Click on Files and make sure the path of the folders are set right.

13. Go to C:\Program Files\HammerDB-*.*\Hammerdb (windows batch file)

14. Double click on SQL

    

15. Select SQL-Server and TPC-C options and click on OK

    

16. Expand Schema Build and Double click on Options.

17. Change Sql Server Database name to tpcc.

18. Change number of warehouses to 150.

19. Change virtual users to build schema to 16.

20. Click on OK

    

21. Double click on Build option. click on ok, data will start building.

    

22. Click on Start Transaction Counter and observer transactions.

    
    

23. Do not close HammerDB, just minimize the window.

    Note

    If you close HammerDB, populating of data will stop

24. Go to the drives E\data, F:\data, F:\data, I:\logs and check if the size of the folders is increasing.

25. Wait until the data gets generated. This generates up to 15GB of data.

    Note

    It may take from 15 - 20 minutes for data population

26. Once the data is generated, open hammer db that is already minimized.

27. Click on Destroy Virtual Users.

    

28. Double click on Driver Script > Options. Make sure SQL Server Database name is tpcc (the database you created in the previous few steps).

29. Select “TPC-C driver script” as Timed Driver Script.

30. Leave rest of them as-is and select OK.

    Note

    Optional: You can also try using the option Keying and thinking time for making the IOPS more intensive.

    

31. Double click on Load

32. Go to Virtual users and click on Options.

33. Make sure Virtual users in the popped-up window is 17 and click OK

34. Double click on Create and then double click on Run operations.

    

35. While IO is getting generated, click on Transactions Counter and note the TPM. (Start the TPM counter if not already started)

    

36. Take screenshots and send TPM results to prospective customers or use it for your own reference.

Database Test 2 (without best practices on SQL)

Let’s simulate a scenario where best practices for MS SQL databases are not followed. In this screnario the data and log files for a SQL database is in the same drive.

1. Repeat the same procedure for another database.

2. Name the datbase tpcc1

3. For tpcc1, set path to E:\data.

4. For tpcc1_log, set path to E:\logs. (create logs folder)

5. Populate the database with data using the same procedure as above in HammerDB.

6. Wait for the data to be populated

   Note

   It may take from 15 - 20 minutes for data population

7. Confirm data is populated using the same procedure as above.

8. Click on Destroy Virtual Users.

   

9. Double click on Driver Script > Options. Make sure SQL Server Database name is tpcc1 (the database you created in the previous few steps).

10. Select “TPC-C driver script” as Timed Driver Script.

11. Leave rest of them as-is and select OK.

    Note

    Optional: You can also try using the option Keying and thinking time for making the IOPS more intensive.

    

12. Double click on Load

13. Go to Virtual users and click on Options.

14. Make sure Virtual users in the popped-up window is 17 and click OK

15. Double click on Create and then double click on Run operations.

    

16. While IO is getting generated, click on Transactions Counter and note the TPM. (Start the TPM counter if not already started)

    

17. Take screenshots and send TPM results to prospective customers or use it for your own reference.

    Note

    You should now notice that a database configured withouth following best practices performs slower than the database created following best practices. In this case, the database tpcc1 is four times slower than database tpcc.

    Note

    Please note that the test used here are using heavy I/O. Consider changing them in your own test to suit customers workloads.

18. Also check the I/O Metrics in Prism Element to see if you can observe I/O patterns, latencies, SSD/HDD usage and block sizes of files used by the VM you are running HammerDB tests on.

    

Takeaways

1. HammerDB gives you a way to test DB performance with dummy data that it generates

2. HammerDB is free and easy to use

3. Following best practices is the key to SQL DB Performance

4. Always right-size DB and DB Servers (do not over-provision or under-provision)

5. Introduce performance benchmarking to your customers as much as possible. It will make your life easier

6. Nutanix Era deploys databases with best practices