Get Average Data/Index Row Size

DROP FUNCTION dbo.udf_GetAverageRowSize

Go

CREATE FUNCTION dbo.udf_GetAverageRowSize (@TableName SYSNAME)

RETURNS @AverageRowSize TABLE

(

DatabaseName SYSNAME,

TableName SYSNAME,

IndexName SYSNAME NULL,

IndexID INT NULL,

IndexTypeDesc VARCHAR(50) NULL,

AllocUnitTypeDesc VARCHAR(50) NULL,

MinRecordSizeInBytes INT NULL,

MaxRecordSizeInBytes INT NULL,

AvgRecordSizeInBytes DECIMAL(18,2) NULL

)

AS

/***************************************************************

Purpose: To retrieve row size statistics from the DMV

SYS.DM_DB_INDEX_PHYSICAL_STATS for one or all

tables in a DB

From SQl BOL

"For an index, one row is returned for each level of the B-tree in each partition.

For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition.

For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition.

If row-overflow data exists in the table, one row is returned for the

ROW_OVERFLOW_DATA allocation unit in each partition."

Author: Unknown/ChillyDBA

History: 24 Dec 2010 - Initial Issue

26 Jun 2012 - ChillyDBA - Converted to a table valued function

with optional table name parameter

****************************************************************/

BEGIN

INSERT @AverageRowSize

(

DatabaseName,

TableName,

IndexName,

IndexID,

IndexTypeDesc,

AllocUnitTypeDesc,

MinRecordSizeInBytes,

MaxRecordSizeInBytes,

AvgRecordSizeInBytes

)

SELECT

CAST(DB_NAME() AS VARCHAR(20)) AS 'DatabaseName',

CAST(OBJECT_NAME(ips.object_id) AS VARCHAR(20)) AS 'TableName',

si.name AS 'IndexName',

ips.index_id AS 'IndexID',

ips.index_type_desc AS 'IndexTypeDesc',

ips.alloc_unit_type_desc AS 'AllocUnitTypeDesc',

ips.min_record_size_in_bytes AS 'MinRecordSizeInBytes',

ips.max_record_size_in_bytes AS 'MaxRecordSizeInBytes',

ips.avg_record_size_in_bytes AS 'AvgRecordSizeInBytes'

FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,'DETAILED') ips

INNER JOIN sys.indexes si

ON ips.object_id = si.object_ID

AND ips.index_id = si.index_id

WHERE ips.object_id = ISNULL(OBJECT_ID(@TableName), ips.object_id)

ORDER BY

OBJECT_NAME(ips.object_id),

ips.index_id,

ips.avg_record_size_in_bytes DESC

RETURN

END

GO

USE Adventureworks

GO

SELECT * FROM dbo.udf_GetAverageRowSize(NULL)

SELECT * FROM dbo.udf_GetAverageRowSize('DimCustomer')