Narcis Miclaus

7 min de citit

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

Indici acoperitori și coloane incluse

Taxa pentru key lookup, INCLUDE vs coloane cheie, când să faci un indice acoperitor și cum găsești candidați de indici din DMV-urile pentru missing indexes.

Lecția 21 a acoperit indicii clustered și non-clustered. Astăzi intrăm în cel mai impactant pattern de indexare din SQL Server: indicii acoperitori. Pattern-ul în care indicele tău non-clustered conține fiecare coloană de care are nevoie query-ul, astfel încât engine-ul nu se mai întoarce la indicele clustered pentru date suplimentare.

Când dashboard-ul de customer support al Runehold face „găsește toate comenzile pentru acest client, cu status și tracking”, acel query poate fi fie un singur seek curat, fie un seek plus zece mii de key lookups. Diferența e o singură clauză INCLUDE în definiția indicelui. Pe bune — o singură linie de SQL poate tăia runtime-ul query-ului de 50 de ori.

Cât costă un Key Lookup

Recap din lecția 21: un indice non-clustered conține coloanele cheie plus un pointer către indicele clustered. Când un query are nevoie de coloane care nu sunt în indicele non-clustered, engine-ul face un Key Lookup (sub un nume mai vechi, Bookmark Lookup) pentru fiecare rând — un drum dus-întors per rând până la indicele clustered, pentru a aduce coloanele lipsă.

Pentru un rând: ok. Pentru cinci: ok. Pentru 5.000: catastrofal. Fiecare lookup e I/O random, nu poate fi lotat eficient și costul scalează liniar.

Într-un plan de execuție:

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

Acel Key Lookup e taxa. De fiecare dată când îl vezi pe un query fierbinte, primul tău reflex ar trebui să fie: pot să fac indicele non-clustered să acopere acest query?

INCLUDE: cuvântul magic

-- Înainte: face seek pe Status, dar are nevoie de CustomerName și OrderTotal
-- care nu sunt în indice
CREATE NONCLUSTERED INDEX IX_Orders_Status
    ON Sales.Orders (Status);

-- După: fiecare coloană pe care o vrea dashboard-ul e acum în indice
CREATE NONCLUSTERED INDEX IX_Orders_Status
    ON Sales.Orders (Status)
    INCLUDE (OrderDate, Total, CountryCode, CustomerId);

Coloanele INCLUDE trăiesc doar în paginile leaf ale indicelui non-clustered. Nu sunt sortabile, nu sunt în nivelurile de sus ale arborelui B, nu schimbă comportamentul de seek al indicelui. Sunt acolo doar la „cărăușie”, asigurându-se că query-ul poate fi rezolvat în întregime din acest indice.

Forma planului de execuție după adăugarea INCLUDE:

Index Seek (NonClustered) → SELECT

Un singur operator. Săgeată subțire. Gata.

Coloane cheie vs coloane incluse

Regulă rapidă care acoperă 95% din cazuri:

  • Coloane cheie — lucruri pe care filtrezi, faci join sau sortezi.
  • Coloane INCLUDE — lucruri pe care doar le returnezi (le proiectezi).
-- Query:
SELECT OrderId, Total, OrderDate
FROM Sales.Orders
WHERE CustomerId = @cid AND Status = @status
ORDER BY OrderDate DESC;

-- Indice gândit pentru el:
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Status
    ON Sales.Orders (CustomerId, Status, OrderDate DESC)   -- chei: filtru + sort
    INCLUDE (Total);                                          -- include: doar returnat

CustomerId și Status sunt în cheie pentru că sunt filtrul. OrderDate DESC e în cheie din cauza sortării (ordinea de sortare potrivită înseamnă că engine-ul nu are nevoie de un operator separat de sort). Total e în include pentru că query-ul doar îl returnează.

Ordinea coloanelor cheie contează

Cheile sunt ordonate. Dacă indicele tău e (CustomerId, Status), e util pentru:

  • WHERE CustomerId = @c — seek pe prima coloană.
  • WHERE CustomerId = @c AND Status = @s — seek pe ambele.

Nu e util pentru:

  • WHERE Status = @s singur — nu poate face seek, trebuie să facă scan.

Regula: pune prima coloana de egalitate cu cea mai mare selectivitate. Sau: „egalitate, range, sort, include”.

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

