Question: How to Find Size of All the Indexes on the Database?
Answer: Just another day while I was doing SQL Server Performance Tuning for a leading organization, I was asked by if I have a query which brings out all the indexes on the table with its size. I love this kind of questions as this helps me understand my client’s mindset and understanding of the subject.
Here is a script which will list all the indexes in your database with its size.
SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName, OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name 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 GROUP BY i.OBJECT_ID,i.index_id,i.name ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id
If you are interested in helping me with you to improve your SQL Server Performance, you can opt for my extremely popular Index Tuning and Strategy Guidance 99 minute session.
In 99 minutes of time we together fix issues with your indexes on your one instance and I teach how you can do it yourself for the rest of the instances. Together we carry out the following tasks:
- Analysis every single index on your one instance for its uselessness
- Identify indexes which are not useful anymore with your current schema and workload
- Propose new indexes which are very impactful for your system
- Check index related settings on your database and suggest optimal value
- Explain you and your team how to read execution plan
- Explain a very powerful real world demonstration to understand how to create a proper index for your system
At the end of the session I hand over every single script to you so you can do it yourself in future.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Although deprecated, the famous [sysindexes] contains both [dpages] and [rowcount] value which makes the query much easier
I believe there is something wrong. If I go to Index Properties -> Fragmentation -> General -> Pages. And multiply the total pages by 8, that number doesn’t match with the Indexsize(KB) of this query
we should run profiler and check source of information in UI.
First, @roncansan, you used the term query instead of index which is not correct.
Second, @Pinal Dave, I think @roncansan is right about there is a something wrong.
I run your script on my database it says that the PK of one of my table related to attachments is about 41 GB.
I checked the table properties and under storage I noticed the Index space is 0.055 MB and the Data space is 40 387 523 MB. There is no included columns in the index however it appears that the fragmentation is really huge (>30%) and take more than 17min to rebuild…any update for this script?
Is the PK also a clustered index? If so, then that is why it shows the index as being so large. Clustered indexes are in actuality all of the data in the table. Otherwise, without a clustered index, you would just have a heap. This script needs to exclude clustered indexes if you are wanting to see just the other types of indexes such as non-clustered.
@AMax : My PK is a default clustered index created by SQL Server. So you mean that because each row have one PK column, the size of the PK should include the size of other columns? I can understand the relation but it doesn’t make sens for me to say the index is the same size than his relative table…but well…I will do as you mentionned and will exclude the clustered indexes. Thank you!
You will also need to exclude the heaps themselves. Below is the query modified to exclude heaps and clustered indexes.
OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name 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
WHERE i.index_id NOT IN (0,1) –exclude heaps and clustered indexes
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY 5 DESC
Thanks for sharing.
I have 1 million records in my table. how to create quickly nonclustered index…?If Any one know let me know
I ran into the heap issue as well. It does show NULL as the IndexName but that wasn’t before I told the developer they had a 368 GB index on their “backup” table (my fault to not double check). Maybe you should update to exclude “NULL” in the IndexName or put a *warning* message the blog post to watch out for this. Thanks for all you do for the community Pinal!