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

fragmentationsql-server-2008

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.

Best Answer

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.

Related Question