Elke tabel in een OLTP database zou een clustered index moeten hebben.

Waarom? Een tabel zonder clustered index wordt een heap genoemd, en een heap biedt totaal geen structuur. SQL Server houdt slechts bij welke database pagina’s gealloceerd zijn voor de tabel en kan in principe dus niks anders dan sequentieel langs die database pagina’s lopen. Wanneer non-clustered indexen worden geplaatst bovenop een heap, dan kan SQL Server niet anders dan absolute verwijzingen maken naar de data. Een absolute verwijzing bestaat uit een row-id: filenr+pagenr+slotnr. Als dan vervolgens een update wordt gedaan op een record en dat record past daardoor niet meer op de pagina, dan moet het record verhuizen naar een andere pagina. Om te voorkomen dat SQL Server daardoor de dataverwijzingen in de indexen ook moet aanpassen, wordt er op het oude adres een doorverwijzing gemaakt naar het nieuwe adres; een zogenaamd forwarding record. Dit proces kan zich voor hetzelfde record herhalen
waardoor er na verloop van tijd een lange keten van doorverwijzingen ontstaat. Dit geeft een sub-optimale performance, maar is ook foutgevoelig. Eén keer een schrijffoutje op disk kan de ketting van doorverwijzingen verbreken en omdat er voor de rest geen structuur is, kan dit niet automatisch worden hersteld. Dat betekent meestal verlies van data. Kortom, een heap is prima geschikt als tijdelijke tabel, maar niet voor een tabel die updates te verwerken krijgt.

 

Ok, elke tabel krijgt dus een clustered index. Maar wat houdt dat in? Bij een clustered index ligt de data fysiek gesorteerd op de sleutel opgeslagen. En bovenop die data ligt een gebalanceerde index boom met sleutelwaarden en verwijzingen naar het volgende niveau in de boom. Het onderste niveau in de index boom is de data zelf en daardoor heeft een
clustered index meestal een niveau minder dan een non-clustered index. Bij het muteren blijft de sleutelvolgorde altijd gehandhaafd. Bij het toevoegen van een record kan dit betekenen dat op de gewenste plek geen ruimte genoeg meer is. Er zal dan een page split plaatsvinden. De helft van de rijen verhuizen naar de nieuwe pagina zodat er plek genoeg is. Hetzelfde kan gebeuren bij updates: de sleutelwaarde kan gemuteerd worden, maar het record kan ook groeien doordat bijvoorbeeld een varchar kolom een grotere waarde krijgt. Een page split leidt tot
fragmentatie: externe fragmentatie omdat de nieuwe page uit een andere plek in de datafile wordt gealloceerd. Maar ook interne fragmentatie omdat alle pagina’s niet meer volledig vol zijn. Al reden genoeg om de index regelmatig te defragmenteren.

 

Op een tabel met een clustered index kunnen uiteraard ook non-clustered indexen worden geplaatst. Het onderste niveau van de non-clustered index bevat verwijzingen naar de data. Maar niet in de vorm van een row-id zoals bij een heap, maar in de vorm van de clustered index sleutel attributen. Om via een non-clustered index een record op te zoeken, moet dus niet alleen de non-clustered index worden doorlopen, maar ook nog eens de clustered-index. Dit heeft een aantal consequenties.

De eerste is dat de clustered index sleutel goed moet worden gekozen. De ideale clustered index sleutel is:

1)    Smal, zodat de overhead in het onderste niveau van de non-clustered indexen minimaal blijft.

2)    Continu oplopend, zodat inserts efficient uitgevoerd kunnen worden zonder page splits

3)    Onveranderlijk, zodat non-clustered indexen niet bijgewerkt hoeven worden en ook geen page splits nodig zijn in de clustered index.

4)    Uniek, zodat SQL Server geen uniquefiers hoeft toe te voegen om een record uniek identificeerbaar te maken.

Dit maakt een identity kolom tot een ideale kandidaat. Mijn stelregel is dan ook dat elke OLTP tabel een identity kolom als kunstmatige sleutel moet hebben. Niet
toevallig wordt een primary key constraint geimplementeerd met een unieke clustered index.

 

Een andere consequentie van het feit dat een non-clustered index lookup ook altijd resulteert in een key-lookup in de clustered index, is dat de non-clustered index bij een range scan niet altijd interessant is. De bovengrens voor het aantal logical I/O’s dat SQL Server moet doen om alle records van een tabel te lezen is een table scan. Een clustered index heeft meestal 3 niveau’s. Dat wil zeggen dat voor een key-lookup 3 logical I/O’s nodig zijn. Wanneer een range scan op de non-clustered index 10 records oplevert, dan zijn nog eens 3 x 10 = 30 logical I/O’s nodig om de betreffende records te lezen. Wanneer de hele tabel maar 25 pagina’s beslaat, dan is een table scan goedkoper.  Het omslagpunt ligt zelfs nog
iets lager dan 25 I/O’s, omdat een table scan sneller is uit te voeren dan een verzameling index traversals.

 

De meerwaarde van een non-clustered index lijkt dus beperkt te zijn tot key-lookups en kleine range scans. Maar dat is niet zo. De waarde van een non-clustered index kan worden vergroot door hem covering te maken. Een covering index is een index waarbij alle benodigde attributen voor een query in de index zijn opgenomen, zodat de data records zelf niet geraadpleegd hoeven te worden.  Dat doe je door extra kolommen te includen. Deze worden dan meegenomen op het onderste niveau in de index boom.

Dus stel je hebt de query:

SELECT attr2, attr3, attr4
FROM tabel
WHERE attr1 = x AND attr2 BETWEEN y AND z

De bijbehorende covering index is dan:

CREATE NONCLUSTERED INDEX naam
ON tabel (attr1, attr2)
INCLUDE (attr3, attr4)

 

Covering indexen hebben wel een prijs in termen van benodigde schijfruimte, maar ook in termen van kosten bij updates: wanneer een attribuut wijzigt dat in een index is opgenomen, dan moet de wijziging niet alleen in de data verwerkt worden, maar ook in de index. Er dient dus altijd een goede afweging gemaakt te worden tussen select performance en update performance. De Performance Tuning Wizard houdt daar geen rekening mee, dus die komt altijd met een heleboel covering index voorstellen. Implementeer deze niet zonder er bij na te denken. Maar als het om select performance gaat geldt wel: het beste query plan is altijd op basis van covering indexen.

 

Het includen van columns is nieuw sinds SQL Server versie 2005. Sindsdien optimaliseer je queries altijd met non-clustered indexen. De clustered index is slechts om de tabel een goede opslagstructuur te geven.

 

Wat veel mensen zich niet realiseren is dat een foreign key constraint niet per default een index krijgt. Maar bij het afdwingen van referentiele integriteit is een index wel degelijk nodig; anders resulteert de controle of er nog aan een sleutel gerefereerd wordt in een table scan op de refererende tabel. Een goede gewoonte is dus om op elke foreign key ook een index te leggen. Eventueel kan de index later nog uitgebreid worden met included columns.