Cost estimation for SQL Server Azure VMs

  • Deepak Prasad Sahu

    Ten Centuries

    Points: 1091

    Comments posted to this topic are about the item Cost estimation for SQL Server Azure VMs

  • Jeff Moden

    SSC Guru

    Points: 994555

    Ok... what does a "Storage Transaction" actually consist of?  I ask because many of the hits are pretty large batches.  By the same token, we also have tens of million single row hits per hour (and that's just from one app).  If each one of those is going to cost $0.0004, then there's going to be hell to pay when someone sees the bill for a month. πŸ˜‰

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Deepak Prasad Sahu

    Ten Centuries

    Points: 1091

    Hi Jeff,

    Storage Transactions are the number of requests performed against your storage account.

    Storage Transactions consists of Each individual Blob, Table and Queue REST request to the storage service is considered as a potential transaction for billing. Applications can then control their transaction costs by controlling how often and how many requests they send to the storage service. We analyze each request received and then classify it as billable or not billable based upon our ability to process the request and the request’s outcome.

    How Transactions are Counted
    The first area we would want to cover for transactions is what equals 1 transaction to Windows Azure Storage. Each and every REST call to Windows Azure Blobs, Tables and Queues counts as 1 transaction (whether that transaction is counted towards billing is determined by the billing classification discussed later in this posting). The REST calls are detailed here:

    Blobs
    Tables
    Queues
    Each one of the above REST calls counts as 1 transaction. This includes the following types of requests:

    Query/List Requests and Continuation Tokens – A Table Query, and Listing Blob Containers, Tables and Queues can return continuation tokens. This means that the query/listing must be continued to complete it. As described above, each REST request to the storage service counts as 1 transaction. Therefore, each continuation of the query/list counts as an additional 1 transaction, since it is another REST request to the storage service.
    Batch Operations– We currently support two types of batch operations:
    Get Messages - the ability to get up to 32 messages at once from a Queue.
    Entity Group Transactions – the ability to perform an atomic transaction across up to 100 entities with any combination of Insert, Update, or Delete for Azure Tables. The requirement is that all of the entities have to be in the same table and have the same PartitionKey value and the total request size must be under 4Mbytes.

    From the pricing perspective the standard managed disks now following the pricing of premium managed disks. That means you are billed for the disk size and not for used GB per month. For standard managed disks you will be also charged for the number of storage transactions.

    Both types of managed disks, standard and premium, will have a charge for the managed disk snapshots and outbound data transfer.

    Have a look at the following table to get an overview.

                                                                   Standard Unmanaged    Standard Managed        Premium Unmanaged    Premium Managed
    Storage price unit -                                 Used GB per month        Disk size                          Disk size                        Disk size
    Storage transaction charge -                         Per 100.000             Per 100.000                              –                                  –
    Snapshot charge-                                   Used GB per month              –                               Used GB per month                –
    Outbound data transfer -                           GB per month             GB per month                   GB per month              GB per month

    If you have tens of million single row hits per hour (and that's just from one app), would suggest to go for Premium managed/unmanaged storage which doesnot have any charge for storage transactions.
    Thanks,
    Deepak Prasad Sahu.

  • Jeff Moden

    SSC Guru

    Points: 994555

    Deepak Sahu - Tuesday, December 4, 2018 4:13 AM

    Hi Jeff,

    Storage Transactions are the number of requests performed against your storage account.

    Storage Transactions consists of Each individual Blob, Table and Queue REST request to the storage service is considered as a potential transaction for billing. Applications can then control their transaction costs by controlling how often and how many requests they send to the storage service. We analyze each request received and then classify it as billable or not billable based upon our ability to process the request and the request’s outcome.

    How Transactions are Counted
    The first area we would want to cover for transactions is what equals 1 transaction to Windows Azure Storage. Each and every REST call to Windows Azure Blobs, Tables and Queues counts as 1 transaction (whether that transaction is counted towards billing is determined by the billing classification discussed later in this posting). The REST calls are detailed here:

    Blobs
    Tables
    Queues
    Each one of the above REST calls counts as 1 transaction. This includes the following types of requests:

    Query/List Requests and Continuation Tokens – A Table Query, and Listing Blob Containers, Tables and Queues can return continuation tokens. This means that the query/listing must be continued to complete it. As described above, each REST request to the storage service counts as 1 transaction. Therefore, each continuation of the query/list counts as an additional 1 transaction, since it is another REST request to the storage service.
    Batch Operations– We currently support two types of batch operations:
    Get Messages - the ability to get up to 32 messages at once from a Queue.
    Entity Group Transactions – the ability to perform an atomic transaction across up to 100 entities with any combination of Insert, Update, or Delete for Azure Tables. The requirement is that all of the entities have to be in the same table and have the same PartitionKey value and the total request size must be under 4Mbytes.

    From the pricing perspective the standard managed disks now following the pricing of premium managed disks. That means you are billed for the disk size and not for used GB per month. For standard managed disks you will be also charged for the number of storage transactions.

    Both types of managed disks, standard and premium, will have a charge for the managed disk snapshots and outbound data transfer.

    Have a look at the following table to get an overview.

                                                                   Standard Unmanaged    Standard Managed        Premium Unmanaged    Premium Managed
    Storage price unit -                                 Used GB per month        Disk size                          Disk size                        Disk size
    Storage transaction charge -                         Per 100.000             Per 100.000                              –                                  –
    Snapshot charge-                                   Used GB per month              –                               Used GB per month                –
    Outbound data transfer -                           GB per month             GB per month                   GB per month              GB per month

    If you have tens of million single row hits per hour (and that's just from one app), would suggest to go for Premium managed/unmanaged storage which doesnot have any charge for storage transactions.
    Thanks,
    Deepak Prasad Sahu.

    So a simple SELECT that does an Index Seek to a single row and return just that row is considered a transaction in this case?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Matt Miller (4)

    SSC Guru

    Points: 124180

    Jeff Moden - Monday, December 3, 2018 10:33 PM

    Ok... what does a "Storage Transaction" actually consist of?  I ask because many of the hits are pretty large batches.  By the same token, we also have tens of million single row hits per hour (and that's just from one app).  If each one of those is going to cost $0.0004, then there's going to be hell to pay when someone sees the bill for a month. πŸ˜‰

    Jeff - not sure I completely follow how this gets translated into English, but this might help a bit with the definitions.

    https://blogs.msdn.microsoft.com/windowsazurestorage/2010/07/08/understanding-windows-azure-storage-billing-bandwidth-transactions-and-capacity/

    I'm hoping that the price per transaction is low because it sounds like you would have a LOT of those suckers in a given month.If I follow the logic it would be substantially more granular than SQL server transactions.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Jeff Moden

    SSC Guru

    Points: 994555

    Matt Miller (4) - Tuesday, December 4, 2018 3:04 PM

    Jeff Moden - Monday, December 3, 2018 10:33 PM

    Ok... what does a "Storage Transaction" actually consist of?  I ask because many of the hits are pretty large batches.  By the same token, we also have tens of million single row hits per hour (and that's just from one app).  If each one of those is going to cost $0.0004, then there's going to be hell to pay when someone sees the bill for a month. πŸ˜‰

    Jeff - not sure I completely follow how this gets translated into English, but this might help a bit with the definitions.

    https://blogs.msdn.microsoft.com/windowsazurestorage/2010/07/08/understanding-windows-azure-storage-billing-bandwidth-transactions-and-capacity/

    I'm hoping that the price per transaction is low because it sounds like you would have a LOT of those suckers in a given month.If I follow the logic it would be substantially more granular than SQL server transactions.

    Thanks, Matt.  They're a bit non-specific when it comes to the definition of the word "entity", which seems to be a key to how they charge for data access.  It would appear from the way they use the word, that they're talking about a single row from a single table.  They later go on to describe a transaction as (paraphrased on my part) that a query that returns 500 rows from a single table is a transaction. 

    That little app I was talking about is a bit of 3rd party artificial intelligence (no comment) that analyses key words and the positions and proximity of those keywords on a document to identify what type of document it is and to which department it should be assigned to.  It takes about 8,000 rows of data to analyze a single key word (like I said, no comment)).  The long and short of it is that we have approximately 100 million such queries that execute in about 8-10 hours on a normal day.  8,000 rows per query / 500 = 16 transactions.  16*100 million is 1.6 Billion.  1.6 Billion times $0.0004 per transaction is $640,000.  That's per 8-10 hour DAY!  That's enough to pay for building a wicked good on-premise data center per day along with a year's rent!

    Of course, I could be reading all of this the wrong way but the cost calculation just for that 1 part of one app seems to be right according to what I've read.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Matt Miller (4)

    SSC Guru

    Points: 124180

    Jeff Moden - Wednesday, December 5, 2018 6:51 AM

    Matt Miller (4) - Tuesday, December 4, 2018 3:04 PM

    Jeff Moden - Monday, December 3, 2018 10:33 PM

    Ok... what does a "Storage Transaction" actually consist of?  I ask because many of the hits are pretty large batches.  By the same token, we also have tens of million single row hits per hour (and that's just from one app).  If each one of those is going to cost $0.0004, then there's going to be hell to pay when someone sees the bill for a month. πŸ˜‰

    Jeff - not sure I completely follow how this gets translated into English, but this might help a bit with the definitions.

    https://blogs.msdn.microsoft.com/windowsazurestorage/2010/07/08/understanding-windows-azure-storage-billing-bandwidth-transactions-and-capacity/

    I'm hoping that the price per transaction is low because it sounds like you would have a LOT of those suckers in a given month.If I follow the logic it would be substantially more granular than SQL server transactions.

    Thanks, Matt.  They're a bit non-specific when it comes to the definition of the word "entity", which seems to be a key to how they charge for data access.  It would appear from the way they use the word, that they're talking about a single row from a single table.  They later go on to describe a transaction as (paraphrased on my part) that a query that returns 500 rows from a single table is a transaction. 

    That little app I was talking about is a bit of 3rd party artificial intelligence (no comment) that analyses key words and the positions and proximity of those keywords on a document to identify what type of document it is and to which department it should be assigned to.  It takes about 8,000 rows of data to analyze a single key word (like I said, no comment)).  The long and short of it is that we have approximately 100 million such queries that execute in about 8-10 hours on a normal day.  8,000 rows per query / 500 = 16 transactions.  16*100 million is 1.6 Billion.  1.6 Billion times $0.0004 per transaction is $640,000.  That's per 8-10 hour DAY!  That's enough to pay for building a wicked good on-premise data center per day along with a year's rent!

    Of course, I could be reading all of this the wrong way but the cost calculation just for that 1 part of one app seems to be right according to what I've read.

    I think you forgot "gold-plated" in the description of that data center you're building πŸ˜›

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply