Ik doe regelmatig reviews van de SQL Server omgeving bij klanten. Een van de aandachtspunten die telkens terug komt is de keuze van de clustered index sleutel.

SQL Server heeft twee verschillende opslagstructuren: Heap en Clustered Index.

Bij een Heap is er nauwelijks sprake van een structuur; SQL houdt slechts bij welke database pagina’s gealloceerd zijn voor de betreffende tabel.

Een Heap is alleen te benaderen dmv een table scan, tenzij er ook non-clustered indexen zijn gedefinieerd. Een non-clustered index bovenop een heap heeft verwijzingen naar de betreffende data records mbv row-id’s. Een row-id bestaat uit een file number, page number en slot number. Een redelijk absolute adressering dus. Je vindt het navigeren naar de data in het query plan terug als een row lookup. Wanneer een record een update ondergaat waardoor hij niet meer past binnen de page, zal hij worden verplaatst naar een andere page waar nog wel voldoende ruimte is. Wat betekent dit voor de adressering van het record vanuit de non-clustered index? Deze moet eigenlijk worden bijgewerkt; het record is immers verhuisd naar een andere locatie! Dat doet SQL Server niet; in plaats daarvan plaats hij een forwarding record op het oorspronkelijk adres, met de verwijzing naar het nieuwe adres. Dat is alsof je een afspraak hebt met iemand en op de plaats van afspraak een briefje vindt dat de persoon op een andere locatie gevonden kan worden. Dit is een nogal bewerkelijk manier van doen. Al reden genoeg om de heap structuur af te wijzen voor een OLTP database tabel. In praktijk ben ik gevallen van database corruptie tegengekomen omdat de keten van forwarding pointers ergens gebroken was; en omdat een heap daarnaast geen structuur biedt, is het direct einde oefening. Een HEAP is prima als een scratchpad voor data die je tijdelijk ergens wilt parkeren om het later in zijn geheel te verwerken. Als staging tabel in een data warehouse bijvoorbeeld.

Bij een Clustered Index daarin tegen is heel duidelijk wel sprake van structuur. Allereerst is de data fysiek gesorteerd conform de index sleutel. Dit impliceert direct dat er maar een clustered index per tabel kan zijn. Daarnaast is er daadwerkelijk sprake van een index, dus een boomstructuur van indexsleutels. Op elk niveau in de index boom zijn de index pagina’s aan elkaar gelinked; zelfs twee kanten op. Dat maakt het navigeren door de index boom heel efficient. Omdat het onderste niveau in de boomstructuur de data zelf is, heeft een clustered index meestal een niveau minder dan een non-clustered index. Wat veel mensen niet weten, is dat een non-clustered index boven op een tabel met een clustered index verwijst naar de data met behulp van de clustered index sleutel. Dus het onderste niveau in de non-clustered index boom bevat zowel de non-clustered index sleutelattributen als de clustered index sleutelattributen. Wanneer een record wordt gezocht mbv de non-clustered index, dan wordt eerst de non-clustered index boom doorlopen. En vervolgens nog eens de clustered index om bij de data te komen. Deze actie zie je in het query plan terug als een key lookup.

Dat brengt me bij de vraag boven deze blog post: wat is nu een goede clustered index sleutel?

Een goede clustered index sleutel is:

1) Smal, zodat de verwijzingen in de non-clustered indexen zo weinig mogelijk ruimte in beslag nemen

2) Continu oplopend, zodat het toevoegen van nieuwe records nooit leidt tot page splits om de data volgens de sleutel gesorteerd te houden

3) Onveranderlijk, zodat er geen enkele pagina bijgewerkt hoeft te worden als gevolg van een update.

Een uitstekende kandidaat voor de clustered index is dus een identity column! Tunen van queries doe je vervolgens met non-clustered indexen. Voer voor een volgende blog post.