Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

Indizes defragmentieren

15. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Im Groben, Großen und Ganzen läuft eine Microsoft Sql Server Datenbank so ohne weiteres Zutun, wenn sie richtig konfiguriert ist. Alles Indizes und Statistiken werden automatisch aktualisiert, man muss sich eigentlich um nichts Weiteres kümmern.

Um eine optimale Performanz bei zu behalten, sollte man seinen Indizes und Statistiken eine regelmäßige Pflege zukommen lassen. Dazu legt man einen Wartungsplan an, der diese neue aufbaut; dazu gibt es extra einen Wizard mit den nötigen Optionen, was einem schon zeigt, dass das auch von Microsoft als regelmäßige Wartung angesehen wird.

Das Re-Indizieren hat zum einen das kleine Problem, das es eine Offline-Operation ist (Ausnahme: Enterprise Edition, da geht es auch Online), die betroffene Tabelle ist für den Zeitraum gesperrt.

Zum Anderen ist es mit wachsender Tabellengröße eine zeitaufwendige Angelegenheit und es wird alles mit protokolliert, wodurch das Transaktionsprotokoll entsprechend anwächst.

Eine Alternative stellt das Defragmentieren von Indizes dar. Es ist zwar nicht so effektive wie ein Reindex, dafür läuft es schneller ab und ist eine echte Online-Operation, auf die betroffene Tabelle kann also weiterhin zugegriffen werden. Man sollte aber trotzdem dabei nicht vergessen, dass es eine entsprechend hohe I/O Last erzeugt.

Hier ein T-SQL Script, das über die Fragmentierung-Informationen der Indizes diese ermittelt und defragmentiert.

 
 -- Indizes in absteigender Reihenfolge nach 
 -- Umfang der Fragmentation defragmentieren 
 DECLARE @TableName sysname, @IndexID smallint, @IndexName sysname; 
 DECLARE @MinFragPer float, @FragScale as varchar(5); 
 SET @MinFragPer = 10.0 -- Min Fragmentierungsgrad in Prozent 
   
 -- Alle relevanten Indizes ermitteln, die den Mindest-Fragmentierungsgrad 
 -- übersteigen 
 DECLARE Fragment CURSOR LOCAL FOR  
  SELECT SCH.name + '.' + OBJ.name AS TableName,  
  IDX.index_id AS IndexID, IDX.name as IndexName, 
  CONVERT(varchar(5), ROUND(FRG.avg_fragmentation_in_percent, 2)) AS FragScale 
  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS FRG 
  INNER JOIN sys.objects AS OBJ 
  ON FRG.object_id = OBJ.object_id 
  INNER JOIN sys.schemas as SCH 
  ON OBJ.schema_id = SCH.schema_id 
  INNER JOIN sys.indexes AS IDX 
  ON FRG.index_id = IDX.index_id 
  AND FRG.object_id = IDX.object_id 
  -- Nur wirklich fragmentierte IDX rausfiltern 
  WHERE IDX.index_id > 0 -- keine Heaps 
  AND FRG.avg_fragment_size_in_pages > 1 -- min. über eine Seite 
  AND FRG.avg_fragmentation_in_percent > @MinFragPer 
 ORDER BY FRG.fragment_count DESC, FRG.page_count DESC, FRG.avg_fragment_size_in_pages DESC 
   
 -- Cursor mit den Tabellen / Indizes öffnen 
 OPEN Fragment 
 FETCH NEXT FROM Fragment 
  INTO @TableName, @IndexID, @IndexName, @FragScale 
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
  -- Info Ausgabe 
  PRINT @Tablename + ' - ' +  
   @IndexName + '(' +CONVERT(varchar(10), @IndexID) + ') = ' + 
   @FragScale + ' %' 
   
  FETCH NEXT FROM Fragment 
  INTO @TableName, @IndexID, @IndexName, @FragScale 
  DBCC INDEXDEFRAG (0, @TableName, @IndexID) WITH NO_INFOMSGS  
 END 
   
 -- Cursor schließen und abräumen 
 CLOSE Fragment 
 DEALLOCATE Fragment 

 

Protokollausgabe:

 

 Production.WorkOrder - IX_WorkOrder_ProductID(3) = 10.1 % 
 Production.BillOfMaterials - PK_BillOfMaterials_BillOfMaterialsID(2) = 33.33 % 
 Production.BillOfMaterials - AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate(1) = 15 % 
 Purchasing.PurchaseOrderDetail - IX_PurchaseOrderDetail_ProductID(2) = 17.65 % 
 Production.Product - PK_Product_ProductID(1) = 23.08 % 
 Production.BillOfMaterials - IX_BillOfMaterials_UnitMeasureCode(3) = 30 % 
 Production.ProductInventory - PK_ProductInventory_ProductID_LocationID(1) = 42.86 % 
 HumanResources.JobCandidate - PK_JobCandidate_JobCandidateID(1) = 18.18 % 
 Purchasing.PurchaseOrderHeader - IX_PurchaseOrderHeader_VendorID(2) = 33.33 % 
 Purchasing.PurchaseOrderHeader - IX_PurchaseOrderHeader_EmployeeID(3) = 33.33 % 
 Sales.StoreContact - PK_StoreContact_CustomerID_ContactID(1) = 20 % 
 Sales.SpecialOfferProduct - PK_SpecialOfferProduct_SpecialOfferID_ProductID(1) = 33.33 %
Production.ProductCostHistory - PK_ProductCostHistory_ProductID_StartDate(1) = 33.33 %

Diesen Post teilen

Repost 0

Kommentiere diesen Post