Table Fragmentation

Question

If I execute the following:

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent as avg,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID('dbname'), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID('dbname')
ORDER BY indexstats.avg_fragmentation_in_percent desc

It shows fragmentation level. I then ran Ola Hallengren's index optimise script which obviously reduces indexes.

If I run the query again it now shows high fragmentation in tables without an index e.g:

Schema  Table       Index   alloc_unit_type_desc   avg                 page_cont
dbo     tablename   NULL    IN_ROW_DATA            99.4362934362934    8176

Should I be concerned by these?

Anything I can do?

Anything I should be doing?

We are experiencing performance issues. I am aware that 2008 is not ideal and that is being addressed.

The datafile is also showing as 220gb but the actual space used is 140gb.

Answer 1

Ola Hallengren's index optimize script does not perform Heap defragmentation.

In other words table rebuilds are not happening when running the procedure and that is expected.

You could run ALTER TABLE dbo.tablename REBUILD; to remove the fragmentation.

This also rebuilds the nonclustered indexes on the heap.

Better option than rebuilding your heap tables

You do have to ask yourself or the application team why you can't add a clustered index to the table. as rebuilding the heap uses a lot of resources and is a temporary solution.

If you add a clustered index, that index is included in the Index Optimize script, and fragmentation will be removed when the script runs.

Another option is to add the clustered index and stop worrying about index fragmentation.

---

We are experiencing performance issues. I am aware that 2008 is not ideal and that is being addressed.

It could be that it is because of the forwarded records in the heap tables, but at 8167 pages I would say that that is not likely. A starting point could be looking at the queries being executed on your server.

SP_BlitzCache can help with finding your worst performing queries.

---

The datafile is also showing as 220gb but the actual space used is 140gb.

This should not be a problem. Apart from disk space you should not be too worried. You are not relying on autogrowth, which is a good thing.