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.11)
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: 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.
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) : 1
Ramp-up period (seconds) : 1 (how much to delay starting each user)
Loop Count : 1 (number of iterations)
Step 4: Adding JDBC requests(SQL queries): Add "JDBC Request" element under thread group by right clicking on thread group->Add->Sampler->JDBC Request(left click).
Now we need to give a variable name under "Variable name bound to pool" section i.e "JDBC Request". 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".
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 Connections: 10
3. Pool Timeout: 10000
4. Idle cleanup interval: 60000
5. Auto Commit: True
6. Maximum Connection Age:5000
7. Validation query: SELECT top 10 * FROM [iDBTest] WHERE TestID like '%3156%'
8. DatabaseURL: jdbc:sqlserver://10.0.0.116:1435;Databasename=northwind;
9. JDBC Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
10. User Name: sa (SQL server access user name)
11. Password: (user's password)
Step 6: Adding Listeners: We need to add listerners by which we will view the performance test results. For basic level of report add "View Results Tree". To be noted that listeners will use a lot of memory.
Finally, 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.