none
Table's "Index Space" too big RRS feed

  • Question

  • HI All,

    While checking the performance of one of our servers, I happened to see the properties of the biggest table in the database (from SSMS). There is a section "Storage" in the properties window, which lists "Index Space" as one of the parameters along with Data Space, Row Count, etc. The "Index Space" - which is the "Size of the data in the table's indexes in megabites" - shows 41,918 MB - which is almost 42 GB!! This is too big I think.

    Can anyone provide more insight into this parameter and how can I reduce this to gain some disk space?

    I also checked the DBCC SHOWCONTIG (after doing a DBCC INDEXDEFRAG) and here is the output if that's helpful -

    TABLE level scan performed.
    - Pages Scanned................................: 12720829
    - Extents Scanned..............................: 1600522
    - Extent Switches..............................: 1600522
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 99.35% [1590104:1600523]
    - Logical Scan Fragmentation ..................: 0.64%
    - Extent Scan Fragmentation ...................: 17.51%
    - Avg. Bytes Free per Page.....................: 152.9
    - Avg. Page Density (full).....................: 98.11%1

    Thanks!


    Thanks very much, Manoj Deshpande.
     
    Wednesday, May 19, 2010 12:15 PM

Answers

  • It is ok if you have big table and all indexes are good per bussiness requirement, with no performance issue.

    please paste the complete output of

    sp_space_used

    with more info like how many rows.

    please check dmv

    sys.dm_db_index_physical_stats

    sys.dm_db_index_physical_stats

    sys.dm_db_missing_index*

    also run the database tunning advicer if you want to remove the indexes which are not in use *Beware it may impact performance.

    My suggession would be if things are good, dont touch. but should analyze.

     

    My one cent.

    Thanx.

    Vinay


    Vinay Thakur http://rdbmsexperts.com/Blogs http://vinay-thakur.spaces.live.com/ http://twitter.com/ThakurVinay
    Wednesday, May 19, 2010 2:38 PM
  • Hi,

    I also suggest you maintenance  the indexes  by rebuilding or reorganizing indexes based on the fragmentation level. For more information, please see
    http://technet.microsoft.com/en-us/library/ms189858.aspx


    If there are any more problems, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, May 21, 2010 6:46 AM
    Moderator

All replies

  • sp_spaceused on this big table gives the index size as 42924584 KB


    Thanks very much, Manoj Deshpande.
    Wednesday, May 19, 2010 12:22 PM
  • Could you please the belowinformation

    SQL Server Version

    SELECT @@VERSION

    and the table strecture script


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Wednesday, May 19, 2010 1:33 PM
  • Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)


    Thanks very much, Manoj Deshpande.
    Wednesday, May 19, 2010 1:46 PM
  • It is ok if you have big table and all indexes are good per bussiness requirement, with no performance issue.

    please paste the complete output of

    sp_space_used

    with more info like how many rows.

    please check dmv

    sys.dm_db_index_physical_stats

    sys.dm_db_index_physical_stats

    sys.dm_db_missing_index*

    also run the database tunning advicer if you want to remove the indexes which are not in use *Beware it may impact performance.

    My suggession would be if things are good, dont touch. but should analyze.

     

    My one cent.

    Thanx.

    Vinay


    Vinay Thakur http://rdbmsexperts.com/Blogs http://vinay-thakur.spaces.live.com/ http://twitter.com/ThakurVinay
    Wednesday, May 19, 2010 2:38 PM
  • WHY do you think this is too big? How big is the table?  How many indexes do you have? What are the index definitions?

    You DBCC SHOWCONTIG output is meaningless since we don't know what you are looking at. If you are using SQL 2005 or later, you should select from sys.dm_db_index_physical_stats instead of using DBCC SHOWCONTIG. Then you can get all the rows for all the indexes on this table in a single set of results, and get summary information as well.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Wednesday, May 19, 2010 3:48 PM
    Moderator
  • Having too many indexes on the table will also increase the size of the table and indexes.

    You have to figure out how many indexes are there on the table and also which indexes are never been used.


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Wednesday, May 19, 2010 5:54 PM
  • As rightly mentioned by vinay and Kalen, try to make use of DMVs to determine the indexes which are properly being used and the indexes which are not being used and take appropriate action. Use missing indexes DMV as guidence and not as decision/recommendation.

    Regards,


    Phani Note: Please mark the post as answered if it answers your question.
    Thursday, May 20, 2010 10:34 AM
  • Hi,

    I also suggest you maintenance  the indexes  by rebuilding or reorganizing indexes based on the fragmentation level. For more information, please see
    http://technet.microsoft.com/en-us/library/ms189858.aspx


    If there are any more problems, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, May 21, 2010 6:46 AM
    Moderator
  • I'm having a similar problem running SQL Server 2008, version 10.0.1600.22.  The symptom I'm having is that a table delete exceeds a 24,576 MB restriction on the log size when the size of the MDF file is 12,201,664 KB ~= 11,916 MB.  The table is foreign key referenced by another table, but the referencing table is empty at the time of the delete.

    After running a succesful consistency check on the datbase, and a successul reorg on the table's indices, MSSMS properties on this table gives an index space of 1,227.641 MB and a data space of 965.602 MB.  I'm willing to say these are too big, not ecause of my application, but because these numbers exceed the size of the MDF file by 2 orders of magnitude (though they only exceed the size of the disk by 1).

    Hoping for enlightenment,

    Harry

     

    Thursday, January 6, 2011 5:42 PM