Wednesday, February 1, 2012

Calculate the size of you indexes

I am using this script to see how much size my indexes are using inside my database.


SET NOCOUNT ON

CREATE TABLE #index_size
(Table_view_name varchar(200)
,Index_name varchar(200)
,Index_size_kb decimal(20,6)
,Index_size_Mb decimal(20,6)
,Index_size_Gb decimal(20,6))


DECLARE @object_name NVARCHAR(255)

DECLARE object_cur CURSOR FOR
  SELECT t2.name + '.' + t1.NAME
    FROM SYS.OBJECTS t1
  INNER JOIN dbo.sysusers t2 on t1.schema_id = t2.uid
   WHERE t1.type = 'U' --in ('U','V')
   ORDER BY t2.name, t1.name

OPEN object_cur

FETCH NEXT FROM object_cur INTO @object_name

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #index_size (Table_view_name)
SELECT @object_name

PRINT @object_name

INSERT INTO #index_size
SELECT     @object_name
     ,     i.name              AS IndexName
     ,     SUM(page_count * 8) AS IndexSizeKB
     ,     SUM(page_count * 8.00) / 1024 AS IndexSizeMB
     ,     SUM(page_count * 8.00) / 1024 / 1024 AS IndexSizeGB
  FROM sys.dm_db_index_physical_stats(db_id(), object_id(@object_name), NULL, NULL, 'DETAILED') AS s
  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

FETCH NEXT FROM object_cur INTO @object_name
END

CLOSE object_cur
DEALLOCATE object_cur

select @@SERVERNAME, db_name(), Table_view_name,
CASE
  WHEN index_name IS NULL
    THEN CASE
           WHEN Index_size_kb IS NOT NULL
             THEN 'HEAP'
             ELSE index_name
         END
    ELSE index_name
END as index_name, Index_size_kb, Index_size_MB, Index_size_Gb
     from #index_size
where Index_size_kb is not null

drop table #index_size

No comments:

Post a Comment