March Madness – SQL Azure – sys.dm_db_partition_stats – Row Cost

Yesterday we talked about how you could find the size of your database, and the size of objects inside of your database. Today we are going to go one level deeper and look at the sizes of rows inside of SQL Azure. We will be using systems views that have already been covered previously in the first six installments of this series.

Why are we doing this?

Simple: Because by doing so we will be able to calculate just how much that one row costs.

Think about that for a moment. Have you ever wanted to be able to show someone just how much their bad database design is costing the company in terms of storage, or I/O operations that wear out disks faster, or clog the network, or in the amount of time it takes for queries to finish? With SQL Azure you can quickly and easily determine just how much each row is costing you.

Stop and think about that thought, again, for a moment.

Consider that you will be able to see just how much it would cost you to store the data for one customer. Let’s say you find out that it costs $0.01, which doesn’t sound like much. But, now let’s say that you have 1,000,000 customers. That would mean it would cost you $10,000 a month just to store the customer details. That’s a kick in the teeth, huh? And it might make you rethink how you have designed your database.

Let’s take a look at how we got here so far.

1. I can calculate the estimated cost for my database.
2. I can find the size of my database, broken down by object.

We will use the concepts from those two blog posts to arrive at our final result below. Before we get there I need to break down the number of rows for the objects as well as the number of bytes per row. The following code will get me there:

SELECT sys.objects.name, sum(reserved_page_count) * 8192 [Bytes],
row_count [Row Count],
(CASE row_count WHEN 0 THEN 0 ELSE
(sum(reserved_page_count) * 8192)/ row_count END) [Bytes Per Row]
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name, row_count
ORDER BY [Bytes Per Row] DESC

I now have all of the information that I need in order to find the cost per row. The downside to this is that I can only connect to one database at a time, and I need to get the cost details from master and the size details from the user database. That means I will either need to input the cost details manually, or I need to create a table in my user database that just holds the tiered pricing details. I don’t like the second idea because that is way more overhead than what I need here, so I am going to insert the cost details manually. [Yes, I am certain there must be a better way, and if anyone has a one-script-fits-all solution here I would love to see it…I’m looking at you, Buck Woody (blog | @buckwoody).]

Another item worth noting is that the code used to estimate the billing costs is a summary of all databases for your SQL Azure instance. So if you have three Web edition databases then you need to be aware that your costs are being returned as a sum of all three. For Web editions the pricing is very straightforward, but for Business editions things get a bit more complicated. I would simply make my life easy by just looking at the percentages overall. If I had three Business edition databases sized at 6, 12, and 18GB and my estimated costs were $100, I would just input the costs for each as (roughly) $16.60, $33.20, and $49.80. That way I have a better idea about the costs for this row of data, in this database, for this SQL Azure instance.

OK, let’s walk through an example now. We will assume I have one Business edition database of (roughly) 37GB in size, which would cost (also roughly) about $100 per month. I could then run the following code to get the cost per byte:

DECLARE @SizeInBytes bigint
SELECT @SizeInBytes =
(SUM(reserved_page_count) * 8192)
FROM sys.dm_db_partition_stats
DECLARE @Cost float
DECLARE @CostPerByte float
SET @Cost = 100.00

SELECT @CostPerByte = @Cost/@SizeInBytes
SELECT @CostPerByte AS [CostPerByte]

I can then take that value (in this example it is roughly $0.00000257749) and use it in the following code:

SELECT sys.objects.name, sum(reserved_page_count) * 8192 [Bytes],
row_count [Row Count],
(CASE row_count WHEN 0 THEN 0 ELSE
(sum(reserved_page_count) * 8192)/ row_count END) [Bytes Per Row],
(CASE row_count WHEN 0 THEN 0 ELSE
((sum(reserved_page_count) * 8192)/ row_count)
* (0.00000257749) END) [Monthly Cost Per Row]
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name, row_count
ORDER BY [Bytes Per Row] DESC

And now I can have an idea as to how much one row of data inside a table is costing me in SQL Azure. My hope is that this may force some people to rethink the way they have done their database design in the past. Tomorrow we will build upon this example to look at other database design choices that can affect cost.