Narcis Miclaus

6 min di lettura

SQL Server, dalle fondamenta Lezione 26 / 40

Statistiche e stima della cardinalità

Come l'ottimizzatore tira a indovinare, quando sbaglia, il cardinality estimator legacy contro quello moderno, e le DMV che ti dicono la verità sulle tue stats.

L’ottimizzatore di SQL Server è uno che tira a indovinare. Prima di eseguire la tua query si chiede: “Quante righe matcheranno questo filtro? Quante righe produrrà questa join?” Le sue risposte guidano ogni scelta del piano — se fare seek o scan, hash o merge o nested-loop, spillare in tempdb o tenere in memoria.

Le stime si basano sulle statistiche — istogrammi e campioni di densità memorizzati accanto a ogni indice e a volte su singole colonne. Quando le stats sono fresche e rappresentative, le stime dell’ottimizzatore sono accurate e il piano è buono. Quando le stats sono obsolete o sbilanciate, le stime sono sbagliate, e ti ritrovi con un piano che funzionava splendidamente su 1.000 righe a sbriciolarsi su 10 milioni.

Questa è la lezione 26. È quella che trasforma “perché la mia query è lenta?” in “ah, l’ottimizzatore aveva stimato 1 riga e ne ha trovate 2 milioni.”

Come sono fatte le statistiche

Esegui questo per vedere un istogramma:

DBCC SHOW_STATISTICS ('Sales.Orders', 'IX_Orders_CustomerId');

Tornano tre risultati:

  1. Header — quando le stats sono state aggiornate l’ultima volta, quante righe, quante campionate, quanti valori distinti.
  2. Vettore di densità — selettività media per i prefissi della chiave.
  3. Istogramma — fino a 200 bucket di intervalli, ognuno con conteggi di righe per un range “uguale” e un range “circostante”.

L’istogramma è la parte interessante. Per una colonna CustomerId, potrebbe dire: “Per i valori di CustomerId da 1 a 100 ci sono circa 50 righe ciascuno. Per 101-500, circa 10 righe ciascuno.” Quando esegui WHERE CustomerId = 42, l’ottimizzatore stima ~50 righe. Se hai 1.000 righe reali con quel CustomerId (perché 42 è il tuo cliente più grande), la stima è sbagliata di 20×, e il piano rifletterà quella scorrettezza.

Righe stimate vs effettive

Questo è lo strumento diagnostico #1 nella lettura dei piani (lezione 25):

  • Attiva “Include Actual Execution Plan” (Ctrl+M) in SSMS.
  • Esegui la query.
  • Passa il mouse su ogni operatore.
  • Confronta “Estimated Number of Rows” con “Actual Number of Rows.”

Sbagliato di 2×? Boh. Sbagliato di 10×? Sospetto. Sbagliato di 100×? Le stats sono quasi sicuramente la causa della tua query lenta.

Quando le stats diventano obsolete

SQL Server aggiorna le statistiche automaticamente quando:

  • 500 + 20% delle righe della tabella sono cambiate dall’ultimo aggiornamento (vecchia soglia).
  • SQL Server 2016+ con compatibility level 130+ usa una soglia dinamica che scatta più spesso su tabelle grandi.

Problema: su una tabella da un miliardo di righe con soglia del 20%, devono cambiare 200 milioni di righe prima che le stats si aggiornino. Tanto scostamento.

Esegui UPDATE STATISTICS ... WITH FULLSCAN con regolarità. Lo abbiamo accennato nella lezione 24. Per le tabelle calde, le stats con full-scan notturne valgono il costo della manutenzione.

-- Una tabella, full scan
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

-- Tutte le stats sulla tabella
UPDATE STATISTICS Sales.Orders;   -- campionamento di default

-- Tutte le tabelle nel database
EXEC sp_updatestats;

IndexOptimize di Ola ha default intelligenti che campionano la maggior parte delle tabelle e fanno full-scan su quelle critiche. Usalo.

Auto create e auto update

Due impostazioni del database controllano il comportamento automatico delle statistiche:

SELECT name,
       is_auto_create_stats_on,
       is_auto_update_stats_on,
       is_auto_update_stats_async_on
FROM sys.databases
WHERE name = 'Runehold';
  • AUTO_CREATE_STATISTICS — attivo di default; il motore crea statistiche su singola colonna al volo quando una query ne ha bisogno.
  • AUTO_UPDATE_STATISTICS — attivo di default; il motore aggiorna le stats quando viene superata la soglia.
  • AUTO_UPDATE_STATISTICS_ASYNC — disattivo di default; quando attivo, le query non aspettano l’aggiornamento delle stats, girano con le vecchie stats mentre l’aggiornamento avviene in background.

Raccomandazione per la maggior parte dei workload OLTP: auto-update on, auto-update-async on. Le query non si bloccano in attesa del motore delle stats.

Statistiche multi-colonna

Le stats su singola colonna vanno bene per i predicati su singola colonna. Per i predicati multi-colonna, SQL Server può creare stats multi-colonna automaticamente (sulle colonne di testa di un indice) oppure le puoi creare manualmente:

CREATE STATISTICS STAT_Orders_CountryStatus
    ON Sales.Orders (CountryCode, Status);

