Three ways to estimate the index size, resulting in different results

/****Method 1: Estimate the index size by using sp_spaceused.

 

The calculation is based on the statistics in sys.partitions and sys.allocatin_units, and the page count.

Use this method, yolu can find the combined size of all indexes in each table.

For the StoreContact table in the AdventureWorks databse, the index_size is 120KB from this method.

From SSMS, the properties of the table shows this table has 37KB data and 117KB indexes.

*/

 

USE AdventureWorks

GO

 

EXEC sp_MSforeachtable @command1 = "EXEC sp_spaceused '?'"

 

/***Method 2: Estimate the index size by using sys.dm_db_index_physical_stats and the page_count column

 

This method can estimate the size of each individual index.

 

Page_count=Total number of index or data pages (Remember, a page is 8K)

·       For an index,it is the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit.

·       For a heap, it is the total number of data pages in the IN_ROW_DATA allocation unit.

·       For LOB_DATA or ROW_OVERFLOW_DATA allocation units, it is the total number of pages in the allocation unit.

 

Index in the table StoreContact                Size in KB

-------------------------------------          --------------

AK_StoreContact_rowguid                        32

IX_StoreContact_ContactID                      24

IX_StoreContact_ContactTypeID                  24

PK_StoreContact_CustomerID_ContactID           48

 

Notice the total (128KB) based on this method is larger than that based on sp_spaceused, even both estimates are based on page.

The reason for that is the current method counts the pages at both the leaf and root level.

For instance, the PK index has 5 pages at the leaf-node and one page at the root, adding up to

6 pages or 48KB. If you like to limit to the leaf level, add the WHERE clause.

*/

 

SELECT  i.name AS IndexName ,

        SUM(page_count * 8) AS IndexSizeKB -- A page is 8K

FROM    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.TableName'),NULL, NULL, 'DETAILED') AS s

        JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]

                                 AND s.index_id = i.index_id

--WHERE avg_fragmentation_in_percent<>0 --AND i.object_id=OBJECT_ID('Sales.StoreContact')

GROUP BY i.name

ORDER BY i.name

 

/****Method 3: Estimate the index size by using sys.dm_db_index_physical_stats and and the number of rows

 

This method also uses sys.dm_db_index_physical_stats,

but it estimaes the index size via avg_record_size_in_bytes and the number of rows

                

What is average record size in bytes?

 

·       For an index, the average record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

·       For a heap, it is the average record size in the IN_ROW_DATA allocation unit.

·       For LOB_DATA or ROW_OVERFLOW_DATA allocation units, it is the average record size in the complete allocation unit.

·       NULL when mode = LIMITED.

                  

Index in the table StoreContact                    Size in KB

------------------------------------               --------------

AK_StoreContact_rowguid                            18

IX_StoreContact_ContactID                          7

IX_StoreContact_ContactTypeID                      10

PK_StoreContact_CustomerID_ContactID               32

 

Total is 67KB, much less than that by the other two methods.

*/

 

SELECT  [DatabaseName] ,

        [ObjectId] ,

        [ObjectName] ,

        [IndexId] ,

        [IndexDescription] ,

        CONVERT(DECIMAL(16, 0), ( SUM([avg_record_size_in_bytes]

                                      * [record_count]) / ( 1024.0 ) )) AS [IndexSize(KB)] ,

        [lastupdated] AS [StatisticLastUpdated] ,

        [AvgFragmentationInPercent]

FROM    ( SELECT DISTINCT

                    DB_NAME(database_id) AS 'DatabaseName' ,

                    object_id AS ObjectId ,

                    OBJECT_NAME(object_id) AS ObjectName ,

                    index_id AS IndexId ,

                    index_type_desc AS IndexDescription ,

                    avg_record_size_in_bytes ,

                    record_count ,

                    STATS_DATE(object_id, index_id) AS 'lastupdated' ,

                    CONVERT([VARCHAR](512), ROUND(avg_fragmentation_in_percent,

                                                  3)) AS 'AvgFragmentationInPercent'

          FROM      sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,

                                                   'detailed')

          WHERE     object_id IS NOT NULL

                    AND avg_fragmentation_in_percent <> 0

        ) T

GROUP BY DatabaseName ,

        ObjectId ,

        ObjectName ,

        IndexId ,

        IndexDescription ,

        lastupdated ,

        AvgFragmentationInPercent