Learn how to Load Test SQL Database using JMeter

This article walks you through how to create and configure the JMeter test plan to get you started with load testing MS SQL database. It is assumed that you already have a basic understanding of JMeter and performance testing as this article will not cover configuring test plans for different load scenarios.

Please note that this guide is for beginners trying to get started with SQL database load testing.

Why did I write this guide?

Because the first time I used JMeter for SQL load testing, I started from not knowing about it and encountered errors that took a lot of searching and tweaks to resolve. So, I thought someone else might try to do the same thing and this guide would help.

Why database performance testing is important?

Database performance testing is used to identify performance bottlenecks and gain insight into its reliability, scalability, and performance under varying load.

In many cases the database can be a performance bottleneck for the entire application and by baseline and optimizing the database performance, the overall application performance can be improved.

Here are some of the reasons for database performance bottleneck:

• Badly designed schema
• Missing indexes
• Too small buffer pool
• Poor index design
• Inefficient queries
• Inadequate hardware selection

So let’s get started.

Prerequisites

• Install the latest Java Development Kit (JDK)
• Install latest Apache JMeter

Step 1 - Creating a Database Test Plan

To create a JMeter database load test plan add the following elements:

• Thread Group - it the beginning point of a test plan and is used to set the number of threads, set the ramp-up period, set the number of times to execute the test.

• To add a thread group Right-click on Test Plan > Add > Thread (Users) > Thread Group

• Configuration Element - will be used to add and configure JDBC connection configuration to enable JMeter to connect to the database.

• To add configuration element Right-click on Thread Group > Add > Config Element > JDBC Connection Configuration

• Sampler - it tells JMeter to send the request to the server and wait for the response. For database testing JDBC Request sampler.

• To add a JDBC Request sampler Right click on Thread Group > Add > Sampler > JDBC Request

And finally…

• Listener - provides access to information JMeter collects about the test case during the test run. For the purpose of this setup, we will be using the View Results Tree Listener that shows details of sampler requests and responses.
• To add View Results Tree Listener Right-click on Thread Group > Add > Listener > View Results Tree

Step 2 - JDBC Connection Configuration

• Download Microsoft JDBC Driver for SQL Server and save it in the same directory as JMeter installation or any other directory of your choice as we will need to config add the path to Test Plan so that SQL JDBC driver can be used by JMeter. Please refer to the image below demonstrating how to reference SQL JCBC driver in the test plan:

• Now configure JDBC Connection configuration element as below:
• Add variable Name for created pool
• Max numbers of connection
• Database configuration
• Database URL jdbc:sqlserver://{sql server name}:{sql serverport};database={database name}
• JDBC Driver class, select com.microsoft.sqlserver.jdbc.SQLServerDriver
• User Name
• Password

Step 3 Configuring Sampler (JDBC Request)

Select the Sampler created in Step 1 and provide the following details:

• Variable Name bound to Pool (same as in the JDBC connection configuration element).
• Enter the SQL Query string field as shown in the below diagram:

Step 4 - Running test test

• Save the test plan and click Run > Start or Ctrl + R to run the test.
• Click on the listener to view the results.

If you encounter TCP/IP and firewall access related error, follow the steps below to resolve them:

• If you are testing a local MS SQL database on windows, make sure that TCP/IP network protocol is enabled for your SQL server. Here are the steps on how to enable TCP/IP network protocol for MS SQL server.

• If you are testing a SQL database in cloud say on Azure, make sure that your IP whitelisted so that JMeter can access the database on cloud.

• If you are testing SQL queries returning a large amount of data JMeter may throw java.lang.outOfMemoryError: Java heap space, to resolve this:

  • consider optimising your query and/or
  • increase JVM heap size to cope up with data being stored. Detailed instructions can be found here 9 Easy Solutions for a JMeter Load Test “Out of Memory” Failure

You can download the example JMeter test plan from git.

#HappyTesting

Thanks for reading! 🎊 Hope you found this useful. Don’t hesitate to share, or post a comment or send me a message on LinkedIn 🙏

Please leave the comment below if you want to know more or have any questions. I will be happy to help.