SQL Server indexering – Analys av hastighet vid fragmentering

Många använder idag Ola Hallengrens Maintenance Solution för att hantera sina SQL Servrar. Denna lösning åtgärdar bland annat fragmenterade index. Ett fragmenterat index kan orsaka prestandaproblem vid “index scans”, speciellt om du har ett långsamt disksystem.

Fragmentering uppstår när SQL Server vill skriva till en 8kb page som är full och då skapas ytterligare en 8kb page och hälften av informationen flyttas in i den nya pagen. Vad som kunde ha varit en skrivning på 8kb blir helt plötsligt 16kb och du har nu även två halvfulla pages. Att läsa upp den ursprungliga informationen kräver nu dubbelt så mycket IO eftersom den nu är delad på 2 pages.

Vid en defragmentering så flyttas informationen om igen så att den urprungliga fyllnadsgraden återfås. MEN den vanligaste inställningen för fyllnadsgraden är 0 vilket är detsamma som 100%! Det innebär då att nästa skrivning blir en page split igen såvida det inte är en ny post och alla index är på kolumner som är autoinkrementella.

Vilken fyllnadsgrad skall man ha på ett index då? Ja det är en omtvistad fråga eftersom lägre fyllnadsgrad kan komma på bekostnad av längre svarstider vid läsning, men någonstans mellan 80-100% brukar vara ett lagom intervall.

Hur tar man reda på vilken fyllnadsgrad som man skall ha då? Ja ett sätt är att titta på hur snabbt indexen blir fragmenterade. Använder man Ola Hallengrens Maintenenance Solution så loggas all defragmentering i master databasen. Denna logg kan med fördel användas för att förstå hur snabbt fragmenteringen uppstår. För att underlätta denna analys så har jag tagit fram ett skript som tittar på hur många dagar det är mellan defragmentering av ett index:

;with cte
AS
(
select 
	cl.DatabaseName,
	cl.SchemaName, 
	cl.ObjectName, 
	cl.IndexName, 
	cl.Command, 
	cl.StartTime,
	Pages = node.value('./PageCount[1]','int') ,
	Fragmentation = node.value('./Fragmentation[1]','DECIMAL(7,4)') ,
	FragmentationRate = DATEDIFF(dd, LAG(StartTime,1) OVER(PARTITION BY cl.DatabaseName, cl.SchemaName, cl.ObjectName, cl.IndexName, cl.PartitionNumber ORDER BY cl.StartTime),StartTime)
from CommandLog cl
cross apply cl.ExtendedInfo.nodes('//ExtendedInfo') as ei(node)
)
SELECT DatabaseName, SchemaName, ObjectName, IndexName, avgFragmentation = avg(Fragmentation), avgFragmentationRate = avg(FragmentationRate)  
FROM cte WHERE FragmentationRate <5 and Fragmentation > 15 and Pages >64
GROUP BY DatabaseName, SchemaName, ObjectName, IndexName
ORDER BY DatabaseName, SchemaName, ObjectName, IndexName

Detta skript hämtar alla index som fragmenteras till mer än 15% på mindre än 5 dagar. Jag har i det här fallet valt mindre än 5 dagar eftersom en arbetsvecka normalt är 5 dagar, men detta värde kan man anpassa efter sin egen situation.

Hur vet man om den nya fyllnadsgraden blev bra då? Jo, det är tyvärr bara tester som gäller. Har man satt den för lågt så kommer läsningarna ta längre tid och sätter man den för högt så kommer indexet fortsätta att fragmenteras för snabbt.