Query to find the row size in a user table

  • 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)

  • @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

  • 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
  • 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

  • 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:

  • 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
  • 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

  • The OP posted this question four years ago.

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

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