March Madness – SQL Azure – sys.database_usage

Yesterday we talked a bit about the sys.databases system view in SQL Azure. It contains some decent information about your SQL Azure databases but what it doesn’t tell you is the one thing many folks want to know about their SQL Azure databases; what is this costing me?

What is this system view for?

This system view will return one row for each day of your subscription and list the date, the subscription edition, and the maximum number of databases of that edition that existed during that day.

Why should you care?

You would use this system view to track your costs over time. Most people track database usage with on-premise editions of SQL Server already but they rarely equate their usage patterns to a cost. I know many DBAs that could tell me the growth pattern for their databases over the past year but they have little to no idea about the actual costs associated with that usage.

Last December Microsoft slashed their prices for SQL Azure. Prior to that announcement, you were able to run this code to find out your estimated costs based upon the sys.database_usage view (courtesy of Glenn Berry):

-- Get overall cost by SKU in dollars
 SELECT SKU, SUM (CASE WHEN USAGE.SKU = N'Web'
 THEN (Quantity * 9.99/31)
 WHEN USAGE.SKU = N'Business'
 THEN (Quantity * 99.99/31)
 END ) AS [CostInDollars]
 FROM sys.Database_Usage AS USAGE
 WHERE DATEPART(yy, TIME) = DATEPART(yy, GetUTCDate())
 AND DATEPART(mm, TIME) = DATEPART(mm, GetUTCDate())
 GROUP BY SKU;

Here is what I have from the Azure website for database sizes and price per database per month:

• 0 to 100 MB – Flat $4.995
• Greater than 100 MB to 1 GB – Flat $9.99
• Greater than 1 GB to 10 GB – $9.99 for first GB, $3.996 for each additional GB
• Greater than 10 GB to 50 GB – $45.954 for first 10 GB, $1.998 for each additional GB
• Great than 50 GB to 150 GB – $125.874 for first 50 GB, $0.999 for each additional GB

This is only slightly confusing when you compare to the code above because there is no mention of Web or Business in the pricing details. Those names really have no meaning as far as features go, they are really just marketing terms. And yet they are embedded into this system view that we are given to determine pricing. The old code will still work for the “Web” edition up to 1GB, because that is a flat price of $9.99. But what about for larger Web editions/ And What about the tiered pricing for “Business” editions? Will the code still work if I use the old $99.99 price?

I believe it will because SQL Azure will insert the correct fractions for usage. So I created a few extra databases, pumped some of them with data, made copies, and waited for the daily usage info to get updated.

In my case, I was being billed at .5 units per day, and $4.995 is one half of $9.99, so that code works when it comes time for me to calculate my bill because the base value for Web is still $9.99. So, I am going to load up some data this morning, about 52GB worth, and let’s see what happens when my usage is updated later tonight.

OK, I’m back (did you miss me?) This is what I get back from a query against the sys.database_usage system view:

 

And, here is what I get back when I run the code above:

 

And here is what I see on the billing page for my Azure account right now:

The code still works! And somehow this makes complete sense to me. SQL Azure is going to perform some voodoo on their end so that the usage fractions are updated according to the new pricing, so we don’t have to worry about changing our summations. Think of it this way: they have a table somewhere with one column that has valued from 0 to 150 (the sizes of databases allowed in SQL Azure, in GB). Next to each of those values they have a fraction. Nightly they have a process that simply sums your usage, and spits out a number (in my case, a 2.1), with which you can multiply by $99.99 and divide by 31 to get your daily cost.

Well, that’s my theory anyway. And until someone from the SQL Azure team can tell me otherwise, I’m sticking to my theory.

Tomorrow we will look at another system view that will help us determine our usage information.