Friday, November 9, 2012

Calculate Indexes Size via dm_db_index_physical_stats

Sometimes we may want to know the space used by a index. We can use the sp_spaceused system stored procedure to measure how much space used by different objects.

This code allows you to list how many indexes are present on a table, their type and how big (kb and mb) they are.

SELECT [name],type_desc,space_used_in_kb = (page_count * 8.0),space_used_in_mb = (page_count * 8.0 / 1024.0) 
  FROM sys.indexes I JOIN sys.dm_db_index_physical_stats(db_id(),object_id('.'),null,null,null) P 
    ON I.[object_id] = P.[object_id] 
   AND I.[index_id] = P.[index_id]

No comments:

Post a Comment