How to simulate load in a database?

Question

I have an SQL Server database that recently had performance problems in production by the large amount of simultaneous users added to some implementation and architecture problems.

After taking some measures to minimize problems, I need to test these changes without being on the production server.

How to simulate such a load, from a number of users (on average 4000 machines) and many darlings to see if such changes have taken effect?

I don’t know if this question fits the case that already proposes a solution (XY problem). So, what other way would I have to test my changes?

Answer 1

These types of tests you want to perform call stress testing.

Stress Test

Tools

In the case of sql server, there is a tool called sqlquerystress, that can help you take this test. With it it is possible:

• Check instance behavior with a certain amount of running sessions
• Run multiple queries at the same time to stress the server’s CPU, disk and memory and check its behavior
• Simulation of a critical environment with multiple transactions per second during system testing and SQL queries
• Identify how many times a SP is executed per day, perform an optimization and simulate what would be the time gain in the day
• Run a query or SP multiple times, with random parameters and analyze its behavior

How To Use

After downloading the tool executable, 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 environments, unless it is at a scheduled maintenance time and you want to do stress testing in the instance.

Sqlquerystress start screen On this screen, you can enter the server name connection instance as well as authentication type data, user and password.

Database connection configuration screen Back to the start screen, let’s define the query that will be executed, the execution parameters and start processing.

Parameters of execution: - Number of Iterations: The number of times the query will be executed - Number of Threads: Number of sessions to be run in parallel (at the same time) - Delay between queries (ms): Amount of milliseconds between the end of a query and the beginning of the next one.

Note 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:https://www.dirceuresende.com/blog/sql-server-utilizando-a-ferramenta-sqlquerystress-para-otimizar-suas-consultas-sql/