We are planning to enable Multi AZ on our production database which uses Amazon RDS as a managed service. Our Database is using SQL Server 2012 standard version.

Before enabling that, I wanted to simulate our production workload on a dummy database to check how the read write latency are getting affected after making the dummy database as Multi AZ.

Whats the best way to do the above ?

  • 1
    Generally enabling the multiaz will create a bit latency in both read and write operations. SQL servers are using database.mirroring (Brent (who answered this question) already wrote a blog about this, since you are a sql server person so just take a look ). But yeah you must do a benchmark to compare the latency.
    – TheDataGuy
    Jul 6, 2018 at 14:46
  • One option is to use SQL Agent and ostress: Simulating Workload With ostress And Agent Jobs. Jul 6, 2018 at 18:40

2 Answers 2


I like Brent’s idea, nice and simple and will give you an indication of what you need. Here are also options to replicate the actual real workload:

  • record it in production and replay it using SQL distributed replay

  • manually capture select statements using profiler (may be tricky)

  • involve more advanced performances testing tools such as Hammer DB, SQLStress

  • write your own queries and in SSMS run them in different tabs with GO x, where x is the number of iterations (yes SSMS can do this) to simulate concurrent workload (although that’s more or less what SQLStress would do)

Hope this helps


The fastest, easiest way is to rebuild an index on a large table.

That pushes a lot of write activity through, and it’s consistent, easily repeatable, and easily timed.

It’s not your workload (or anything near it) but helps you get a fast idea of the overhead right away.

  • Hi @Brent, To compare the overhead before and after enabling Multi AZ , Are you suggesting that I pickup an index which has a lot of write activity and then measure the time taken while rebuilding index for that specific table both before and after ? Can i use the same index to rebuild twice for before and after Multi AZ? Jul 10, 2018 at 6:17
  • 1
    When you rebuild an index, it doesn't matter if it's frequently written or not - an index rebuild builds a new copy of the index from scratch.
    – Brent Ozar
    Jul 10, 2018 at 9:45
  • Hi @Brent, which approach shall i take for rebuilding indexes ? 1. Using SSMS, rebuild task or via script ? If its through script , can you share some useful links. Jul 17, 2018 at 10:41
  • 1
    – Brent Ozar
    Jul 17, 2018 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.