I have multiple client using same database, i need to find client wise size utilized.
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