DMV Queries - Get Average Data/Index Row Size

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown/ChillyDBA
Date:        24 Dec 2010/26 Jun 2012

Description

Retrieves the average row size for all data and indexes on one or all tables in a database.
The code is reasonably well commented, but please note that there may be one or many rows returned for each table/index combination.  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.

Code

Function:

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

 
 

Test Code:

USE Adventureworks
GO

SELECT * FROM dbo.udf_GetAverageRowSize(NULL)

SELECT * FROM dbo.udf_GetAverageRowSize('DimCustomer')


ċ
udf_GetAverageRowSize.sql
(3k)
Andy Hughes,
Jun 23, 2013, 5:53 PM
Comments