http://www.sqlservercentral.com/blogs/basits-sql-server-tips/2012/07/06/find-the-size-of-index-in-sql-server/

Printed 2014/09/19 03:53PM

Find the size of Index in SQL Server

2012/07/06

As we know, sp_spaceused gives the size of table and index but it gives the sum of size of all indexes on a table. What if you need to capture size of individual index on a table? Thats where the following two queries comes handy:

Query 1:
Uses sys.indexes and sys.dm_db_partition_stats DMF to calculate the size of individual index on a table.

USE [<DatabaseName>]
GO

--Query 1
SELECT i.[name] AS IndexName
      ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB 
FROM sys.dm_db_partition_stats AS s 
JOIN sys.indexes AS i 
ON s.[object_id] = i.[object_id] 
AND s.[index_id] = i.[index_id] 
GROUP BY i.[name] 
ORDER BY i.[name] 
GO

 
Sample output of the first query when executed against AdventureWorks database:

Query 2:
Uses sys.dm_db_index_physical_stats and sys.dm_db_partition_stats to calculate the size of individual index on a table. This query is more reliable as compared to first query because it uses DMFs.

USE [<DatabaseName>]
GO

--Query 2
SELECT [DatabaseName]
      ,[ObjectId]
      ,[ObjectName]
      ,[IndexId]
      ,[IndexDescription]
      ,CONVERT(DECIMAL(16,1)
                ,(SUM([avg_record_size_in_bytes] * [record_count])
                        / (1024.0 *1024))) AS  [IndexSize(MB)]
      ,[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('PM_Db'), 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

 
Sample output of the second query when executed against AdventureWorks database:

I hope you will find this information useful :)



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.