[Sql Server] Performance tuning / clustered indexes

[whoami]

Ik heb een databank waarop in eerste instantie heel wat inserts zullen gebeuren.
(De databank wordt opgevuld dmv informatie die uit 'flat files' komt. De eerste load zal ervoor zorgen dat er in bepaalde tabellen 100.000den rows komen te zitten.

Nu heb ik het (fysische) design van de databank eens zitten bekijken, en ik heb gemerkt dat er heel wat tabellen zijn die geen clustered index hebben.
De primary keys zijn GUID's, dus die zijn alleszins geen goede keuze om daar een clustered index op te leggen, aangezien GUID's random zijn; bij een INSERT zou dit dus nogal wat page-splits kunnen veroorzaken.
Nu heb ik zitten denken om een clustered index op het veld 'LastUpdated' (DateTime) te leggen: bij een eerste insert zal de nde row een hogere waarde hebben voor 'LastUpdated' dan de n-1de row, enz.... IMHO zou dat dus geen vertragende factor mogen zijn bij de eerste load, aangezien de fysieke volgorde dan gewoon dezelfde zal zijn als de volgorde waarop de rows geinsert worden.
Nog een bijkomend voordeel van een clustered index op dat veld, is dat er bij de ingebruikname van de applicatie geregeld selects zullen gedaan worden op een datumrange (Op dat lastupdated veld dus).

Een nadeel is natuurlijk als iemand een record wijzigt, het LastUpdated veld ook gewijzigd wordt, en SQL Server zal dan de fysieke opslagvolgorde moeten gaan aanpassen. Ik vraag me af wat dit zal geven bij een 'Save' operatie op 1 of meerdere records, als de tabel 100.000 records bevat. Zal het bepalen van de nieuwe opslagvolgorde significant merkbaar zijn, of zal dat nog wel meevallen aangezien de 'LastUpdated' datum gewoon groter zal zijn dan het laatste geupdate record, en zal het record gewoon achteraan opnieuw toegevoegd worden?

[Robbemans]

Het posten van een record kost niet zo veel tijd indien je niet te veel indexen aanmaakt. Als je alleen een index legt op LastUpdated valt dit dus wel mee.

Hoe kun je echter 'snel' je specifieke record localiseren als je van LastUpdated uitgaat? Referentieel kun je hier bijvoorbeeld niets mee, omdat je referentie wijzigt.

Ik zou persoonlijk OF een ID nummer bijhouden in de tabel (snelle index) OF een unieke index leggen op het GUID veld, alhoewel de laatste optie natuurlijk duur is.

Bijkomend nadeel van de LastUpdated index is dat elke update resulteert in een indexupdate...

Je zou er natuurlijk ook voor kunnen kiezen om pas NA de eerste load je index aan te maken.

[ Voor 29% gewijzigd door Robbemans op 21-09-2004 10:00 ]

[curry684]

LastUpdated is definitely geen goede kandidaat voor een clustered index als ie regelmatig wijzigt. Een tabel hoeft helemaal geen clustered index te hebben, en het feit dat Enterprise Manager default altijd een clustered index op de primary key legt wordt vaak gezien als bug (zeker in het geval van GUIDs inderdaad, maar ook op identity columns zijn ze vaak pointless (is een B-tree index vaak sneller).

Clustered indexes zijn bedoeld voor (vrijwel) statische data die vaak sequentieel opgevraagd wordt. Als je geen veld hebt dat aan die beschrijving voldoet, zet je lekker geen clustered index en pak je een van de 255 nonclustereds als een index wel nodig is

[whoami]

Robbemans schreef op 21 september 2004 @ 09:57:
Het posten van een record kost niet zo veel tijd indien je niet te veel indexen aanmaakt. Als je alleen een index legt op LastUpdated valt dit dus wel mee.

Dat weet ik wel, maar het gaat hier wel om een 'clustered' index.

Hoe kun je echter 'snel' je specifieke record localiseren als je van LastUpdated uitgaat? Referentieel kun je hier bijvoorbeeld niets mee, omdat je referentie wijzigt.

Een clustered index bepaalt de fysieke opslagvolgorde van de records in de tabel.
Als ik een range select moet doen op die datum, en ik vind de eerste datum, dan heb ik direct ook alle andere datums die binnen die range vallen.

Bijkomend nadeel van de LastUpdated index is dat elke update resulteert in een indexupdate...

Dat was ook mijn vraag, wat zal de performance impact zijn als ik een clustered index heb op dat veld. Zal het voor de gebruiker significant trager zijn.

Je zou er natuurlijk ook voor kunnen kiezen om pas NA de eerste load je index aan te maken.

Dat kan ik natuurlijk ook doen.

[whoami]

curry684 schreef op 21 september 2004 @ 10:08:
Een tabel hoeft helemaal geen clustered index te hebben

Als je tabel geen clustered index heeft, dan ziet sql server je tabel als een 'unordered heap'. Op die manier kan het dbms sneller beslissen om een table scan te doen, zelfs al zijn er indexen op die tabel. (Althans, volgens sql-server-performance.com.

[Robbemans]

Een tablescan kun je in principe voorkomen als het veld waarop je zoekt in een index staat. Als je random zoekt (welk veld dan ook) dan is een scan niet te voorkomen.

Dat verandert volgens mij niet met een clustered index... Zoals Curry al zei: een tabel hoeft geen clustered index te hebben.

[whoami]

Robbemans schreef op 21 september 2004 @ 10:18:
Een tablescan kun je in principe voorkomen als het veld waarop je zoekt in een index staat.

Het is de optimizer van het dbms die bepaald of er een index gebruikt wordt of niet.
Indien die index niet uniek genoeg is, zal de index niet gebruikt worden. Als het DBMS nog een andere reden vind om die index niet te gebruiken, zal dat ook niet gebeuren.

Ik weet echt wel de basics van indexen / dbms'en; het gaat hier over dit specifieke geval, dus misschien kunnen we daar ontopic blijven.

[Robbemans]

Je hebt het in je eerste stukje (probleemstelling) over performance-impact bij toevoegen/wijzigen. Zoals je zelf en curry stellen:

- Clustered index alleen op 'statische' data. Dus NIET op LastUpdated
- Index op lastupdated ivm localiseren wijzigingen gewenst

Hiervanuitgaande is het inserten dus geen bottleneck / probleem? Het updaten van index daarmee dus ook niet?

Wat is dan WEL het probleem? Vraag je je alleen af wat het slimste is? Of wil je discussieren over wat het snelste resultaat oplevert voor opvragen/inserten?

Wat ik zou doen:
- Tabel aanmaken zonder index
- Intiele gegevens toevoegen
- Index op PK en LastUpdated (beide non-clustered)

Daarna onderzoeken WELKE queries er tegen deze tabel worden uitgevoerd om gegevens op te vragen en proefondervindelijk index toevoegen op de velden.

[ Voor 22% gewijzigd door Robbemans op 21-09-2004 10:30 ]

[curry684]

whoami schreef op 21 september 2004 @ 10:11:
[...]

Als je tabel geen clustered index heeft, dan ziet sql server je tabel als een 'unordered heap'. Op die manier kan het dbms sneller beslissen om een table scan te doen, zelfs al zijn er indexen op die tabel. (Althans, volgens sql-server-performance.com.

Het hele voordeel van het weglaten van een clustered index is dan ook dat je een unordered heap hebt, en inserts dus maximale performance halen. Als je vervolgens met SQL Profiler table scans tegenkomt zou een unclustered index op de selectie- of sorteringsfields voldoende moeten zijn om altijd een table scan te voorkomen: tenzij de tabel dermate weinig records bevat dat de index laden relatief duurder is, anders mag de optimizer simpelweg geen table scan toepassen. Ik geloof wat dat betreft m'n MCDBA cursus-materiaal sneller dan die website (heb je overigens een deeplink die die claim onderbouwt? )

De essentie dat ik stel dat ik ammenooitniet een clustered index op een veranderlijk veld zou leggen lijkt me trouwens wel ontopic 1 update per dag of zo op een clustered index okee, maar meer is imho onacceptabel.

[ Voor 7% gewijzigd door curry684 op 21-09-2004 10:30 ]

[-FoX-]

Teneerste zou ik pas, indien ik clustered-indices zou opleggen, deze pas na de eerste load opleggen. Of heb je een specifieke reden waarom je dit ervoor zou doen?

Een regel voor het leggen van een index is imho deze zo lang mogelijk uit te stellen totdat er effectief klachten ivm performance komen.

[EfBe]

Je LastUpdated is geen goede kandidaat voor een clustered index, want deze wijzigt nogal. Een clustered index is met name geschikt voor zaken die nauwelijks wijzigen (zoals een PK). Zaken die veel wijzigen (en LastUpdated is een veld dat ALTIJD wijzigt zodra iets wijzigt in het record), zou ik niet indexeren of althans alleen met een normale index.

Je moet indexes definieren op plekken waar table-scans plaatsvinden. Dit houdt dus in dat je je queries moet analyseren, niet je tabellen. Alleen dan kom je er achter waar een dure tablescan plaatsvindt in een where predicate en je dat dus kunt voorkomen dmv een index. Lukraak indexes definieren 'want dat zal wel efficient zijn' lijkt me niet nuttig, want je vergeet er dan juist een paar.

Verder sluit ik me aan bij Fox, wachten totdat je klachten krijgt.

[whoami]

Lukraak indexen definieren doe ik ook niet
Ik weet ook dat een veranderlijk veld geen goede kanditaat is voor een clustered index, maar ik dacht dat dit misschien in dit geval wel kon, aangezien de datetime altijd hoger zal zijn dan de op dat moment hoogste.
Een clustered index op m'n primary key kan ik zowiezo niet zetten, aangezien dit GUID's zijn.

(Over het optimizen van de queries: ik denk dat m'n queries best goed zijn nu; ik heb wel gemerkt dat sommige queries geen index gebruikten maar een table - scan deden, ook al lag er een index op dat veld.
De reden hiervoor was dat bepaalde code er zo uit zag:

code:

cmdQuery.Parameters.Add ("@name", custName);

Het veld waarop deze parameter mee vergeleken werd is een VARCHAR en .NET ging er van uit dat dit een NVARCHAR was (string mapped met NVARCHAR). Dit resulteerde in een convert op DBMS niveau en een index scan ipv een index seek.
De code werd herschreven naar:

code:

cmdQuery.Parameters.Add ("@name", SqlDbType.Varchar);
cmdQuery.Parameters["@name"].Value = ...

en toen werd er wel een index seek gebruikt.
(Maar dat is offtopic)

[whoami]

Ik heb er even zitten aan denken om de indexen te disablen, en ik zou dat kunnen doen. Althans, ik zou misschien enkele indexen kunnen disablen:
tijdens de load worden er nl. ook een aantal checks op de data gedaan (SELECT statements, etc...), dus die zouden toch wel voordeel hebben van de indexen die ze nu ook gebruiken.

[curry684]

Die kun je overigens ook in 1 regel schrijven als:

C#:

cmdQuery.Parameters.Add ("@name", custName).DbType = SqlDbType.Varchar;

[ACM]

whoami schreef op 21 september 2004 @ 09:50:
IMHO zou dat dus geen vertragende factor mogen zijn bij de eerste load, aangezien de fysieke volgorde dan gewoon dezelfde zal zijn als de volgorde waarop de rows geinsert worden.

whoami schreef op 21 september 2004 @ 11:14:
Ik heb er even zitten aan denken om de indexen te disablen, en ik zou dat kunnen doen. Althans, ik zou misschien enkele indexen kunnen disablen:
tijdens de load worden er nl. ook een aantal checks op de data gedaan (SELECT statements, etc...), dus die zouden toch wel voordeel hebben van de indexen die ze nu ook gebruiken.

Maar hoe belangrijk is die performance van die ene keer dat je die data inlaad eigenlijk? Imho moet je niet te veel moeite doen voor dergelijke eenmalige handelingen, of dat nou 10 minuten of 15 minuten duurt, dat maakt meestal niet zo veel uit. Uiteraard moet je zodra die performance wel van belang is, de boel wel wat anders aanpakken.

Robbemans schreef op 21 september 2004 @ 09:57:
Het posten van een record kost niet zo veel tijd indien je niet te veel indexen aanmaakt. Als je alleen een index legt op LastUpdated valt dit dus wel mee.

Als je overwegend leest op de database dan is zelfs het aantal indices nauwelijks van belang, zolang je maar geen zinloze indices hebt lopen aanmaken. Sterker nog, ik vermoed dat je eerder last hebt van table scans dan van "te veel" indices als je bijvoorbeeld een update of delete op je database doet...
Persoonlijk zou ik pas het aantal indices terugbrengen als de tabel echt alleen maar een insert-only tabel is die na een hele serie inserts compleet uitgelezen wordt en vervolgens leeggegooid of iets dergelijks.

[whoami]

ACM schreef op 21 september 2004 @ 11:37:
[...]


[...]

Maar hoe belangrijk is die performance van die ene keer dat je die data inlaad eigenlijk? Imho moet je niet te veel moeite doen voor dergelijke eenmalige handelingen, of dat nou 10 minuten of 15 minuten duurt, dat maakt meestal niet zo veel uit. Uiteraard moet je zodra die performance wel van belang is, de boel wel wat anders aanpakken.

10 of 15 minuten maakt idd niet uit.... 6 of 12 uur dan weer wel.