2013-01-28

How to perform MSSQL load testing in JMeter?

In this article we are going to see how to perform Load / Stress test for MS SQL Server. It is applicable for SQL 2008, 2005, SQL Express.

For Basic Idea on jmeter, See my this post.

Step : 1 
To Test Database, we have to access the database directly as jmeter does not send request via browser. So,we need a driver or communication way to access DB. As Jmeter is build on JAVA, so we have to use JDBC driver. JDBC driver is different for different Database Server. As we are focused on MS SQL in this post, we will be needing a JDBC driver.
I am using MS SQL Server jdbc driver 4.0. Download the from here(download). 
[Note : Use sqljdbc4.jar . sqljdbc.jar is for old java versions]

Step : 2
-After download driver , Unzip and we will get sqljdbc4.jar we have to copy this to \apache-jmeter-2.8\lib (Library Directory)
 

Step : 3 
- Run Jmeter

Step : 4 
-Add a Thread Group to default test plan(I use 2 user, 1 second, 2 iteration)

Step : 6 
-Add a JDBC Connection Configuration under thread group( it is one kind of Config Element)
[This will control the settings to communicate with database]

Step : 7 
-Provide a Variable Name
Note : Every Variable name represents a configuration settings, so if we add more JDBC Connection Configuration, each one should identically differently from other by variable names. I use SQL Authentication,
Database Connection Configuration :  
Database URL: jdbc:sqlserver://[server address];databaseName=[DataBase Name]
JDBC Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
Username: (Ex-shantonu)
Password: (Ex-p@ssw0rD)
You can find the syntax details here

Step : 8 
-Add JDBC Request( it is one kind of Sampler) under the thread group. [This sends the query, procedure on DB]

Step : 9 
- Provide Variable Name as the mane from JDBC Connection Configuration. This name should be same as any one of JDBC Connection Configuration that is present in the test plan.

So,
-We have to add sql query in SQL Section. This is the the test query that will be using for load in the Database. I have tasted
1. Local SQLEXPRESS : "Select count(*) from t_TestTable"
2. Remote Server : Select COUNT(*) from Configurations







My sample Database Structure :
- Select the type of Query




Note : We can also use Sourceforge provide a JDBC driver (JTDL for MSSQL Server) link. 
In that Case :
Database URL jdbc:jtds:sqlserver:// [server name or ip] / [db name]]
JDBC Driver Class: net.sourceforge.jtds.jdbc.Driver
Username: (Ex-shantonu)
Password: (Ex-p@ssw0rD)

Note :
-If you use SQLExpress don't miss the followings a: Active SQL Express Service, b: Active  SQL Express Browsing Service, c: Active  SQL Express Browsing enabled from TCP/IP ( Use SQL Server Configuration Manager)
-It is better to Use SQL Authentication (so, know your sa password. This link might help)
-If you do not use SQL Authentication the Database URL : jdbc:sqlserver://[server address];instanceName=[name];databaseName=[dbName]

-Add listeners, You can add any kind of listeners, I have used only View Results in tree.

Now, Run the Test and monitor the data.

...Thanks....:)

4 comments:

  1. Hi
    Here you use select statement.What about the store procedure?Could you describe plz

    ReplyDelete
    Replies
    1. In JDBC Request sampler, there is a quarry type combo for select, if you select that , your query will be send as select query.
      For store procedure, the scenario is complex. It depends on what type of store procedure you are doing. (type are in combo).. jmeter Executes the query and expect results that you select in type , so if your store procedure does get any thing, you may use select and insert your full store procedure.
      if you need your help in store procedure details, send me your query, I will check what type you can select in jmeter for your store procedure.

      Delete
  2. Again Shantonu
    I insert full store procedure in query field.In that case i had to select Query Type=Callable Statement and execute successfully.But I faced a different problem here.
    If i select any input parameter value then after executing it fails and the Response message: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
    Could you help me plz if this issue is known to you.

    Thanks a lot for your earlier reply.

    ReplyDelete
    Replies
    1. I guess what happened, your Insert(i guess, this apply with your term input parameter) query need some things to care about like primary key(parametrize , unless duplication will cause exception) , or dependent table's minimum steps requirement. I suggest mail me your query and let me see the statement. Because , without looking your particular SQL it is hard for me provide solution.
      And, please see the event log for detail SQL Server Exception either from database or system events(if it is listed their)
      Thanks

      Delete