Narcis Miclaus

6 min de citit

SQL Server, de la zero Lecția 26 / 40

Statistici și estimare de cardinalitate

Cum ghicește optimizatorul, când ghicește greșit, cardinality estimator-ul legacy vs cel modern și DMV-urile care îți spun adevărul despre stats-urile tale.

Optimizatorul SQL Server e un ghicitor. Înainte să-ți ruleze query-ul, întreabă: „Câte rânduri va potrivi filtrul ăsta? Câte rânduri va produce join-ul ăsta?” Răspunsurile lui dictează fiecare alegere de plan — seek sau scan, hash sau merge sau nested-loop, spill în tempdb sau ținut în memorie.

Ghicirile se bazează pe statistici — histograme și sample-uri de densitate stocate alături de fiecare index și uneori pe coloane individuale. Când stats-urile sunt proaspete și reprezentative, ghicirile optimizatorului sunt precise și planul e bun. Când stats-urile sunt învechite sau dezechilibrate, ghicirile sunt greșite, iar tu ajungi cu un plan care mergea splendid pe 1.000 de rânduri să se prăbușească pe 10 milioane.

Asta e lecția 26. E cea care transformă „de ce e lent query-ul ăsta?” în „aha, optimizatorul a ghicit 1 rând și a primit 2 milioane.”

Cum arată statisticile

Rulează asta ca să vezi o histogramă:

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

Se întorc trei rezultate:

  1. Header — când au fost actualizate ultima oară stats-urile, câte rânduri, câte eșantionate, câte valori distincte.
  2. Density vector — selectivitatea medie pentru prefixe ale cheii.
  3. Histogram — până la 200 de bucket-uri, fiecare cu numere de rânduri pentru un range „equal” și unul „înconjurător”.

Histograma e partea interesantă. Pentru o coloană CustomerId, ar putea spune: „Pentru valorile CustomerId de la 1 la 100, sunt cam 50 de rânduri fiecare. Pentru 101 până la 500, cam 10 rânduri fiecare.” Când rulezi WHERE CustomerId = 42, optimizatorul estimează ~50 de rânduri. Dacă ai 1.000 de rânduri reale cu acel CustomerId (pentru că 42 e cel mai mare client al tău), estimarea e de 20× greșită, iar planul va reflecta acea greșeală.

Estimated vs actual rows

Asta e instrumentul de diagnostic numărul 1 în citirea planurilor (lecția 25):

  • Activează „Include Actual Execution Plan” (Ctrl+M) în SSMS.
  • Rulează query-ul.
  • Treci cu mouse-ul peste fiecare operator.
  • Compară „Estimated Number of Rows” cu „Actual Number of Rows.”

Diferență de 2×? Bă. De 10×? Suspect. De 100×? Aproape sigur stats-urile sunt cauza query-ului tău lent.

Când stats-urile devin învechite

SQL Server actualizează statisticile automat când:

  • 500 + 20% din rândurile tabelului s-au schimbat de la ultima actualizare (pragul vechi).
  • SQL Server 2016+ cu compatibility level 130+ folosește un prag dinamic care se declanșează mai des pe tabele mari.

Problema: pe un tabel de un miliard de rânduri cu pragul de 20%, trebuie să se schimbe 200 de milioane de rânduri înainte să se actualizeze stats-urile. E mult drift.

Rulează UPDATE STATISTICS ... WITH FULLSCAN pe un orar. Am menționat asta în lecția 24. Pentru tabele hot, stats full-scan în fiecare noapte merită costul de mentenanță.

-- Un tabel, full scan
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

-- Toate stats-urile pe tabel
UPDATE STATISTICS Sales.Orders;   -- sample default

-- Toate tabelele din baza de date
EXEC sp_updatestats;

IndexOptimize de la Ola are default-uri inteligente care eșantionează majoritatea tabelelor și fac full-scan pe cele critice. Folosește-l.

Auto create și auto update

Două setări de bază de date controlează comportamentul automat al statisticilor:

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 — on by default; engine-ul creează statistici single-column on the fly când un query are nevoie.
  • AUTO_UPDATE_STATISTICS — on by default; engine-ul reîmprospătează stats-urile când se trece pragul.
  • AUTO_UPDATE_STATISTICS_ASYNC — off by default; când e on, query-urile nu așteaptă update-ul de stats, rulează cu cele vechi în timp ce update-ul se întâmplă în fundal.

Recomandare pentru majoritatea workload-urilor OLTP: auto-update on, auto-update-async on. Query-urile nu se blochează așteptând engine-ul de statistici.

Statistici multi-coloană

Stats single-column sunt OK pentru predicate single-column. Pentru predicate multi-coloană, SQL Server poate crea stats multi-coloană automat (pe coloanele leading ale unui index) sau le poți crea manual:

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

