Table size and Index size

  • The Gug

    SSC Veteran

    Points: 238

    Good afternoon

    I ran across an issue that I don't understand. I have a DB that is 177gb. When I dug through the DB, I found a table that said it had 8gb of data space used and 90gb of index space used. How does that happen?

  • g.britton

    SSChampion

    Points: 13686

    How many indexes? How are you measuring the space used? What's the fragmentation like?

    Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

  • The Gug

    SSC Veteran

    Points: 238

    Thank you for responding. I'm looking at Object Explorer Details in SSMS. The column names are Data Space Used (KB) and Index Space Used (KB). I didn't check the fragmentation on the indexes - yet. Would fragmentation product that kind of a result?

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Fragmentation can increase the space an index uses. It's more likely though that the table just has lots and lots and lots of indexes each with lots of columns.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The Gug

    SSC Veteran

    Points: 238

    Good afternoon

    Thank you for your response Gail. I looked at the amount of indexes on the table - there are seven in total. All are listed as non-clustered, although there is one that has a prefix of PK_xxx (Unique, Non-Clustered). Could I be looking at a corrupt index??

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Unlikely. Corruption causes high-severity error messages.

    You may have an incorrect page space usage. Run DBCC updateusage

    Also check the columns in those indexes. If they have lots and lots of columns, they can be quite large.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The Gug

    SSC Veteran

    Points: 238

    Ok - I'll fast forward to the end of the story. After working with the vendor, they decided the data in the table was not need. They truncated the table and now the DB is down to 18gb. I wish they would have told me what they were looking at BEFORE they truncated the table but it is a vendor system...

    Thank you to both of you for offering your advice - I appreciate your assistance.

    The Gug

  • Kim Crosser

    SSCommitted

    Points: 1763

    Easy ways to create indexes using multiple times the actual table space:

    1. Create multiple indexes on MANY columns in the table (I have actually seen indexes defined that were the entire set of columns of the table - and there were a lot of columns).

    2. "Include" inappropriate columns from the table (either with "Include ()" or just as part of a covering index). ("Inappropriate" = columns that will rarely if ever help queries to find the desired data.)

    3. Create duplicate indexes. (I once found a table with THREE identical indexes on it. BTW - an index on "Tbl(a,b)" is essentially the same as "Tbl(a) include (b)", so if you see two indexes like that, one of them can be dropped.)

    4. Use GUIDs for your primary index columns and watch your index pages split.

    5. Don't ever rebuild your indexes.

    They may have truncated the table, but you can still look at the index definitions and see how many of the above occurred... 😉

  • Perry Whittle

    SSC Guru

    Points: 233794

    The Gug (11/2/2015)


    Good afternoon

    I ran across an issue that I don't understand. I have a DB that is 177gb. When I dug through the DB, I found a table that said it had 8gb of data space used and 90gb of index space used. How does that happen?

    For future reference, you may find the query in the article below helpful

    http://www.sqlservercentral.com/scripts/Index+Size/128428/[/url]

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

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

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