How to identify fragmentation level of the table data itself not the table indexes, and then defrag

Question

Every where I can find descriptions on how to identify the fragmentation level of the table indexes, but I cant find how to identify the same for the table data iself. Also defragmentation solution is found for table indexes but for table data can not find.

I am not talking about physical fragmentation of the database files either, nor vertical/horizontal fragmentation of tables.

I have a database (SQLServer 2008 R2) with about 20 tables (among about other 20 tables) that are continuosly being inserted and deleted their records (which do not have indexes). Those tables will cause fragmentation of themselfs and the other tables inside the same filegroup.

So even if I make the filegroup files big to hold the data and reduced physical fragmentation due to auto grow, the internal fragmentation eventually will be increased, generating performance drops.

I hope my question gets picked for an answer.

Answer 1

Tables in SQL Server can be either organised with a clustered index or have no CI in which case they are a heap.

You need to look at sys.dm_db_index_physical_stats. Despite the name this also does analysis of heaps too (though logical fragmentation does not apply to these, pages cannot be out of logical order as there is no "correct" ordering in a heap).

For tables with a CI the clustered index has an index_id of 1. The leaf level of the CI is the table. For a heap this is given an index_id of 0.

Answer 2

If your table has a clustered index, the state of the index reflects the state of the table.

If it does not, the table is considered a HEAP. Heaps will show up in your contig reports, and you can see the fragmentation percentage along with the other index frag stats. Note that a heap index number will be zero.

One technique I can share with you for fixing fragmented heaps is to create, then drop a clustered index on the heap (This is assuming you aren't able to keep a clustered index on that table which is why it's a heap to begin with.) The act of creating the clustered index will reorder the heap as a result of the process.