Query to find the row size in a user table

Question

Query to find the row size in a user table

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

DBA-640728 (10/29/2009)
hi, would that be the column Average Record Size? in
DBCC SHOWCONTIG WITH TABLERESULTS
I need to find the average size of a columm in my table to be able to get the average capacity needed if the table keeps growing and I don't find the column Average Row Size in the results.

No... it's the average size of all the records in a given table.

I have to admit, I'm really getting confused as to what you want... first you ask for row size, then table size in the OS, then the size of 75000 rows, and now average size of a column. What is it that you really want or are you just trolling? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mpotluri Grasshopper Points: 19 More actions · Answer 2

@lowell,

i tried using your code .

create table ##tmp (TableName varchar(40),DefinedRowSize int)

sp_msforeachtable 'INSERT INTO ##TMP Select ''?'' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id(''?'') '

select * from ##tmp order by DefinedRowSize desc

It is throwing error

Incorrect syntax near 'sp_msforeachtable'.

please help

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

create table ##tmp (TableName varchar(40),DefinedRowSize int)

exec sp_msforeachtable 'INSERT INTO ##TMP Select ''?'' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id(''?'') '

select * from ##tmp order by DefinedRowSize desc

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125562 More actions · Answer 4

jishar (11/20/2008)
Hi all, I need to find out the size of selected rows in a table of Sql server 2005.

For what it's worth, the dynamic management view [sys.dm_db_index_physical_stats] returns statistics for the row, index, and lob pages related to a table object which can be filtered. Each row contains avg, min, and max record size (and a lot of other useful columns) for a page, which can then be aggregated. For a clustered table, the statistics on the clustered index will give you the record counts and stats for the table data itself. For heap (non-clustered) tables, the index_id will be 0 and will return the same. Maybe you can start from here to get what you really want.

http://msdn.microsoft.com/en-us/library/ms188917.aspx

I'm not sure why you would need the row size only for records returned in the resultset. The size of the record in the table or index versus it's size in the network packet or it's size in the application grid, dataset, Excel sheet, etc. will be different, because it's different data structures.

select

cast(db_name(ps.database_id)+'.'+object_name(ps.object_id)+'.'+isnull(i.name,'heap') as varchar(60)) as db_table_index_name,

sum(ps.record_count) as sum_record_count,

-- cast(((sum(ps.page_count) * 8192) / 1000000.00) as numeric(9,2)) as size_mb,

avg(ps.max_record_size_in_bytes) as avg_record_size_in_bytes,

max(ps.max_record_size_in_bytes) as max_record_size_in_bytes

-- cast(avg(avg_fragmentation_in_percent) as numeric(9,1)) as avg_fragmentation_in_percent,

-- cast(avg(ps.avg_page_space_used_in_percent) as numeric(9,1)) as avg_page_space_used_in_percent

from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed') as ps

left join sys.indexes as i on i.object_id = ps.object_id and i.index_id = ps.index_id

where object_name(ps.object_id) in ('employee')

group bydb_name(ps.database_id), object_name(ps.object_id), i.name

order bydb_name(ps.database_id), object_name(ps.object_id), i.name;

DB_Table_Index_Name sum_record_count avg_record_size_in_bytes max_record_size_in_bytes

------------------------------------------------------------ -------------------- ------------------------ ------------------------

AdventureWorks.Employee.AK_Employee_LoginID 293 60 65

AdventureWorks.Employee.AK_Employee_NationalIDNumber 292 28 29

AdventureWorks.Employee.AK_Employee_rowguid 290 21 21

AdventureWorks.Employee.IX_Employee_ManagerID 290 12 12

AdventureWorks.Employee.PK_Employee_EmployeeID 301 115 219

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

grahamc SSCertifiable Points: 6762 More actions · Answer 5

simple solution for me, take the total table size, divide by the total number of records in the table and multiply by the number of records chosen, unless you need exact amounts.

However I believe that exact values would be debatable.

Would you include index size?

What about statistics?

Depending on how the discs are setup blocks could be written in specific sizes which means that 8kb of disc space is used for a 4kb record.

loads of other things to consider.... keep it simple :hehe:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please note this thread is 2 years old.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kannan -134995 Newbie Points: 5 More actions · Answer 7

Hi,

You can calculate the row size using sp_spaceused.

The syntax is: sp_spaceused 'tablename'

You have to look in to two column mainly. Data & Rows.

Row size in bytes = Rows/ (Data*1024)

Hope it helps.

Kind Regards

Kannan

USA

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

Michael Valentine Jones

SSC Guru

Points: 64818

November 4, 2012 at 1:02 am

#1555802

The OP posted this question four years ago.