Frammentazione, rebuild, reorg: il mito del 30%

“Fai il rebuild degli indici con più del 30% di frammentazione. Reorganize tra il 5% e il 30%. Lascia stare il resto.”

Questo consiglio si trova in un milione di tutorial, nei vecchi documenti Microsoft, e nei maintenance plan della maggior parte delle aziende. Viene da un singolo esempio che Paul Randal (che letteralmente ha scritto lo storage engine di SQL Server) diede in un post su un forum nel 2008. Da allora ha passato 17 anni a dire “scusatemi, smettete di citarmi su quello, mi sono inventato i numeri a scopo illustrativo.”

Oggi parliamo di cosa conta davvero per la manutenzione degli indici nel 2026, su storage SSD moderno, con RCSI abilitato. È meno di quello che ti hanno raccontato e più semplice dei vecchi maintenance plan.

Cos’è davvero la frammentazione

Due tipi, confusamente simili nel nome:

• Frammentazione esterna (logica) — l’ordine fisico delle pagine foglia dell’indice non corrisponde all’ordine logico. Una range scan saltella sul disco.
• Frammentazione interna — le pagine foglia non sono piene. Pagine mezze vuote sprecano RAM e disco.

sys.dm_db_index_physical_stats riporta entrambe:

SELECT
    OBJECT_NAME(ips.object_id)                     AS table_name,
    i.name                                          AS index_name,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent               AS logical_frag_pct,
    ips.avg_page_space_used_in_percent             AS page_fill_pct,
    ips.page_count,
    ips.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
    ON i.object_id = ips.object_id
   AND i.index_id  = ips.index_id
WHERE ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

La modalità 'SAMPLED' è veloce. 'DETAILED' è approfondita ma può richiedere ore su tabelle enormi. Inizia con sampled.

Perché il vecchio consiglio era per i dischi a piatti rotanti

Il consiglio “rebuild oltre il 30%” aveva senso quando:

• Lo storage era a dischi rotanti. L’I/O random era 10× più lento di quello sequenziale.
• Le range scan erano il pattern di accesso dominante.
• I server avevano 32GB di RAM e non potevano tenere molti dati in cache.
• SQL Server non poteva fare batch mode o scan parallele moderne.

Nel 2026:

• La maggior parte dei SQL Server di produzione gira su SSD o NVMe. L’I/O random è quasi veloce quanto quello sequenziale.
• La memoria costa poco. La maggior parte dei dati caldi vive in RAM (il buffer pool). La frammentazione delle pagine sul disco è irrilevante quando la pagina è in cache.
• I workload sono diversi — i seek su indici stretti dominano nell’OLTP, e il pattern del covering index significa meno range scan grandi.
• Columnstore e batch mode hanno pattern di storage tutti loro.

Il netto: la frammentazione conta meno di quanto contava nel 2008. Non è gratis — fare il rebuild di pagine deframmentate aiuta ancora le scan grandi e riempie meglio le pagine — ma non è più la priorità numero uno.

Cosa fare davvero

Consigli pragmatici per un workload Runehold-sized nel 2026:

1. Non far girare la manutenzione ogni notte. Settimanale di solito va bene; alcune aziende vanno mensili.
2. Usa gli script di Ola Hallengren, non il maintenance plan integrato. Li copriremo nella lezione 35.
3. Salta tutto sotto le ~1.000 pagine. La manutenzione su indici minuscoli è puro overhead.
4. Preferisci REORGANIZE di default per la frammentazione moderata. Online, basso impatto, non prende lock che bloccano le query.
5. Usa REBUILD WITH (ONLINE = ON) (solo Enterprise) per la frammentazione pesante su tabelle calde.
6. Su Standard Edition, REBUILD prende uno schema lock — fallo in una finestra di manutenzione.
7. Esegui UPDATE STATISTICS ... WITH FULLSCAN più spesso di quanto pensi. Lo scostamento delle statistiche causa più regressioni di query della frammentazione.

Rebuild vs reorganize

Confronto rapido:

Aspetto	REBUILD	REORGANIZE
Ricostruisce le pagine foglia	Sì	Sì
Ricostruisce le pagine intermedie	Sì	No
Aggiorna le statistiche	Sì (full scan)	No
Online (Enterprise)	Sì con ONLINE = ON	Sempre online
Online (Standard)	No (schema lock)	Sempre online
Memoria richiesta	Alta	Bassa
Si può interrompere	No (rollback)	Sì (riprende dalla volta successiva)
Usalo quando…	Frammentazione > 30% E ti puoi permettere il rebuild	Frammentazione 5-30%, vincoli di online

REORGANIZE non aggiorna le statistiche. Se lo esegui, fallo seguire da un UPDATE STATISTICS sullo stesso indice. REBUILD aggiorna le stats gratis.

Le statistiche battono la frammentazione

Paul Randal lo ha detto così tante volte che meriterebbe di essere il titolo di un libro: prima aggiorna le statistiche, poi preoccupati della frammentazione. Le stats obsolete fanno scegliere all’ottimizzatore piani sbagliati, il che rallenta le query di ordini di grandezza. La frammentazione le rallenta forse del 10-20% sulle scan. Priorità.

-- Aggiornamento delle stats nucleare-dall'orbita
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

-- Tutto nel database
EXEC sp_updatestats;

