SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
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 - 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

 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 (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

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

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

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer