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. Xudong You15 December 2014 at 22:06

    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. S.Mizrohi16 December 2014 at 09:14

      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
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

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 numbe
4 comments
Read more