Script to find large indexes in a SQL Server Database

— Ensure a USE statement has been executed first.
SELECT i.[name] AS IndexName
,SUM(s.[used_page_count]) * 8 / 1024 AS IndexSizeMb
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
AND s.[index_id] = i.[index_id]
GROUP BY i.[name] Having SUM(s.[used_page_count]) * 8 / 1024>100
ORDER BY i.[name]

 

IndexSize

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.