Utile quando una combinazione di due colonne è sbilanciata — la maggior parte degli ordini con CountryCode = 'DE' ha Status = 1, ma la maggior parte degli ordini con CountryCode = 'IT' ha Status = 3. Un oggetto stats che cattura entrambe le colonne aiuta l’ottimizzatore a stimare la selettività combinata.

Di solito è una necessità di nicchia. Non crearle di default; aggiungile se la stima della cardinalità di una query specifica è sbagliata.

Il Cardinality Estimator legacy contro quello moderno

SQL Server 2014 ha riscritto il Cardinality Estimator (CE). Il comportamento è cambiato in molti modi sottili. Alcune query sono diventate più veloci, altre più lente. Il compatibility level del database controlla quale CE viene usato:

  • Compatibility level 110 (SQL 2012) e inferiori: CE legacy.
  • Compatibility level 120 (SQL 2014) e superiori: CE moderno.

Verifica e imposta:

SELECT name, compatibility_level FROM sys.databases WHERE name = 'Runehold';

-- Cambia (con cautela, testa prima)
ALTER DATABASE Runehold SET COMPATIBILITY_LEVEL = 160;

Aggiornare il compatibility level — spesso fatto dopo aver migrato a una versione più recente di SQL Server — può causare regressioni di piano. Usa Query Store (lezione 28) per rilevarle e fissare i piani buoni.

Per forzare il CE legacy per una query specifica, aggiungi un hint:

SELECT ... OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Oppure a livello di database:

ALTER DATABASE SCOPED CONFIGURATION
    SET LEGACY_CARDINALITY_ESTIMATION = ON;

Usalo come fix chirurgico per query con regressioni note, non come default generale.

Quando l’ottimizzatore davvero non riesce a stimare

Alcuni casi fanno tirare a indovinare l’ottimizzatore alla cieca:

  • Table-valued parameter — trattati come stime da 1 riga a meno che tu non usi un recompile hint.
  • OPTION (RECOMPILE) — piano fresco ogni volta ma compilazione costosa.
  • OPTIMIZE FOR UNKNOWN — usa la densità media invece dei valori specifici.
  • WHERE molto complesse — l’ottimizzatore si arrende e usa una stima di default del 10%.

Quando sai che un valore specifico produrrà conteggi di righe selvaggiamente diversi, considera:

  • Parametrizzare diversamente (proc separate per range diversi).
  • Usare OPTION (OPTIMIZE FOR (@p = 'DE')) per suggerire un valore rappresentativo.
  • Forzare un piano specifico via Query Store.

Copriremo questi pattern a fondo nella prossima lezione sul parameter sniffing.

Come diagnosticare “la stima è sbagliata”

Workflow standard:

  1. Esegui la query con Actual Execution Plan attivo.
  2. Trova l’operatore dove stima ed effettivo divergono di più.
  3. Guarda quali stats supportano quell’operatore (l’indice o la colonna su cui sta facendo seek/scan).
  4. DBCC SHOW_STATISTICS su quell’indice.
  5. Controlla quando le stats sono state aggiornate l’ultima volta.
  6. UPDATE STATISTICS ... WITH FULLSCAN su quella tabella.
  7. Riesegui. La stima è migliorata? Se sì, hai trovato il problema.

Se le stats sono fresche e accurate ma la stima è ancora sbagliata, potresti avere:

  • Una distribuzione sbilanciata che l’istogramma non riesce a rappresentare (200 bucket potrebbero non bastare per una colonna con 100 milioni di valori).
  • Una correlazione tra colonne che le stats su singola colonna non vedono.
  • Una dipendenza funzionale di cui l’ottimizzatore non sa nulla.

Per quei casi: stats multi-colonna, filtered stats (stats con una clausola WHERE, come gli indici filtrati), o riscrivere la query.

Esegui questo sulla tua macchina

USE Runehold;
GO

-- 1. Guarda le stats su un indice
DBCC SHOW_STATISTICS ('Sales.Orders', 'pk_Orders');

-- 2. Trova le tabelle con stats antiche
SELECT
    OBJECT_NAME(s.object_id) AS table_name,
    s.name                   AS stat_name,
    STATS_DATE(s.object_id, s.stats_id) AS last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter  AS rows_changed_since
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id > 100
ORDER BY STATS_DATE(s.object_id, s.stats_id);

-- 3. Forza un refresh completo su una tabella specifica
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

-- 4. Attiva auto-update-stats-async (di solito raccomandato)
ALTER DATABASE Runehold SET AUTO_UPDATE_STATISTICS_ASYNC ON;

-- 5. Vedi la stima della cardinalità in azione
-- Prima: tabella piccola, stima approssimativa
SELECT Name FROM Sales.Customer WHERE Country = 'IT';

-- Controlla il piano, passa il mouse su Index Scan / Seek, guarda "Estimated Number of Rows"
-- vs "Actual Number of Rows" nel piano effettivo.

Quando inizi a prestare attenzione a stime-vs-effettivi su ogni query lenta, troverai più bug di quanti ti aspettassi, più velocemente di qualsiasi altra diagnostica.

Prossima lezione: parameter sniffing — il problema “veloce in SSMS, lento nell’app”, perché succede, e i fix moderni.

Cerca