Ottimizzazione in SQL Server: Tecniche Avanzate di Indexing

Introduzione

L’indicizzazione è uno degli strumenti più potenti a disposizione di chi lavora con SQL Server per migliorare le prestazioni delle query. Tuttavia, un uso scorretto o superficiale può portare a risultati controproducenti: query più lente, maggiore uso di risorse, frammentazione dei dati e aumento dello spazio su disco. In questo articolo esploreremo tecniche avanzate di indexing, con esempi pratici, richiami alla magia e suggerimenti per ottenere il massimo da ogni SELECT.

Clustered vs Non-Clustered Index

In SQL Server ogni tabella può avere un solo indice clustered, che determina l’ordine fisico dei dati su disco. Pensalo come l’indice principale del libro degli incantesimi: tutti i dati sono ordinati secondo quella logica.

CREATE TABLE Spells (
    ID_Spell INT PRIMARY KEY CLUSTERED,
    Name NVARCHAR(100),
    Category NVARCHAR(50),
    Level INT
);

Tutti gli altri indici saranno non-clustered, ovvero strutture separate che puntano ai dati reali.

CREATE NONCLUSTERED INDEX IX_Spells_Category
ON Spells (Category);

Quando cerchi tutti gli incantesimi della categoria “Defense”, l’indice non-clustered ti aiuta a trovarli senza dover leggere l’intera tabella.

Index Covering e INCLUDE

Un indice può diventare ancora più potente se copre tutte le colonne di una query. Ad esempio, considera questa query:

SELECT Name, Level
FROM Spells
WHERE Category = 'Defense';

Un indice classico su Categoria ti aiuta a filtrare, ma poi SQL Server dovrà fare un lookup per recuperare Nome e Livello.

Per evitarlo, puoi usare INCLUDE:

CREATE NONCLUSTERED INDEX IX_Spells_Category_Include
ON Spells (Category)
INCLUDE (Name, Level);

Così SQL Server ha tutto ciò che gli serve direttamente nell’indice.

Indici Filtrati

Gli indici filtrati sono ideali quando interroghi solo una porzione dei dati.

CREATE NONCLUSTERED INDEX IX_Spells_HighLevel
ON Spells (Level)
WHERE Level >= 5;

Questo tipo di indice è utile quando lavori spesso con incantesimi avanzati come Expecto Patronum o Sectumsempra, che appaiono solo in un sottoinsieme del tuo grimorio.

Columnstore Index

I columnstore index sono pensati per analisi su grandi volumi di dati, come nel caso di report complessi o statistiche sul comportamento magico.

CREATE CLUSTERED COLUMNSTORE INDEX CCI_Spells
ON SpellsLarge;

Perfetti per aggregazioni veloci su tabelle molto ampie come UsedSpells con milioni di righe. Immagina di voler sapere quanti incantesimi di tipo “Light” sono stati lanciati da ogni mago nel 2023:

SELECT ID_Wizard , COUNT(*)
FROM UsedSpells
WHERE Type = 'Light'
AND Year = 2023
GROUP BY ID_Wizard;

Un columnstore index può accelerare enormemente queste query.

Gestione della Frammentazione

Le operazioni di INSERT, UPDATE, DELETE frammentano gli indici nel tempo. Come un armadio pieno di bacchette disordinate, serve fare pulizia ogni tanto.

Verifica della frammentazione

SELECT
    OBJECT_NAME(i.object_id) AS Table,
    i.name AS Index,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (
    DB_ID('HogwartsDB'),
    OBJECT_ID('Spells'),
    NULL, NULL, 'LIMITED'
) AS stats
JOIN sys.indexes AS i
    ON stats.object_id = i.object_id AND stats.index_id = i.index_id
WHERE avg_fragmentation_in_percent > 10;

Reorganize vs Rebuild

• REORGANIZE (frammentazione tra 10% e 30%)
• REBUILD (frammentazione > 30%)

ALTER INDEX IX_Spells_Category ON Spells REBUILD;

Come il professor Vitious che rimette ordine nella classe dopo una lezione su Expelliarmus, anche tu devi pianificare questa manutenzione.

DMV per Analisi degli Indici

Per sapere se un indice è effettivamente usato, usa questa query:

SELECT
    OBJECT_NAME(i.object_id) AS Table,
    i.name AS Index,
    user_seeks, user_scans, user_lookups, user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1;

Se un indice ha user_updates ma zero user_seeks e user_scans, è come mantenere attivo un incantesimo non utilizzato: c’è, consuma risorse, ma non serve davvero.

Esempio Completo: Ottimizzare una Query Complessa

Query iniziale:

SELECT
    W.Name,
    COUNT(S.ID_Spell) AS SpellsCount
FROM Wizards W
JOIN Spells S ON W.ID_Wizard = S.ID_Wizard
WHERE S.Category = 'Alhomora'
AND S.CastedDate >= '2024-01-01'
GROUP BY W.Name;

Indici proposti:

CREATE NONCLUSTERED INDEX IX_Spells_Category_UsageDate
ON Spells (Category, CastedDate)
INCLUDE (ID_Wizard);

CREATE NONCLUSTERED INDEX IX_Wizards_Name
ON Wizards (ID_Wizard)
INCLUDE (Name);

Il primo indice aiuta il filtro e la JOIN, il secondo evita lookup nella tabella Maghi. Dopo l’aggiunta degli indici, i tempi si riducono drasticamente e SQL Server può usare index seek e operazioni hash o merge join più efficienti.

Considerazioni Finali

• Non esagerare: troppi indici rallentano le scritture.
• Misura sempre: ogni indice deve avere uno scopo concreto.
• Usa INCLUDE e indici filtrati per casi d’uso specifici.
• Mantieni ordinati gli indici con REBUILD e REORGANIZE.
• Sfrutta DMV e Query Store per prendere decisioni basate sui dati.

Gli indici, come gli incantesimi, sono potenti e devono essere usati con saggezza. Se ben progettati, possono trasformare un database lento in un ambiente reattivo e scalabile, capace di rispondere come Hermione a una domanda del professor Lupin.