24 July,2013 by Jack Vamvas
Question: How can I calculate the sql row size?
Answer: Calculating the row-level row size is complicated and challenging. One of the key problems is SQL Server defines space at the extent level .
An extent is made up of 8 x 8 KB physically contiguous pages . The complication for calculating a row size is : Even if one row exists – for example 4 bytes , an extent has to be allocated.
Read this post for information on the difference between uniform and mixed extents - Disk IO performance , disk block size tuning and SQL Servers
There is complicated formula on the MSDN Online - http://msdn.microsoft.com/en-us/library/ms189124.aspx to predict the space required to store data in a heap
There are some reliable methods of estimating row sizes . Using DBCC SHOWCONTIG , sys.dm_db_index_physical_stats and SysColumns.
Each method offers a slightly different perspective on row size.
---METHOD 1 : DBCC SHOWCONTIG dbcc showcontig ('TableName') with tableresults --check max min and average record size --METHOD 2 : sys.dm_db_index_physical_stats --dbcc showcontig is to be deprecated SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'DatabaseName'), OBJECT_ID(N'TableName'), NULL, NULL , 'DETAILED') --METHOD 3 : SysColumns use db_name go create table ##tmpRowSize (TableName varchar(100),RowSizeDefinition int) exec sp_msforeachtable 'INSERT INTO ##tmpRowSize Select ''?'' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id(''?'') ' select * from ##tmpRowSize order by RowSizeDefinition desc drop table ##tmpRowSize