Calculate sql row size

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




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
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 = object_id(''?'') '
select * from ##tmpRowSize order by RowSizeDefinition  desc
drop table ##tmpRowSize

 Read More

SQL Server - How to find the largest sql index and table size

SQL Server - Calculate table size with existing data

Storage Checklist FAQ

Author: Jack Vamvas (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Calculate sql row size | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer