Skip to main content

SQL 2012 AlwaysOn: Synchronous vs. Asynchronous commit. Performance impact

Recently I've had a chance to build a 3-server AlwaysOn environment distributed between the primary and secondary data centers.

The configuration looks like this:

Primary Data Center                         Secondary Data Center              
         SQLDEV1                                        SQLDEV3
         SQLDEV2


The availability group was crated with synchronous commit replicas on SQLDEV1 and SQLDEV2 and the replica on SQLDEV3 was configured for asynchronous commit.

The link between the data centers was not great and when I pinged SQLDEV3 from SQLDEV1 I got these results


Approximate round trip times in milli-seconds:
    Minimum = 39ms, Maximum = 63ms, Average = 42ms
I also created a very simple application that:
- generates a random INT
- if the table has a record with such key its updates the record otherwise it adds a new record to the table.
- every 5 seconds the app also records to a different (result) table StartTime, EndTime, and total number of inserts/updates

 And I query the result table and sum up totals updates occurred in past 10 seconds.

 I executed this application with 4 concurrent users from SQLDEV1 and the test database was also hosted on the same server.

 Then the test database was not a part of the availability group the average number of transaction per second was 5,500-6,000.

 When the test database was a part of the availability group with synchronous commit to SQLDEV2 and asynchronous commit to SQLDEV3 the average number of the transactions was 2,800 - 3,300

 And in the final test I changed the replica on SQLDEV3 to by in the synchronous-commit mode. And the average number of the transactions dropped to 40-60 per second (yes, decrease of two orders of magnitudes).

 Certainly, this is not a valid performance  tests but nevertheless it gives some feeling of the performace impact ones can expect when dealing with AlwaysOn.




Comments

  1. Dustin W. Jones3 April 2013 at 11:38

    would you be able share the tool you created for your test? Also, how did you measure transactions per second? I am working on a similar test myself.

    ReplyDelete
    Replies
    1. S.Mizrohi3 April 2013 at 12:17

      Hi Dustin,

      I've posted the script here

      http://smizrohi.blogspot.ca/2013/04/a-simple-script-to-test-server.html

      Let me know if it works for you

      Regards,
      Serguei

      Delete
  2. Azure DevOps6 November 2018 at 03:19

    Your new valuable key points imply much a person like me and extremely more to my office workers. With thanks; from every one of us.
    Amazon Web Services Online Training

    ReplyDelete
  3. Sowmiya R24 July 2020 at 23:59

    Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site. This article is very much helpful and i hope this will be an useful information for the needed one.Keep on updating these kinds of informative things.
    oracle training in chennai

    oracle training institute in chennai

    oracle training in bangalore

    oracle training in hyderabad

    oracle training

    oracle online training

    hadoop training in chennai

    hadoop training in bangalore





    ReplyDelete

Post a Comment

Popular posts from this blog

Joining Windows 10 to Azure AD Domain

As of October 2016 to join Windows 10 computers to Azure AD Domain service requires these steps: Create a VNET in the classic portal . The VNET must be placed to a region where Azure AD domain service is available (( https://azure.microsoft.com/en-us/regions/services/ )  In the classic portal  go to Directory -> Configure and enable the domain service. And wait for ~ 30 min When completed the IP address will be populated Go back to the VNET configuration and add a DNS server with the IP (10.0.0.4 in this case) Create the "AAD DC Administrator" administrators group (again in Directory -> Group). Members of this group are granted administrative privileges on machines that are domain-joined to the Azure AD Domain Services managed domain. Add to the group your users who are supposed to have the administrative access on a Windows 10 computer go to Settings -> Accounts (this is true for Windows 10 version  1607) then select 'Access work
1 comment
Read more

Create 3-Node Windows 2012 Multi-subnet Cluster

Environment There are two Data centers connected via a WAN link. Two Windows 2012 Servers (called SQLDEV1 and SQLDEV2) are located in the Primary Data Center (on the IP subnet 192.168.79.0/24) and the third server is placed in the Secondary Data Center with the 192.168.69.0/24 subnet. We’ll be creating a three-node Windows cluster with no shared storage on the multi subnet network with a file share witness at the Primary Data Center. We’ll be using a file share witness to protect from the cluster failure in a situation when the network between the Data Centers is unavailable and one of the servers in the Primary Data Center is also down (or being rebooted). The final state will look like depicted above: -           Two Virtual IP’s will be assigned (192.168.76.218 and 192.168.69.134) to the cluster -           The servers at the Primary Data Center will have a vote (Vote=1) and the server at the Secondary Data Center will have no vote (Vote=0). The file share witness al
2 comments
Read more

Generate Calendar Table in Power BI with Fiscal Year Attributes

In Power BI go to Get Data --> Blank Query and paste into the Function windows the text below. This function takes as parameters: - StartYear (e.g., 2012) - EndYear (e.g., 2018) -FiscalYearStartMonth (e.g., 4) And it will generate a calendar table for dates from Jan-1-<StartYear> till Dec-31-<EndYear> and columns for Fiscal Year, Fiscal Month, Fiscal Quarter, Fiscal Year Day where the Fiscal year begins on FiscalYearStartMonth = (StartYear as number, EndYear as number, FiscalYearStartMonth as number)=> let     //Capture the date range from the parameters     StartDate = #date(StartYear, 1, 1),     EndDate = #date(EndYear, 12, 31), //Get the number of dates that will be required for the table     GetDateCount = Duration.Days(EndDate - StartDate)+1, //Take the count of dates and turn it into a list of dates     GetDateList = List.Dates(StartDate, GetDateCount,     #duration(1,0,0,0)), //Convert the list into a table     DateListToTable
2 comments
Read more