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

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

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 always has a vote -The cluster network na…