6

I am trying to figure out the disk size a number of rows in various tables take up.

I can define this rows by a query. How can I find out the actual disk size that corresponds to the result set in SQL Server?

The reason why I want to know this, is that I am trying to analyse a multi-tenant system. I am trying to get the total size each tenant takes up on the system. So I need the size of all rows pertaining to each tenant and the size each tenant is using in index tables, etc.

The tenants all share the same schema, so it is not easy to do.

gonsalu
  • 928
  • 8
  • 17
dublintech
  • 1,559
  • 8
  • 21
  • 26

2 Answers2

9

One way is to look at the sys.dm_db_index_physical_stats output. The min_record_size_in_bytes, max_record_size_in_bytes and avg_record_size_in_bytes will give you the sizes you want.

If you want to see the size of a particular record, another way is to find the physical row location, see SQL Server 2008: New (undocumented) physical row locator function. You can then dump the row physical structure, see Anatomy of a Record.

Finally you can use the product documentation to estimate the size:

Remus Rusanu
  • 52,054
  • 4
  • 96
  • 172
0

If you just want to know the datalength of all columns then you could summarize the datalength() for each column.

If you want to know the actual storage space used for specific rows then it is not possible to give a straight "do it like this" answer. Every piece of information takes space. Perhaps you have a lot of indexes which take space or have text columns which not only store the data but also have a pointer in the pagefile. Besides this there is the "empty" space because of the fill factor of your tables.

amaters
  • 179
  • 1
  • 2
  • 11