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
(NAME = file_data1, FILENAME = 'C:\TEMP\filedata1.mdf', SIZE = 10GB, FILEGROWTH = 256 MB )
(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
(NAME = file_data1, FILENAME = 'F:\TMP\filedata1.mdf', SIZE = 10GB, FILEGROWTH = 256 MB )
(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
(NAME = file_data1, FILENAME = 'https://sql2014storage99.blob.core.windows.net/sqldata/filedata1.mdf', SIZE = 10GB, FILEGROWTH = 256 MB )
(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.