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;
  2.  
  3. SELECT
  4.     ind.name              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 ind.name
  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.

image

0 comments:

Reference: Shahed Kazi at AspNetify.com