Find the size of Index in SQL Server

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.

-- 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:

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.

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 :)

About these ads

3 thoughts on “Find the size of Index in SQL Server

  1. 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?

    Like

  2. 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 :)

    Like

  3. 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:

    –Query 2
    ,IDX_NAME = (SELECT name FROM sys.indexes WHERE object_id = [ObjectId] and index_id = [IndexId])

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s