Hello people,
Good Morning!
In this post, I will introduce and demonstrate the use of the open source tool SQLQueryStress, by Adam Machanic (creator of sp_WhoIsActive), which serves to perform stress tests by running a given query, allowing you to perform a series of tests on your SQL instance. Since this project is no longer maintained and updated by Adam, Erik Ejlskov Jensen created a Github to create new features and enhancements to the tool.
The tool allows you to define how many threads will run concurrently, how many runs and how long each run will be, allowing you to perform various types of tests, including:
- Check instance behavior with a certain number of running sessions
- Run multiple queries at the same time to stress server CPU, disk and memory and check their behavior
- Simulation of a critical environment with multiple transactions per second during system testing and SQL queries
- Identify how many times an SP runs per day, perform an optimization, and simulate how much time would be spent on the day.
- Run a query or SP multiple times with random parameters and analyze their behavior
Finally, the list of possibilities is very large and the software gives you several tools to improve your analysis, such as the possibility of using or not, connection pooling, clearing caches, clearing buffer, etc.
The use of the tool is quite simple, so I will not do many demonstrations. The purpose of this post is to share this tool to those who do not know it, so that you can improve your performance analysis of SQL Server instances that you demonstrate or perform performance testing on some query.
After downloading the tool executable on this link, you will see the SQLQueryStress home screen. Click on the "Database" button to configure the connection to the database.
I do not recommend using this software in production environmentsunless it's on a scheduled maintenance schedule and you want to do stress testing on the instance.
SQLQueryStress splash screen
In this screen you can enter the server name \ connection instance as well as authentication type, user and password data.
Database Connection Setup Screen
Back to the home screen, let's define the query to be executed, the execution parameters and start processing.
Execution Parameters:
- Number of Iterations: Is the number of times the query will be executed
- Number of Threads: Number of sessions that will run in parallel (at the same time)
- Delay between queries (ms): Amount of milliseconds between the end of one query and the beginning of the next.
It is worth mentioning that the number of times the query will be executed is through the Number of Iterations x Number of Threads account, ie if you put 10 iterations with 5 threads, your query will be executed 50 times.
Execution Result:
WhoIsActive result, demonstrating the connections being made:
(Note: I had to put a WAITFOR DELAY in SP so that they appear in WhoIsActive, since the test SP is very simple and runs very fast)
How to use Parameter Substitution
Click here to learn more about Parameter Substitution
In this example, I will create a table and enter several random values that will be used as parameters for the SP calls I will test in SQLQueryStress.
Function creation to generate random numbers:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | IF (OBJECT_ID('dbo.fncRand') IS NOT NULL) DROP FUNCTION dbo.fncRand GO /* Vou criar uma função para usar no SELECT e realmente trazer valores aleatórios para cada linha */ CREATE FUNCTION dbo.fncRand(@Numero BIGINT) RETURNS BIGINT AS BEGIN RETURN (ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) * @Numero END GO |
Random test data generation
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | IF (OBJECT_ID('dbo.Testes') IS NOT NULL) DROP TABLE dbo.Testes CREATE TABLE dbo.Testes ( Nome VARCHAR(60), Dt_Nascimento DATETIME, CPF VARCHAR(11) ) -- Carga inicial INSERT INTO dbo.Testes SELECT CAST(NEWID() AS VARCHAR(50)) AS Nome, DATEADD(DAY, dbo.fncRand(12000), '1980-01-01') AS Dt_Nascimento, RIGHT(REPLICATE('0', 11) + CAST(dbo.fncRand(99999999999) AS VARCHAR(11)), 11) AS CPF DECLARE @Contador INT = 1, @Total INT = 16 WHILE(@Contador <= @Total) BEGIN -- Executo esse trecho várias vezes INSERT INTO dbo.Testes SELECT CAST(NEWID() AS VARCHAR(50)) AS Nome, DATEADD(DAY, dbo.fncRand(12000), '1980-01-01') AS Dt_Nascimento, RIGHT(REPLICATE('0', 11) + CAST(dbo.fncRand(99999999999) AS VARCHAR(11)), 11) AS CPF FROM dbo.Testes SET @Contador += 1 END |
Example table with random data:
Once the table with the test data has already been created and populated, simply configure the parameter override in the software:
That's it folks!
I hope you enjoyed this post and this great tool for testing queries and the processing limit of your SQL Server instance.
A hug and see you next.
