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
SQL Server - How to find the largest sql index and table size
SQL Server - Calculate table size with existing data
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |