JMeter - Database Testing using MS-SQL server

Load Testing of SQL Server Using JMeter Step by Step Guide

Load Testing of SQL Server Using JMeter: 

For SQL Server 2008 and above load testing can be performed via JMeter’s JDBC sampler. This guide will provide step by step load testing walkthrough.

Pre Requisites:

Following software should be installed on your system
  • Microsoft SQL Server 2008 R2 or above.
  • Notepad ++ Download it from Here
  • Install JMeter (Latest Version) from Here  Download the zip file under Binaries Heading.
  • Download the latest plugins from Here
  • Study the read notes of Plugins and do the same. 

JMeter Installation:

This section will explain how to install JMeter. 

  •  Install Java 7 or above
  •  For Windows 7: copy and paste the Java folder from C:\Program Files (x86) to C:\Program Files
  •  Set the path in environment Variables in My case it is C:\Program Files\Java\jre7\bin
  • Go to the JMeter Bin directory and Edit the JMeter.Batch file. Set set HEAP=-Xms1024m -Xmx1024m or you can increase the size as you want. 

Drivers Download:

JMeter requires SQL drivers for connectivity with SQL Server. There are two different types connectivities in SQL Server 
  • SQL Server Authentication Mode 
  • Windows Authentication Mode 
Most of the time we have to work with SQL Server Authentication mode, but there are some applications that need Windows authentication mode. we can use different drivers for both authentication modes. 
SQL Server Authentication Mode 
For SQL Server Authentication mode we generally use SQLJDBC Driver. Download SQL JDBC Driver from the following location DownloadLink . Click on Download Button and you will see the below pop up menu.

Note: Download version 6 or 4.2 if you have java 8 or above installed. If you have java 6 or 7 download version 4.0 or 4.1
  • After Download, extract the .exe file and copy the sqljdbc4.jar file and paste it to the lib folder of JMeter.
  • Place the “sqljdbc_auth.dll” file to the lib folder of JMeter installation. This file is in the auth folder of the extracted version of SQLJDBC4 exe. This dll file will be placed w.r.t the x86 or x64 folder. Copy the dll from the folder w.r.t the operating system (if its 64 bit than copy the file from x64 folder)

Windows Authentication Mode:
In SQL Server Windows authentication mode does not require user and Password to connect to the database. It directly takes the windows credentials to login. Most of the time we require to use domain account. So, for this , SQLJDBC  driver will not work. 

For accessing the database with windows authentication mode , using domain account JTDC driver will be used. 

Download JTDC driver from Here . You will see the below image. 
  • Download the latest version (in my case I downloaded version jtds-1.3.1), also place the “jtds-1.3.1.jar “file in the libfolder of JMeter Installation. 

Plugins Download:

  • Download all the latest stable release JMeter Plugins from Here  
  • Extract the plugins and place them in the related folders e.g. (bin , lib , ext )
  • Restart JMeter 

Script Generation:

From this section you will learn how to generate scripts inJMeter step by step . This will provide a step by step guide to create / generate JMeter scripts for  load testing of SQL Server. 

Step1: Setting up SQL Profiler

SQL Server Profiler is used to capture the SQL queries and stored procedures. Following are the steps to capture scripts in SQL server Profiler.

  • Run your application  that needs to be load tested
  • Open MSSQL Server Management Studio
  • Connect with your database by providing database credentials
  • Go to Tools > SQL Profiler
  • Enter the database credentials. Trace properties screen will open 
  • Go to Event Selection tab and check /Uncheck the following as mentioned in the screen below
SQL Profiler Event Selection
  • Click Run Button
  • Go to your application and open any form.
  • Go to Sql Profiler and see the SPID against the queries that have been generated. Note that SPID
  • Close this SQL Server Trace
  • Create a new trace file.
  • Go to Event Selection
  • Click on Column Filters button
  • Find the SPID filter. Click that filter and enter the SPID noted above in the “Equals” filter 
Filters
  • Click OK button.
  • Click Run button on the Trace properties screen.
  • Run load testing scenario
  • Click on Stop Trace button on the SQL server profiler once the scenario is completed. 

Step 2: Parameterize and Correlate: 


  • Copy and paste the queries to Notepad +
  • Find out the values to be parameterized and correlate (Hint: Find out in Insert , Update or Stored procedure calls)
  • Use the following syntax to parameterize the values ${var_name} .e.g. if you need to parameterize an ID then you can do it like ${ID}
  • Find and replace all the values to be parameterized. 
  • Correlation is done using Regular Expression extractor



Step 3: JMeter Scripting:

Load testing of SQL server can be done by using JDBC sampler in JMeter. For scripting , Test Plan is required . 
  • Add Thread Groups to Test Plan
  • Add JDBC connection configurations to Test Plan
  • Add JDBC Sampler to Test Plan
  • Add Listeners to Test Plan
