Rollback

A count() függvénnyel kapcsolatosan van pár dolog, ami itt ugrál a fejemben már egy ideje, most kiborítom őket.

Először is: van egy komoly vallási kérdés a témában, miszerint vajon a count(1) vagy a count(*) a jobb. Ezt mind Oracle, mind MSSQL platformon keményen nyomják emberek. Legyünk egyszerűek, nézzünk meg egy végrehajtási tervet, és lássuk a valót: pontosan ugyanazt csinálja a szerver mindkét esetben. Lelke mélyén ő tudja, hogy pontosan ugyanaz a kérdés. Na de honnan van a válasz? Mivel azt kérdezzük ilyenkor, hogy hány sor is van a táblában, ez egy index scan lesz. És mivel az SQL Server okos, ezért a legkisebb lapszámú indexet fogja felolvasni. Nézzétek meg sok indexet hordozó táblákon, hogy milyen lehetetlen indexet bök ki. Általában azokat, amiknek nagy a NULL aránya, mivel azok kicsik. Persze filtered indexre nem ugrik.

A másik egy személyes élmény. Azt vettem észre egy szép napon, hogy a szerverek nagyon sok időt töltenek azzal, hogy select count(*) from sysfiles vagy select count(*) from sysobjects lekérdezéseket futtatnak. Hamar rájöttem, hogy ez egy marék Java alkalmazás műve, melyek sok kicsi lekérdezést indítottak, melyek előtt a JDBC driver futtatott egy health check kverit, hogy tudja, hogy jó az adatbáziskapcsolat, amit a poolból vett ki. Történetesen a health check drágább volt, mint sokszor a lekérdezés maga. A masteren a resourcedb miatt még join is van az execution planben. Mi egyszerűen átálltunk az alulmúlhatatlan select 1-re, de ha valakinek van hasonló gondja, inkább azt ajánlom, hogy válasszon egy táblát, és abból kérje le az első sort. Nekem ez sajnos a sok kis adatbázis miatt nem menő, mert senki nem fog mindegyikben táblát keresni, de másnak bejöhet. Nálam ez hatszoros telejsítménykülönbséget jelentett: 0.003 vs 0.018 total subtree costok jelentek meg.

Az egyik újdonság a Denali-ban a szekvencia. Eddig ilyen volt Oracle-ben, PostgreSQL-ben, most már van MSSQL-ben is. Eddig MSSQL-ben volt IDENTITY, de az egy kicsit más. Az IDENTITY egy adott tábla sémájához kötött autoincrementes oszlop, megadott kezdőértékkel és növekedési értékkel. Értéket neki nem adunk, hanem ő magától kitölti, kivéve ha azt mondjuk, hogy SET IDENTITY_INSERT tabla ON. Az általunk utoljára felhasznált értéket a @@IDENTITY változó illetve SCOPE_IDENTITY() függvény megmondja, ha szeretnénk. Ez jó. Viszont olyan mértékben a sémához van kötve, hogy az identity tulajdonság nem változtatható. Nem lehet se rátenni egy meglévő oszlopra, se levenni róla. Ez igen fájdalmas tud lenni időnként, és én még nem tudtam feldolgozni, hogy ez miért ilyen béna.
Continue reading ‘Denali I. – SEQUENCE vs IDENTITY’ »

(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.