Wednesday, May 4, 2011

SQL query performance test with JMeter

There are lot of tools in the market by which you can analyze performance of SQL query or stored procedures. Which tool you select is a matter of choice and compliance with your database server.

Apache JMeter is a open source software which had been built with JAVA. Main purpose of this tool is to do load test function behavior and measure performance. Basically anything that has request and respond feature can be load tested by JMeter.
Website link: JMeter

Scenario: I need to know how efficiently my SQL queries can perform under some given load.

I will use JMeter tool to accomplish my objective. In order to use JMeter you need to have JAVA run-time environment present in your machine. JMeter uses JDBC to connect to any database. As my targeted server is Microsoft SQL Server(MSSQL) 2008, I need to have proper JDBC driver for MSSQL and need to place that driver under "JMeter/lib" directory. Also we need to monitor the performance of the server where the database has been stored. "Performance Monitor" provided by Microsoft can be very useful for this purpose.

JMeter Download Link
(note: I have used JMeter v 2.4)
JRE download link
Microsoft JDBC Driver for SQL Server
Performance Monitor: Control Panel\System and Security\Administrative Tools\Performance Monitor (path in Win 7)


Basic JMeter configuration breakdown:

Test Plan->Add Users(thread group)->Add JDBC requests(SQL queries)->Add JDBC connection config-> Add Listeners(to view/store test results)->Add Assertions to JDBC request(measure matrices for analyzing response)

Step 1: Put downloaded JDBC driver (sqljdbc4.jar) under "JMeter/lib/" directory.

