SQL Server Performance

Why is Shrinkfile slower on heaps vs. clustered indexes?

Discussion in 'SQL Server 2005 General DBA Questions' started by DBADave, May 11, 2009.

  1. DBADave New Member

    We need to shrink a 600GB database. The vendor has numerous fields defined as Decimal (28,12). They approved changing all Decimal to VarDecimal. This change will save us over 400GB of space. Some of the larger tables do not contain clustered indexes and as a result the shrinkfile could take more then 14 hours. We found that adding a clustered index to the largest tables caused the shrinkfile to take about 1.5 hours in our test environment. Why does a shrinkfile operation perform better with clustered indexes then heaps? I know the data is reorganized to the front of the database during shrinkfile, but I don't know why a clustered index would make a big difference.

    Thanks, Dave
  2. MohammedU New Member

    Only I think of when you create the clustered index data is organized based ont he key and it will be fast to move continuouse data than the random one.
  3. moh_hassan20 New Member

    It is nice to hear the safe in storage due to vardecimal format.
    The Vardecimal was good compression for decimal fields starting from SQL 2005 SP2.
    but i was surprised, when sql 2008 is released and microsoft say:

    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ROW and PAGE compression instead. For more information, see Creating Compressed Tables and Indexes.

    review: http://msdn.microsoft.com/en-us/library/bb326755.aspx
    It is necessary when using vardecimal, to study the limitations of the Vardecimal Storage Format in http://msdn.microsoft.com/en-us/library/bb326755.aspx
  4. DBADave New Member

    Yes. We noticed it will not be supported after 2008. In this particular case it is vendor code so we don't need to be concerned about anything beyond 2008. We're just happy to get significant disk space back.
    Dave
  5. satya Moderator

    Have you tried REORGANIZE or REBUILD that indexes (On tables) then SHRINK the LOG file, as it performs better in any case.

Share This Page