March 21, 2014

Index Size in Sql Server Database

In the previous post, I showed how to retrieve the size of individual tables of a sql server database. In this post, I will show how to get the size of indexes in a sql server database. Here is the script that does that.

  1. USE AdventureWorks2012;
  4.              AS IndexName,
  5.     SUM(page_count * 8) AS IndexSizeKB,
  6.     SUM(page_count * 8) / 1024 as IndexSizeMB
  7. FROM sys.dm_db_index_physical_stats(
  8.     db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS t
  9. JOIN sys.indexes AS ind
  10. ON t.[object_id] = ind.[object_id] AND t.index_id = ind.index_id
  11. GROUP BY
  12. ORDER BY IndexSizeKB DESC


The script looks up the sys.dm_db_index_physical_stats table and finds the page count and converts it to index size. Here's a screen shot of the result.



Reference: Shahed Kazi at