Skip to main content

SQL 2014 performance - Local disk vs. Azure Blob vs. Azure VM attached disk


Today I decided to compare SQL 2014 (RTM) performance when running a test against

 - a local database (created on  WD7500BPKT - 750 GB 7200 RPM)
 - a DB created on a disk attached to the A3 (3 cores, 7 GB) VM in Azure
- a DB created on an Azure blob

The VM in Azure was created in the US East region using the SQL 2014 template from the gallary provided by Microsoft.


All databases were created with a single 10 GB data file (10 GB) and 1GB  log file.

On the local SQL instance the DB was created as

CREATE DATABASE TestDBonLocal
ON 
   (NAME = file_data1, FILENAME = 'C:\TEMP\filedata1.mdf', SIZE = 10GB, FILEGROWTH = 256 MB )
 LOG ON 
(NAME = file_log1, FILENAME = 'C:\TEMP\filelog1.ldf', SIZE = 1GB, FILEGROWTH = 32 MB) 


On the Azure VM the database on the attached disk (the disk was mapped as drive F:) was created as such

CREATE DATABASE TestDBonDisk
ON 
   (NAME = file_data1, FILENAME = 'F:\TMP\filedata1.mdf', SIZE = 10GB, FILEGROWTH = 256 MB )
 LOG ON 
(NAME = file_log1, FILENAME = 'F:\TMP\filelog1.ldf', SIZE = 1GB, FILEGROWTH = 32 MB) 


And the second database on the same Azure VM was created with data and log files stored in the Azure Blob:

CREATE DATABASE TestDBonAzure
ON 
   (NAME = file_data1, FILENAME = 'https://sql2014storage99.blob.core.windows.net/sqldata/filedata1.mdf', SIZE = 10GB, FILEGROWTH = 256 MB )
 LOG ON 
(NAME = file_log1, FILENAME = 'https://sql2014storage99.blob.core.windows.net/sqldata/filelog1.ldf', SIZE = 1GB, FILEGROWTH = 32 MB) 

The test table has the following structure

CREATE TABLE [dbo].[T1](
[ID] [int] NOT NULL,
[NumOfUpdates] [int] NULL DEFAULT ((0)),
[LastSPID] [int] NULL,
[DummyText] [varchar](1024) NULL,
[UpdateTime] [datetime2](7) NULL,
[CreateTime] [datetime2](7) NULL DEFAULT (sysdatetime()),
 CONSTRAINT [PK_dbo_T1_ID] PRIMARY KEY CLUSTERED 
) ON [PRIMARY]


The test was running from 5 clients doing the following:
- drop and create the test table
- generate a random ID - integer within the [0, 2000000] range
- if a table has no row witch such ID then insert a new row
- if there is a row with such ID then update the row with the UpdateTime and a new DummyText (randomly generated length)

I ran the test 6 times against each of the databases. Results from the first tests were discarded and the the following 5 test results were averaged.

As you can see SQL 2014 in  Azure cannot beat the locally installed SQL instance when accessing the direct attached disk.
But using Azure Blob for storing the databases files brings 40%  performance improvement over database files stored on a disk attached to the Azure VM.



N.B. Database with Filestream and in-memory tables are not supported on the Azure Blob storage.




Comments

  1. Interesting post. Thanks sharing.
    I have a question, any insight that the Azure Blob way has better performance than attached disk way?
    Seems to me, the attached disk way is just same as local disk way?

    Thanks!

    ReplyDelete
    Replies
    1. Hi Xudong You,
      Thanks for the comment.

      Based on what MS says (http://blogs.msdn.com/b/windowsazurestorage/archive/2014/05/12/introducing-microsoft-azure-file-service.aspx ) about its Azure storage I'd says that the local disk (if you mean drive C:) is the same as the attached disks.
      the only technical document about Azure storage architecture I'm wawre of is this one (and it's a good one)
      http://www-bcf.usc.edu/~minlanyu/teach/csci599-fall12/papers/11-calder.pdf


      HTH,
      Serguei


      Delete

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

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

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