Narcis Miclaus

7 min di lettura

SQL Server, dalle fondamenta Lezione 22 / 40

Indici di copertura e colonne incluse

La tassa del key lookup, INCLUDE vs colonne chiave, quando rendere un indice di copertura, e come trovare indici candidati dalle DMV degli indici mancanti.

La lezione 21 ha coperto gli indici clustered e non clustered. Oggi scaviamo nel singolo pattern di indicizzazione a più alto impatto in SQL Server: gli indici di copertura. Il pattern in cui il tuo indice non clustered contiene ogni colonna di cui la query ha bisogno, in modo che il motore non debba mai tornare all’indice clustered per altri dati.

Quando la dashboard del customer support di Runehold fa “trova tutti gli ordini per questo cliente, con stato e tracking”, quella query può essere o un singolo seek pulito o un seek più diecimila key lookup. La differenza è una singola clausola INCLUDE nella definizione dell’indice. Non sto scherzando — una riga di SQL può tagliare il runtime della query di 50×.

Quanto costa un Key Lookup

Ricapitolando dalla lezione 21: un indice non clustered contiene le colonne chiave più un puntatore all’indice clustered. Quando una query ha bisogno di colonne non presenti nell’indice non clustered, il motore fa un Key Lookup (in un nome più vecchio, Bookmark Lookup) per ogni riga — un round trip per riga di ritorno all’indice clustered per recuperare le colonne mancanti.

Per una riga: ok. Per cinque: ok. Per 5.000: catastrofico. Ogni lookup è I/O random, non può essere batchato in modo efficiente, e il costo scala linearmente.

In un piano di esecuzione:

Index Seek (NonClustered) → Nested Loops → Key Lookup (Clustered) → SELECT

Quel Key Lookup è la tassa. Ogni volta che lo vedi su una query calda, il tuo primo istinto dovrebbe essere: posso rendere l’indice non clustered di copertura per questa query?

INCLUDE: la parola magica

-- Prima: fa seek per Status, ma serve CustomerName e OrderTotal
-- che non sono nell'indice
CREATE NONCLUSTERED INDEX IX_Orders_Status
    ON Sales.Orders (Status);

-- Dopo: ogni colonna che la dashboard vuole è ora nell'indice
CREATE NONCLUSTERED INDEX IX_Orders_Status
    ON Sales.Orders (Status)
    INCLUDE (OrderDate, Total, CountryCode, CustomerId);

Le colonne in INCLUDE vivono solo nelle pagine foglia dell’indice non clustered. Non sono ordinabili, non sono nei livelli alti del B-tree, non cambiano il comportamento di seek dell’indice. Sono solo lì per il viaggio, assicurando che la query possa essere risolta interamente da questo indice.

La forma del piano di esecuzione dopo aver aggiunto INCLUDE:

Index Seek (NonClustered) → SELECT

Un operatore. Freccia sottile. Fatto.

Colonne chiave vs colonne incluse

Regola veloce che copre il 95% dei casi:

  • Colonne chiave — cose su cui filtri, su cui fai join, o su cui ordini.
  • Colonne INCLUDE — cose che restituisci soltanto (proietti).
-- Query:
SELECT OrderId, Total, OrderDate
FROM Sales.Orders
WHERE CustomerId = @cid AND Status = @status
ORDER BY OrderDate DESC;

-- Indice progettato per essa:
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Status
    ON Sales.Orders (CustomerId, Status, OrderDate DESC)   -- chiavi: filtro + sort
    INCLUDE (Total);                                          -- include: solo restituita

CustomerId e Status sono nella chiave perché sono il filtro. OrderDate DESC è nella chiave a causa del sort (l’ordine di sort coincidente significa che il motore non ha bisogno di un operatore di sort separato). Total è nell’include perché la query la restituisce e basta.

L’ordine delle colonne chiave conta

Le chiavi sono ordinate. Se il tuo indice è (CustomerId, Status), è utile per:

  • WHERE CustomerId = @c — seek sulla prima colonna.
  • WHERE CustomerId = @c AND Status = @s — seek su entrambe.

Non è utile per:

  • WHERE Status = @s da solo — non può fare seek, deve scansionare.

La regola: metti per prima la colonna di equality più selettiva. Oppure: “equality, range, sort, include.”

-- Per:
SELECT ...
FROM Sales.Orders
WHERE CustomerId = @c AND OrderDate >= @start AND OrderDate < @end
ORDER BY OrderDate DESC;

-- L'indice giusto:
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
    ON Sales.Orders (CustomerId, OrderDate DESC)
    INCLUDE (Status, Total);

CustomerId per primo (equality). OrderDate DESC per secondo (range + sort, stessa direzione della query). Tutto il resto incluso.

Invertire l’ordine delle chiavi — (OrderDate, CustomerId) — rompe questo. Una range scan su OrderDate rende CustomerId non seekable sotto il filtro. L’abbiamo coperto nella lezione 21; mette conto ripeterlo perché morde tutti almeno una volta.

DMV degli indici mancanti

SQL Server traccia ogni query che avrebbe potuto beneficiare di un indice che non aveva. Quei suggerimenti di “missing index” sono disponibili tramite DMV:

SELECT TOP (20)
    [Impact] = mig.avg_total_user_cost * (mig.avg_user_impact / 100.0) * (mid.user_seeks + mid.user_scans),
    mig.group_handle,
    mid.statement                AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    mid.user_seeks + mid.user_scans AS [seeks+scans]
FROM sys.dm_db_missing_index_groups             AS mig
JOIN sys.dm_db_missing_index_group_stats         AS mig_stats ON mig_stats.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details             AS mid       ON mid.index_handle    = mig.index_handle
ORDER BY [Impact] DESC;

