Waarom je SQL Server-query niet doet wat je verwacht

Je schrijft een SQL-query die er op het eerste gezicht prima uitziet. Er staat een index op de kolom waarop je zoekt, de query retourneert precies de gegevens die je nodig hebt en bij een kleine dataset lijkt alles goed te werken. Toch kan dezelfde query in een productieomgeving ineens veel langer duren.

SQL Server doet daarbij niet zomaar iets verkeerd. De database-engine probeert zelf te bepalen hoe een query op de meest efficiënte manier kan worden uitgevoerd. Daarbij kijkt SQL Server onder andere naar indexes, statistics, de totale hoeveelheid data en het verwachte aantal records.

Juist daar kan het misgaan. SQL Server maakt namelijk geen plan op basis van wat jij als ontwikkelaar logisch vindt, maar op basis wat hij logisch vindt. En dat baseert hij op de informatie die op dat moment beschikbaar is.

In deze blog bespreek ik een aantal situaties waarin een query anders werkt dan je zou verwachten. Daarbij kijken we onder andere naar execution plans, parameter sniffing, implicit conversions en functies in WHERE clausules.

Tekst: Sander van der Bom

Als ontwikkelaar zie je vooral de SQL die je schrijft:

SELECT *
FROM Orders
WHERE KlantId = 1234

SQL Server moet deze opdracht echter eerst vertalen naar een uitvoeringsplan. Dat plan bepaalt bijvoorbeeld:

• welke index wordt gebruikt
• welke tabellen worden als eerste gelezen
• hoe worden tabellen aan elkaar gekoppeld
• hoeveel records verwacht SQL Server als resultaat te krijgen
• moeten er gegevens worden gesorteerd
• moet een volledige tabel worden gelezen

Je kunt een execution plan daarom zien als de route die SQL Server kiest om een resultset terug te geven. En die route is niet altijd de route die je zelf zou kiezen.

Een veelgemaakte aanname is dat SQL Server een index gebruikt zodra die beschikbaar is. Stel, je hebt een tabel met tien miljoen records en een index op de kolom KlantId:

CREATE INDEX IX_Orders_KlantId
ON Orders (KlantId)

Vervolgens voer je de volgende query uit:

SELECT *
FROM Orders
WHERE KlantId = 1234

Je zou verwachten dat SQL Server een Index Seek uitvoert. Maar dat gebeurt niet altijd. Als KlantId = 1234 bijvoorbeeld miljoenen records oplevert, kan SQL Server besluiten dat het efficiënter is om een groot deel van de index of tabel te lezen. In dat geval kan een Scan sneller zijn dan steeds afzonderlijke records via de index opzoeken.

Een Index Seek klinkt vrijwel altijd beter dan een Index Scan, maar ook dat gaat ook niet op in alle gevallen. Afhankelijk van de hoeveelheid data kan een scan in sommige situaties juist efficiënter zijn.

Dat betekent dat de belangrijkste vraag niet de volgende zou moeten zijn "Gebruikt mijn query een index?", maar deze vraag: "Heeft SQL Server een efficiënte uitvoeringsstrategie gekozen?"

Daarvoor moet je naar het daadwerkelijke execution plan kijken.

Een paar van de interessantste onderdelen van een execution plan zijn de Estimated Rows en Actual Rows.

Stel dat SQL Server verwacht dat een filter ongeveer 100 records oplevert (Estimated Rows is 100), maar dat de query in werkelijkheid 100.000 records oplevert (Actual Rows is 100.000). Dan heeft SQL Server een groot probleem. De geschatte hoeveelheid data heeft namelijk invloed op de keuzes die SQL Server maakt. Bij een klein aantal records kan een Nested Loops Join bijvoorbeeld een goede keuze zijn. Bij grote hoeveelheden data kan een Hash Match veel efficiënter zijn.

Een verkeerde inschatting kan daardoor leiden tot een verkeerd execution plan.

Hoe ontstaat zo'n verkeerde inschatting?

SQL Server gebruikt hiervoor onder andere Statistics. Statistics bevatten informatie over de verdeling van waarden in een kolom. Maar Statistics zijn geen exacte kopie van je tabel. Ze geven SQL Server een beeld van de data waarop de query optimizer zijn beslissingen baseert. Wanneer de aanwezige data veel wordt gewijzigd, dan kunnen de verwachtingen van SQL Server steeds verder afwijken van de werkelijkheid.

Als je in een execution plan grote verschillen ziet tussen Estimated Rows en Actual Rows, is dat dus een belangrijk signaal om verder te onderzoeken.

Een ander veelvoorkomend probleem is Parameter Sniffing. Stel dat je de volgende stored procedure hebt gemaakt:

CREATE PROCEDURE GetOrders
    @KlantId int
AS

BEGIN
    SELECT *
    FROM Orders
    WHERE KlantId = @KlantId

END

