1

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. – Bhuvanesh Jul 6 '18 at 14:46
  • One option is to use SQL Agent and ostress: Simulating Workload With ostress And Agent Jobs. – Erik Darling Jul 6 '18 at 18:40
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

2

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? – Sayantani Nath Jul 10 '18 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 '18 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. – Sayantani Nath Jul 17 '18 at 10:41
  • 1
    ALTER INDEX ALL ON dbo.MyTableName REBUILD; – Brent Ozar Jul 17 '18 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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