Mostra gli indici mancanti a maggior impatto. Le colonne equality_columns, inequality_columns, included_columns sono esattamente la forma che serve a un INDEX ... INCLUDE (...).

Importante distinguo: le raccomandazioni di missing index di SQL Server sono suggerimenti, non leggi. Sono generate dall’optimizer in base ai piani delle singole query. Creare ciecamente ogni suggerimento è il modo per ritrovarsi con 40 indici su una sola tabella e un workload write-heavy in agonia.

Regola: guarda i primi 5-10 suggerimenti, valutane ognuno, e crea quelli che corrispondono alle tue query calde. Ignora il resto.

Quanti indici sono troppi?

Regole pratiche che reggono da dieci anni:

  • Tabelle OLTP: 4-6 indici non clustered di solito bastano. Più di 10 e le tue performance in scrittura probabilmente stanno soffrendo.
  • Tabelle reporting / DW: ne tollerano di più, perché le scritture sono caricate in bulk e le letture dominano.
  • Heap larghi di SKU che tutti cercano per colonne diverse: considera il columnstore (lezione successiva).

Ogni indice non clustered aggiunge costo a ogni INSERT, UPDATE che tocca colonne indicizzate, e DELETE. Dieci indici significano 11× il costo di scrittura della tabella base.

Strumento per il lavoro: trovare gli indici che non vengono usati:

SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name                   AS index_name,
    i.type_desc,
    ius.user_seeks + ius.user_scans + ius.user_lookups AS reads,
    ius.user_updates                                      AS writes
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ius
    ON ius.object_id = i.object_id
   AND ius.index_id   = i.index_id
   AND ius.database_id = DB_ID()
WHERE i.object_id > 100            -- salta le tabelle di sistema
  AND i.type_desc = 'NONCLUSTERED'
ORDER BY reads, writes DESC;

Gli indici in cima hanno zero o quasi-zero letture ma scritture vere. Ti stanno costando e non ti danno nulla in cambio. Droppali.

Distinguo: dm_db_index_usage_stats si resetta al riavvio del server. Esegui questo su un server che è stato attivo almeno una settimana, idealmente attraverso un ciclo di business (reportistica di fine mese, ecc.) in modo da vedere il pattern d’uso completo.

Coprire i JOIN

Le query con join spesso beneficiano di indici di copertura su entrambi i lati. La query “ordini con nome cliente” di Runehold:

SELECT o.OrderId, o.Total, c.Name
FROM Sales.Orders   AS o
JOIN Sales.Customer AS c ON c.CustomerId = o.CustomerId
WHERE o.OrderDate >= '2026-04-01';

Sul lato Orders, un indice su (OrderDate, CustomerId) con Total incluso copre la parte ordini. Il join a Customer usa Customer.CustomerId (la PK, già indicizzata). c.Name viene restituito; ci serve coperto anche quello. Se Customer.CustomerId è la PK clustered, Name è già nella foglia dell’indice clustered — nessun non clustered extra necessario. Piano pulito: due seek, nessun lookup, fatto.

Esegui questo sulla tua macchina

USE Runehold;
GO

-- Crea una tabella calda realistica
IF OBJECT_ID('dbo.HotOrders', 'U') IS NOT NULL DROP TABLE dbo.HotOrders;

CREATE TABLE dbo.HotOrders (
    OrderId     BIGINT IDENTITY(1,1) PRIMARY KEY,
    CustomerId  INT     NOT NULL,
    Status      TINYINT NOT NULL,
    Total       DECIMAL(19,4) NOT NULL,
    CountryCode CHAR(2) NOT NULL,
    OrderDate   DATETIME2(0) NOT NULL
);

-- Riempila con 500k righe
INSERT INTO dbo.HotOrders (CustomerId, Status, Total, CountryCode, OrderDate)
SELECT TOP (500000)
    ABS(CHECKSUM(NEWID())) % 1000,
    ABS(CHECKSUM(NEWID())) % 5,
    ABS(CHECKSUM(NEWID())) % 1000 / 10.0,
    CHAR(65 + ABS(CHECKSUM(NEWID())) % 5) + CHAR(65 + ABS(CHECKSUM(NEWID())) % 5),
    DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE())
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

SET STATISTICS IO, TIME ON;

-- 1. Nessun indice: scan completa
SELECT Total, OrderDate, Status
FROM dbo.HotOrders
WHERE CustomerId = 42;

-- 2. Aggiungi un indice stretto: seek + key lookup
CREATE NONCLUSTERED INDEX IX_HotOrders_Customer
    ON dbo.HotOrders (CustomerId);

SELECT Total, OrderDate, Status
FROM dbo.HotOrders
WHERE CustomerId = 42;

-- 3. Rendilo di copertura: seek pulito
DROP INDEX IX_HotOrders_Customer ON dbo.HotOrders;
CREATE NONCLUSTERED INDEX IX_HotOrders_Customer_Cover
    ON dbo.HotOrders (CustomerId)
    INCLUDE (Total, OrderDate, Status);

SELECT Total, OrderDate, Status
FROM dbo.HotOrders
WHERE CustomerId = 42;

SET STATISTICS IO, TIME OFF;

-- Controlla i suggerimenti
SELECT TOP (5) mid.statement, mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_details AS mid
ORDER BY mid.index_handle DESC;

-- Pulizia
DROP TABLE dbo.HotOrders;

Esegui ogni SELECT con Include Actual Execution Plan (Ctrl+M) abilitato. Guarda il piano andare da scan → seek+lookup → seek. Guarda le letture logiche di STATISTICS IO scendere da migliaia a una manciata.

Prossima lezione: indici filtrati e columnstore. I due indici di nicchia che calzano a pennello su forme specifiche di problema.

Cerca