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