Houd je SQL Server-statistieken vers: een gids voor optimale databaseprestaties

Inleiding

Vraag je je weleens af waarom je perfect getunede SQL-query ineens slakkentraag wordt? Dan zijn verouderde statistieken misschien de boosdoener. In SQL Server zijn statistieken als het navigatiesysteem voor je query optimizer: ze helpen hem de snelste route naar je data te kiezen. Zijn ze verouderd, dan neemt je database misschien de toeristische route terwijl hij over de snelweg had moeten gaan.

In deze post bekijk ik waarom het bijwerken van statistieken zo belangrijk is, hoe vaak je het moet doen en welke manieren er zijn om ze vers te houden, met speciale aandacht voor onderhoud per schema.

Wat zijn SQL Server-statistieken?

Statistieken in SQL Server zijn objecten met informatie over de verdeling van waarden in een of meer kolommen van een tabel of geïndexeerde view. Zie ze als een samenvatting van je data die de query optimizer helpt om slimme keuzes te maken over hoe hij queries uitvoert.

Weet de optimizer dat een kolom vooral unieke waarden bevat en niet veel dubbele, of dat de meeste waarden binnen een bepaald bereik vallen, dan kan hij efficiëntere execution plans kiezen. Zonder juiste statistieken is het alsof je de weg zoekt in een stad met een verouderde kaart.

Waarom moet je statistieken bijwerken?

1. Queryprestaties

Het meest directe voordeel is betere queryprestaties. Zijn de statistieken actueel, dan kan de optimizer goed inschatten hoeveel rijen verschillende bewerkingen teruggeven. Dat leidt tot betere execution plans.

2. Zuinig met resources

Juiste statistieken helpen SQL Server om resources goed te verdelen. Dat betekent minder verspild geheugen, minder CPU-gebruik en minder I/O-bewerkingen.

3. Problemen met parameter sniffing voorkomen

Het is geen complete oplossing, maar verse statistieken kunnen sommige problemen met parameter sniffing verminderen. De optimizer heeft dan actuele informatie over de verdeling van de data.

4. Query timeouts voorkomen

Queries die normaal in seconden klaar zijn, kunnen een timeout krijgen als de statistieken flink verouderd zijn. De optimizer kiest dan misschien nested loops waar hash joins beter passen.

Hoe vaak moet je statistieken bijwerken?

Hoe vaak hangt af van een paar factoren:

Tabellen met veel transacties

• Dagelijks bijwerken voor tabellen waarin meer dan 10-20% van de data per dag wijzigt
• Overweeg meerdere keren per dag voor tabellen die extreem vaak wijzigen

Tabellen met gemiddelde activiteit

• Wekelijks bijwerken voor tabellen met 5-10% wijzigingen per week
• Goed voor de meeste OLTP-systemen met gewone kantoortijden

Statische of langzaam wijzigende tabellen

• Maandelijks of per kwartaal bijwerken voor referentietabellen
• Configuratie- of lookup-tabellen die zelden wijzigen

Na grote databewerkingen

Werk statistieken altijd bij na:

• Bulk inserts of updates
• Archiveringen
• Grote DELETE-bewerkingen
• Index rebuilds (al werken REBUILD-bewerkingen de statistieken automatisch bij)

Verschillende manieren om statistieken bij te werken

1. De voorhamer: alle statistieken bijwerken

De eenvoudigste manier is om alle statistieken in je database of schema bij te werken:

1
-- Update all statistics in a specific schema
2
EXEC sp_MSforeachtable
3
    @command1 = 'UPDATE STATISTICS ? WITH FULLSCAN',
4
    @whereand = 'AND SCHEMA_NAME(schema_id) = ''dyn''';

Voordelen:

• Eenvoudig te bouwen
• Je mist niets
• Goed voor kleinere databases

Nadelen:

• Kost veel resources
• Werkt misschien statistieken bij die het niet nodig hebben
• Kan blokkeringen geven op drukke systemen

2. De chirurg: gericht bijwerken op basis van wijzigingen

Een verfijndere manier werkt alleen de statistieken bij die flink gewijzigd zijn:

1
-- Update only stale statistics (>10% modifications)
2
SELECT 'UPDATE STATISTICS [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] WITH FULLSCAN;'
3
FROM sys.tables t
4
INNER JOIN sys.stats s ON t.object_id = s.object_id
5
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
6
WHERE sp.modification_counter > 0.1 * sp.rows

Voordelen:

• Zuinig met resources
• Weinig belasting voor het systeem
• Kan vaker draaien

Nadelen:

• Lastiger te bouwen
• Mist misschien statistieken die langzaam verouderen