Step 2: Creating Test Plan: I will create 20 users, each will send three SQL requests to the database server and each user will repeat sending requests four times(# of iteration). Total (20x3x4=)240 JDBC requests will be sent by 20 users. After first opening JMeter by default a empty test plan will be created with default name as "Test Plan". Give a meaningful name to your test plan relating with your objective.

Screen-shot: Test Plan

Step 3: Adding Users: Add "Thread Group" element under the test plan by right clicking on test plan->Add->Threads(Users)->Thread Group(left click). Name it like "DB users" or something meaningful. In this element I will give no. of users to be simulated at database server. Now I'll give inputs according to my test plan under section "Thread Properties":
Number of Threads (Users) : 20
Ramp-up period (seconds) : 1 (how much to delay starting each user)
Loop Count : 4 (number of iterations)

Screen-shot: Adding thread Group
Screen-shot: Configuring thread group




Step 4: Adding JDBC requests(SQL queries): Add "JDBC Request" element under thread group by right clicking on thread group->Add->Sampler->JDBC Resuest(left click). Name it with relevant to your query i.e. "Key Report->Period MAT".
Now we need to give a variable name under "Variable name bound to pool" section i.e "BI". It is used by the JDBC Sampler to identify the configuration to be used.Several different JDBC Configuration elements can be used, but they must have unique names.I will use same JDBC config for all the JDBC requests.
Now input SQL query under "Query:" section. Select your query type from the "Query Type:" drop down list. I will give queries traced by SQL Profiler, so I have given query type as "Callable Statement". For SELECT statement you should select query type as "Select Statement".
Following the above process I have created another two JDBC requests as "Key Report->Period QTR" and "Key Report-> Period YTD".

Screen-shot: Adding JDBC Request
Screen-shot:Query input


Step 5: Adding JDBC Connection Configuration: Add "JDBC Connection Configuration" element by right clicking on thread group->Add->Config Element->JDBC Connection Configuration (left click).
Now give following inputs to configure:
1. Variable Name: (Same variable name given at JDBC Request elements)
2. Maximum Number of Connecitons: 0 (if you want connection to be shared then give value more than zero)
3. Pool Timeout: 10000
4. Idle cleanup interval: 60000
5. Auto Commit: True
6. Maximum Connection Age: 5000
7. Validation query: Select 1
8. DatabaseURL: jdbc:sqlserver://(server IP);instanceName=(Instance);databaseName=(DBName)
9. JDBC Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
10. User Name: (SQL server access user name)
11. Password: (user's password)

Screen-shot: Adding JDBC Connection Configuration
Screen-shot: Configuring JDBC Config element


Step 6: Adding Listeners: We need to add listerners by which we will view the performance test results. For basic level of report add "Summary Report" & "View Results Tree". To be noted that listeners will use a lot of memory. I also use third party listeners named "Response time vs Threads" & "Response time over time" which can be found at http://code.google.com/p/jmeter-plugins/.

Screen-shot: Adding listener

Step 7: Adding Assertions: Assertions allow you to assert facts about responses received from the server being tested. Using an assertion, you can essentially "test" that your application is returning the results you expect it to.For this case I will add three types of assertion to each of my JDBC request & they are "Response Assertion", "Duration Assertion", "Size Assertion". Response assertion will check whether expected pattern of values are returned or not.
Duration assertion will check whether the response have taken more than expected time or not. Size assertion will check whether the response size matches with expected size.

Finally, for my objective the configurations have been been completed. All I need is to do is run the load by left clicking Run->Start or by pressing ctrl+R. After running the load view the results shown at added listeners and run "Performance Monitor" at database server PC to monitor server performance due to this load.

Screen-shot: Summary Table

Screen-shot: View Results Tree

Screen-shot: Response Time Over Time

Screen-shot: Response Time vs Threads

Screen-shot: Performance Monitor

26 comments:

Sarath M said...

Nice step by Step guide. Quite helpful!

Mag said...

Good one. Informative.
For info for JMeter beginners like me.
I downloaded jdbc driver from Microsoft site, but it didnt work for me. Then I copy-pasted the sqljdbc4.jar file into jakarta-jmeter-2.4\lib directory. JMeter then worked. Also I had to change JMEter jdbc database URL accordingly. jdbc didnt allow integrated security, so set it to false or not give the parameter (jdbc:sqlserver://localhost:1433;user=sa;password=YourPassword;databaseName=YourDBName;integratedSecurity=false) I had to change in Surface Area to allow TCP/IP and named pipes.

Rushmila Islam said...

Nice & helpful .. :)

Theazyfa said...

very helpful

mahendra said...

Dear Sir,

I followed same method mentioned here. But My Jmeter is not making any connections to the database. I copied jdbc drivers into /lib directory as followed.
sqljdbc_auth.dll
sqljdbc_xa.dll
sqljdbc
sqljdbc4

These Errors we are facing in Jmeter Log Files -
WARN – jmeter.protocol.jdbc.config.DataSourceElement: Could not return Connection java.sql.SQLException: No suitable driver found for jdbc:sqlserver://serverip:1433;databaseName=dbname

WARN – jmeter.protocol.jdbc.config.DataSourceElement: Could not return Connection java.lang.Exception: Could not create enough Components to service your request (Timed out).

Please response us asap
Thanks & Regards

Chinmay BRAHMA said...

Hi
I want to learn more on Jmeter.
Can I have your skype ID to do the above.

My mail ID is chinmay.brahma@gmail.com

Thanks

GK said...

i have executed but i am facing issue.

In Result tree, i am getting error so i am unable to view other reports like, Response Assertion, Duration Assertion and size.

Mohammad Ashik Elahi said...
This comment has been removed by the author.
Mohammad Ashik Elahi said...

Dear all,

Sorry for not giving reply lately as I was very very busy with my professional life and work.


@Chinmay: my skypeID is: ashik1206

Regarding your mail: Make sure you have enabled TCP/IP protocol from SQL server configuration Management.

1. Open "SQL Server Configuration Manager"
2. Expand "SQL Server Configuration Manager"
3. Click on your DB instance name "Protocols for {your DB instance name}"
4. Enable TCP/IP protocol

I think this will solve all of your problem.

Mritunjay B Shettar said...

Hello.. i'm trying to connect with sql server for my local system but after making all configuration according to ur instructions, still when i run tread group it showing status failed... not getting wat to do.. can u mail me any videos regarding this... my mail id is mritunjaybs.shettar@gmail.com thank you

Mohammad Ashik Elahi said...

@Mritunjay:

Can you send me screen-shot of the error message.

But I do not have any video.

Regards
Ashik

vishu said...

hi can we call stored procedure in jmeter if yes then how?

if no then suggest me some tool to test stored procedure.....

thanks,
vishal

vishu said...

hi,

can we call and test stored procedure in Jmeter if yes then how ?

if no then suggest something else to me ...

Anonymous said...

Hi Ashik,

How can we test a Stored procedure using JMeter?

Thanks,
R.C

Anonymous said...

This post was incredibly helpful, thank you!

Sachin Chaudhary said...

This is really helpful information.

I have a client which need to know performace of SQL store procedure Or you say say time required to create one invoice.

When I added SQL store proc in JDBC request one generated dynamic Id which needs for second query.

I have tried with correlation method but no success.

Ex:

In Reponse of first query, I am getting ParentID = MRKNKKD which need to pass to second query.

Please suggest if anybody have faced this issue.

Katha Download said...

Actually, I am able to run a Jmeter test using JDBC Connection Configuration if database is in my local machine, however in my company's network database can only be access via ssh tunnel, where as i need to pass SSH Host, SSH User & SSH Password along with the database username & password.

Can you please guide me where can i specify SSH details to connect to the database using JDBC Connection Configuration in JMeter

Alan Alvarez said...

Can JMETER limit the amount of records being returned by the Database, with some setting??? I know that you can set a limit request at the SQL level(TOP, ROWNUM<100, etc), but that might behave differently on different platforms and change the actual query execution plan. I believe JMETER saves the entire resultset into memory. All I want to measure is the time it takes the Database to process the SQL request, not the time it takes to return all records.
Do you know if such setting exists?

christi parks said...

Hello all,I am new and I would like to ask that what are the benefits of sql training, what all topics should be covered and it is kinda bothering me ... and has anyone studies from this course wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
would really appreciate help... and Also i would like to thank for all the information you are providing on sql.

Dzmitry said...

Thank you for article, in addition I'd like to show you another post about building DB testplan.
building DB testplan

Anonymous said...

Hi Mohammad,

Thanks for the info, I found it very helpful.

I managed to call an Oracle stored procedure from a JDBC request (Callable statement).

The procedure returns an error code and 8 Oracle ref cursors.
I use the error code of 0 in the Response Assertion.

So far all good, but when I start a load test the first 37 requests are successful. After that I get error ORA-01000: maximum open cursors exceeded.

Turns out that the ref cursors are not closed in the stored procedure because the expectation is that the client (normally a web service call) will close them after the data was processed.

In Jmeter I am only interested on the response time and the error code.
Therfore I want to close the ref cursors (result sets) as soon as I get them.

Does anybody have an idea how to achieve this in a Jmeter JDBC request?

Much appreciated
Bernhard

chandrashekhar gawade said...

i was try to connect mysql server with jmeter but i got it connection errror

i put mysql connector jar in apache jmeter/lib file

still this problem occured

chandrashekhar gawade said...

so suggest me something

Thanks
Chandrashekhar

Suma latha said...

Very helpfull.
Thanks

Software Development Company said...

Hello,
The Article on SQL query performance test with JMeter. It gives detailed information about it.Thanks for Sharing the information about the Performance testing SSAS for evaluating scalability. For More information check the detail on the Performance Testing here Software Testing Company

Navaneetha Krishnan said...

Hello,

I'm trying to prepare a stored procedure SQL query script using jmeter using JDBC connection im not able to execute the query directly in jmeter could u pls share document regarding this it would be more helpful.

Thanks,
Navaneeth.