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 (http://blog.sqlauthority.com)

About these ads

20 thoughts on “SQL SERVER – Size of Index Table for Each Index – Solution 2

  1. 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:\temp\servers.txt) {
    Get-SqlDatabase -sqlserver $Server | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused
    }

    :)

    Like

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

    Like

  3. Pingback: SQL SERVER – Size of Index Table for Each Index – Solution 3 – Powershell Journey to SQL Authority with Pinal Dave

  4. Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave

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

    Like

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

    Like

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

    Like

  8. 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 ?

    Like

  9. Pingback: SQL SERVER – Difference between Create Index – Drop Index – Rebuild Index – Quiz – Puzzle – 21 of 31 « SQL Server Journey with SQL Authority

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

    Like

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

    Like

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

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

    Like

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s