SQL Server-diagnostiikka ja wait statistics -analyysi: Intro
Kirjoitan tämän blogisarjan siksi, että olen huomannut, ettei SQL Serverin wait statistics-analyysistä ole kirjoitettu kovinkaan montaa kattavaa blogia siitä, miten se todella toimii ja kuinka tätä tulisi analysoida. Aihe on kuitenkin tuiki tärkeä siksi, että suuri osa SQL Serverin oireista ilmenee wait statseja diagnosoimalla. Wait statsit ovat kuin lääkärin keinovalikoima analysoida kipuilevan potilaan oireita, joiden pohjalta voidaan rajata potentiaalista diagnoosia, sekä syventää ymmärrystä ongelman juurisyystä, parhaimmillaan jopa suoraan ratkaista se.
Wait statistics – mikä se on?
Kun SQL Server käy ja kukkuu, se käytännössä aina odottaa jotakin resurssia, olkoon se verkko, levyoperaatio, taululukko, prosessorisuoritus, mikä ikinä. Tämä siksi, että SQL Serverin toiminta koostuu, rajusti yksinkertaisettuna, lukuisista rinnakkaisista tehtävistä (task), joita allokoidaan prosessorin suoritussäikeille (worker thread). Nämä suoritussäikeet pyrkivät huolehtimaan siitä, että kaikki tehtävät suoritetaan tietyllä prioriteetilla. Joskus tulee ruuhkaa johonkin yksittäiseen resurssiin, kuten vaikkapa verkkoon tai tallennusjärjestelmään, ja silloin joudutaan odottelemaan tavallista kauemmin säikeiden suoritusta loppuun. Tämä on juurikin sitä statistiikkaa, jonka normaaliolojen (baseline) sekä otosten (snapshot) välinen ero kertoo siitä, mitkä arvot poikkeavat normaalista. SQL Server kerää tätä statistiikkaa systeeminäkymiinsä millisekunnin tarkkuudella. Tässähän on suora allegoria verikokeisiin! SQL Serverin wait stats -diagnostiikka eroaa kuitenkin ihmisen verenkuvan diagnosoinnista pääsääntöisesti siinä, että jokaisella SQL Serverillä on yksilöllinen baseline toisin kuin ihmisten ”ihannearvoilla” esim. hemoglobiinin suhteen. Tämä johtuu siitä, että wait statsit ovat suhteellisia: Verkko, palvelimet ja storage ovat eri, instanssikongfiguraatiot ja tietokanta-asetukset ovat eri, SQL Serverin versiot ja editiot ovat eri ja tietokantaskeemat, -sisällöt ja niiden käyttötapa optioineen ovat aivan eri, miltei aina, ympäristökohtaisesti.
Vaikka aihe on erittäin keskeinen SQL Serverin sielunelämän ymmärtämiseksi, kuitenkaan moni tietokantojen kanssa päivittäin toimiva koodari, Data Engineer tai jopa DBA ei huomiokseni tiedä riittävästi tai lainkaan tästä asiasta. Ehkä kuitenkin kannattaisi tietää. Perään heti varoituksen sana kuitenkin: Aihe on hyvin monsäikeinen ja paikoin vaikeaselkoinen, siksi aiheeseen perehtyminen vaatii hieman kärsivällisyyttä – ja aikaa. Jo yksistään siksi, että SQL Server 2022:ssa on pitkästi yli 1000 erilaista wait statsia ja lisää tulee koko ajan, jokaisessa SQL Server-versiossa (SQL Server 2017: 921 kpl, SQL Server 2022: 1335 kpl). Tosin kaikkia wait statseja sinun ei tarvitse tietää, koska kaikki niistä eivät ole merkittäviä SQL Serverin performanssidiagnostiikan kannalta – jo muutaman kymmenen keskeisimmän wait statsin ymmärtäminen auttaa sinua paljon.
SQL Server-diagnostiikka yleisesti
SQL Serverin suorituskykyä diagnosoitaessa on huomioitava hyvin laaja-alainen joukko asioita, kuten:
- Palvelin- ja virtualisointialusta
- Verkko- ja tallennusratkaisu
- Käyttöjärjestelmä asetuksineen
- SQL Server-instanssit ja tietokannat asetuksineen
- Indeksointi, statistiikat ja tietokantakyselyt
- Wait statistis
- Plan cache
- Query tracing
Wait statistics-analyysi on erinomainen tapa aloittaa SQL Server-diagnostiikka eritoten silloin, kun vallitsevasta ongelmasta ei ole mitään hajua. Toki myös järjestelmäkonfiguraatiot on syytä käydä läpi, sillä verrattain usein törmää ns. ”sellofaaniasetuksiin” sekä epäoptimaalisiin systeemikonfiguraatioihin, jotka voivat vaikuttaa järjestelmän kokonaissuorituskykyyn ratkaisevasti. Tosin nämä ovat jo sinällään monesti juurisyitä, eivät niinkään ”taudin oireita”, joita juurikin haetaan tyypillisesti wait statsien avulla.
Vaikka saatkin kattavan käsityksen kokonaisuudesta diagnosoimalla wait statseja, tässä on kuitenkin koira haudattuna: Kuten jo aiemmin mainitsin, kullakin SQL Serverillä on yksilöllinen baseline. Tällöin, kun ongelma on jo päällä, on haastavempaa nähdä, mikä on poikkeuksellista wait statseissa. Ja koska SQL Server nollaa wait statsit aina instanssin restartin yhteydessä, on viisasta käyttää eritoten liiketoimintakriittisissä tietokantapalvelimissa aina jotain ulkoista SQL Serverin monitorointi- / performanssioptimointisoftaa, kuten SQL Governoria, jolloin pystyt vertailemaan reaaliaikaista wait stats -otostasi sen aiemmin muodostuneeseen baselineen.
Wait Statisticsin historiaa – lyhyt oppimäärä
SQL Serverin ensimmäinen versio julkaistiin vuonna 1989 OS/2 -alustalle. Microsoft työskenteli yhteistyössä Sybasen kanssa vuoteen 1995 saakka aiheen tiimoilta. Kun sitten SQL Serverin 6.0 -version myötä Sybasen ja Microsoftin tiet erkanivat, wait statsit ovat olleet se mekanismi, jolla SQL Server Operating System, tuttavallisemmin SQLOS, tallentaa sisäisten prosessiensa muutoksia ajan funktiona. SQL Serverin 2005-versiosta lähtien wait statseja on voinut kysellä nykymuotoisesti Dynamic Management Viewien kautta (DMV) aiemmin käytössä olleiden DBCC-kyselyjen sijaan.
SQL Serverisi wait statseja voit ihmetellä tästä systeeminäkymästä:
select * from sys.dm_os_wait_stats;
SQLOS – ensikurkkaus konepellin alle
Ennen SQL Server 2005:ää sen alustakerros oli hyvin rajoittunut ja SQL Serverin moni operaatio suoritettiin käyttöjärjestelmätasolla. Tämä tarkoitti sitä, että SQL Serverin oli vaikeaa pysyä perässä uusissa hardwaremuutoksissa ja tekivät siitä käyttöjärjestelmäversioriippuvaisen, mikä hidasti ja rajoitti konkreettisesti uusien, tehokkaampien hardware-ominaisuuksien hyödyntämismahdollisuuksia. Tämän takia SQL Serverin versioon 2005 kehitettiin kokonaan uusi alustakerros, SQLOS, joka on pitkälle konfiguroitava. Sen tärkein tehtävä on ohittaa käyttöjärjestelmätaso ja sopeutua hyödyntämään maksimaalisesti nykyistä ja tulevaa hardware-alustaa.
Miksi SQLOS?
Windows OS käyttää ns. ”preemptive scheduling” -lähestymistapaa: Se tarkoittaa, että jokainen prosessi, joka tarvitsee prosessoriaikaa, saa sitä Windosilta viipaleen, jota kutsutaan nimellä ”quantum”. Prosessin prioriteetti taas lasketaan monimuuttujayhtälöstä, johon vaikuttavat mm. resurssien käyttö, odotettu ajoaika, aktiivisuustaso jne. Preemptive scheduling -mekanismia hyödyntämällä Windows voi keskeyttää yhden prosessin, mikäli jokin toinen prosessi korkeammalla prioriteetilla tarvitsee prosessointiaikaa. Tällainen skedulointimekanismi olisi myrkkyä SQL Serverin suorituskyvylle, koska SQL Server voisi helposti jäädä jonkin toisen, korkeamman prioriteetin sovelluksen jalkoihin. Tämän takia SQL Serverillä onkin SQLOS:ssä ikioma, non-preemptive skedulointimekanisminsa, joka varmistaa, ettei käyttöjärjestelmä pääse ”väliin sekoilemaan” silloin, kun käyttöjärjestelmää ei tarvita.
Summa summarum
Tässä blogijaksossa opimme, että wait statisticsit näyttelevät tärkeää roolia SQL Serverin performanssidiagnostiikassa. Tämän lisäksi opimme, että kaiken tämän mahdollistaa SQLOS. Seuraavaksi sinun tulisi ymmärtää, mitä ovat sellaiset veijarit kuin SQLOS:n Schedulerit, Taskit ja Worker threadit. Niiden toiminnalliset perusteet onkin syytä ymmärtää, ennen kuin voimme porautua tarkemmin wait statsien kiintoisaan sielunelämään. Siitä seuraavassa blogijaksossani!
Jani K. Savolainen
