Viime blogissa pulahdimme skeduloinnin ihmeelliseen maailmaan. Kuitenkaan emme ole vielä juurikaan käsitelleet sitä, miten wait statsit tarkalleen ottaen syntyvät SQLOS:n syövereissä. Nyt sukelletaan pohjatuntumaan ja vastataan tähän kysymykseen.

Waiter list yleisesti

Palauttakaamme mieleen kolme eri tilaa, jotka worker thread voi saada schedulerin alaisuudessa:

• SUSPENDED

• RUNNABLE

• RUNNING

Kun työsäie suorittaa tehtäväänsä, se pomppii näissä kolmessa eri tilassa tietyllä logiikalla. Jokainen näistä tiloista on oikeastaan jono (=queue). Voit ajatella kokonaisuutta tilakoneena, jossa työsäikeelle vallitsee kussakin tilassa sille dedikoitu jono, seuraavasti:

Voisi myös naiivisti ajatella työsäikeitä ravintolan asiakkaina, jotka joita ohjataan eri ajoiksi eri pöytiin istumaan ja ruokailemaan eri pituisiksi ajoiksi siten, että jokaiseen pöytään on jonkin verran jonoa.

Prosessi

Kun työsäie saa pääsyn scheduleriin, se aloittaa ns. “waiter listillä”, eli odotuslistalla. Tällöin se saa tilan “SUSPENDED”. Waiter list on käytännössä järjestämätön lista työsäikeitä SUSPENDED -tilassa, ja ne odottavat tiettyjen resurssien vapautumista systeemissä, kuten muistia, lukkoja, datasivuja jne. Kun työsäie on tällä odotuslistalla, silloin SQLOS tallentaa sen resurssityypin, jota työsäie tarvitsee jatkaakseen suoritustaan, sekä keston resurssityypille ennen kuin tämä resurssi tulee käytettäväksi ko. työsäikeelle. Lyhyesti; SQLOS tallentaa työsäikeen wait typen sekä sen resource wait timen tässä yhteydessä.

Milloin tahansa työsäie saa pääsyn resurseihin joita se tarvitsee, se siirtyy “RUNNABLE” -jonoon. Tämä FIFO-pohjainen jono on tarkoitettu kaikille työsäikeille, joilla on pääsy niiden tarvitsemiin resursseihin ja ovat valmiina ajettavaksi prosessorissa. Tätä aikaa, jonka työsäie odottaa RUNNABLE -jonossa, kutsutaan signal wait timeksi.

Tämän jälkeen, ensimmäinen työsäie RUNNABLE -jonosta siirtyy “RUNNING“ -tilaan, jossa sille allokoituu prosessoriaikaa suorittamaan työtehtäväänsä. Tätä aikaa kutsutaan nimellä CPU time. Samanaikaisesti muut työsäikeet hypähtävät pykälän ylemmäs RUNNABLE -jonossa, ja ne työsäikeet, jotka ovat saaneet pyytämänsä resurssit singahtavat waiter listiltä RUNNABLE -jonoon.

RUNNING-tilan jälkeen tilasiirtymävaihtoehtoja on kolmea erilaista:

• Työsäie tarvitsee mahdollisesti muitakin resursseja. Säie suorittaa tehtävänsä loppuun RUNNING-tilassa thread quantumin puitteissa, ja hyppää takaisin waiter listiin.

• Työsäie kuluttaa koko thread quantuminsa (aina 4 ms) ja jää kesken, jolloin sen on pakko vapauttaa suorituksensa (=yield). Tällöin työsäie palaa RUNNABLE -jonoon.

• Työsäie on tehnyt tehtävänsä kaikkien sille dedikoitujen resurssien suhteen ja vapautuu schedulerista.

Työsäikeet siis “ravaavat” tätä kolminaisuutta ees-taas, toiset pidemmän aikaa ja toiset lyhemmän aikaa, riippuen työsäikeelle annetun tehtävän luonteesta ja laajuudesta.

Tästä voidaan johtaa kaavat:

• Signal time + Resource wait time = Total wait time, eli odotusaika (ms)

• CPU time + Signal wait time + Resource wait time = Total execution time, eli suoritusaika (ms)

Suoritusaika on siis odotusaika + prosessoriaika.

Signal wait timea voi kertyä paljonkin eritoten OLTP-järjestelmissä, jossa suuri määrä säikeitä prosessoi dataa samanaikaisesti. Tämä on tärkeä mittari CPU-paineen (=CPU pressure) havaitsemiseksi SQL Serverissä: Jos signal wait time on yli 25% kokonaisodotusajasta, työsäikeet odottavat merkittävissä määrin prosessoria vapautumaan sen sijaan, että käyttäisivät niille dedikoituja resursseja.

HUOM! Työsäikeet suorittavat käytännössä aina jotain session id:tä, ja ne saavat jonkin tietyn wait typen ollessaan wait listillä, kuten vaikkapa CX_PACKET.

Wait statsien kyseleminen

Wait statseja voit kysellä dmv-näkymien lisäksi Perfmonilla sekä extended eventsien kautta. En kuitenkaan käy niitä läpi tässä blogisarjassa.

sys.dm_os_wait_stats

Tämä näkymä näyttää SQL Serverin kumulatiivisen odotusajan wait typeittäin instanssin käynnistyksen tai uudelleenkäynnistyksen alkuajasta lähtien. Voit nollata näkymän lausahduksella:

DBCC SQLPERF(‘sys.dm_os_wait_stats’, CLEAR)

