/fdata%2F2470622%2Favatar-blog-1074652166-tmpphpjYbJuw.jpg){kind=avatar}
Indizes defragmentieren
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 %
/idata%2F2470622%2FDESCRIBE_SC1.jpg)
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