-- Indicele potrivit:
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
    ON Sales.Orders (CustomerId, OrderDate DESC)
    INCLUDE (Status, Total);

CustomerId primul (egalitate). OrderDate DESC al doilea (range + sort, aceeași direcție ca a query-ului). Restul incluse.

Inversarea ordinii cheilor — (OrderDate, CustomerId) — strică asta. Un range scan pe OrderDate face CustomerId non-căutabil sub filtru. Am acoperit asta în lecția 21; merită repetat pentru că mușcă pe toată lumea o dată.

DMV-uri pentru missing indexes

SQL Server urmărește fiecare query care ar fi putut beneficia de un indice pe care nu îl avea. Acele sugestii „missing index” sunt disponibile prin DMV-uri:

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;

Arată indicii lipsă cu cel mai mare impact. Coloanele equality_columns, inequality_columns, included_columns sunt exact forma de care are nevoie un INDEX ... INCLUDE (...).

Avertisment important: recomandările pentru missing indexes ale SQL Server sunt sugestii, nu legi. Sunt generate de optimizator pe baza planurilor individuale de query. A crea orbește fiecare sugestie e cum ajungi cu 40 de indici pe o tabelă și un workload heavy-write care e în agonie.

Regulă: uită-te la primele 5-10 sugestii, evaluează fiecare și creează-i pe cei care se potrivesc cu query-urile tale fierbinți. Ignoră restul.

Câți indici sunt prea mulți?

Reguli care s-au ținut zece ani:

  • Tabele OLTP: 4-6 indici non-clustered sunt de obicei suficienți. Mai mult de 10 și performanța la scriere probabil suferă.
  • Tabele de raportare / DW: pot tolera mai mulți, pentru că scrierile sunt bulk-loaded și citirile domină.
  • Heap larg de SKU-uri unde toți caută după coloane diferite: ia în considerare columnstore (lecția următoare).

Fiecare indice non-clustered adaugă cost la fiecare INSERT, UPDATE care atinge coloane indexate și DELETE. Zece indici înseamnă cost de scriere de 11× față de tabela de bază.

Unealta pentru job: găsește indici care nu sunt folosiți:

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            -- sare peste tabelele de sistem
  AND i.type_desc = 'NONCLUSTERED'
ORDER BY reads, writes DESC;

Indicii de la top au zero sau aproape zero citiri, dar scrieri reale. Te costă și nu îți dau nimic înapoi. Aruncă-i.

Avertisment: dm_db_index_usage_stats se resetează la repornirea serverului. Rulează asta pe un server care a fost pornit de cel puțin o săptămână, ideal printr-un ciclu de business (raportare de sfârșit de lună, etc.) ca să vezi pattern-ul complet de utilizare.

Acoperire pentru JOIN-uri

Query-urile cu join beneficiază adesea de indici acoperitori pe ambele părți. Query-ul Runehold „comenzi cu numele clientului”:

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';

Pe partea Orders, un indice pe (OrderDate, CustomerId) cu Total inclus acoperă partea de orders. Join-ul către Customer folosește Customer.CustomerId (PK-ul, deja indexat). c.Name e returnat; trebuie acoperit și el. Dacă Customer.CustomerId e PK-ul clustered, Name e deja în leaf-ul indicelui clustered — fără non-clustered suplimentar. Plan curat: două seek-uri, fără lookups, gata.

Rulează asta pe mașina ta

USE Runehold;
GO

-- Creează o tabelă fierbinte realistă
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
);

-- Umple cu 500k rânduri
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. Fără indice: full scan
SELECT Total, OrderDate, Status
FROM dbo.HotOrders
WHERE CustomerId = 42;

-- 2. Adaugă un indice îngust: seek + key lookup
CREATE NONCLUSTERED INDEX IX_HotOrders_Customer
    ON dbo.HotOrders (CustomerId);

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

-- 3. Fă-l acoperitor: seek curat
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;

-- Verifică sugestiile
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;

-- Curățenie
DROP TABLE dbo.HotOrders;

Rulează fiecare SELECT cu Include Actual Execution Plan (Ctrl+M) activat. Privește planul evoluând de la scan → seek+lookup → seek. Privește citirile logice din STATISTICS IO scăzând de la mii la o mână.

Următoarea lecție: indici filtrați și columnstore. Cei doi indici de specialitate ciudată care se mulează pe forme specifice de probleme ca o mănușă.

Caută