Rollback

(apró technikai malőr miatt a táblázat pillanatnyilag bebújik a sidebar mögé, bocs, legjobb embereink dolgoznak a hiba elhárításán)

Az egyik nagy jósága a SQL 2005+ szervernek, hogy megmutatja az indexek ki(nem)használtságát, illetve elmondja, hogy milyen jelenleg nemlétező index mekkora hasznot hozna. Ezeket természetesen DMV-ken keresztül teszi meg velünk.

Az indexeink kihasználtságát a sys.dm_db_index_usage_stats DMV mutatja meg, egy kis minta így néz ki belőle:

SELECT top 3 * FROM sys.dm_db_index_usage_stats
WHERE object_id > 255

Az erősen irányított válasz pedig

database_id	object_id	index_id	user_seeks	user_scans	user_lookups	user_updates	last_user_seek	last_user_scan	last_user_lookup	last_user_update	system_seeks	system_scans	system_lookups	system_updates	last_system_seek	last_system_scan	last_system_lookup	last_system_update
5	709577566	2	3	3456	0	2154356	NULL	2009.10.18 15:46	NULL	2009-10-18 16:00:37.097	0	34	0	0	NULL	2009-10-18 01:00:23.000	NULL	NULL
5	709577566	1	2154356	0	2154356	2154356	2009-10-18 16:00:37.097	NULL	2009-10-18 16:00:37.097	2009-10-18 16:00:37.097	0	34	0	0	NULL	2009-10-18 01:00:23.000	NULL	NULL
5	709577566	3	0	0	0	2154356	NULL	NULL	NULL	2009-10-18 16:00:37.097	0	34	0	0	NULL	2009-10-18 01:00:23.000	NULL	NULL

Ebből a következőket kell figyelnünk: a db-object-index id-k magukért beszélnek, abból tudjuk, hogy minek is nézzük az adatait. A user seek/scan/lookup és a hozzájuk tartozó last… oszlopok talán a legfontosabbak, ők mutatják meg a tényleges indexhasználatot. Az user_update oszlop érdekes, az index ugyanis akkor update-elődik, ha a tartalmazott adat változik. Ez az úgynevezett indexkarbantartás, ami kissé drága, ezért nem szeretjük a felesleges indexeket. Merő véletlenségből a fenti minta pont tartalmaz egy ilyen indexet, a 3-as számút (egy tábla három indexének az értékeit választottam ki). Látszik, hogy semmi másra nem használja a szerver az indexet, csak karbantartja, frissítgeti. Ez egy tipikus jó jelölt arra, hogy letörölje az ember fia egy vidám hétfő reggelen, ha elég nagy minta alapján még mindig nulla a három mező összege. Volt szerencsém találkozni olyan indexszel, amit 1.1 milliárdszor kellett update-elnie az engine-nek, és soha nem használta. Lehet azt mondani, hogy nem kér enni, de hát szemmel láthatóan ez nem igaz.

Éles szemű egyének észrevehetik, hogy azért a system_scan oszlop értéke egyáltalán nem nulla. Ez anna kköszönhető, hogy az indexstatisztikák frissítéséhez az SQL szerver scannel. Úgyhogy ezt nem kell feltétlenül figyelembe venni.

Mire jó még ez a használati statisztika? Láthatjuk, hogy user_lookup mindig csak az index_id = 1 indexeken történik, azaz a clustered indexen. Természetes, hiszen a bookmark/key lookup itt szokott történni. Lehet, hogy van rá ellenpélda, de azt most kihagynám. És most keressük meg a legnagyobb user_scan értékű indexeket és a hozzájuk tartozó clustered indexeket: ha nagyon magas a clustered indexen a user_lookup érték is és kicsi a scan, akkor találtunk egy jó ellenjelöltet clustered indexre.

És megint, csak egyetlen DMV a sokból, és milyen sok hasznos dolgot árul el… Hát nem csodálatos? (nem, a csodálatos az az, hogy három tesztgépre sikerült három különböző SQL verziót/editiont telepítenem, anélkül, hogy észrevettem volna addig, amíg el nem frakkolt a teszt…)

