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
|