Tällä ei sinällään ole vaikutusta SQL Serverin suorituskykyyn, mutta wait statistiikan kerääminen alkaa nollasta uudelleen ilman, että sinun tarvitsee restartata itse SQL Serveriä.

Kentät selityksineen:

• wait_type -kenttä palauttaa spesifin wait typen tiedot. Tämä on siis näkymän yksilöivä kenttä, sillä tässä näkymässä esitetään aina kaikki wait typet SQL Server-versiosta riippuen.

• waiting_tasks_count kertoo, kuinka monta kertaa työsäikeen tuli odottaa tiettyä wait typeä.

• wait_time_ms palauttaa kokonais wait timen millisekunneissa.

• max_wait_time_ms palauttaa suurimman wait timen, jonka työsäie joutui odottamaan tiettyä wait typeä.

• signal_wait_time_ms kertoo, kuinka kauan työsäie vietti aikaa RUNNABLE -tilassa.

sys.dm_exec_session_wait_stats

Tämä näkymä on ollut käytössä SQL Server 2016 SP1:stä lähtien, ja listaa wait statsit sessiotasolla. Näkymä toimii samalla logiikalla kuin sys.dm_os_wait_stats, joten siihen kumuloituvat kaikki session aikana suoritettujen requestien waitit kenttien ollessa Samat kuin edellisessä näkymässä. Se on siis eräänlainen filtteröity näkymä. Myöskin on hyvä ymmärtää, että vanhat session_id:t kierrätetään, eli sama session_id voi näkyä uudelleen wait statseissa sen jälkeen, kun vanha sessio on jo suljettu – nämä ovat kuitenkin täysin eri asia. Edelleen; mikäli sama session_id uusiokäytetään, se nollaa joka tapauksessa ko. session_id:hen liittyvät wait statsit, kuinkas muutenkaan. Tämä näkymä sopii hyvin vikaselvitystilanteisiin, joissa haluat uudelleengeneroida havaitun session kyselyt ja tarkkailla, kuinka ne kuluttavat wait statseja.

sys.dm_os_waiting_tasks

Tämä näkymä listaa vain sellaiset taskit, joilla on työsäikeitä joko odottamassa resursseja waiter listillä tai RUNNABLE -jonossa.

Kentät selityksineen:

• waiting_task_address näyttää odottavan taskin muistiosoitteen.

• session_id kertoo taskin session id:n.

• exec_context_id -kenttä kertoo, ajetaanko taskia parallelisoituna vai ei. Oletusarvo on NULL ja parallellisoidun taskin arvo on 0.

• wait_duration_ms kertoo taskin kokonaisodotusajan (=resource wait time + signal wait time).

• wait_type kertoo taskin silloisen wait typen.

• resource_address palauttaa sen resurssin muistiosoitteen, jota odotetaan. Voi myös Saada arvon NULL, koska kaikki wait typet eivät lokita resurssin muistiosoitetta.

• blocking_task_address on tärkeä, koska se palauttaa sen taskin muistiosoitteen, joka blokkaa parhaillaan odottavaa taskia. Oletusarvona NULL (ei blokkausta).

• blocking_session_id palauttaa ylätasolla sen session id:n, joka blokkaa odottavaa taskia, muutoin se on NULL, jolloin se ei joko blokkaa tai sitten sessiota ei syystä tai toisesta saada kiinni.

• Blocking_exec_context_id vielä yksi kenttä, joka liittyy blokkaukseen. Se palauttaa execution contextin id:n. Se on muutoin NULL, paitsi jos taski on parallelisoitu ja yksi säikeistä on vastuussa blokkauksesta – tällöin blocking_exec_context_id määrittää, mikä säie on vastuussa blokeerauksesta.

• resource_description kertoo lisätietoja resurssista, jota taski odottaa.

sys.dm_exec_requests

Tämä näkymä palauttaa kaikki ne requestit sessioittain, joita SQL Server parhaillaan suorittaa.

Keskeisimmät kentät selityksineen:

• session_id kertoo requestin session id:n.

• start_time näyttää requestin alkupäivän ja kellonajan.

• command kertoo sen, millaista toimintoa request on suorittamassa, kuten SELECT, INSERT, UPDATE tai DELETE jne.

• sql_handle antaa requestin suorittaman SQL textin hash-koodin. Kenttä on useimmiten NULL, ellei kyseessä ole tavanomainen käyttäjän suorittama T-SQL -kysely. HUOM! Tätä sql handlea voidaan käyttää sys.dm_exec_sql_text -DMF-funktion inputtina, jolloin saadaan kyseisen requestin suorittama kysely.

• plan_handle palauttaa execution planin hash-koodin. HUOM! Tätä plan handlea voidaan käyttää sys.dm_exec_query_plan -DMF-funktion inputtina, jolloin saadaan kyseisen requestin suorittaman kyselyn execution plan.

• wait_type -kenttä palauttaa spesifin wait typen tiedot, mikäli request on joko “SUSPENDED” tai “RUNNABLE”. Muutoin arvo on NULL (=RUNNING).

• last_wait_type palauttaa viimeisimmän wait typen, jota request on joutunut odottamaan suorituksen aikana.

• total_elapsed_time palauttaa requestin kokonaisuoritussajan millisekunteina sen alkuajasta (=start_time) lähtien.

Yhteenveto

Tämä blogi teki hieman syvemmän safarin wait statsien kolmikantaviidakkoon. Sait myös perusteet siitä, mitä näkymiä kannattaa kysellä wait statseihin liittyen. Seuraavassa blogissani perehdymme siihen, kuinka wait statseja analysoidaan SQL Serverin query planeissa.