Visszatérve a címre:

select * from sys.dm_db_index_usage_stats
where object_id > 255
and user_seeks + user_scans + user_lookups = 0
order by database_id, object_id

Ők azok az indexek, akiket a kutya sem használt – az SQL Server legutóbbi indulása óta. Ez a DMV ugyanis minden szerver restartkor kiürül és újrakezdi a feltöltését a szerver. Tehát ha tegnap óta fut a gép, ne használjuk nagytakarításra az outputot. Tanácsos elmenteni a kimenetet – én mindennap elmentem egy külön táblába, és ha nem indult újra előző nap óta a szerver, akkor az előző napit meeg kitörlöm. Így csak legfeljebb egy napi statisztikát veszíthetek, de megvan hónapokra visszamenőleg minden.

Egyelőre még mindig nem jutottam el oda, hogy egy normális szakmai cikket írjak, mert a *** automatizált szerver menedzsmentet csinálom (kárpótlásul a végén majd néhányat felteszek a scriptekből :), és úgy látszik, hogy nem tudok beszélni/írni róla meg csinálni is.

Viszont van két csodás hír, amivel nem is olyan régen találkoztam: az egyik az, hogy van új Books Online az SQL 2008-hoz (és csak ismételni tudom magam, használjátok mindig a legfrissebb BOL-t), ha minden igaz, az SP1 CU2 verzióhoz van update-elve, letölthető innen.

Az SQL 2008 SP1 CU2 pedig az a verzió, amiről már korábban írtam, hogy nagyon várom, mondjuk én főleg SQL 2005-höz. Ugyanis itt válik lehetővé – egy külön megadott trace flaggel – az SQL szerver memóriájának a fizikai memóriában tartása, a fantasztikus lock pages in memory. Az SQL Server Support Team blogjában olvasható egy szép cikk erről. Az SQL 2005 SP3 CU4 elméletileg egy hónap múlva itt lesz, tekintve, hogy eddig tartották a határidőt, én nagyon optimista vagyok.

A memória, a diszk és a tranzakció

