Defragmentering av Heap:ar

När man uppdaterar informationen i en tabell så sker normalt sett en viss fragmentering. Den fragmenteringen gör att tabellen tar upp mer utrymme (fler 8K sidor) än vad den skulle behöva. Vilket i sin tur resulterar i att alla sökningar i tabellen tar längre tid eftersom det är mer att söka igenom.

Har man ett clustrat index på tabellen så sker en defragmentering när man gör en reorg/rebuild av det clustrade indexet.

Vad gör man då om man inte har ett clustrat index på tabellen? Då sker ju ingen defragmentering.

Det första alternativet är givetvis att skapa ett lämpligt clustrat index. Men det finns lägen där detta inte är möjligt t.ex. du vet inte vad som är en unik nyckel till tabellen eller om du har en köpt applikation där supporten upphör att gälla om man ändrar något i databasen.

Om du har en SQL Server version som är äldre än 2008 finns nog bara ett sätt att hantera detta på.

• Skapa ett ur ditt tycke vettigt clustrat index som förslagsvis gör att nya rader till tabellen kommer att hamna sist i tabellen.
• När det clustrade indexet är skapat och tabellen packad. Ta bort det clustrade indexet.

Om man har en SQL Server version from 2008 eller senare finns en ny funktion ihop med Alter Table.

Genom att skriva: Alter Table database.schema.table REBUILD; så kommer det att ske en rebuild av hela tabellen inkl alla dess index.

Detta är ett mycket enkelt sätt att defragmentera en Heap.

Här är ett enkelt exempel på hur man kan söka igenom alla heap:ar om de har mer än 10 % fragmentering.

Declare @ObjectId int;
Declare @ObjectName varchar(255);
Declare @sql varchar(3000);
Declare @Page_Space_Dev decimal(10,2);
 
Declare Heaps Cursor Static For 
   Select i.object_id, s.name + '.' +o.name
   From sys.indexes as i 
   Inner Join sys.objects as o ON o.object_id = i.object_id
   Inner Join sys.schemas as s ON o.schema_id = s.schema_id
   Where i.type_desc = 'HEAP' and o.type_desc = 'USER_TABLE';
 
Open Heaps
Fetch From Heaps Into @ObjectId, @ObjectName;
While @@FETCH_STATUS = 0
Begin
    Select @Page_Space_Dev = Case When P.avg_record_size_in_bytes > 0
                             Then ((FLOOR(8060/P.avg_record_size_in_bytes) 
                                   * P.avg_record_size_in_bytes) / 8060) 
                                   * 100 - P.avg_page_space_used_in_percent
                             Else 0
                             End 
    From sys.dm_db_index_physical_stats(DB_ID(), @ObjectId, 0, NULL, 'DETAILED') AS p
    Where p.alloc_unit_type_desc = 'IN_ROW_DATA' and p.page_count > 0;
    
    If @Page_Space_Dev > 10
    Begin
       Set @sql = 'Alter Table ' + @ObjectName + ' ReBuild'
       Exec(@sql)
    End
    Fetch From Heaps Into @ObjectId, @ObjectName;
End
Close Heaps;
Deallocate Heaps;