List all indexes with keys, description and size

  • Eugene Bereznuk

    SSC Veteran

    Points: 270

    Comments posted to this topic are about the item List all indexes with keys, description and size

  • microslave

    Mr or Mrs. 500

    Points: 525

    While trying to find more about sizes of specific indexes, unlike the sum of used space as returned with sp_spaceused, I changed the queries used by sp_spaceused a bit to return individual index sizes.

    At a first glans the sum of the returned sizes seems to fit the sum returned by sp_spaceused itself, so this query could be adapted to filter a specific index name (joined with sys.indexes) etc.

    Maybe someone else is trying to find out about specific index sizes too, so you might have a look (still trying to restructure this query a bit, too make it smoother, this is just a first shot 😉 ):

    SELECT

    reserved_page_count,

    used_page_count,

    CASE

    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

    ELSE lob_used_page_count + row_overflow_used_page_count

    END pages,

    CASE

    WHEN (index_id < 2) THEN row_count

    ELSE 0

    END rows,

    LTRIM (STR ((CASE WHEN used_page_count >

    (CASE

    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

    ELSE lob_used_page_count + row_overflow_used_page_count

    END)

    THEN (used_page_count -

    (CASE

    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

    ELSE lob_used_page_count + row_overflow_used_page_count

    END) )

    ELSE 0 END) * 8, 15, 0) + ' KB') indexsize

    FROM sys.dm_db_partition_stats

    WHERE object_id = 546100986 -- filter for object_id of a table here

    AND index_id in (1,2,5)

  • microslave

    Mr or Mrs. 500

    Points: 525

    After putting together the previous query, I came along this undocumented sp:

    sp_MSIndexSpace tablename

    which also returns the sizes for each single index. Just in case anyone's looking for that... 🙂

  • Willie-876180

    Newbie

    Points: 3

    After hours of looking around on the net, I found this script, copied it and immediately ran it. Let me just add that I am by now means a DBA or anything similar.

    When the results came back, I copied it to Excel and summed it. It came up with a total of 19Gb worth of indexes. I thought that this couldn't be as te database is 24Gb in total. I ran the sp_spaceused against a specific table and compared the two. The results were:

    Index Name Size (sp_spaceused) Size (your script)

    ----------------------------------------------------------------------------

    PK_CustID 7872KB 2277

    So this leaves me unsure as to what the size actually is. Is it 7.69Mb or 2.22Gb. Could you clarify this for me, please?

    Thanks

  • microslave

    Mr or Mrs. 500

    Points: 525

    Hello Willie,

    you're right, the result from sp_spaceused might differ from the query, since the query itself doesn't add xml indexes or fulltext indexes for the given table, in contrast to sp_spaceused. I didn't need these so I left them away.

    Anyway, I modified the query so you can query for a tablename and only one index in this table if desired. If I find some time, I'll add the query for fulltext and xml index space too.

    BTW, the original query wasn't put together by me, but it's also a part of the sp_spaceused sys storeproc (sp_helptext 'sp_spaceused'), I just put it together for a quick view while performance tuning a database.

    Regards

    /*** Show index sizes for each index of a table ***/

    SELECT o.name TableName, i.name IndexName, reserved_page_count, used_page_count,

    CASE

    WHEN (s.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

    ELSE lob_used_page_count + row_overflow_used_page_count

    END pages,

    row_count,

    LTRIM (STR ((CASE WHEN used_page_count >

    (CASE

    WHEN (s.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

    ELSE lob_used_page_count + row_overflow_used_page_count

    END)

    THEN (used_page_count -

    (CASE

    WHEN (s.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

    ELSE lob_used_page_count + row_overflow_used_page_count

    END))

    ELSE 0 END) * 8, 15, 0) + ' KB') IndexSize

    FROM sys.dm_db_partition_stats s

    INNER JOIN sys.objects o ON o.object_id = s.object_id

    INNER JOIN sys.indexes i ON i.object_id = o.object_id and s.index_id = i.index_id

    WHERE o.name = 'TableName'

    --AND i.name = 'IndexName' -- use this to query size of one index in the table

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

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