3. De optimizer: sampling die zich aanpast

Gebruik bij grote databases verschillende sample rates op basis van de tabelgrootte:

1
-- FULLSCAN for small tables, SAMPLE for large ones
2
CASE
3
    WHEN rows < 1000000 THEN 'WITH FULLSCAN'
4
    ELSE 'WITH SAMPLE 30 PERCENT'
5
END

Voordelen:

• Houdt nauwkeurigheid en prestaties in balans
• Schaalt goed mee met de grootte van de database
• Redelijke looptijden

Nadelen:

• Statistieken op basis van een sample zijn minder nauwkeurig
• De grens voor de tabelgrootte moet je goed afstellen

4. De planner: bijwerken op basis van tijd

Werk statistieken bij op basis van hun leeftijd in plaats van het aantal wijzigingen:

1
-- Update statistics older than 7 days
2
WHERE DATEDIFF(DAY, last_updated, GETDATE()) > 7

Voordelen:

• Voorspelbare onderhoudsvensters
• Vangt langzaam wijzigende tabellen op
• Eenvoudig in te plannen

Nadelen:

• Werkt misschien onnodig bij
• Mist misschien snelle wijzigingen

5. De hybride: meerdere criteria

Combineer meerdere criteria om slim bij te werken:

1
WHERE
2
    last_updated IS NULL -- Never updated
3
    OR DATEDIFF(DAY, last_updated, GETDATE()) > 7 -- Age threshold
4
    OR modification_counter > 0.1 * rows -- Change threshold

Voordelen:

• Dekt alles af
• Flexibel en aan te passen
• Vangt randgevallen op

Nadelen:

• Lastigere logica
• Moet je zorgvuldig afstellen

Best practices en aanbevelingen

1. Maak een onderhoudsstrategie

• Leg je beleid voor het bijwerken van statistieken vast
• Verschillende schema’s hebben misschien een andere aanpak nodig
• Houd rekening met bedrijfscycli en piekmomenten

2. Meet en stel bij

• Volg de queryprestaties in de loop van de tijd
• Houd de leeftijd van statistieken en de modification counters in de gaten
• Stel de grenzen bij op basis van wat je ziet

3. Gebruik SQL Agent jobs

• Automatiseer het bijwerken van statistieken
• Plan het in op rustige momenten
• Bouw foutafhandeling en meldingen in

4. Neem het indexonderhoud mee

• Index rebuilds werken statistieken automatisch bij
• Een reorganize doet dat niet
• Plan deze bewerkingen samen

5. Test eerst buiten productie

• Controleer wat je onderhoudsstrategie doet
• Test verschillende sample rates
• Meet de looptijden

Veelgemaakte fouten

1. Te vaak bijwerken: te vaak draaien verspilt resources
2. Te kleine samples: een sample rate die te laag is voor juiste statistieken
3. Bijwerken in de piek: blokkeringen tijdens kantoortijden
4. Kleine tabellen negeren: ook kleine tabellen hebben baat bij juiste statistieken
5. Instellen en vergeten: niet bijhouden of je strategie werkt

Conclusie

Statistieken vers houden is als het olie verversen van je auto: preventief onderhoud dat alles soepel laat lopen. SQL Server doet zelf wat automatisch onderhoud aan statistieken. Neem je dit proces zelf in handen, dan krijg je de beste prestaties voor jouw specifieke werklast.

Begin eenvoudig en verfijn je aanpak op basis van wat je ziet. Volg de queryprestaties, houd de leeftijd van statistieken bij en pas je strategie aan waar nodig. Onthoud: het beste onderhoudsplan voor statistieken houdt nauwkeurigheid en resourcegebruik in balans en haalt de prestaties die je nodig hebt.

Of je nu één schema beheert of een hele databaseserver: regelmatig statistieken bijwerken hoort een vast onderdeel van je onderhoudsstrategie te zijn. Je queries bedanken je met constante, voorspelbare prestaties.

Snel overzicht: beslisboom voor het bijwerken van statistieken

1. Is je tabel statisch? → Maandelijks bijwerken met FULLSCAN
2. Wijzigt je tabel >20% per dag? → Dagelijks bijwerken, overweeg SAMPLE voor grote tabellen
3. Wijzigt je tabel tussen 1-20% per dag? → Wekelijks bijwerken met sampling die zich aanpast
4. Heb je net bulkbewerkingen gedaan? → Meteen bijwerken met FULLSCAN
5. Krijgen queries een timeout? → Controleer meteen de leeftijd van de statistieken

Onthoud: twijfel je, meet dan en stel bij. Je datapatronen zijn uniek, en je strategie voor statistieken hoort daarbij te passen.