simulating sql server production database load.

Question

hello,
is there any tool available that i can use to simulate production load in our non production environment. Our non prod environment does not have transaction load as that of our production environment, hence if we want to test any settings or configuration changes its never an apple to apple comparison of the production load.

Is there any way to simulate production database like load into non prod environment?

Answer 1

Yes, using Profiler for this task is a really bad idea that can take down your system if you have a chatty workload that runs many short statements.

What you do is to set up the Trace to your liking in Profiler and maybe let it run for a few seconds as a test. Then you stop it, and from the File menu you select Export and Script Trace Definition. This gives you and SQL file that starts the trace. You need to edit the script to set the file name, and you may also want to change the file size, or else you may get a myriad of rollover files. Be sure to check the documentation for sp_trace_create so that you understand the parameters.

Now you will get a server-side Trace. It will still add an overhead, not the least if the system has the characteristics that I outlined above. But it is nowhere as severe as Profiler, because there is now UI slow things down.

When you set up a trace, be sure to set a stop time for the trace. You can do that on the first page when you set up the trace in Profiler.

Answer 2

Hi @SQLServerBro,

You can start from below blog written by Brent Ozar, it shares us many tools for loading test, and gives some introductions and comparisons of these tools.

Free SQL Server Load Testing Tools

Hope this could help.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

SQL Server comes with Distributed Replay, which is intended for exactly this sort of thing. You record data for a day with Trace, and you can replay that trace in Production.

The feature as such is available in all editions of SQL Server, but it is only in Enterprise Edition, you actually can distribute the replay over many nodes. (And only Enterprise! This is the only feature where Developer is different from Enterprise.)

I have never used Distributed Replay myself, and I can imagine that it takes some work to set it up.

Answer 4

Thank you both. That leaves me with one more question. I've heard lot of bad things about running sql profiler. I myself have run sql profiler in production capturing 10-12 events and ran it for 30 mins or so. So my question is , at what point in time running sql profiler start hurting your performance. Or is there any way to run sql profiler without doing a performance hit.? It looks like to run these kind of load test, profiler usage is important and i may have to run it for at least few hours.