Ez minden alkalommal tanulságos (és hosszú) beszélgetésekhez vezetett. Van egy nyitott tranzakcióm, ami már csinált egy csomó módosítást. Hol tartja a megváltozott, illetve a régi adatot az SQL szerver, iletve mi szerepel a tranzakciós naplóban? Bónusz: mi van a diszkre kiírva, és mi van csupán a memóriában? A kérdés megválaszolása némi adatbázis ismerettel és nagy adag józan paraszti ésszel (vagy ezek más jellegű lineáris kombinációjával) megválaszolható.
Induljunk az elejétől: hol lehet a megváltozott adat? Vagy a diszken vagy memóriában, vagy egyéb helyen. Nézzük a diszket: Vajon kiír-e az SQL minden egyes változást külön-külön diszkre? (Lehet-e okos az SQL szerver, és megvárhatja-e a módosítások végét? Nyilvánvalóan nem, hiszen ha olyanom van, öt napig futtatom a tranzakciómat.) Milyen érzést okozna ez abban, aki mondjuk másfélmillió rekordot módosít…? Mostanra eljutottunk oda, hogy nem ír ki diszkre mindent. Esetleg a memória? Hát, a temptáblákhoz hasonló ellenpélda itt is ül: végre lehet hajtani egy akkora tranzakciót, amelyik több adatot módosít, mint amennyit fejben (memóriában) tudna tartani az adatbáziskezelő. Marad az egyéb, és a kis adatbáziskezelői ismeret: a memóriában tartott módosult lapok esetében az SQL szervert olyan nagyon nem érdekli, hogy a módosító tranzakció kommitált lett-e vagy még fut. Ha a lap módosult (azaz dirty page lett belőle, nem egyezik meg a memóriában lévő másolat a diszken lévő másolattal), a checkpoint processz ki fogja írni a diszkre. Na ja, de mi van rollbacknél? Hát, egyrészt a tranzakciók elsöprő többsége committal zárul, tehát a hurráoptimizmusa az SQL szervernek elég indokolt a hatékonyság nézőpontjából. Amúgy pedig rollbacknél egyszerűen visszaírja a régi adatokat a lapokra, amik ettől megint dirty page-ek lesznek, és a következő checkpoint majd kiírja a diszkre megint.
Egy kérdést megválaszoltunk, itt az új: honnan írja vissza az eredeti adatot rollbacknél a szerver? Hát a tranzakciós naplóból! (Kis gondolkozással, illetve egy áramszünet utáni konzisztens adatbázis elképzelésével rájövünk, hogy a logot leginkább diszkre írja az SQL szerver.) És mi kerül a logba? Az, ami a rollbackhez illetve az esetleges újra-végrehajtáshoz kell (képzeljük el, hogy a tranzakció commitálódott, de a módosult rekordok még dirty page-en voltak, amikor a szomszédos építkezésen elkaparták a villamos kábelt a földben – ezt kiválóan fel tudja dolgozni az SQL Server – meg minden civilizált RDBMS). (És most nagyvonalúan ignorálom a bulk műveletek naplózásának kérdését.) Tehát kell minden módosult rekordnak az azonosítója (a primary key megteszi, ha van) és a változott oszlopok régi és új értékei. Azaz a naplóba nem maguk az utasítások kerülnek rögzítésre, hanem a hatásuk.
Mostanra már azt is tudjuk, hogy mikor mi van a diszken és a memóriában. Memóriában az az adat van, amit legutóbb használt az SQL, a diszken meg a többi. Nem volt ebben semmi különös, próbáltam kicsit illusztrálni, hogy hogyan lehet mély ismeretek nélkül mély ismereteket szerezni :) – vagy legalábbis megfejteni működési elveket: állítsunk fel modelleket, és teszteljük őket általános és szélsőséges esetekre.

Pár hónapja fejeztük be új DBA-nk beszerzését (welcome Andor), és az interjúk embert próbáló órái alatt napokat öregedtünk, és sok tévhittel találkoztunk. Nekem kicsit érdekes élmény volt, hogy emberek egymástól függetlenül ugyanazt gondolják rosszul. (Az is érdekes, sőt megdöbbentő élmény volt, hogy sokan dolgoznak informatikusként, tekiként úgy, hogy nem tudják, hogyan is működik a TCP/IP protokoll – subnet, gateway, router, stb.). Arra gondoltam végül, hogy megosztom élményeimet a nagyérdeművel. Hát tessék:

I. A temptáblák

Sokan gondolják, hogy a temptáblák a memóriában laknak. Ez nem igaz (sőt, a tábla típusú változókra sem igaz). Egyszerű kísérlettel igazolható: készíts egy akkora temptáblát, mint az SQL szervernek adott összes memória. Ez sikerülni fog, és az SQL szerver – lehet, hogy kicsit lassan, de – működni fog, amit memória nélkül pedig nem tudna megtenni. Még egy gondolat a temptáblákról: kétféle van belőlük: lokális és globális. A lokális így készül:

create table #a (int a)

Ezt az adott processz látja csak, senki másnak nem lesz hozzáférése, és akkor szűnik meg, amikor a készítő processz kilép. Ezzel szemben a globális temptáblaát minden processz eléri. Az állítás bizonyítása megint egyszerű: Az előző sorhoz csapjunk hozzá még egyet:

create table ##a (int a)

Majd nyissunk egy másik query window-t (aminek más lesz a spidje), és futtassuk le az alábbit:

select a from #a
select a from ##a

A különbség érezhető lesz: az első elbukik, a második nem. A globális tábla élettartama a lokálishoz hasonlóan nem nyúlhat túl az őt létrehozó session életén, legkésőbb a kapcsolat megszakításakor törlődik.