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.

Örömömre szolgál elmondani, hogy két hónapon belül meglesz a tranzakcionális replikáció további boncolása, melyet mostanában (2008-ban vagy 2009-ben) ígértem meg. Cserébe egy kicsit többet fogok mondani róla, mint terveztem, mivel éppen abból készülök előadni az SQLU Summit 2011-en. (Kis reklám: ez egy kiváló kis 2-3 napos konferencia Budapesten, olyan emberek, mint Itzik Ben-Gan meg Dejan Sarka adnak elő – na ŐK tényleg értenek a SQL Serverhez. Ráadásul nem is drága, olyan 200K HUF körül van, és aki hétfőig benevez, az megkapja 650 EUR-ért. Na jó, ez már durván reklám, de sebaj :).

Mi lesz az extra a tranzakcionális replikáció elemzésében? Hát, például az olyanok, hogy central subscriber berhelése, adat inkonzisztencia kezelése is leíródik, amiket eredetileg nem terveztem. A central subscriber kevés embert hoz lázba, mert homályos. Arról van pedig csak szó, hogy van egy szerver, ami több hasonló és/vagy kapcsolódó adatbázis publikációira iratkozott fel, és összefésüli az adatot. Például több régióba telepített ugyanolyan alkalmazások adatbázisait rántja össze. Ebben az első kihívás az inicializálás: a második subscription már békén kell, hogy hagyja a létező adatokat. És az igazi öröm az, amikor ez eltörik, és újra kell gyártani. Ami egyébként nem nehéz, ne hintsük itt a port :) Majd részletezem, ahogy haladok.

Viszont azt előre megmondom, hogy a konferenciáig az Availability Groupokról egy betűt nem fogok írni. Inkább gyertek el ;) Amúgy tényleg.

Előzőleg elmondtam ,hogy milyen fontos a DBCC CHECKDB futtatása, és erre gondolom mindenki kedvet kapott arra, hogy megnézze, mikor futott ez le az ő kis adatbázisain utoljára sikeresen. Felütötte mindenki a BOL-t, ééés… nem volt benne. Ja, ez is egyike azon undocumented apróságoknak:

DBCC DBINFO ('master') WITH TABLERESULTS;

Ez visszaad egy recordsetet, és abban select * where field = ‘dbi_dbccLastKnownGood’. Hát ennyi.

Nemrégiben volt szerencsém felfrissíteni DR, vagyis disaster recovery (=füstölgő romokból hozz ki valamit) emlékeimet. Egy kb 50GB-os adatbázisunk összehalta magát. Már írt pár hibát a logba, hogy nem tud olvasni egy page-et, aztán emiatt a backupok sem sikerültek, úgyhogy jött a szerver restart. A szerver pedig aszonta, hogy az adatbázisunk suspect módba került. Ez azt jelenti, hogy nem lehet belépni se, megnézni se, semmise. Mit lehet tenni ilyenkor?

Az első tennivaló, hogy az összes létező tamtamot elkezdjük verni, és lepereg előttünk életünk filmje. Mert ha ez az egy adatbázis megsérült, megsérülhet más is – akár hardver, akár szoftver, akár csillagállás, igaz a többiekre is. Ha van mirror vagy log shipping secondary adatbázis, akkor aktiváljuk, ellenőrizzük, és nézzük meg, hogy ott vannak-e az adatok. Ha nem, akkor vakarjuk meg a fejünket, és olvassunk tovább.

Ezután igyunk egy kávét/kólát/ánizsteát, dohányosok szívjanak el egy cigit, és gondolkodjunk el azon, hogy hogy lehet a legjobban kimászni a helyzetből: itt jön a legutóbbi backupok kérdése,valamint a legutóbbi sikeres DBCC CHECKDB ideje, hogy lehetőleg ne állítsuk vissza mentésből a sérült adatbázist. A korrupt adatbázisra a hozzáértők egybehangzó tanácsa: állítsd vissza mentésből. És ez így igaz. Ez az egyetlen tuti módszer van, ha nincs egy működő tartalékod. Ha meg tudná csinálni tutibiztosan a SQL Server, megcsinálná magától, és nem lenne sérült az adatbázis.

