Index tuning: clustered, nonclustered en included columns

-- indexen · Ronald de Groot

De juiste index maakt het verschil tussen milliseconden en minuten. Maar "gooi er een index tegenaan" is geen strategie: elke index kost schrijfperformance, opslag en onderhoud. In dit artikel de afwegingen die ik in de praktijk maak.

De clustered index: kies hem bewust

De clustered index is de tabel: de bladpagina's bevatten alle data, fysiek gesorteerd op de clustering key. Je hebt er maar één, dus kies bewust. Een goede clustering key is:

• Smal — de key komt terug in élke nonclustered index van de tabel.
• Uniek — anders plakt SQL Server er zelf een 4-byte uniquifier aan.
• Oplopend — nieuwe rijen komen achteraan, geen page splits midden in de tabel.
• Statisch — een wijziging van de clustering key verplaatst de hele rij én raakt alle nonclustered indexen.

Een INT IDENTITY of oplopende BIGINT voldoet meestal prima. Een random UNIQUEIDENTIFIER (via NEWID()) is de klassieke valkuil: page splits en fragmentatie gegarandeerd.

Nonclustered: kolomvolgorde is alles

Een nonclustered index op (A, B) is gesorteerd op A, en pas bínnen gelijke A-waarden op B. Denk aan een telefoonboek: gesorteerd op achternaam, dan voornaam. Zoeken op voornaam alleen? Dan moet je het hele boek door.

CREATE INDEX IX_Orders_Customer_Date
ON dbo.Orders (CustomerID, OrderDate);

-- Index Seek: leidende kolom in het predicaat
WHERE CustomerID = 42
WHERE CustomerID = 42 AND OrderDate >= '2026-01-01'

-- Geen seek mogelijk: leidende kolom ontbreekt
WHERE OrderDate >= '2026-01-01'

Vuistregels voor de volgorde van de key-kolommen:

• Kolommen waarop je met = filtert eerst, daarna de range-kolommen (>, <, BETWEEN).
• Bij meerdere equality-kolommen: de meest selectieve combinatie voorop is mooi, maar herbruikbaarheid voor andere queries weegt vaak zwaarder.
• Een index op (A, B) maakt een aparte index op (A) overbodig.

INCLUDE: de covering index

Kolommen in de INCLUDE-lijst staan alleen op de bladpagina's van de index — ze sturen de sortering niet, maar voorkomen wél de Key Lookup terug naar de tabel. Zie je in een executieplan een Key Lookup met veel rijen, dan is dit bijna altijd de fix:

-- Query:
SELECT OrderDate, TotalAmount
FROM   dbo.Orders
WHERE  CustomerID = 42;

-- Covering index: seek op CustomerID, geen lookup meer nodig
CREATE INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID)
INCLUDE (OrderDate, TotalAmount);

Waarom niet gewoon alles in de key? Omdat key-kolommen op elk niveau van de index-boom staan en de 900-byte/32-kolommen keylimiet meetellen. INCLUDE-kolommen staan alleen in het blad: goedkoper. Maar wees zuinig — een index die "voor de zekerheid" tien kolommen include't, is een halve kopie van de tabel die bij elke update mee moet.

Opruimen: welke indexen doen niets?

Indexen die nooit gelezen maar wel onderhouden worden, kosten alleen maar. Zo vind je ze:

SELECT o.name AS tabel, i.name AS [index],
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM   sys.dm_db_index_usage_stats s
JOIN   sys.indexes i  ON i.object_id = s.object_id AND i.index_id = s.index_id
JOIN   sys.objects o  ON o.object_id = s.object_id
WHERE  s.database_id = DB_ID()
  AND  o.is_ms_shipped = 0
  AND  s.user_seeks + s.user_scans + s.user_lookups = 0
ORDER BY s.user_updates DESC;

Let op: deze DMV wordt geleegd bij een herstart. Oordeel pas na een volledige businesscyclus — die ene index kan voor het maandeinde- of jaarwerkproces bestaan.

Missing index suggesties: startpunt, geen gospel

De missing-index-DMV's (en de groene hint in SSMS) zijn handig, maar naïef: ze kijken per query, stellen kolomvolgordes voor die niet altijd kloppen en stapelen graag tien bijna-identieke indexen op elkaar. Mijn werkwijze: verzamel de suggesties, groepeer ze per tabel, en ontwerp dan zélf één of twee indexen die meerdere queries tegelijk bedienen.

Zo houd je een indexlandschap dat je queries draagt in plaats van je writes verstikt: een bewuste clustered index, een handvol doordachte nonclustered indexen met de juiste kolomvolgorde, INCLUDE waar het lookup-pijn wegneemt — en periodiek opruimen wat niet gebruikt wordt.

Twijfel over het indexontwerp van een drukke tabel? Ik kijk graag even mee.