none
Why premium storage can't write 64k blocks faster than 50MB?

    Question

  • From Brian Keating (@brianbruff) via Twitter who tweets:

    “Do you have any documentation confirming why premium storage can't write 64k blocks faster than 50MB/s. 64k is what SqlServer writes. Specifically, why the storage is limited circa 50MB/s, even with 10 1TB disks and 62500 IOPS VM?”

    Tweet URL: https://twitter.com/brianbruff/status/758256406583705600

    Appreciate if you may be able to assist the customer on this matter.

    Thanks,
    @AzureSupport

    Wednesday, July 27, 2016 11:20 AM

All replies

  • Some additional information on this issue:


    I tried with 4 Premium 1TB disks, I went for a DS15 machine with 62.5k iops and 10 premium 1TB disks and

    everything was still much slower than a single 750MB provisioned I/O disk on AWS. (and slower than my own laptop with SATA3 SSD).

     

    So I benchmarked the disk around this block size and results correlate with the times of my perf tests

     

    Disk Benchmarks:

    Disk throughput @64K with ATTO disk benchmark 

    Azure: 46MB/s (with 10TB of Premium disks and 20 core machine with 62.5k IOPS)

    AWS: 140MB/s (8 core machine, 1 750MB Provisioned I/O disk with 20k IOPS)

    Local SSD: 250MB/s

     

    All tests were performed in Dublin Datacentre.

    Is azure expected to be slow at this block size?

    • Edited by Brian Keating Thursday, July 28, 2016 3:37 PM removed unnecessary information
    Wednesday, July 27, 2016 12:30 PM
  • Hi,

    Thank you for posting here!

    We are currently researching on this and will keep you posted with the findings.

    We appreciate your patience.

    Regards,

    Vikranth S.

    Friday, July 29, 2016 10:21 AM
    Moderator
  • Hi Brian,

    I am by no means a SQL performance expert, however I came across this whitepaper that was published this month on SQL server performance best practices in Azure VMs, I would recommend checking it out.

    https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-performance/

    --Brady

    Friday, July 29, 2016 11:34 PM
  • Hi Brady,

    HI Brady,

    tnx had seen that,

    in fact with azure market place they pretty much do it for you if you provision a sql server image.

    however fact remains that premium storage is unusually slow when 64k blocks are used.

    i'm not saying that the correlation between disk speed and time to load i've seen on aws,azure and my local machine at this block size indicates causation, but it would be good to eliminate it and figure out if there is reason it performs so poorly here.

    personally i'm hoping azure to have some issue that can be fixed as i can't currently sell it over AWS to my clients

    i'd be even happier if i'm the one doing something silly:

    but a maxed out Azure DS series VM with 10TB prem storage can't compete with a 8 core AWS machine with a single 750MB 20k provisioned iops disk, when i repeat the exact same setup on both clouds.

    CPU's are pretty much same so my feeling is that it's not that. 

    i'm doing nothing weird like having multiple tempdb's with different sizes etc.

    I am however writing big blogs to the DB and this is what's making me think it's disk related.

    kr,

    Brian.


    Tuesday, August 2, 2016 9:07 PM
  • Hey Brian,

    What queue depth are you running the benchmark at? In general we recommend 8-16 outstanding IOs per disk since Azure Storage is highly parallelized, so for 4 disks you would want a queue depth of 32-64 outstanding IOs, evenly distributed across the disks. If you're running it lower, bump it up to follow these recs and let us know how it goes.

    --Brady

    Friday, August 5, 2016 7:55 PM
  • Hi Brady

    I was running with queue depth of 64.

    Let me know if you are unable to reproduce my findings, I'm happy to spend 30 mins set up a VM and repeat my tests to ensure it wasn't just an anomaly I noticed at the time.

    Regards,

    Brian. 

    Monday, August 8, 2016 7:58 PM
  • any update on this ?

    tnx

    Monday, August 15, 2016 11:40 AM
  • Hi Brian,

    After reviewing the settings of your test - 62.5kIOPS, 64KB block size, and queue depth at 64, theoretically you should have gotten better results.

    We consulted with our engineering team, we suspect this may be due to disk misconfiguration - Disks should
    ideally be striped with a strip size of 64KB-256KB and 10 columns.
     You will need to perform the configuration through Powershell as UI will max out at 8 columns and would not get the expected configuration/ performance.

    If issue persists, we would like to gather additional details and help further investigating the situation.  

    Let us know how it goes.

    Tuesday, August 16, 2016 10:34 PM
  • ok i'll give it a go

    however I used you Azure Template for a best practice Sql Server and just selected 10 disk this way.

    to be honest i've had to move to AWS for the particular application, when i did a like for like install it was simply faster there

    but thanks for trying.

    Kind regards,

    Brian

    Friday, August 19, 2016 1:01 PM