How to Find Size of All the Indexes on the Database – Interview Question of the Week #097

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.

Solarwinds
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

How to Find Size of All the Indexes on the Database - Interview Question of the Week #097 indexsize-800x663

What Next:

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)

Solarwinds
, ,
Previous Post
How Many Foreign Key Can You Have on A Single Table? – Interview Question of the Week #096
Next Post
How to Find Longest Running Query With Execution Plan – Interview Question of the Week #098

Related Posts

9 Comments. Leave new

  • Wilfred van Dijk
    November 13, 2016 3:22 pm

    Although deprecated, the famous [sysindexes] contains both [dpages] and [rowcount] value which makes the query much easier

    Reply
  • 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

    Reply
  • Hans Prestat
    May 22, 2018 6:20 am

    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?

    Reply
    • 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.

      Reply
  • Hans Prestat
    July 27, 2018 4:19 am

    @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!

    Reply
    • You will also need to exclude the heaps themselves. Below is the query modified to exclude heaps and clustered indexes.

      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
      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

      Reply
  • I have 1 million records in my table. how to create quickly nonclustered index…?If Any one know let me know

    Reply

Leave a Reply

Menu