We will go through each of the above in detail with images and steps by step. 

Add Thread Group:


The first thing that needs to be done in creating a Test Plan is to create a Thread Group, right click on Test Plan > Add> Threads(Users)> JP@GC Stepping Thread Group.   Thread Group will get added under the Test Plan node. Rename this Thread Group as JDBC Users. The following Thread Group will be added in JMeter Test Plan. 

Stepping Thread Group

Now, the question arises that why am I using JP@GC Stepping thread group, which is a plugin? The reason is simple , this thread  group allows us more control over the thread scheduling. This is very similar to Load Runner . There are more thread groups like Concurrency thread group , which is more better option than stepping thread group. But in this post we will use Stepping thread Group. 


Add JDBC Connection Configuration (SQL Server Authentication mode, SQLJDBC Driver )

As I mentioned above that there are two modes of connection in SQL Server. JDBC Connection configuration will be setup according to the authentication mode you need to use. This section will describe the JMeter JDBC connection configuration w.r.t SQL Server Authentication mode.

You need to do the following to add and configure JDBC connection configuration

  • Right click on the JDBC Users element, select Add > Config Element > JDBC Connection Configuration.
Setup the following fields in JDBC Connection Configuration 
  • Variable name bound to pool. This needs to uniquely identify the configuration. It is used by the JDBC Sampler to identify the configuration to be used. This pool contains jdbc connections . Whenever a user is initiated it borrows a connection from this pool and returns the connection back to the pool after completing the work
  •  Database URL: jdbc:sqlserver://<serverName>:<PortNumber>;instance = <instance name> ; Database=<DatabaseName> (Please note that the port name could be dynamic).
  • JDBC Driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver
  • User Name: user name of the database
  • Password: password to connect the database
  • Max No of Connections:  Total Number of virtual users
  • Max Connection Age: Total time(ms) the test to be executed 
SQLJDBC Driver

How to check the Dynamic Port Number: 
Check the port name from (Go to SQL Server Configuration Manager > SQL Server Network Configuration>Protocols for MSSQLSERVER(your instance name) > Double Click TCP/IP>Click on IP Addresses Tab in the TCP/IP Properties pop up window> Under IPALL heading see the dynamic port, TCP Port. If dynamic port is provided than use that port number otherwise use TCP Port Number)



Add JDBC Connection Configuration (Windows Authentication mode, JTDC Driver )
As I mentioned above that there are two modes of connection in SQL Server. JDBC Connection configuration will be setup according to the authentication mode you need to use. This section will describe the JMeter JDBC connection configuration w.r.t Windows authentication mode

You need to do the following to add and configure JDBC connection configuration

  • Right click on the JDBC Users element, select Add > Config Element > JDBC Connection Configuration.
Setup the following fields in JDBC Connection Configuration 
  • Variable name bound to pool. This needs to uniquely identify the configuration. It is used by the JDBC Sampler to identify the configuration to be used. This pool contains jdbc connections . Whenever a user is initiated it borrows a connection from this pool and returns the connection back to the pool after completing the work
  •  Database URL:jdbc:jtds:sqlserver://<serverName>;Instance=<InstanceName>;DatabaseName=<databaseName;domain=<domainName>
  • JDBC Driver class: net.sourceforge.jtds.jdbc.Driver
  • Max Connection Age: Total time(ms) the test to be executed 
  • Max No of Connections:  Total Number of virtual users
  • Password: Domain password
  • User Name: Windows domain user name (used for login)
JTDC Driver
Add JDBC Request: 
Now , a point comes here that where do we write the queries or copy the queries in JMeter ? Yes, it is JDBC Sampler. This is how JDBC Sampler is added

Now add a JDBC Request which refers to the JDBC Configuration pool defined above. Select the Thread Group,right click the mouse on the Thread Group and hover to Add menu, and then select Add > Sampler > JDBC Request. Click on JDBC Request.

Some fields in the JDBC request needs to be updated / modified to make it working . Set the following fields. 
  • Variable name bound to pool.  This needs to uniquely identify the configuration. It is used by the JDBC Sampler to identify the configuration to be used. (same as in the configuration element)
  • Name: Learn (any name)
  • Query Type: Select the query type from the drop downs. More info on query types Click here
  • Enter the SQL Query String field.

Note:
Use Update Query Type for Insert, Update and Delete
Use Prepared Select statement query type for “sp_prepare” select statements only
Use Callable Query type for stored procedure (exec) and SET statement

Listeners to be used:

Listeners are the areas which receive the response from the server. Generally we use the following listeners 
  • Aggregate Graph
  • View Result in Tree (Use only in debugging)
I will be posting details about listeners in the Next Blog post. 




At the End , you will come up with the following hierarchy

Comments

Post a Comment