These types of tests you want to carry out call stress testing.
Stress Test
Tools
In the sql server case, there is a tool called sqlquerystress , which can help you do this test. With it you can:
- Check the behavior of the instance with a certain amount of running sessions
- Run multiple queries at the same time to stress CPU, disk and server memory and verify their behavior
- Simulating 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 gained on the day
- Run a query or SP multiple times, with random parameters and analyze their behavior
How to use
After downloading the tool executable, you will see the SQLQueryStress splash screen. Click the "Database" button to configure the connection to the database.
I do not recommend using this software in production environments, unless it is a scheduled maintenance schedule and you want to do stress testing on the instance.
SQLQueryStress home screen
In this screen, you can enter the server name \ connection instance, as well as authentication, user, and password type data.
Database connection configuration screen
Back to the initial screen, we will define the query that will 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 be executed in parallel (at the same time)
- Delay between queries (ms): Number of milliseconds between the end of a 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, that is, if you put 10 iterations with 5 threads, your query will be executed 50 times. >
Source: link
