This is inspired by this StackOverflow question. I have this code:
SELECT CONVERT(INT, SUM(reserved_page_count) * 8 / 1024) FROM sys.dm_db_partition_stats
for detecting database space consumption and it returns exactly the same result as SQL Azure management portal pie chart. The problem is the number fluctuates greatly without apparent reasons – sometimes it’s around 35% and sometimes it’s around 54%.
This is kinda worrying – I don’t get what happens and the major concern is that we suddenly consume all the space and our service comes to a halt.
I seriously tried to look into
sys.partitions but they look like a pile of magic numbers to me.
What data should I use to find the root cause of the fluctuations?
I’d have you look at tables and indexes with the following code:
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as [SizeinMB] 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 ORDER BY [SizeinMB] DESC
You will want to check the size of the objects at regular intervals to see if you can determine which ones are in flux.
I would start by looking at the tables individually to see if this is caused by a single table or if it is a global phenomenon. You can use this query for that:
SELECT OBJECT_SCHEMA_NAME(ps.object_id)+'.'+OBJECT_NAME(ps.object_id) Tbl, SUM(ps.used_page_count) AS used_page_count, SUM(ps.reserved_page_count) AS reserved_page_count, SUM(CASE WHEN ps.index_id<2 THEN ps.row_count ELSE 0 END) AS row_count FROM sys.dm_db_partition_stats ps JOIN sys.tables t ON ps.object_id = t.object_id GROUP BY ps.object_id ORDER BY reserved_page_count DESC;
Store the results either in a new table or in a spreadsheet and compare over time.
If it is caused by a single table (or a small group of tables) you should check if this is expected behavior. This could happen for example if besides of inserts that are also deletes (think regular purging) executed against that table.