With this tool you can load test any SQL Database hitting a given procedure, with test data from a csv. Some of the salient features are given below. When used with Query Store it can help resolve multiple SQL Performance and Bottleneck issues
- Can hit any SQL Database.
- Dynamic connection check before execution.
- Ability to read test data from CSV (details below)
- Ability to fetch store procedures from database dynamically.
- Support MARS, and both Integrated Security and uid/pw options.
- Provide overall metrics viz: Execution Time, Max, Min
- Provide metrics per execution which can be logged to a file for further analysis.
- Provide detailed logging.
- Automatic Handling for multiple resultsets returned.
Download Link provided at the end of post. If you want to cut the long story short, watch the video ab bottom of this page.
Background
I have been looking in the the QuertStore as provided by the SQL 2016 in all features. So I fired up my SQL Azure db and cracked in code, but i could not see any data in there. First thing i tried out, was to run some queries manually, but no luck. On further analysis get to know that you need to have some significant amount of queries executed for it to give some result. For this i got the idea to build this tool.
Fire Up….!!..
The application is simple a simple interface as shown below with basic controls.
The first grid contains the connection details while second grid contains the test data and execution options, while the last grid contains the logging and metrics.
Firstly I setup a simple stored procedure for the purpose of this demo. The procedure has 2 input args and return single row from database. If you want some more adventure, you can use wide world exporters demo database that is available from Microsoft.
CREATE PROC [dbo].[PersonLooker] @firstName varchar, @lastName varchar AS SELECT @firstName as FirstName, @lastName as Lastname; GO
So Lets Start……
Step 1. Start the application and fill in the connection credentials. And Click CONNECT.
Step 2. Click on the Procs Button to get the list of available procedures.
Step 3. Click on the Test data button to select the file for test data.
Once done, this will have the data loaded with details mentioned as in log.
For Test Data Format read below.
Step 4. Click START….
Step 5: Either wait for it to complete, or in middle you may press Stop at some point.
At the completion, it will show the max, min and average for the complete test.
Now there are two more options.
Option A. Write Execution Log. Write Log to file. This simly dumps the on screen logs to a file.
Option B: Write Metrics- Write the individual test case data to a file. This is more interesting because this can give a prospective introspect on which data the problem lies. The raw output and formatted output are shown below.
The options are customizable from the checkboxes and will give required data.
Performance Analysis
Performance analysis from this data in combination with Query Store will be in the next post. Please hold your horses till then.
Test Data
The test data needs to be in CSV format. The constraints for test data are:
- It should be CSV
- The number of columns as in rows should match with first line.
- There is no constraint on number of rows.
- First line should contains the name of columns of parameters as in stored proc.
for example our procedure takes ‘firstName’ and ‘lastName’ so our test data is something loke below
firstName,lastName Aaron,Aaberg Aaron,Aaby Abbey,Aadland Abbie,Aagaard Abby,Aakre and so on....
So thats it for this post. I shall be updating this as required. Please do post in any of your queries or suggestion in comments, and I shall try to answer them asap.
The details for application usage are shown in following video.
now as promised, here are download links:
Download Application
Download firstName, lastName sample Data
License: I have always complex and confusing license. This application is available totally Free for Commercial or Non-Commercial Use. However any donations would be appreciated (paypal, bitcoins, i take everything 🙂 ). Please write in comments for details.