query to get the size of all indexes in a database

  • sundar329

    Say Hey Kid

    Points: 680

    Hi Friends,

    Is there any query to get the size of all indexes in a database? Let us assume a database size is 400Gb. I need to know how much space index is occupying.

    Thanks in advance.

  • John Mitchell-245523

    SSC Guru

    Points: 148364

    I think if you use your favourite search engine to search for "Is there any query to get the size of all indexes in a database", you'd get the answer much quicker than you would by waiting for one of us to reply.

    John

  • Alan Burstein

    SSC Guru

    Points: 61050

    You could do something like this:

    IF OBJECT_ID('tempdb..#indexInfo') IS NOT NULL

    DROP TABLE #indexInfo;

    SELECT TOP 0

    t.TABLE_CATALOG AS db,

    t.TABLE_SCHEMA AS SchemaName,

    OBJECT_NAME(i.OBJECT_ID) AS TableName,

    ISNULL(i.name,'<HEAP>') AS IndexName,

    i.index_id AS IndexID,

    8 * SUM(a.used_pages) AS 'Indexsize(KB)'

    INTO #indexInfo

    FROM sys.indexes AS i

    JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

    JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

    LEFT JOIN INFORMATION_SCHEMA.TABLES t ON OBJECT_NAME(i.OBJECT_ID)=t.TABLE_NAME

    WHERE t.TABLE_SCHEMA IS NOT NULL

    GROUP BY t.TABLE_CATALOG, t.TABLE_SCHEMA, i.OBJECT_ID,i.index_id,i.name

    ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id;

    EXEC sp_msforeachdb '

    USE [?];

    INSERT INTO #indexInfo

    SELECT t.TABLE_CATALOG AS db,

    t.TABLE_SCHEMA AS SchemaName,

    OBJECT_NAME(i.OBJECT_ID) AS TableName,

    ISNULL(i.name,''<HEAP>'') AS IndexName,

    i.index_id AS IndexID,

    8 * SUM(a.used_pages) AS ''Indexsize(KB)''

    FROM [?].sys.indexes AS i

    JOIN [?].sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

    JOIN [?].sys.allocation_units AS a ON a.container_id = p.partition_id

    LEFT JOIN [?].INFORMATION_SCHEMA.TABLES t ON OBJECT_NAME(i.OBJECT_ID)=t.TABLE_NAME

    WHERE t.TABLE_SCHEMA IS NOT NULL

    GROUP BY t.TABLE_CATALOG, t.TABLE_SCHEMA, i.OBJECT_ID,i.index_id,i.name

    ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id;';

    SELECT ISNULL(db,'<ALL DB>') AS db,

    SchemaName,

    TableName,

    IndexName,

    IndexID,

    [indexsize(KB)]

    FROM #indexInfo

    WHERE [Indexsize(KB)]<>0

    ORDER BY db,SchemaName,TableName,IndexName,IndexID;

    Note: I based my script on this: http://blog.sqlauthority.com/2010/05/09/sql-server-size-of-index-table-for-each-index-solution-2/[/url].

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Hi,

    Use this code for e.g

    select ss.name [schema], object_name(ddips.object_id) table_name, si.name index_name,

    ddips.page_count * 8 [Size KB], ddips.page_count * 8/1024.0 [Size MB]

    from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'SAMPLED') ddips

    join sys.indexes si on ddips.index_id = si.index_id and ddips.object_id = si.object_id

    join sys.tables st on ddips.object_id = st.object_id

    join sys.schemas ss on st.schema_id = ss.schema_id

    group by ss.name,ddips.object_id,si.name,ddips.page_count

    order by table_name asc

    Regards,

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • Bodhisatya

    SSC Veteran

    Points: 263

    Hi ,

    Please find the query that i wrote on my blog

    http://appliedsql.wordpress.com/2013/10/01/script-to-find-the-size-of-all-indexes-in-a-database/

    regards

    Bodhisatya

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply