Post reply

Table Size

  • July 29, 2009 at 11:27 pm

    #207513

    Hi all,

    Can any one tell me how to get table size banker? to which optimal size it should to shrink?

    i have table with rows around 20 lakh.

    Does shrinking table help us in performance?

    Thanks,

    Nero

  • July 30, 2009 at 1:32 am

    #1032218

    Nero (7/29/2009)

    Hi all,

    Can any one tell me how to get table size banker? to which optimal size it should to shrink?

    i have table with rows around 20 lakh.

    Can u explain more here.

    Does shrinking table help us in performance?

    It degrades the performance.


    Pradeep Singh

  • July 30, 2009 at 1:49 am

    #1032225

    hi ps,

    Explain?

  • July 30, 2009 at 1:54 am

    #1032226

    quoting from BOL

    A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree.

    This means fragmentation will be higher after you perform shrink operation on the database which means IO will be more and hence you'll get degraded performance.

    What do you want to do isn't very clear to me....


    Pradeep Singh

  • May 13, 2010 at 9:19 am

    #1166993

    I have a similar situation where I added a single int column to a table that was ~ 60M rows and consuming ~10GB space. After adding the column and populating it with an UPDATE, it has grown to ~20GB. There is a single PK on it. WHy did it double in size?

  • May 13, 2010 at 1:51 pm

    #1167174

    Run dbcc showcontig for that table and check the fragmentation.

  • May 13, 2010 at 4:03 pm

    #1167224

    Yeah, that did it! - it had 90% fragmentaton and data pages were only 50% used. Rebuilding indexes brought it down to its original size.

    But I'm still curious as to why this happens when all I did was add a column and populate it. How does that affect index?

    Thanks!

    Paul

Viewing 7 posts - 1 through 6 (of 6 total)

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