De eerste keer dat deze procedure wordt uitgevoerd, gebruikt SQL Server de meegegeven waarde om een execution plan te maken. Het gemaakte execution plan kan vervolgens worden hergebruikt. Normaal gesproken is dat juist een voordeel. SQL Server hoeft niet iedere keer opnieuw een plan te maken. Maar wat gebeurt er wanneer de data sterk afwijkt ten opzichte van de laatste keer dat de stored procedure werd gebruikt?

Stel dat klant met KlantId 1234 vijf orders heeft gedaan en klant met KlantId 5678 maar liefst 500.000 orders heeft gedaan. Een execution plan dat uitstekend werkt voor klant 1234 hoeft helemaal niet geschikt te zijn voor klant 5678. Toch kan SQL Server hetzelfde plan blijven gebruiken.

Het resultaat kan verrassend zijn:
Klant 10 → 20 milliseconden
Klant 20 → 30 seconden

De query is hetzelfde. De parameter is anders. Het execution plan kan echter zijn gebaseerd op de eerste parameterwaarde waarmee de procedure werd gecompileerd. Dit verschijnsel noemen we Parameter Sniffing.

Er zijn verschillende manieren om dit probleem aan te pakken, afhankelijk van de situatie. Bijvoorbeeld OPTION (RECOMPILE), OPTIMIZE FOR of een andere manier van query-opbouw.

Maar ook hier geldt: verander niet zomaar iets omdat je parameter sniffing vermoedt. Kijk eerst naar het execution plan en naar de verschillen in dataverdeling.

Een andere oorzaak van onverwachte performanceproblemen is een datatype dat niet overeenkomt. Stel dat je tabel de volgende kolom bevat: Klantnummer varchar(20), maar je vergelijkt de kolom met een integer: WHERE Klantnummer = 1234.

In dit geval moet SQL Server mogelijk een datatypeconversie uitvoeren. Dat zie je niet altijd direct terug in je SQL, maar in het execution plan kan bijvoorbeeld een CONVERT_IMPLICIT zichtbaar worden. Bij grote tabellen kan zo'n conversie grote gevolgen hebben voor de performance. Daarom is het verstandig om datatypes bewust gelijk te houden. Niet alleen in tabellen, maar ook in parameters en variabelen die je vanuit bijvoorbeeld .NET gebruikt.

Ook SELECT * lijkt onschuldig:

SELECT *
FROM Orders
WHERE KlantId = 1234

Maar als een tabel twintig of dertig kolommen bevat, haal je mogelijk veel meer data op dan je daadwerkelijk nodig hebt.

Dat heeft verschillende consequenties. De database moet meer data lezen en mogelijk meer data uit een index ophalen. Daarnaast moet meer data vanuit SQL Server naar de applicatie worden verstuurd en vervolgens door de applicatie worden verwerkt. Als je maar drie kolommen nodig hebt, specificeer deze kolommen dan in je select statement:

SELECT OrderId, Datum, Bedrag
FROM Orders
WHERE KlantId = 1234

Het voordeel is niet alleen de leesbaarheid: een goed gekozen index kan soms alle benodigde kolommen bevatten, waardoor SQL Server de onderliggende tabel helemaal niet hoeft te raadplegen. Zo’n index (in combinatie met de juiste query) noemen we een Covering Index.

Wanneer een query onverwacht langzaam is, wordt er vaak voor gekozen om direct een index toe te voegen. Dat is niet altijd de juiste eerste stap, je kunt beter starten met het bestuderen van het execution plan. Let daarbij onder andere op het volgende;

• grote verschillen tussen Estimated en Actual Rows
• Index Scans over grote tabellen
• Key Lookups die heel vaak worden uitgevoerd
• Sorteringen
• waarschuwingen in het execution plan
• Implicit Conversions

Een execution plan vertelt niet automatisch wat het probleem is, maar het geeft wel inzicht in wat SQL Server daadwerkelijk doet. Dat is vaak veel waardevoller dan alleen de SQL-query te analyseren.

Ook een hele kleine wijziging in een query kan grote gevolgen hebben. Stel je zoekt naar klanten met een bepaalde postcode:

SELECT *
FROM Klant
WHERE Postcode = '1234AB'

Als er een geschikte index op Postcode staat, kan SQL Server efficiënt zoeken. Maar stel dat je deze query schrijft:

SELECT *
FROM Klant
WHERE UPPER(Postcode) = '1234AB'

Je voert nu een functie uit op de Postcode kolom voordat SQL Server kan bepalen welke records voldoen. Dat kan ervoor zorgen dat een efficiënte Index Seek niet meer mogelijk is en SQL Server veel meer data moet bekijken. Hetzelfde probleem kan ontstaan wanneer je bijvoorbeeld filtert op een deel van een DATETIME veld: WHERE YEAR(Datum) = 2026.

Je kunt dit specifieke voorbeeld anders formuleren: WHERE Datum >= '20260101' AND Datum <  '20270101'. In deze voor het oog meer complexe oplossing kan SQL Server veel gemakkelijker gebruikmaken van een index op Datum.

Dit principe wordt vaak aangeduid als SARGability. Een predicate is sargable wanneer SQL Server de conditie efficiënt kan gebruiken om data in een index te zoeken.