October 30, 2009 at 11:26 pm
DBA-640728 (10/29/2009)
hi, would that be the column Average Record Size? inDBCC 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
Change is inevitable... Change for the better is not.
January 5, 2011 at 4:16 pm
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
January 5, 2011 at 11:55 pm
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
January 6, 2011 at 8:23 am
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
January 6, 2011 at 8:35 am
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:
January 6, 2011 at 9:06 am
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
November 3, 2012 at 6:57 pm
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
November 4, 2012 at 1:02 am
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