Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is the "Buffer Manager: Page writes/sec" (* 8 KBytes) a good indicator of how much bandwidth will be used to replicate all databases via Availability Groups?

If not, why are these not equivalent? Is there just some overhead or are they largely unrelated?

share|improve this question
add comment

2 Answers

Both AG and DBM will replicate primarily the log being generated on the principal. The buffer flush rate has some relation to the the log generation rate, but is not identical (think how 100 updates of the same row will generate 100 log records but will only flush the page once, and also think how a single insert could be described in maybe 256 bytes of log, but it dirties an entire page that, when flushed, writes 8kb) and the counters are not per database to start with.

There are some additional control messages and when AG is present there is extra logging, but not significant. I would say that the log itself is about 95% of the traffic. On one hand keep in mind that both DBM and AG can and will compress the log in traffic. But on the other hand DBM and AG will require fully logged operation (minimal logging cannot occur on a AG/DBM source). With AG the number of replicas also plays a role (at worse the traffic gets multiplied x times number of replicas).

You need to consider not only throughput, but also latency. In synchronous mode commits have to be hardened by the replica and this impact the operation latency. Check your current log flush rate in SQL Server, Databases Object.

Once established both DBM and AG have their own counters: SQL Server, Database Mirroring Object and SQL Server, Availability Replica. The raw traffic (including protocol overhead) can be measured also in SQL Server, Broker / DBM Transport Object. Of course, these require to first establish an AG and hit it with load :)

But for an estimate of number of bytes that will be sent (before compression), the log file virtual io stats, as suggested by mrdenny, is a very good aggregate indicator, but unfortunately it gives no information on the actual shape of the throughput (ie. spikes). Unfortunately there is no direct perfmon counter for that.

share|improve this answer
add comment

It is probably an OK indicator. Do keep in mind that AlwaysOn is shipping transaction log records not actual data. The amount of writes going to your transaction log LUNs is probably a better indicator.

share|improve this answer
1  
In my case it is on shared disk, is there DMV for transaction log writes? Maybe I could poll it every X minutes for a little while and convert that to a rate... –  Kyle Brandt Jun 5 '12 at 18:14
1  
Yep, sys.dm_io_virtual_file_stats will tell you the number of writes since the SQL instance had been restarted. Monitoring the IO to the log disk should give you a decent number to work off of. –  mrdenny Jun 5 '12 at 18:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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