Skip to main content

Azure SQL Database Scalability for IO and CPU bound scenarios

In the previous post I ran the test with 5 concurrent clients and in this post I present results fgenerated by the same test but executed with different numbers of concurrent users:


  1. Azure SQL databases Standard (S0, S1, S2) and Premium (P1, P2, P3) editions were used
  2. During all test only a single Azure SQL database existed on the server at any given time
  3. The tests were executed for 200 seconds with 1,2,3,5, and 10 concurrent clients and after completion the test for a given number of the concurrent users all tables were dropped and re-created 
  4. Three tests were executed for each edition and number of concurrent users and the average number of transactions was calculated for the table below
  5. After completion the tests against a particular database edition the database was dropped and a new one was created in the next edition and/or tier (e.g., when all 5 tests have been completed against the S0 database that database was dropped and a new one was created in the S1 edition/tier)


The results are presented in this table:


The chart below shows the number of transactions reported for each database performance level and number of concurrent clients



The results show that Azure databases in the Standard edition (for any performance level - S0, S1, and S2) reach their maximum numbers of IO's (as it's defined by Microsoft for each of the levels) with a single client (because the test generates lot's of INSERT/UPDATE operations).

For the Premium edition adding concurrent users does increase the overall performance but only level P3 demonstrates ability to scale up to 10 clients for the given performance test (I did run the test with 15 concurrent users and for level P3 the results were only marginally better by 2.5-2.8 %).

Thus, if your application is IO bound and must handle multiple concurrent active connections then run your tests to choose the database edition and performance level than would meet your needs.

To test how well the Azure SQL databases are able to handle CPU bound load I used this test - calculate 200,000 times SHA-512 hash value for a 1280-byte string.
The results shows that the performance levels at the Premium edition seem to have identical internal cap on CPU usage but for the standard edition the maximum CPU utilization allowed per connection is actively throttled.



Comments

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