query to get the size of all indexes in a database

Question

query to get the size of all indexes in a database

sundar329

Say Hey Kid

Points: 680
More actions
August 1, 2013 at 7:49 am

#296207

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.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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: 61141 More actions · Answer 2

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: 33110 More actions · Answer 3

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 Old Hand Points: 313 More actions · Answer 4

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