Persze lehet, hogy a backup nem épp a legfrissebb (olyat nem írok, hogy nincs), és mégis kell valami adat a beteg db-ből. Ekkor nem ússzuk meg a hősi munkát. Az én általános DR tervem a következő:

0. Ellenőrzöm, hogy van-e elég szabad helyem ahhoz, amit fogok csinálni: adatfájloknak, tranlognak (jó nagy lesz, amit a #2 csinál), backupnak.
1. Elkezdem a restore-t, amilyen gyorsan csak tudom, egy új adatbázisba.
2. A suspect adatbázist felhozom emergency módba (ALTER DATABASE Titanic SET EMERGENCY), és elkezdem kitolni belőle amit csak lehet: a teljes sémát megpróbálom kiscriptelni egy új adatbázisba (indexekkel, foreign keyekkel, default értékekkel, de legfőképpen identity oszlopokkal), aztán megpróbálom az adatokat is átpumpálni bele. Itt jön az izgalom: ha a db korrupt, akkor valahol valami nem fog sikerülni. Jó esetben hibát is dob a SQL. De nekünk itt pl. nem dobott hibát, csak csendben lenyelte az utolsó három napnyi adatot. Tehát mindenképpen kell a humán adatellenőrzés.
3. Ha a #2 lement, rátolok egy DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS-t a db-re, hátha többet vissza tud utána adni, mint a #2-ben. Persze lehet, hogy kevesebbet.
4. Ha megvan a működő adatbázisom valahogy a háromból, akkor leellenőrzöm logikailag az adatokat, tolok rá egy DBCC-t, egy backupot, beállítom a backup jobot hozzá, és ráengedem az alkalmazást.

Miért szivatom magamat mind a hárommal egyszerre? Mert a backup-restore jól hangzik, de ha kiderül, hogy baja van már a backupnak is, akkor az az elvesztett idő később fájni fog. Plusz ha nem csináltam az elmúlt pár percben tranlog backupot, akkor még adatot is veszítek tutira. Ha ki tudom szedni a legutolsó adatokat a sérült DB-ből, az lefedheti az így keletkezett hézagot. A #2 egy logikus lépés, a #3 szintén, és mint jeleztem, más adatokra számíthatunk. Pl. ha megsérült egy tábla közepén a clustered index, amit a repair ki tud vágni, akkor egy 10000 soros táblából #2 visszaadhatja az 1-2450 sorokat, #3 pedig az 1-2100 és 2900-10000 sorokat. Igen, hiányzik a közepe. Na és? Ha annyira fontos, akkor tessék mirrorozni.

Itt jegyzem meg, hogy szerintem az egyetlen tuti biztonságos tartalékképzési módszer a High Safety, azaz szinkron mirroring. Itt ugyanis nem a diszkről olvassa vissza az SQL Server, hogy mit is csinált az aktív adatbázis, hanem a memóriából kitolja mindkét helyre. Ez pl. egy esetlegesen felbukkanó hibánál sokat számít, például ha csak időnként ront el valamit a diszk kontroller, és ráadásul egy ideig még érvényes adat is marad a tranzakciós logba került szemét. Na nem mintha láttam volna már ilyet, csak úgy mondom. Cserébe a szinkorn mirroringnak van hatása a teljesítményre is, konkrétan csökkenti, mivel minden commit előtt van egy rövid megbeszélés a principal (aktív) és mirror (passzív) adatbázis szerverek között.

Végül hadd vonjam le a tanulságokat:

• Legyenek backupjaid, ellenőizd, hogy készülnek-e, teszteld őket időnként.
• Futtass rendszeresen DBCC CHECKDB-t. Ideális esetben legalább minden full backup előtt, így lényegesen nagyobb esélyed van arra, hogy a backupod jó lesz.
• Legyen DR terved, és próbáld is ki, amennyire csak tudod.
• Legyen tartalékod a fontosabb adatbázisaidból. Fantasztikus tud lenni 5 perc alatt megoldani egy DR-nek induló szituációt.

Általános jelenség scriptelés közben, hogy valahol felbukkan egy subquery, pl. a “minden számla, ahol a vevő budapesti” típusú kérdésre. Ez lehet join vagy subquery, általában mindegy, a query optimizer ugyanazt a végrehajtási tervet produkálja belőlük.

-- a subquery
SELECT * FROM Szamlak
WHERE
   vevoid in
   (SELECT vevoid FROM Vevo Where varos = 'Budapest')
-- a join
SELECT s.* FROM Szamlak s
   JOIN Vevo v
   ON v.vevoid = s.vevoid
   WHERE v.varos = 'Budapest'

És vannak esetek, amikor még ez sem elég, azokra a nehéz napokra ott van a CTE, vagyis Common Table Expression. Mielőtt belemennék, hogy mi is ez, nézzük meg a fenti példát CTE-vel, ami teljesen értelmetlen, de legalább a szintaktikát felismerjük:

WITH BpVevok (vevoid)
AS (
   SELECT vevoid FROM Vevo Where varos = 'Budapest'
)
SELECT s.* FROM Szamlak s
   JOIN BpVevok
   ON BpVevok.vevoid = s.vevoid

Semmivel nem tűnik egyszerűbbnek, nem is gyorsabb, akkor meg mire jó? A CTE nem más, mint tuljadonképpen egy ideiglenes nézet, egy olyan view, amit a query elején a WITH clause-zal definiálunk, aztán a queryben meg felhasználjuk, akár többször is. Ez az egyik előnye, hogy újrahasznosítható, és akkor viszont már egyszerűség meg gyorsaság is felbukkan. Viszont amiért én pénteken hozzányúltam, az valami egészen más tulajdonsága: szereti a rekurziót is, és lehet “rekurzív nézeteket” definiálni benne. Ez nagyon jól jön például hierarchikus adatoknál, amikor van egy rekordazonosító meg egy szülőazonosító oszlop. De nézzük inkább, hogy én mire használtam, az talán jobb, mintha értekeznék.

Adott egy tábla, amiben fájlszerverről szóló adatok vannak, többek között: objektumazonosító; fájl, illetve könyvtárnév; projekt; lejárati idő; plusz a szülő könyvtár azonosítója. minta:
id|fajlnev|projektid|lejarat|szuloid
12234|feladatok.doc|4334|2011-12-31|3654
3654|output|4334|2012-01-31|1111
1111|teszt|4334|2020-01-20|NULL

A teljes feladathoz pedig az egyik részfeladat az, hogy minden fájlnak kell a teljes elérési útvonala is, amit subquerykben/joinokban kell majd használni, tehát pl.
12234|feladatok.doc|\teszt\output\feladatok.doc|4334|2011-12-31|3654

Akár a gyökérelem azonosítóját is hozzáadhatnánk, nem bonyolítjuk a helyzetet. Transact-SQL-ben ez egy érdekes feladat lenne CTE nélkül, írhatnék rekurzív függvényt például, de a CTE sokkal cukibb:

WITH FileCTE (id, fajlnev,eleresiut,projektid,lejarat,szuloid)
AS (
  SELECT id, fajlnev, '\' + fajlnev AS eleresiut, projektid, lejarat, szuloid
  FROM filetabla
   WHERE szuloid IS NULL
   UNION ALL
   SELECT f.id, f.fajlnev, c.eleresiut + '\' + f.fajlnev AS eleresiut, f.projektid, f.lejarat, f.szuloid
   FROM filetabla f
     JOIN FileCTE c
     ON c.id = f.szuloid
)
-- itt jön az igazi kelérdezés
SELECT f.id, f.projektid
FROM filetabla f
JOIN FileCTE c
ON f.id = c.id
-- join meg egy csomo minden...
WHERE c.eleresiut LIKE '%penzugyi terv%xls'

Hát erre jó a CTE. (Meg arra, hogy maga a kveri már átlátható, mert a szörnyű subquery-k ki vannak emelve az elejére… :)