none
SQL Server Page Blobs and high latency

    Question

  • Hi,

    I am using SQL Server on Azure VMs, storing data files on Standard Page Blob Storage. (8 VMs, about 1000 dbs).

    My storage accounts created 3 years ago on Classic Portal. 

    Recently I checked account's latency and I can see that they have big difference on the measured latency. One is in the range of 20 ms and the other in the range of 59 ms. Also the best performing account have about 3 times the traffic of the other. The load is analogous and statistical spread on these accounts.

    When I created a new storage account in the new portal and did some benchmarks, I measured much more better performance.

    Can someone explain these differences?

    SQL Server communicates with blob storage with stored SAS (Shared Access Signature). Is it possible to have differences in various versions of the storage API used to access the blobs?

    Thank you,

    Dimitris

    PS: The only use of these accounts is to store SQL Server Data Files. The load is much less than 20000 IOPS which is the limit for each account.



    • Edited by Dimitris V Tuesday, January 3, 2017 5:53 PM
    Tuesday, January 3, 2017 5:50 PM

All replies

  • Hi Dimitris,
    Thank you for contacting Microsoft forums. We are pleased to answer your query.
    Standard storage has varying latency and bandwidth and is only recommended for dev/test workloads. Production workloads should use Premium Storage. What VM size are you using? Please ensure you opt for DS3 or higher VM for SQL Enterprise edition and DS2 or higher for SQL Standard and Web edition.

    If you haven’t gone through it already, then we recommend going over Performance best practices for SQL Server in Azure VMs for a quick check list.


    Regards.

    Md. Shihab

    **************************************************************************

    Please remember to click "Mark as Answer" on the post that helps you as this can be beneficial to other community members reading the thread. And vote as helpful.

    • Proposed as answer by Md Shihab Monday, February 6, 2017 5:33 AM
    Wednesday, January 4, 2017 9:44 AM
  • Hi Md. Shihab,

    I use D12_V2 VMs (4 cores, 28 GB Memory) with SQL Server 2016.

    I cannot use premium storage. The reason is that I have many page blobs in the range of 1 - 4 GB that are used as database files on SQL Server (with the capapility 'Database Files on Azure'). The pricing model Microsoft has on Premium storage charges 128 GB for each (as P10 disk), even if it is only 1 GB. So the cost is too high and I am not using the full storage include in this price.

    I have implemented a failover mechanism to use these files with other VMs in case of failure automatically. If I use Premium storage I have to use disks which are much more difficult to move between VMs in case of failure.

    Thank you,

    Dimitris


    • Edited by Dimitris V Friday, January 6, 2017 5:35 PM
    Friday, January 6, 2017 5:33 PM