SQL Server Performance

Table indexes

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by gabriel_ar, Aug 11, 2011.

  1. gabriel_ar New Member

    hi, im confused with indexes..
    After i execute this command "EXEC sp_spaceused INVIMV, 'true';" i see this

    name rows reserved data index_size unused
    INVIMV 15875 36104 KB 1448 KB 34600 KB 56 KB

    So i run

    ALTER INDEX ALL ON INVIMV
    REORGANIZE;

    The new results
    name rows reserved data index_size unused
    INVIMV 15875 8012 KB 1448 KB 4876 KB 1688 KB

    So i was happy to have an index size smaller, but i then run this to see what happends

    ALTER INDEX ALL ON INVIMV
    REBUILD;

    The new results

    name rows reserved data index_size unused
    INVIMV 15875 36112 KB 1448 KB 34592 KB 72 KB

    Why is that??? and i run REORGANIZE again , but didnt change any more.

    Thx...
    gabriel_ar, Aug 11, 2011
    #1

  2. Luis Martin Moderator

    I was testing severals tables and combinations without problems. Wait for others members opinions.
    Luis Martin, Aug 11, 2011
    #2

  3. FrankKalis Moderator

    What exactly now is your question? Are you concerned about the growth in space reserved when you rebuild your indexes?
    REORGANIZE just works on the leaf level of the indexes and physically reorders them, REBUILD drops and (re)creates the indexes. This requires enough temporary disk space to hold both the old and the new version of the index. See Reorganizing and Rebuilding Indexes for more details.
    FrankKalis, Aug 11, 2011
    #3

  4. satya Moderator

    REORG will only tackle the logical fragmentation there by sizes may not vary on the disk for that file, however the REBUILD (as Frank referred) will modify altogether based on number of rows pertaining to that index.
    satya, Aug 12, 2011
    #4

  5. gabriel_ar New Member

    My original problem was that the size of the database increase from 1.5 GB to 9 GB, in two days.
    So i looked what was happening, and i could see 1 index with 6 GB, 1 with 1GB and others like this one....
    But rebuild or reorganize didnt do anything.
    So my question is "Why the index has 6 GB and the DATA is only 111544 KB, i think that the index can be 400000 KB but not 6 GB.

    name rows reserved data index_size unused
    INVITP 1141953 6216256 KB 111544 KB 6103528 KB 1184 KB

    That is the table of 6 GB index.

    SAME table but in non production enviroment

    name rows reserved data index_size unused
    INVITP 1138041 190632 KB 121416 KB 68160 KB 1056 KB
    gabriel_ar, Aug 12, 2011
    #5

  6. gabriel_ar New Member

    the problem was the fill factor of one index (no cluster) set to 1%

    Thx u all.
    gabriel_ar, Aug 12, 2011
    #6

  7. Luis Martin Moderator

    Thanks for sharing.
    Luis Martin, Aug 12, 2011
    #7

Share This Page