Heap Index Fragmentation

1

Is there a way to fix the heap index fragmentation?

Thank You in advance.

VT

2

Do you mean that you have a table without a clustered index but one or more non-clustered indexes?

In general you can use ALTER INDEX REBUILD/REORGANIZE to remove/reduce fragmentation.

3

Yes table without clustered index but one or more non-clustered indexes.

Rebuild or Reorg indexes will work for clustered and non-clustered indexes. Not for the Heap. Is there a way to fix the heap index fragmentation.

4

Wait, you asked about

fix the heap index fragmentation

Since the data is stored as a heap, there are two ways that I can think of to get fragmentation:

  1. Delete lots of rows
  2. Update lots of rows in such a way as to cause the rows' lengths to grow or shrink.

If that really is the case and the problem is severe (how bad is the fragmentation?) you could create a new table with the same schema, do:

SELECT ...
INTO newtable
FROM Old_Fragmented_table

Then rename both tables:

EXEC sp_rename 'Old_Fragmented_table', 'Old_Fragmented_table_really_old';
EXEC sp_rename 'newtable', 'Old_Fragmented_table';

If all is well, drop the orignal table:

DROP TABLE  'Old_Fragmented_table_really_old';
5

You can do it only indirectly. You have to build a clustered index and then drop it; be sure to explicitly specify a FILLFACTOR, since the default could be 90 (or even lower). That will return the table to a heap but with the rows defragmented.

6

The real problem with heaps is the forwarded record count.

We have a third party app that insists on using heaps; for some unfathomable reason they will not give support if clustered indexes are put on the tables. Fortunately the app is not used much at the weekend so I can run the following ever Saturday:

DECLARE HeapCursor CURSOR
FOR
SELECT
    N'[' + SCHEMA_NAME(O.[schema_id]) + N'].[' + OBJECT_NAME(S.[object_id]) + N']' AS TableName
FROM sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT, DEFAULT) S
    JOIN sys.objects O
        ON S.[object_id] = O.[object_id]
WHERE S.index_type_desc = 'HEAP'
    AND S.forwarded_record_count > 5
ORDER BY S.avg_fragmentation_in_percent DESC

OPEN HeapCursor;
FETCH NEXT FROM HeapCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'ALTER TABLE ' + @TableName + ' REBUILD;';
    EXEC (@sql);
    FETCH NEXT FROM HeapCursor INTO @TableName;
END
CLOSE HeapCursor;
DEALLOCATE HeapCursor;

Be aware that rebuilding the table will rebuild all it's indexes.

7

We are introducing new requirements for 3rd party suppliers.

All tables must have clustered index, or be given a specific exception allowance (table by table)

NOLOCK (and any equivalent "dirty reads" alternatives) not permitted in code. Again, exceptions are permitted, but have to be individually approved.

There are probably other things that I should add to my list ...

8

Good call Kristen!

9

If you have suggestions for "banned materials" please let me know. I don't want to have to negotiate this more than once with suppliers!

Turns out the main 3rd party provider using NOLOCK is using a syntax that is deprecated in 2014 (no WITH) so they are going to have to visit their code anyway, which I am hopeful will help.

10

Remembered one to add!

Backups may only be taken with our in-house backup solution. I don't want some Hot Shot taking a Full, or Log, backup to the C: drive of their Laptop ... which will be a million miles away when we need to restore.

We have an Sproc that will take a backup, just provide the database name and whether Full, Diff or Log backup required (add a comment too, if you like - "Next step is hairy, I might need this one" ... or whatever!)