SQL SERVER – Size of Index Table for Each Index – Solution 2

Earlier I had ran puzzle where I asked question regarding size of index table for each index in database over here SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table. I had received good amount answers and I had blogged about that here SQL SERVER – Size of Index Table for Each Index – Solution. As a comment to that blog I have received another very interesting comment and that provides near accurate answers to original question. Many thanks to Rama Mathanmohan for providing wonderful solution.

SELECT
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

Let me know if you have any better script for the same.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Data Storage, SQL Index, SQL Scripts
Previous Post
SQLAuthority News – MSDN Flash Mentions – TechNet Flash Mention – Top Community Contributors (Annual) Winner
Next Post
SQL SERVER – Size of Index Table for Each Index – Solution 3 – Powershell Index Size

Related Posts

22 Comments. Leave new

  • Laerte Junior
    May 9, 2010 7:53 am

    Hi Pinal,

    I do not say better, but maybe another approach to enthusiasts in powershell and SQLSPX library would be:

    1 – All indexes in all tables and all databases
    Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

    2 – All Indexes in all tables and especific database
    Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

    3 – All Indexes in especific table and database
    Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable “YourTable” | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

    and to output to txt.. pipe Out-File

    Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused | out-file c:IndexesSize.txt

    If you have one txt with all your servers, can be for all of them also.

    Lets say you have all your servers in servers.txt: something like
    NameServer1
    NameServer2
    NameServer3
    NameServer4

    We could Use :
    foreach ($Server in Get-content c:tempservers.txt) {
    Get-SqlDatabase -sqlserver $Server | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused
    }

    :)

    Reply
  • Jeremy Carter
    May 9, 2010 10:48 pm

    Pinal,

    Here is one I wrote. This one gives the filegroup the object belongs to and filters out system objects.

    SELECT
    fg.name as FileGroup,
    obj.name as table_name,
    ISNULL(ind.name,”) as index_name,
    ind.type_desc,
    used_page_count,
    used_page_count/128 AS used_page_size_MB,
    reserved_page_count,
    reserved_page_count/128 AS reserved_page_size_MB
    FROM sys.dm_db_partition_stats AS ps
    INNER JOIN sys.indexes AS ind
    ON ps.object_id = ind.object_id
    and ps.index_id = ind.index_id
    INNER JOIN sys.objects as obj
    ON ind.object_id=obj.object_id
    INNER JOIN sys.filegroups as fg
    ON fg.data_space_id=ind.data_space_id
    WHERE obj.type’S’
    ORDER BY obj.name, ind.name

    Thanks,
    Jeremy

    Reply
  • Hi Pinal,

    I used
    SELECT
    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

    this your query to get the index size which is giving 20496 KB

    for the same m trying now sp_spaceused SP again to get the index size then for total database it is giving me 808KB There is a vast difference.

    AND one more thing
    this size which from this query we are getting is calculated for data pages which stores data not which stores index
    i want seperate index size which not includes data.
    i made a crosscheck and that time i used query

    EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”

    used query is giving me a table size 20336 KB and index size for that table 160 KB

    in a nutshell,

    from ur query i m getting index size 20496 KB and from sp_spaceused i m getting index for that atble 160 KB

    If you wish i can send you the snaps for that in ur email.

    and dont mind if directly or indirectly i had written something not properly as i had only 6 months experience i m fresher

    THANKS and REGARDS
    Kamesh Shah

    Reply
  • i have send you snaps in email

    THANKS and REGARDS
    Kamesh Shah

    Reply
  • hi Sir,

    It is now giving absolute correct result.i made that index non clustered and now perfect result.

    Amazing hands on SQL SERVER Sir.HATS OFF to you..

    i have drawn conclusion that if there is a clustered index then size of index would be displayed index size + table size

    but if the index is non clustered then size of index will be separate from table size and

    sir note that not even a single insert of mine is overflowing the page size that is 8060 bytes so would be in a single partition and no furhter allocation.

    but why sizes are consolidated and separated ?? see as far as i know then it has to do something with mix and uniform extents or else concept is there??

    My second perception on this is (just what i know)
    –> Clustered index consist a data rows in a leaf node thats why its size is consolidated with table size

    –> non cluster index contains a row locator at leaf node thats why index structure is totally separated from table data structure

    my question is like why clustered index size is calculated like=table size+index size
    and non clustered is like only table size different and index size different???

    THANKS AND REGARDS
    Kamesh Shah

    Reply
  • Thnx sir

    its very much clear now about index sizes.

    THANKS AND REGARDS
    Kamesh Shah

    Reply
  • We can use sp_estimate_data_compression_savings to know the size of each index.

    Thanks
    Rohit

    Reply
  • shaikferozbasha
    April 6, 2011 6:49 pm

    hello plz help to get nonclustered index size without any data occupied i mean wat the above queries giving size of each nonclustered index size with data in the columns but i need query to find nonclustered size without any data added in the columns then the size comes within bytes not kb

    Reply
  • by running
    “SELECT
    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”

    Why some indexname are null ?

    Reply
  • Create a temp table, use msforeachtable to query the indicies on each table and save the results back to your temp table.

    CREATE TABLE #indexSizes(ID int, Name nvarchar(4000), SizeInKB int, Comments nvarchar(4000))
    exec sp_MSforeachtable ‘insert into #indexSizes(ID, Name, SizeInKB, Comments) exec sp_MSIndexSpace ”?” ‘
    select * from #indexSizes
    drop table #indexSizes

    Reply
  • Girijesh Pandey
    October 2, 2012 1:08 pm

    Hi Pinal,

    Thanks for this excellent post!
    i read it completely, found simply great.

    A big Thanks to Laerte Junior also, he added nice inputs!

    We can also get the details regarding Index by system stored procedure.

    sp_estimate_data_compression_savings
    [ @schema_name = ] ‘schema_name’
    , [ @object_name = ] ‘object_name’
    , [@index_id = ] index_id
    , [@partition_number = ] partition_number
    , [@data_compression = ] ‘data_compression’
    [;]

    Example:-
    sp_estimate_data_compression_savings ‘dbo’, ‘dep’, NULL, NULL, ‘row’
    Go
    sp_estimate_data_compression_savings ‘dbo’, ’emp’, NULL, NULL, ‘row’

    Note:
    1.Compression and sp_estimate_data_compression_savings are available only in the SQL Server 2008 Enterprise and Developer editions.

    2. If the existing data is fragmented, you might be able to reduce its size without using compression by rebuilding the index. For indexes, the fill factor will be applied during an index rebuild. This could increase the size of the index.

    Regards,
    Girijesh

    Reply
  • Hi,
    I am looking for a script which will give me the tablename,indexname,size of the index, allocated size, fragmentation percent for a database

    can you please help me.

    Reply
  • Hi Pinal,

    Thanks for Information…

    But it shows both database table size and index size..

    Reply
  • it’s being a long time since the creation for this post but I wanted to share my script too:

    WITH CteIndex
    AS
    (
    SELECT
    reservedpages = (reserved_page_count)
    ,usedpages = (used_page_count)
    ,pages = (
    CASE
    WHEN (s.index_id pages THEN CASE WHEN ct.index_id < 2 THEN pages ELSE (usedpages – pages) END ELSE 0 END) * 8, 15, 0) + ' KB')
    FROM CteIndex ct
    INNER JOIN sys.objects o ON o.object_id = ct.object_id
    INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , NULL) ps ON ps.object_id = o.object_id
    AND ps.index_id = ct.index_id
    ORDER BY name ASC

    Reply
  • I see the same behavior: the size reported is not the index-only size

    Reply
  • Here is the one i’m using , i added some details regarding the rows number, pages , space used etc

    –*********************************************************************************
    — Retrieve Tablename, IndexName, IndexType, PrimKey, Nb rows, Indexsize
    — REMINDER:
    — 1/ A clustered index has always the same number of rows as the table itself.
    — 2/ The NON CLUSTERED INDEX will REALLY give the index size (same as properties/storage at table level)
    — 3/ HEAP Table is a Table that doesn’t have a Clustered Index
    –*********************************************************************************
    USE
    GO
    — Do not lock anything, and do not get held up by any locks.
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    GO

    SELECT
    –t.NAME AS TableName,
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    i.type_desc,
    i.is_primary_key,
    p.[rows],
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
    (sum(a.used_pages) * 8) / 1024 as ‘Indexsize(MB)’,
    (sum(a.used_pages) * 8) as ‘Indexsize(KB)’,
    –(sum(a.data_pages) * 8) / 1024 as DataSpaceMB,
    sum(a.total_pages) as TotalPages,
    sum(a.used_pages) as UsedPages,
    sum(a.data_pages) as DataPages

    FROM
    sys.tables t
    INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id

    GROUP BY
    i.OBJECT_ID,i.index_id,i.name,i.type_desc,i.is_primary_key,p.[rows]
    –t.name,i.index_id,i.name,i.type_desc,i.is_primary_key,p.[rows]

    ORDER BY
    –4 DESC
    OBJECT_NAME(i.OBJECT_ID),i.index_id
    –t.name

    –WHERE
    –AND OBJECT_NAME(p.object_id) = ‘YourTableName’
    –t.name = ‘YourTableName’

    Reply
  • Saverio Lorenzini
    January 11, 2017 2:59 pm

    Hello Pinal, thanks for the good script.
    I have developed another script that gives me a little bit different values for index Size.
    If I compare the index size values returned by SSMS GUI (in terms of pages) my script is more precise. I simply use the sys.dm_db_index_physical_stat (it returns the size already) and NOT the sys.allocation_units.

    select object_name(Ix.object_id), Ix.name, ix.index_id, ps.page_count
    from sys.dm_db_index_physical_stats(db_id(),null,null,NULL,’DETAILED’) PS
    INNER JOIN sys.indexes IX
    ON ix.object_id = PS.object_id
    AND ix.index_id = Ps.index_id
    order by 4 desc

    I would like to have your opinion.
    Thanks

    Saverio

    Reply

Leave a Reply