MS SQL Server load testing with JMeter and stored procedures

JMeter is a great tool. I have been using it for quite some time for now, but mainly to test web application and web services. Recently I had to perform bunch of load tests on SQL Server, just to check if new one will be faster than old one. It turns one that JMeter is pretty handy when comes to simple SQL load testing. To perform such tests we will need couple of things:

• JMeter
• Microsoft JDBC Driver for SQL Server
• IP address or domain name, port and database name which we would like to test
• User on SQL Server which have access to all required tables / stored procedures
• Be able to authenticate using SQL Server mode
• Some test data

Preparation

After you have downloaded all required packages, first thing is to unpack jmeter. Then unpack Microsoft JDBC Driver, go to sqljdbc_4.0\enu and copy sqljdbc4.jar into JMeter lib folder.

Configuring JMeter Solution

As usual, create new test plan with empty Thread Group. First thing which needs to be done, is to configure connection to MS SQL Server. To do this, add JDBC Connection Configuration which can be found in context menu under Add -> Config Element. When config element is added we need to put there previously prepared configuration data.

• Variable name: MSSQL_JDBC_Connection
• Connection string: jdbc:sqlserver://<IP>:<PORT>;DatabaseName=<DATABASE_NAME>;
• JDBC Driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver
• Username and password

Filled configuration element should looks like this:

Next step is to configure JDBC Request element to execute some queries against SQL Server. We can add one by selecting from context menu Add -> Sampler -> JDBC Request. I propose to add also View Results Tree as child to JDBC Request sampler. It can be very useful in debugging.

First thing is to set option Variable name bound to pool to the same as Variable name set in JDBC Connection Configuration. In our case it will be MSSQL_JDBC_Connection

In my case, I will be using stored procedures for testing purposes.
Query Type must be set to Callable statement.
To execute stored procedure GetTestData from schema test with two parameters, following line needs to be added to Query text box:

{CALL test.GetTestData(?, ?)}

Query parameters can be passed using Parameter values and Parameter types options. My sample configuration looks like this:
Data for this test in my sample solution are loaded from CSV file. It contains list of comma separated values. First one is ID, second one is VALUE. My CSV Data Set config looks like this: This was the last step of configuration of sample solution. To start testing, configure thread group and click run :)