Cost estimation for SQL Server Azure VMs

Question

Cost estimation for SQL Server Azure VMs

Deepak Prasad Sahu

Ten Centuries

Points: 1160
More actions
December 2, 2018 at 11:14 pm

#369215

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

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

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

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

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

Deepak Prasad Sahu Ten Centuries Points: 1160 More actions · Answer 2

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: 1003857 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

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: 1003857 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

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?