Neem contact op
Bel mij terug

SQL Server Query Store

Door Reitse Eskens, Support Specialist BI

Eén van de innovaties sinds SQL Server 2016 is de zogeheten Query Store. Omdat dit in de praktijk nog weinig gebruikt wordt geef ik in dit blog een kleine introductie over deze nieuwe feature. Ik ga toelichten wat de Query Store is, hoe deze (op hoofdlijnen) werkt en wat u ermee kunt doen.

Wat is de Query Store?

De Query Store is een extra database op uw systeem, waarin de historie van uw query’s, de zogeheten execution plans en de runtime statistics opgeslagen worden. Deze worden opgeslagen zodat u ze later kunt reviewen. Omdat de data op tijd gescheiden wordt kunt u patronen ontdekken in database gebruik en kan het u helpen te achterhalen waarom een queryplan veranderd is gedurende de tijd. Deze informatie is enorm handig als u zich afvraagt waarom een bepaalde query langzamer of juist sneller een resultaat oplevert.

Hoe wordt de informatie verzameld?

De Query Store werkt als een soort flight datarecorder, er wordt continu informatie verzameld rondom de compilatie en uitvoering van query’s. De data wordt vastgelegd in interne tabellen en via views aan gebruikers aangeboden.

  • De Query Store is actief tijdens de eerste compilatie van een query. De query tekst en het bijbehorende plan worden naar de Query Store gestuurd en daar opgeslagen.
  • Als een query opnieuw gecompileerd wordt, wordt het plan bijgewerkt in de Query Store. Als een nieuw plan wordt opgesteld, wordt deze opgeslagen naast de reeds bekende plannen, samen met de rest van de informatie.
  • Tijdens het uitvoeren van de query worden de statistieken van de uitvoering naar de Query Store gestuurd en opgeslagen. De Query Store bewaart de geaggregeerde gegevens van de query’s.
  • Tijdens de compilatie en check of een query een recompile moet krijgen wordt in de Query Store gekeken of daar een plan staat welke gebruikt moet worden voor de uitvoering van de query. Als zo’n plan gevonden wordt, dan wordt de query opnieuw gecompileerd maar dan met het plan wat volgens de query store gebruikt zou moeten worden.
  • Om de IO overhead te beperken wordt de data in-memory opgevangen. Het schrijven naar disk vindt pas later plaats.

Tot zover de techniek. Ik heb dit bewust kort gehouden om vooral aan te geven dat er enige overhead is, maar dat deze niet heel groot is.

Waarvoor is het te gebruiken?

De Query Store is als eerste goed te gebruiken om een analyse te doen op uw systeem. Worden query’s trager? In de Query Store kunt u zien of het plan is aangepast. Als dat zo is kunt u inzoomen op de details om een oorzaak te achterhalen.

Verder kunt u het goed gebruiken om uw query’s te tunen. U kunt zien met welk plan de query het best werkte. Dit kunt u weer koppelen aan uw gebruik van bijvoorbeeld indexen en statistieken.

Dat laatste ga ik gebruiken als uitgangspunt voor een korte, simpele demo.

Demo

Per database kunt u een Query Store aanmaken. Deze vraagt ruimte en per database moet u voor uzelf bepalen hoe belangrijk historie is. Default wordt er 100 MB aan data opgeslagen wat veel lijkt maar al snel onvoldoende blijkt als u over een langere tijd regressie analyses uit wilt voeren. Voor de demo maak ik gebruik van de default Microsoft demo database WideWorldImporters.

Als u kijkt naar de onderdelen van de database ziet u het volgende:

De Query Store. Daar gaan we straks kijken wat er allemaal mogelijk is. Als eerste activeren we deze op database niveau. Daarvoor gaan we naar de opties van de database.

Bij de database eigenschappen heb ik gekozen voor de Query Store. Daar kan ik deze als eerste activeren:

Belangrijk is om hier te kiezen voor Read Write. Daarmee kunnen we gegevens zowel wegschrijven als weer lezen. De Read Only functie kunt u gebruiken als u voldoende informatie verzameld hebt en geen nieuwe gegevens toe wilt voegen.

De volgende opties hebben te maken met het interval waarmee gegevens weggeschreven worden naar disk en het formaat van de Query Store.

De Data Flush Interval wil zeggen hoelang data in het geheugen opgeslagen blijft voordat het naar disk weggeschreven wordt. Als er binnen deze interval een herstart van uw databaseserver plaatsvindt mist u gegevens. Is het voor u op enig moment kritiek om vrijwel alle informatie te registeren, pas deze waarde dan naar beneden aan.

De Statistics Collection interval geeft de granulariteit aan van de verzamelde statistieken. Hoe lager deze waarde, hoe fijner u in kunt zoomen op wijzigingen binnen uw query’s. Maar houd er wel rekening mee dat het formaat van de Query Store gaat groeien.

Het maximale formaat in MB’s kunt u aangeven met de Max Size. Met de Capture Mode geeft u aan hoe u data wilt opvangen. Met All gaat u alle query’s registreren, met Auto worden insignificante query’s genegeerd. Insignificant vindt Microsoft hier een query die niet frequent voorkomt en query’s met die een zeer korte compile en uitvoeringstijd hebben.

De Size Based Cleanup Mode kunt u op auto of off zetten. Daarmee kunt u aangeven of er automatisch geschoond moet worden als de query store database bijna vol is.

De Stale Query Threshold geeft aan hoe lang data in de Query Store mag blijven.

Voor de demo maak ik gebruik van de volgende query. De resultaten laat ik overigens buiten beschouwing aangezien die verder niet relevant zijn.

Deze query is tweemaal uitgevoerd. De eerste keer zonder, de tweede keer met de volgende index:

In de Query Store zien we bij de regressed query’s het volgende:

De query zien we staan en wat andere informatie met betrekking tot de uitvoering van de query.

Daarnaast zien we een grafiek staan met twee bolletjes:

Er zijn twee plannen gevonden voor de betreffende query; plan 28 en plan 40.

We kunnen beide plannen los bekijken en ons suf klikken tussen de twee plannen, maar we kunnen ze ook naast elkaar zetten:

Dan zien we het volgende:

De verschillen worden duidelijk gemarkeerd. In het scherm ernaast worden de details getoond:

Omdat de query vrij simpel is ziet u weinig verschillen maar bij een grote query zult u sneller verschillen zien. Daar waar zaken anders zijn, ziet u ook een duidelijke markering.

Tot slot

De bovenstaande demo is bewust simpel gehouden om u een eerste indruk te geven van de Query Store en de mogelijkheden. De beste manier om deze feature in de hand te krijgen is door deze te gaan gebruiken. Zet het aan op uw testomgeving en kijk wat er aan query’s opgeslagen wordt en wat u met de informatie kan doen.

Wilt u meer informatie of advies op maat? Laat het ons weten via info.bi.nl@axians.comWe zijn u graag van dienst!