SQL Server auto statistics update

Query performance leunt zwaar op SQL Server statistieken. Goede statistieken dragen bij aan een betere performance van query’s. Het belang van SQL Server statistieken is dermate groot, dat ik er een bijdrage in het Nederlands aan wijd.

Wat zijn statistics?
Statistics voor query performance tuning zijn objecten die statistische informatie bevat over de verdeling van waarden in één of meer kolommen van een tabel of indexed view. De query optimizer (de interne tool die SQL Server gebruikt om query plannen te maken) gebruikt deze statistieken om de kardinaliteit, of het aantal rijen, in het query resultaat te schatten. Deze schatting van de kardinaliteit biedt de query optimizer de mogelijk om een optimaal query plan te maken, zodat de performance van de query zo optimaal mogelijk wordt (vertaald uit dit document).

Aanzetten van automatisch bijwerken van statistics op de database
De meeste SQL Server gurus raden aan om statistics te gebruiken. SQL Server geeft de mogelijkheid om de statistics standaard aan te maken bij het aanmaken van tabellen en/of indexed views en geeft de mogelijkheid om deze automatisch bij te laten werken. Via de eigenschappen (properties) op database niveau kunnen in het tabblad ‘Options’ de opties ‘Auto create statistics’ en ‘Auto update statistics’ aan worden gezet.

Automatisch bijwerken van statistics in SQL Server 2014 en daarvoor
Dit is waar deze bijdrage daadwerkelijk om gaat. Statistics worden bijgewerkt wanneer bepaalde drempelwaarden worden bereikt. Deze drempelwaarden zijn:

  1. Een lege tabel wordt met minimaal één record gevuld
  2. Een tabel bereikt de 500 records
  3. In tabellen die al meer dan 500 records hebben, worden statistics bijgewerkt wanneer 500 records + 20% zijn gewijzigd, verwijderd of zijn toegevoegd.
Bovenstaande tabel wordt hier bij Microsoft beschreven.

Potentieel probleem
Vooral in datawarehouse databases die op de Kimball techniek steunen kunnen bovenstaande drempelwaarden tot problemen leiden. Eén van de hoekstenen van de Kimball techniek, is dat onbekende dimensiewaarden in feitentabellen (bijvoorbeeld een omzetregel in de tabel FactOmzet met een regiocode die niet in de DimRegiocode tabel voorkomt) naar een zogenaamd ‘onbekend’ record in de dimensietabel verwijst. Elke dimensietabel in deze techniek heeft een ‘onbekend’ record waarnaar verwezen wordt, indien het dimensierecord niet in de dimensietabel voorkomt.

Hoe kan dat fout gaan? Tijdens het aanmaken van de dimensietabel wordt eerst een ‘onbekend’ record toegevoegd. Daarna wordt de bestaande dimensiewaarden in de tabel ingelezen. Na het inlezen van het eerste (‘onbekend’) record worden de table statistics bijgewerkt. De statistics geven nu aan dat de tabel uit één record bestaat. Wanneer er niet meer dan 498 dimensiewaarden worden toegevoegd, worden de statistics volgens de tabel ‘Automatisch bijwerken van statistics’ niet opnieuw bijgewerkt, omdat de drempelwaarden niet gehaald worden. Vooral wanneer de dimensietabel wat groter is (maar niet meer dan 500 records heeft), kan dit bij het opzetten van een query plan door de query optimizer tot potentiële problemen leiden.

De oplossing
Er zijn een aantal oplossingen voor dit potentiële probleem:

  1. Werk de statistics handmatig bij via de functie UPDATE STATISTICS (staat hier beschreven). Deze optie optie heeft mijn voorkeur.
  2. Zet Trace Flag 2371 aan (staat hier beschreven). Ik adviseer zelf om in een productie omgeving nooit met trace flags te werken, omdat het aan- of uitstaan van trace flags niet goed zichtbaar is.
Automatisch bijwerken van statistics in SQL Server 2016 en verder
In SQL Server 2016 wordt Trace Flag 2371 standaard aangeboden. Deze trace flag heeft geen echter geen gefixeerde drempelwaarde, zoals SQL Server 2014 en daaronder kent. Microsoft geeft aan dat hoe groter de tabel is, hoe minder records relatief hoeven te worden bijgewerkt om de statistics bijgewerkt te krijgen. Dat kan een nadeel zijn en het is daarom zaak om query performance goed in de gaten te houden en eventueel tijdig de statistics handmatig bij te werken.

Tenslotte nog dit ter herinnering…
Statistics op tabellen en indexed views worden pas bijgewerkt wanneer SQL Server een (nieuw) query plan op de tabel maakt. Bij het samenstellen van een nieuw query plan stelt SQL Server pas vast dat de statistieken niet meer up-to-date zijn en worden (wanneer de optie ‘Auto Update Stats’ aanstaat) de statistieken bijgewerkt. Om zelf te kunnen zien of statistics goed zijn bijgewerkt kan van de functie DBCC SHOW_STATISTICS gebruik worden gemaakt. Deze functie wordt hier beschreven.

Meer informatie
Meer informatie is via de volgende Microsoft links beschikbaar in het Engels.

SQL Server Statistics (concepts)
Controlling autostat auto update statistics behavior in SQL Server
Statistical maintenance functionality: autostats in SQL Server
List of trace flags in SQL Server

… en speciaal voor SQL Server 2016

Default auto statistics update treshold change for SQL Server 2016