Compression Table vs Index and how to eliminate fragmentation

  • JSB_89

    Ten Centuries

    Points: 1065


    I have a question regarding compression .. what is the difference between table and index compression (I have googled but it returns we have both table and index )do we need to have both or just index compression . I have heap tables which im compressing with adding a Clustered index with the following script .

    --Index compression


    --table compression


    Do i have to do both which would go first and why ? please shed some light on this.

    Most of the tables are fragmented nearly 100% and once rebuilding indexes is done will it eliminate fragmentation or do we need to run defrag when does this have to be run . how do we eliminate fragmentation  if im doing the incorrect way.

    -- the scanning mode for index statistics

    --available values: 'DEFAULT', NULL, 'LIMITED', 'SAMPLED', or 'DETAILED'

    Best practice to use for mode for index statistics -- Detailed /Sampled (I used Detailed to be through). I'm aware of the Ola maintenance scripts but our policy does not allow to use them so have to use custom scripts ..

    thanks in advance



  • Mr. Brian Gale


    Points: 23167

    There is a lot in this question.

    First, a "heap" with a "clustered index" is no longer a heap.  A heap is an unordered set of data.  A clustered index orders the data.

    Next, if your clustered index has data compression turned on, then the table does too as they are the same thing on disk. NON-clustered indexes though would not benefit from table data compression. See:

    This page has a good reference on fragmentation:

    And for the last question about the scanning mode for index statistics, this is just for reporting purposes.  I am assuming this is when you run something like:

    SELECT *
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , 'SAMPLED')

    Microsoft is going to be your best bet for details on that:

    But basically, it just changes how hard it looks at the indexes to ensure data accuracy.  Limited doesn't look at the leaf level, sampled looks at the leaf level but estimates the compression, detailed looks at everything to get you accurate information.  Limited will be the fastest, sampled will be slower, detailed will be the slowest.  To quote the article:

    With LIMITED mode, compressed_page_count is NULL because the Database Engine only scans non-leaf pages of the B-tree and the IAM and PFS pages of the heap. Use SAMPLED mode to get an estimated value for compressed_page_count, and use DETAILED mode to get the actual value for compressed_page_count. The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. Results in SAMPLED mode should be regarded as approximate. If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.



    Does the above answer your questions?


  • JSB_89

    Ten Centuries

    Points: 1065

    @Brain -- Thanks for clarifying ..appreciate all your help !!



  • ScottPletcher

    SSC Guru

    Points: 98559

    To clarify, you can compress non-clus indexes, and should often do so, but you must compress each one separately.

    Also, 80% is a very low number overall, are you sure you want to leave 20% of the table empty?

    Once you've rebuilt an index, it's done.  Definitely no need to do a REORG after a REBUID.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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