Find records size in kb/mb/gb for one client from multiple table in one database [closed]

Posted on

Question :

I have multiple client using same database, i need to find client wise size utilized.

Answer :

Seems you want to know the size utilization per row, that’s not possible as the data basically stores in page format not in row format, each page can have maximum ~8KB of storage (may vary depending on fill factor), and NOT always a page can store same number of rows. It would be easy in case if you got separate partition per client

Otherwise, you can only get an idea how much utilized per each client based on average calculation factor, but it’s NOT precise number. i.e. if total rows in a table 1,000 and stored in 1024 KB (128 pages), the average per client while having 200 rows would be 204.8 KB (20% of total rows in table). You can use same calculation with non-clustered indexes as well

Following query help you to get size utilization of every user table in database. Consider the clustered index and heap utilization space is actual data size, nonclustered index utilization space is additional storage of table other than actual data.

select  OBJECT_NAME (s.object_id) as ObjectName,
        sc.name SchemaName,
        i.name as IndexName,
        s.partition_number,
        SUM (CASE WHEN s.index_id = 0 -- HEAP (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as HeapData_Mb
        ,SUM (CASE WHEN s.index_id = 1 -- Clustered Indexes (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as CluIndex_Mb
        ,SUM (CASE WHEN s.index_id > 1 -- Clustered Indexes (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as NCluIndex_Mb
        ,max (row_count) as [RowCount]
from sys.dm_db_partition_stats as s
    join sys.indexes as i on s.object_id = i.object_id and s.index_id = i.index_id
    join sys.tables as t on s.object_id = t.object_id
    join sys.schemas as sc on t.schema_id = sc.schema_id
GROUP BY s.object_id, sc.name, i.name, s.partition_number
ORDER BY  ObjectName, IndexName

Additional References:
Partitioned tables and Indexes:
Horizontal Partitioning

Leave a Reply

Your email address will not be published. Required fields are marked *