Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

query to get the size of all indexes in a database Expand / Collapse
Author
Message
Posted Thursday, August 1, 2013 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 6, 2014 10:39 AM
Points: 27, Visits: 116
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.
Post #1479951
Posted Thursday, August 1, 2013 8:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 9:32 AM
Points: 5,490, Visits: 10,354
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
Post #1479959
Posted Thursday, August 1, 2013 9:48 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 7:28 PM
Points: 651, Visits: 3,015
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/.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1480006
Posted Thursday, August 1, 2013 3:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 3,129, Visits: 3,252
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,
SQL Server developer at Seavus
www.seavus.com
Post #1480173
Posted Wednesday, October 23, 2013 11:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:25 AM
Points: 9, Visits: 284
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
Post #1507764
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse