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:
Uses sys.indexes and sys.dm_db_partition_stats DMF to calculate the size of individual index on a table.
-- Ensure a USE <databasename> statement has been executed first. SELECT i.[name] AS IndexName ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB FROM sys.dm_db_partition_stats AS s INNER 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:
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.
Sample output of the second query when executed against AdventureWorks database:
-- Ensure a USE statement has been executed first. 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(), 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
I hope you will find this information useful 🙂
For Query 1, why do you not use (used_page_count + reserved_page_count) * 8 for index size since those pages are already reserved for the index?
I agree with Andrew that the reserved_page_count also deserves consideration. But (at least in SQL Server 2008 R2) reserved_page_count includes the used and unused pages – adding them will double count the used pages 🙂
it takes over 2 hours for Query #2 to run.
It is very useful nevertheless.
I suggest a small modification to it to include NAME of INDEX column: (IDX_NAME) below:
,IDX_NAME = (SELECT name FROM sys.indexes WHERE object_id = [ObjectId] and index_id = [IndexId])
Pingback: The clustered index columns are in all of the non clustered indexes. | SQL Studies
Pingback: The clustered index columns are in all of the non clustered indexes. - SQL Server - SQL Server - Toad World
Pingback: How To Fix Error 35285 Sql Server Errors - Windows Vista, Windows 7 & 8
Pingback: SQL Server Table Index To big | DL-UAT