Compression Table vs Index and how to eliminate fragmentation

Question

Forum,
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 compressionREBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF,DATA_COMPRESSION = PAGE)

--table compression ALTER TABLE [dbo].[B_sometable_huge] REBUILD WITH (DATA_COMPRESSION=PAGE);

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 

Answer 1

. 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

Nothing much, you should only keep in mind that no matter what compression it is, ultimately it would be either pages or rows of index or table that would be compressed. Since clustered index is table itself and when compressed you can say both table or index is compressed. For non clustered index the pages of index will be compressed when compression applied.

If table does not have index and still you want to compress it, then it would be purely table compression with no index. Quoting from Official MS doc

You can configure row and page compression on the following database objects:

A whole table that is stored as a heap.
A whole table that is stored as a clustered index.
A whole nonclustered index.
A whole indexed view.
For partitioned tables and indexes, you can configure the compression option for each partition, and the various partitions of an object do not have to have the same compression setting.

Read MS docs they have lot of valuable information.

I have heap tables which im compressing with adding a Clustered index with the following script .--Index

You simply have to create a clustered index with data compression or page compression

CREATE CLUSTERED INDEX CI_INDEX_1  
  ON Heap_Talle (C2)  
  WITH (DATA_COMPRESSION = ROW);  
GO  

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.

You are doing correct way. If fragmentation > 30 % rebuild and if between 10-30 reorganize. Also for small indexes have page_count < 2000 do not do anything.

Regarding using LIMITED, DETAILED or SAMPLED scan I would suggest you to read Paul Randal's Blog inside-sys-dm_db_index_physical_stats

Answer 2

Contrary to all that has been said on this thread, data compression and fragmentation are anything BUT separate.

ROW compression changes a lot of fixed width datatypes to variable width datatypes. Now, if you look at the documentation, it says you won't get much savings for the DATETIME datatype but let's consider a column that many people have in their tables... the "ModifedDate" column. Most people make the mistake of leaving it as NULL. That will have a compressed size of only 1 byte. Guess what happens when you update that compressed data to the current value of GETDATE()? BOOM! An "ExpAnsive" update for 1 byte to 7 bytes occurs. Even if all of your other columns are fixed width, that one column alone is enough to cause some pretty serious fragmentation.

And, chances are, that's not the only previously fixed width column that you're updating.

And, guess what? Just in case you didn't know, PAGE compression is first ROW compressed before the dictionary algorithm is applied and so the same and worse can happen with PAGE compression.

And guess what happens to your HEAP when such "ExpAnsive" updates occur? Yep... forwarded rows.

As for fixing the fragmentation, the supposed "Best Practice" of using REORGANIZE for 5 to 30% and REBUILD for > 30% is and always has been pure rubbish. In fact, it was never meant to be a "Best Practice" and, in most cases, is the WORST Practice. It's so bad that it's actually the real reason why people think Random GUIDs are bad for fragmentation when, in fact, they're the very epitome of what people think and index should operate like. It's because REORGANIZE does NOT work like people think it does.

If you want proof of that and want to see the destruction of many other common and seriously dangerous myths about fragmentation and index maintenance, watch the following 88 minute tube including the out-take after the Q'n'A section. You'll never look at indexes or index maintenance the same way ever again.

https://www.youtube.com/watch?v=rvZwMNJxqVo

And, be aware that they added 3 advertisements at the 15:00, 30:15, and 45:25. The advertisements are important to help support this event but I wanted to let you know that they're sudden and they're loud! They WILL lift your headset!

Again, though... stop treating data compression and fragmentation as separate issues because they're NOT.

Answer 3

A table is either a heap or a clustered table. It can't be both. If you have a heap table and create a clustered index, you no longer have a heap table - you now have a clustered table.

So, your first decision is whether you want your table to be heaps or clustered tables. Most people avoid heap tables. This decision isn't related to compression.

After making above decision, you decide whether the actual data (being the heap or the clustered index) should be compressed, and which level of compression. PAGE or ROW.

Another decision is whether your non-clustered indexes should be compressed. And, again, ROW or PAGE.

Compression isn't related to fragmentation. Treat the two as separate things. There's a type of fragmentation for heaps (forwarded records) and of course also fragmentation for indexes. Whether you want to do anything about fragmentation is a decision for you to make. We can't do that for you.

Each of the topics you mention (and I elaborate on above) can generate lots and lots of discussion. It is not possible for us to say how and what you should do, we know nothing about your environment, load pattern, business requirements etc. Don't expect simple answers. Make sure you understand the concepts and when you feel confident with that you can come back here with more specific questions.