Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Why do you want to know this? Typically, you're only concerned about average row length and rows per page. Also see dba.stackexchange.com/a/5464/630 and stackoverflow.com/a/3793265/27535 and dba.stackexchange.com/a/3382/630 –  gbn Apr 26 '12 at 7:09
add comment

2 Answers

up vote 6 down vote accepted

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:

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.