Gli script di Ola lo fanno intelligentemente — campionano le tabelle che ne hanno bisogno, saltano il resto. Resta la scelta giusta.

Page fill factor

FILLFACTOR dice a SQL Server quanto riempire le pagine foglia quando costruisce o ricostruisce un indice. Il default è 0 (trattato come 100%). Un fill factor più basso lascia spazio su ogni pagina per insert/update futuri, riducendo i page split.

CREATE NONCLUSTERED INDEX IX_Customer_Email
    ON Sales.Customer (Email)
    WITH (FILLFACTOR = 90);

Regola pratica:

• Tabelle append-only (log di eventi, storico ordini): FILLFACTOR = 100. Niente insert nel mezzo; usa tutto lo spazio.
• Tabelle con update pesanti su pagine intermedie (OLTP caldo con scritture non sequenziali): 85-95%. Lascia spazio per update senza split.
• Qualsiasi cosa con GUID come chiave clustered: fermati, sistema lo schema (lezione 21), poi pensa al fill factor.

Un fill factor più basso significa più pagine, che significa più RAM usata. Compromesso. Non impostare FILLFACTOR = 70 “per sicurezza” — stai solo sprecando memoria.

L’avvertimento su SHRINK, perché salta fuori ogni mese

Ogni junior DBA prima o poi esegue DBCC SHRINKDATABASE “per recuperare spazio.” Non farlo.

SHRINKDATABASE e SHRINKFILE spostano pagine dalla fine del file all’inizio, mescolandole arbitrariamente. Ogni indice che toccano viene massimamente frammentato nel processo. Finisci con:

• Un file più piccolo (bene).
• Frammentazione terribile su ogni indice grande (male).
• Eventi di autogrowth che fanno tornare il file grande in pochi giorni (triste).

Regola: non fare mai SHRINK sui database di produzione. Se hai bisogno di spazio, la mossa giusta di solito è:

• Liberare disco da qualche altra parte.
• Archiviare / fare partition-switch-out dei dati vecchi prima.
• Eliminare gli indici che non vengono usati (la lezione 22 ha la query).
• Come ultima spiaggia, SHRINKFILE (file, truncateonly) ritaglia solo lo spazio libero alla fine del file senza spostare le pagine.

Quando la frammentazione conta davvero

Lista del mondo reale:

• Range scan grandi su un indice caldo basato su disco. La frammentazione logica costa I/O reale.
• Replica di lettura pesanti dove le query girano su disco (mai completamente in cache).
• Tabelle clustered su GUID dove ogni insert causa page split. I rebuild aiutano per un giorno; sistema invece la chiave clustered.
• Dopo un grande delete una tantum che lascia pagine vuote sparse nell’indice.

Per tutto il resto — OLTP tipico Runehold-sized, storage SSD, RCSI attivo, covering index per le query calde — la frammentazione è una preoccupazione minore. Concentrati sui piani delle query e sulle statistiche, non sul maintenance plan.

Un job di manutenzione sensato

Sul server di produzione di Runehold, il job dell’Agent assomiglia grossomodo a questo:

• Quotidiano, 03:00: UPDATE STATISTICS sulle tabelle modificate da ieri (usando IndexOptimize di Ola).
• Settimanale, domenica 01:00: IndexOptimize di Ola su tutti i database utente con soglie sensate:
  • Indici < 1.000 pagine: salta.
  • Frammentati 5-30%: reorganize.
  • Frammentati 30%+: rebuild.
  • Tutto questo è il default di Ola, quindi è una sola riga.
• Mensile: DBCC CHECKDB completo su ogni database (controllo di integrità, non frammentazione).
• Mai: SHRINKDATABASE.

Noioso, efficace, gira per anni senza attenzione.

Esegui questo sulla tua macchina

USE Runehold;
GO

-- 1. Censisci i tuoi indici
SELECT
    OBJECT_NAME(ips.object_id)                     AS table_name,
    i.name                                          AS index_name,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent               AS frag_pct,
    ips.avg_page_space_used_in_percent             AS fill_pct,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
    ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 100
ORDER BY frag_pct DESC;

-- 2. Reorganize di un indice specifico (online, basso impatto)
ALTER INDEX IX_Customer_Email ON Sales.Customer REORGANIZE;

-- 3. Rebuild (Enterprise solo per farlo online)
ALTER INDEX IX_Customer_Email ON Sales.Customer
    REBUILD WITH (ONLINE = ON, FILLFACTOR = 90);

-- 4. Aggiorna le statistiche con full scan
UPDATE STATISTICS Sales.Customer WITH FULLSCAN;

-- 5. Fallo tutto in una volta per una tabella (Ola-lite)
ALTER INDEX ALL ON Sales.Orders REBUILD WITH (ONLINE = ON, FILLFACTOR = 90);
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

Esegui la query di censimento su un server in produzione da un po’. Nota quanti pochi dei tuoi indici hanno effettivamente frammentazione significativa. La maggior parte delle aziende che ho visitato ha forse cinque indici che contano e cinquanta che non contano.

Modulo 4 fatto. Indici, covering, filtered, columnstore, frammentazione — tutto coperto. Adesso hai opinioni su quando indicizzare e quando lasciare le cose in pace.

Prossimo: modulo 5, piani di esecuzione e performance. La lezione 25 (già scritta) è la lettura principale; le lezioni 26-28 entrano più in profondità su statistiche, parameter sniffing e Query Store.