Utile când o combinație de două coloane e dezechilibrată — majoritatea comenzilor cu CountryCode = 'DE' au Status = 1, dar majoritatea celor cu CountryCode = 'IT' au Status = 3. Un obiect de stats care captează ambele coloane ajută optimizatorul să estimeze selectivitatea combinată.

De obicei o nevoie de nișă. Nu le crea by default; adaugă-le dacă estimarea de cardinalitate a unui query specific e greșită.

Cardinality Estimator legacy vs modern

SQL Server 2014 a rescris Cardinality Estimator-ul (CE). Comportamentul s-a schimbat în multe feluri subtile. Unele query-uri au devenit mai rapide, altele mai lente. Compatibility level-ul bazei de date controlează ce CE e folosit:

  • Compatibility level 110 (SQL 2012) și mai jos: Legacy CE.
  • Compatibility level 120 (SQL 2014) și mai sus: Modern CE.

Verifică și setează:

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

-- Schimbă (cu grijă, testează întâi)
ALTER DATABASE Runehold SET COMPATIBILITY_LEVEL = 160;

Upgrade-ul compatibility level-ului — adesea făcut după migrarea pe o versiune SQL Server mai nouă — poate cauza regresii de plan. Folosește Query Store (lecția 28) ca să le detectezi și să fixezi planurile bune.

Ca să forțezi CE-ul legacy pentru un query specific, adaugă un hint:

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

Sau la nivel de bază de date:

ALTER DATABASE SCOPED CONFIGURATION
    SET LEGACY_CARDINALITY_ESTIMATION = ON;

Folosește-l ca fix chirurgical pentru query-uri cunoscute ca regresate, nu ca default general.

Când optimizatorul chiar nu poate estima

Unele cazuri îl fac pe optimizator să ghicească orbește:

  • Table-valued parameters — tratate ca estimări de 1 rând dacă nu folosești un hint de recompile.
  • OPTION (RECOMPILE) — plan proaspăt de fiecare dată, dar compile costisitor.
  • OPTIMIZE FOR UNKNOWN — folosește densitatea medie în loc de valori specifice.
  • WHERE-uri foarte complexe — optimizatorul renunță la o estimare default de 10%.

Când știi că o valoare specifică va produce numere de rânduri sălbatic diferite, ia în considerare:

  • Parametrizare diferită (proceduri separate pentru range-uri diferite).
  • Folosirea OPTION (OPTIMIZE FOR (@p = 'DE')) pentru a sugera o valoare reprezentativă.
  • Forțarea unui plan specific prin Query Store.

Vom acoperi aceste pattern-uri temeinic în lecția următoare despre parameter sniffing.

Cum diagnostichezi „estimarea e greșită”

Workflow standard:

  1. Rulează query-ul cu Actual Execution Plan on.
  2. Găsește operatorul unde estimated vs actual divergează cel mai mult.
  3. Uită-te la ce stats stau în spatele acelui operator (indexul sau coloana pe care face seek/scan).
  4. DBCC SHOW_STATISTICS pe acel index.
  5. Verifică când au fost actualizate ultima oară stats-urile.
  6. UPDATE STATISTICS ... WITH FULLSCAN pe acel tabel.
  7. Rulează din nou. S-a îmbunătățit estimarea? Dacă da, ai găsit problema.

Dacă stats-urile sunt proaspete și precise dar estimarea e încă greșită, s-ar putea să ai:

  • O distribuție dezechilibrată pe care histograma nu o poate reprezenta (200 de bucket-uri ar putea să nu fie suficiente pentru o coloană cu 100 de milioane de valori).
  • O corelație între coloane pe care stats-urile single-column nu o văd.
  • O dependență funcțională despre care optimizatorul nu știe.

Pentru aceste cazuri: stats multi-coloană, filtered stats (stats cu o clauză WHERE, ca filtered indexes) sau rescrierea query-ului.

Rulează asta pe propria mașină

USE Runehold;
GO

-- 1. Uită-te la stats-urile pe un index
DBCC SHOW_STATISTICS ('Sales.Orders', 'pk_Orders');

-- 2. Găsește tabele cu stats antice
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. Forțează un refresh complet pe un tabel specific
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

-- 4. Activează auto-update-stats-async (de obicei recomandat)
ALTER DATABASE Runehold SET AUTO_UPDATE_STATISTICS_ASYNC ON;

-- 5. Vezi estimarea de cardinalitate în acțiune
-- Înainte: tabel mic, estimare grosieră
SELECT Name FROM Sales.Customer WHERE Country = 'IT';

-- Verifică planul, treci peste Index Scan / Seek, uită-te la „Estimated Number of Rows”
-- vs „Actual Number of Rows” în planul efectiv.

Când începi să fii atent la estimated-vs-actuals pe fiecare query lent, vei găsi mai multe bug-uri decât te așteptai, mai repede decât cu orice alt diagnostic.

Lecția următoare: parameter sniffing — problema „rulează rapid în SSMS, lent în aplicație”, de ce se întâmplă și fix-urile moderne.

Caută