Skip to main content

SQL 2014 performance - Azure VM (SSD) vs. Local SSD vs. Azure DB (V12)

Microsoft has recently added a new G-series of VMs built on  the latest Intel Xeon processor E5 v3 family with plenty (up to 6.5 TB0 of  local Solid State Drive (SSD) space.
Microsoft has also made available (in preview mode) the latest Azure SQL Database V12 which provides nearly complete compatibility with the Microsoft SQL 2014 Server engine and promises better performance (in the Premium level).

I've decided to run my simple OLTP test against the database created:

  • on the local instance of SQL 2014 with data and log files placed on the SATA hard drive (labelled below as DELL (HDD))
  • on the local instance of SQL 2014 with data and log files placed on the SSD drive (labelled below as DELL (SSD))
  • on the SQL 2014 installed within a G2-series VM (4 cores, 56 GB) with data and log files placed on an attached 100 GB drive (labelled below as Azure G2)
  • on the SQL 2014 installed within a G2-series VM with data and log files placed on a virual disk created on top of the 8 x 100 GB attached disks (the disks were created in the US West region - the sme region where the G2 VM was located)
  • on the SQL 2014 installed within a G2-series VM (4 cores, 56 GB) with data and log files placed on the local SSD drive (labelled below as Azure G2(SSD))
  • within the Azure SQL V12 P3  (the highest performance level available) server (labelled below  Azure SQL V12 (P3)


The build number of the SQL server installed locally on my laptop (DELL M4800) and on the Azure G2 VM  was identical:

Microsoft SQL Server 2014 - 12.0.2430.0 (X64)

The SQL instance was configured with Instant File initialization enabled, and maximum server memory as configured as 20000 MB for the Azure VM and 10240 MB for the instance installed on the laptop.

The VM was created based on the Microsoft provided template with SQL 2014 installed


At that time the G2-series VMs were available in the US-West region


The CPU information returned by WMIC for the VM was as  follows

C:\>wmic cpu get name,CurrentClockSpeed,MaxClockSpeed

CurrentClockSpeed  MaxClockSpeed  Name
1995                     1995           Intel(R) Xeon(R) CPU E5-2698B v3 @ 2.00GHz



The same command on the laptop shows this information about the CPU

C:\Windows\system32>wmic cpu get name, CurrentClockSpeed, MaxClockSpeed

CurrentClockSpeed  MaxClockSpeed  Name

2494                      2494                  Intel(R) Core(TM) i7-4710MQ CPU @ 2.50GHz


And the disks installed in the laptop (as reported by the get-disk cmdlet) are here:

Friendly Name                            Total Size 
-------------                            ---------- 
LITEONIT LMT-256L9M-11 MSATA 256GB        238.47 GB 
HGST HTS725050A7E630                      465.76 GB 



And then I created a test Azure SQL V12 database as shown below




I executed the test for 500 seconds with 1, 5, 10, and 15 concurrent clients  and here are the average number of transactions reported:


And the same data in a chart



As the results show:

  • placing the database on the local SSD (DELL (SSD)) delivers the best performance and none of other options come even close to it
  • the second place goes to the case when the database is placed on the SSD disk in the  the Azure G2 VM, but unfortunately, the SSD disk is not persistent and placing production databases on a non-persistent storage is not an option
  • the new Azure SQL V12 database demonstrated a good performance and if compare this results with the same test ran against Azire SQL V11 we can clearly see 3-fold performance improvement in the current (still in preview mode) SQL version.
  • the second disk in the laptop (DELL (HDD)) gets almost 100% saturated even with a single client
  •  using a single disk attached to the Azure VM is just a fraction of a percent better then using a virtual disk created on top  volume comprising 8 attached Azure disks

Thus, if the application expects to handle a huge number of small transactions then a solution leveraging the local SSD disks is the best possible option.

  



  

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