How does fillfactor impact on big data load

I förra bloggen tittade vi på hur tabell-designen påverkar laddtider. Alla dessa laddningar gjordes utan att vi gjorde någon rebuild (reorganisation) av indexen.

Anta att vi nu vill ladda 24 miljoner rader (i en batch) varje dag. Vi vill också göra en rebuild av det icke klustrade indexet som finns på tabellen en gång i veckan.

Om vi börjar med fillfactor = 100 % så får vi följande ladd tider:

Här ser vi att laddtiden (blå linje) har en pik dagen efter varje rebuilde (orange linje). När man packat ihop indexet med (fillfactor = 100) och sen laddar på många rader igen, så måste det göras plats för de nya raderna på rätt ställe i indexet det vill säga det sker många så kallade pagesplits.

Om man tittar på antal pages i indexet och vad som händer när man gör rebuild och har fillfactor= 100.

Man ser att antalet pages minskar med ca ½ miljon pages. Här packas alltså indexet ihop för att få plats med så många index-rader per 8k-sida som möjligt (100% fullt).

När man sedan laddar 24 miljoner nya rader som skall placeras på rätt ställe i indexet, så innebär det att väldigt många sidor måste delas för att vi skall få in de nya raderna. Det innebär i detta fall att vi får drygt 1 miljon nya sidor under första laddningen efter rebuild. Därefter har vi fått så mycket plats i indexet att vi får in hela veckas rader utan att behöva skapa fler sidor.

Om vi då istället skapar lite plats på alla dessa sidor när vi gör rebuild så borde vi ju kunna klara oss.

Hur ser det ut med fillfactor på 90% och 80%?

Det blev lite bättre, det vill säga vi fick in lite rader innan sidorna var fulla och det var dags för page splitt.

Vid fillfactor på 90 % så klarade vi bara 1 dag innan det var dags medan vi klarade 3 dagar med en fillfactor på 80%.

Samtidigt blir ju indexen lite större eftersom det finns en del luft i alla sidor (10% resp. 20%).

Om man inte gör rebuild överhuvud taget så får man en jämnare fördelning när sidorna har blivit full, vilket gör att skapandet av nya sidor sprids ut över flera dagar. Därmed blir det en jämnare laddtid sett över en vecka.

Alla ovanstående tester är gjorda med rebuild och bara tittat på laddning, ingen hänsyn taget till annat som kan påverka antalet sidor och behovet av rebuild.

Hur ska man göra då?

Svaret är väl som ofta i SQL Server världen, det beror på. En av det viktigaste faktorerna att titta på är ju hur ofta man kan göra ReBuild och anpassa fillfactor därefter. Man kan även fundera på om det är OK att hoppa över ReBuild på så pass stora tabeller.

Tabell utseende, med olika fillfactor

-- CL index (Id) + NonCL index (UnitId + Ts) 
Create Table Test 
(Id   BigInt Identity(1,1) NOT NULL,
 UnitId Int NOT NULL,
 Ts   SmallDateTime NOT NULL,
 V1   Decimal(15,3) NULL,
 V2   Decimal(15,3) NULL,
 V3   Decimal(15,3) NULL,
 V4   Decimal(15,3) NULL,
 Status Int NOT NULL,
 Comment varchar(200) NULL,
 ExpFlag Int NOT NULL,
 Constraint PK_Test_Id Primary Key Clustered (Id)) On [Primary] 
GO 
Create NonClustered Index IX_Test_UnitIdTs on Test (UnitId, Ts) on [Primary] With (Fillfactor = 100) 
GO