Soci blog

Nappal DP tanfolyamot tartottam, éjszaka meg adatbázist optimalizálok egy ügyfelemnek, így most csak egy rövid, de érdekes cikket linkelek be.

Eléggé elfeledett téma ez az SQL Serverben, pedig önmagában ez az új szolgáltatás eladta volna az SQL 2005-öt.
Cikk.

A Connection Poolról még pár gondolat. Jó, hogy van ez a pool, meg gyorsít is, örülünk neki, főleg webalkalmazásokban. Olyan appokban viszont, ahol állandóan futnak a dolgaink, mint egy asztali alkalmazás esetén, nem biztos, hogy érdemes mohón nyitni-zárni a kapcsolatot.
A tőzsdei kereskedési algoritmusaim backtestje során sok százezer paraméterkombinációt néz végig a gép, próbálja meghatározni a legvalószínűbb nyerési esélyűt vagy legnagyobb profit/szórással rendelkezőt (ennél jóval bonyolultabb a dolog, de ez most nem tőzsdés bejegyzés lesz).
A DAL-t úgy írtam meg ahogy webalkalmazásokban megszoktam, így minden egyes trade mentésénél nyitottam-zártam a connectiont. Profilerrel megnézve kiderült, hogy a pool ellenére is a futási idő harmada az open/close-zal ment el. Emiatt készítettem kétféle SqlHelper osztályt, az egyik állandóan nyitott kapcsolattal működik, a másik az egyéb helyekre nyit-zár mind eddig.
Összegezve, a connection pool kiváló dolog pillanatokra futó alkalmazásokhoz, mint a webalkalmazások, de nem érdemes erőltetni, ha több százezerszer kell nyitni-zárni a kapcsolatot.

(Sose tudom, kell pont ilyenkor a dátumba?)
Előadok a fenti konfon Reporting Services témában. Mivel az SQL Server BI része (OLAP, SSIS, SSRS) sokkal kevésbé ismertek mint a relációs motor, ezért ezen az Informatika Tisztán nem a technet vagy msdn konferenciákon megszokott itt a legújabb cucc, nézd milyen kúl megközelítés lesz, hanem a kezdő lökést szeretnénk megadni az érdeklődőknek.

Érdekes nekem ez a konferencia?

Ha már régóta érzed, hogy jó lenne érteni az Analysis Serviceshez (OLAP izék), vagy nem mertél még belefogni, mert olyan megfoghatatlan katyvasznak tűnik.
Ha tudod, hogy az Integration Services a DTS utódja, ahhoz még értettél (vagy azt se tudod mi volt az), de ezt még nem nézted meg, mert azt mondták az annyira más, hogy meg se merted nézni.
Ha hallottál róla, hogy nem menő már a Crystal Reports, vagy azt se tudod hogyan kellene belefogni az első riportba (amit egyébként szó szerint 5 perc alatt össze tudsz dobni), és érdekel a Reporting Services.
Ha hallottál valami adatbányászatról, de nem tudod, hogy melyik földkéreg tartalmaz adatokat, és érdekel, hogy ez tényleg valami használható technológia, vagy csak ráérős Microsoft Research kutatók gumicicája?

Szóval alapozást ígérünk, földközelbe hozzuk ezeket a kevésbé szem előtt lévő technológiákat. Gyere, várunk.

Szeretjük a connection poolt, de nem mindig.

Tudjuk, hogy manapság nem illik sokáig nyitva tartani az adatbázis kapcsolatot, hanem bemegyünk, kihozzuk a szükséges adatokat, majd lezárjuk a kapcsolatot. Régen (n > 10 év) ez nem volt okos dolog, mert lassú volt a belépés-kilépés. Ezért találták ki a Connection Poolt, amit OLEDB-ben még Session Poolnak hívtak. Egykutya.
Ez arról szól, hogy a kliens oldali adatelérő komponens, pl. az ADO.NET SqlClient providere (System.Data.SqlClient névtér és kölkei) újrahasznosítja a lezárt kapcsolatot. Azaz mi SqlConnection.Close-t mondunk, ő viszont a háttérben nem zárja le a kapcsolatot az adatbázis felé. Ha ezek után újra meg akarjuk nyitni egy kapcsolatot az adatbázis felé, nem hoz létre új kapcsolatot a provider, hanem visszaad egy használtat a poolból. Miért jó ez? Nyilván egy tényleges kapcsolat kiépítése igen költséges, TCP csatorna vagy Named Pipe session felépítése, autentikáció, ecetera. Ezt nagyon sokszor megússzuk, hála a poolnak. A pool 4-8 perc közötti véletlenszerű időközönként azért lezárogatja a használt kapcsolatokat, ott ne zápuljanak.
Nyilvánvaló, hogy felhasználónként egyedi poolok vannak, így egy sysadmin által eldobott kapcsolatot nem adja oda nekem, lópicinek a provider, ez csúnya luk lenne.
Lehet azért így is aljaskodni az új bérlőkkel. Mi van, ha becsatlakozok, kiadok egy use másik adatbázist, majd röhögve lezárom a kapcsolatot? És ha egy-ket set opciót átírok, pl. SET LANGUAGE urdi? Mit lát a következő hívó, aki megkapja a használt kapcsolatot? Hát amit beállítottunk az előző lépésben. Azért ez durván hangzik, ugye? Szerencsére a helyzet az, hogy alapban tiszta környezetet kapunk, köszönhetően annak, hogy a provider pool manager-e végrehajt egy sp_reset_connection hívást mielőtt visszaadná nekünk a használt kapcsolatot. Hogy ez pontosan mit csinál, azt egyszer már leírtam, tessék megnézni a listát.
Azaz bár nem kell minden egyes kéréshez új kapcsolatot megnyitni, azért egy sp hívás csak történik pluszban a háttérben. SQL Profilerben látszik, hogy ez nagyon gyors, de azért ez mégis csak egy hálózati körülfordulás.
És most jön a trükk. Ha teljesen biztosak vagyunk benne, hogy nem tolunk ki a következő hívóval aki megkapja a használt kapcsolatunkat, akkor kikapcsolhatjuk a takarítást. A connection stringbe ezt kell beírni:

Connection Reset=false;

Nézzük meg az alábbi kódot:

SqlConnection conn = new SqlConnection("data source=.;initial catalog=ATS;Integrated security=true;");
SqlCommand cmd = new SqlCommand("sp_who", conn);
cmd.CommandType = CommandType.StoredProcedure;

for (int i = 0; i < 3; i++)
{
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
}

Az SQL Profilerben ez látszik:

EventClass	TextData	SPID
Audit Login	-- network protocol: LPC
RPC:Completed	exec sp_who 	52
Audit Logout		52
RPC:Completed	exec sp_reset_connection 	52
Audit Login	-- network protocol: LPC
RPC:Completed	exec sp_who 	52
Audit Logout		52
RPC:Completed	exec sp_reset_connection 	52
Audit Login	-- network protocol: LPC
RPC:Completed	exec sp_who 	52
Audit Logout		52

Hoppá, ez nem az, amit vártunk! Csak 1 logint az elején és 1 logoutot a végén, közben meg az sp_resetconnectiont és az sp_who-nkat. Nem működik a pooling?
De. Csibész a profiler.
“The Audit Login event class indicates that a user has successfully logged in to Microsoft SQL Server. Events in this class are fired by new connections or by connections that are reused from a connection pool.”

Ha bekapcsoljuk a profilerben az Event Subclasst, kicsit tisztul a kép:

EventClass	TextData	SPID	EventSubClass
Audit Login	-- network protocol: LPC		1 - Nonpooled
RPC:Completed	exec sp_who 	52
Audit Logout		52	2 - Pooled
RPC:Completed	exec sp_reset_connection 	52
Audit Login	-- network protocol: LPC		2 - Pooled
RPC:Completed	exec sp_who 	52
Audit Logout		52	2 - Pooled
RPC:Completed	exec sp_reset_connection 	52
Audit Login	-- network protocol: LPC		2 - Pooled
RPC:Completed	exec sp_who 	52
Audit Logout		52	1 - Nonpooled

Szóval van pooling, csak a profiler ravaszkodik. Már kivert a víz. Viszont valami ebben zavar. A pooling ugye a kliensen van. Honnan tudja a szerver egy új parancs beérkeztekor az egy új parancsnak számít a kliens részéről a még nyitott kapcsolaton, vagy csak a poolból visszakapott kapcsolaton hajtanak végre egy új parancsot?
Szerintem ő mesterségesen szintetizálja a poolos login-logoutokat, az sp_reset_connection-ök beérkeztekor veszi őket körbe login-logout párossal, a feeling kedvéért. De ez csak tipp.

Kapcsoljuk ki a connection resetet:

SqlConnection conn = new SqlConnection(
    "data source=.;initial catalog=ATS;Integrated security=true;connection reset=false;");

Mit látunk a profilerben? Semmi nem változott! .NET fw. 2.0 felett nem lehet kikapcsolni az sp_reset_connection-t! Ezért nincs már a legújabb doksiban benne ez a beállítás.

Foly. köv.

Itt.

A screencast nem olyan pörgős és humoros mint az elő előadás volt, de nem szeretek a monitornak bohóckodni. 500 embernek már lehet. Előadás közben meglepően más helyen vagyok a tudatállapotok szivárványán, pedig nem is kokszolok. :)
Persze tartalmilag minden benne van, ami élőben ment. Jó tanulást hozzá!

Örömmel és nagy reményekkel közzéteszem, hogy 2009. január 1.-től független szaktanácsadóként folytatom a szakmai pályafutásom. Ennek megfelelően év végére átalakítom a website-om főlapját is, a megfelelő üzleti, kontakt, stb. információkkal kiegészítve.
A blogom változatlanul fog üzemelni, sőt, várhatóan több időm lesz rá, és jóval több témával is fogok foglalkozni (WCF, WWF, Windows Internals, DP újult erővel, stb.).

Ha .NET-alapú fejlesztéssel, tervezéssel vagy SQL Serverrel kapcsolatban tudok valakinek segíteni, januártól szabad vagyok. Az utóbbi két évben jó pár felkérést utasítottam vissza, jövőre erre már nem lesz szükség.

Részletek később, most igen sok intéznivalóm van…

Előző héten és ma is vizsgáztam egy-egy SQL 2008 vizsgán, hamarosan leírom az élményeimet. Addig is egy kis csemege, ami benne volt.
Mit jelent, ha a várakozások (sys.dm_os_wait_stats) között jelentős szereplő a SOS_SCHEDULER_YIELD?
Az SOS az SQL OS rövidítése, kvázi OS az OS-ben, sok írás van róla az interneten.

Konkrétan, ha a SOS_SCHEDULER_YIELD értéke jelentős, akkor túl van terhelve a proci.

A hack szót nem szeretem, mert valami kókányolás, tákolás jut róla az eszembe, de ez a site megér egy misét. Sok-sok hasznos tipp van rajta, sql programolóknak.
Pl. nézzük ezt. Nagyon ravasz eset, ami bárki belefuthat, ha egyszer csak megjelenik NULL valamelyik oszlopban, és beborul a lekérdezés tőle.
Sajnos ennél például nem írják le, milyen logika miatt nem az az eredmény jön vissza, amit elvárunk, de itt pl. van hozzá némi ideológia.
Ha én kezdő vagy középhaladó sql programoló lennék, minden nap elolvasnék is kipróbálnék egy cikket, sokat lehet belőlük tanulni. (Így is el fogom, csak nem napi egyet, hanem egyszerre sokat, valamelyik éjszaka. :)

Update: érdemes feliratkozni a feedjére.
Sajnos nem találtam feedet, ami csak erről szólna, ebben más is benne van (mondjuk azok se rosszak, de szeretek célirányosan infókat olvasni.) Ha valaki találna, kérem jelezze.

Sokan nem identity-s int-et hanem uniqueidentifiert használnak pk-ként olyan táblákban, amelyet több helyen módosítanak, pl. replikációs felállásban. Nagyon helyesen.
A gond csak az, hogy a NEWID()-val generált értékek tényleg nagyon véletlenszerű értékek (és a közhiedelemmel ellentétben nincs benne a MAC address, mint régen). Ha a generált értéket egy clustered indexes (leggyakrabban primary key-es) oszlopban használjuk, akkor ez egymás után következő beszúrások véletlenszerű helyre kerülnek az adatbázis fájlban (ez így pongyola, de nem akarom túlrészletezni), és széttöredezik a tábla ezer darabra, ráadásul a lapok nem lesznek jól kitöltve, és mégis sok page split lesz. Szóval gáz lesz az insert teljesítménye. Ezen segíthet a NEWSEQUENTIALID függvény, ennek teljesítményét és fragmentálási hatását vizsgálja ez a cikk.
Akinek ilyen jellegű az adatbázisa feltétlen olvassa el a cikket. Százszoros fragmentáció esetén csak megéri elgondolkodni, nem?

Emailben kérdezték tőlem gáz-e, hogy a profilerben a logut eseményeknél jó nagy duration és egyéb értékek vannak.
Röviden: nem.

Hosszabban: a logout események adatai nem túl érdekesek, azok az adott connection által végzett összes munka és várakozás idejét tartalmazzák. Ha lezárod rendesen a kapcsolatokat kliens oldalon, akkor azt várod, hogy a login-logut események rövid időn belül követi egymást, de nem ez történik a
connection pooling miatt. A pooling alapban be van kapcsolva az összes adatelérő komponensben, ezért egy kapcsolat percekig fennáll a kliens és a szerver között, amin sok általad megnyitott kapcsolat pöfögi át a parancsait. Ezért a logoutnál látott érték sok lekérdezés és a közöttük eltelt várakozási idő együttes eredménye.

A pooling=false connection string paraméterrel teszt célból kikapcsolhatod a poolingot, hogy lásd a különbséget. De ezt élőben ne tedd, mert nagyon lelassul tőle az appod, nem véletlenül van bekapcsolva.

Szóval általában nem kell foglalkozni a logout idejével.

Annak idején az SQL 2000 kapcsán úgy tanultam, hogy az sql server okosan bánik a memóriával, és ha a szabad RAM mérete 5 MByte alá esik, akkor a Lazy Writer elkezdi kiírni a módosított lapokat a diszkre. Teszi ezt azért, hogy ne kezdjen el lapozni az oprendszer.
2005-ben azonban elkezdtem azt tapasztalni, hogy az sql server túlzabálta magát, és az oprendszer mégis csak keményen lapozott alatta, és úgy tűnt, hogy nem más processzek miatt, hanem az SQL Server miatt.
Az MVP newsgroupon olvasom, hogy másoknak is van ilyen problémája. 8 G RAM volt a gépben, az SQL Server 6 G-ra volt bekorlátozva. Más folyamatok, mint Database Mail, stb. miatt azonban még így is nagy lapozások történtek, erről az SQL Server ír is az error logjában (A significant part of sql server process memory has been paged out. This may result in a performance degradation.).
A kollégának 4 Gigára vissza kellett húzni a max memory beállítást, hogy megnyugodjon a szerver. Ez másnak is bevált. Jó erről tudni.

Van, akinek az OS File Cache viszi el a ramot. Nekem is gyanús volt már egyszer, ezek szerint nem véletlenül. Van egy beállítás a System applet mögött (Performance Options, Advanced, Memory, Adjust for best performace of Programs vagy System Cache), hogy file server vagy application server üzemmódban működjön a szerverünk. Ez pont a File Cache méretét szabályozza, tehát ha ez rosszul van beállítva egy SQL Serveren, akkor ez már gyanús lehet.

Nem csak a File Cache szoríthatja ki az SQL Servert a memóriából, de ha az, arra az alábbi cikk megoldást adhat.

Részletes elemzés és lépésenkénti hibakeresés a problémáról itt található.

Fél nap alatt közel 400-an megnézték vagy belenéztek a screencastba, ennek igazán örülök, nem gondoltam, hogy ennyi embert érdekel a téma.
Közben visszajeleztek barátaim, hogy a probléma -úgy néz ki- megoldódott, az a lekérdezés fullasztotta le a szervert, amit a videóban optimalizáltunk. :)
Hangyál Zoli a NetAcademia SQL szakértője jelezte, hogy a performance counterek és a profiler eredményeinek egyeztetését ügyesebben is meg lehet oldani mint ahogy a screencastban tettem. Én ugye kézzel raktam össze a két adathalmazt.
Az SQL Server 2005 profilere képes az sql trace mellé betölteni az adott időszak peformance logját is, File/Import Performance Data. Ez igen finom dolog, a két adatforrást szinkronban tartva lehet mozogni az időben.
Az alábbi képen az látható, amikor a bűnös lekérdezés éppen lefutott. Ott leesik jó pár perf számláló, és ha a piros függőleges vonallal arra a környékre navigálok felül máris bejön a beteg lekérdezés a profilerbe. Nagyon durva, nem? Köszönet Zolinak a tippért.

(Ez nem az előző heti konferencia felvétele, az az MS honlapján lesz fenn kb. egy hét múlva, majd belinkelem.)
Nemrég segítettem egy barátomnak kinyomozni, hogy mitől hal be a webszerverük illetve mögötte az SQL Server bizonyos időintervallumokban. Ennek a nyomozásnak a folyamatát mutatom be életem első screencastjában. A gyanús lekérdezés IO költségét 300 ezerről 4e-re sikerült lehúzni, ennek a lépéseit is beleraktam a screencastba. Ha valakit érdekel hogyan kell tényleg komplex lekérdezéseket optimalizálni, az szeretni fogja a felvételt. Kicsit visszafogott hangú, mert aludtak a babák amikor rögzítettem, de az enyém. :)
A jövőben sok screencastot fogok készíteni, egy részét csak hobbiból, egy részét az ms részére, így majd fejlődni fog a technika, lesz intro, intro zene, stb, csak ezeket még meg kell szülnöm.
Sreaming avi verzió htmlben, full screenre maximalizálható, avi direkt link (~100M), flash verzió nem olyan jó minőségű, de csak 23M az egész, megállítható, tekerhető, de nem maximalizálható.
Ha valaki veszi a fáradtságot, hogy végignézze (26 perc, nagy idő a mai világban, tudom), akkor kérem jelezzen vissza, hogy van-e igény hasonlóra a jövőben is, mert nagyon sok mindent így sokkal gyorsabban meg tudok mutatni nektek, mint egy full-featured cikkben, ami azért sok energiát és időt igényel.

Update: bocs, rossz avit töltöttem fel, most megy fel a jó. A Camtasia tud smartfocussal is videót létrehozni, ami ránagyít arra a részre, ahol egérrel mutogatunk. Jó ez, de a végrehajtási tervekből pont a lényeg emiatt lemaradt. Egy óra múlva fenn van a rendes verzió, ez ráadásul csak 100 mega lesz. Addig is a flash verzió megfelelő.
Update2: fenn van a jó videó. Firefox alatt nekem befagy az avi-html verzió, IE-ben jól megy. Gondolom bugos a firefox media player addin.

Szerintem egész jó lett, remélem tetszett a hallgatóságnak is. Annak ellenére, hogy nem volt kaja eljött kb. 55% jelentkező (pontosabbat egyelőre nem tudok), és ez nem rossz.

Pptx. Ppt. Lesz belőle screencast is hamarosan, elmondom újra itthon, de csak, ha aludtam rá egyet (ha tudok, mert lehet, hogy skarlátos Bálint, és a két kicsi is beteg). Shit happens.

A Disk Usage riport a Management Studioban kilistázza azt is, mikor-mennyivel nőtt az adatbázisfájlok mérete az AutoGrow miatt. Érdekelt, honnan veszi ezt az infót, hát el is indult a profiler.

Gyorsan kiderült, hogy már 2005-től fut egy default trace a háttérben, ami alap, de fontos dolgokat gyűjt. Pl. adatbázisfájlok méretváltozásai, errorok, missing statisztikák és egyebek, secu audit jellegű dolgok, FT aktivitások, DDL jellegű változások, mem. állapot, stb.

Ebből bányászik a riport. Némi infó a default trace-ről.

Hölgyeim és Uraim, 2008. szeptember 24., Lurdy.

Én az egyik kedvenc, ha nem a legkedvesebb témámról, az SQL Server optimalizálásról fogok beszélni. Nem törekszem rá, hogy mindenáron a 2008-as újdonságokról beszéljek, arról már volt szó májusban, hanem nagyon sok, már sql 7 óta használható technikáról is szó lesz. Persze, szó lesz included column-ökről (2005) és filtered indexekről is (2008), esetleg a Forceseek hintről is (2008).

Erről a témáról nagyon sok mondandóm és gyakorlati élményem van, amit persze nem lehet 70 percbe besűríteni, ezért igen gondosan kigyomláljuk az előadásig azokat a témákat, amelyeket később screencast formájában fogok bemutatni.

Piszok sok screeencast ötlet kavarog a fejemben nem csak sql témakörben, de egyelőre a családi viszonyok nem teszik lehetővé, hogy legyen annyi erőm és csendes nyugalmam, amivel nekivágnék a felvételükhöz. (A ház emeletének végleges beépítése már erősen tervezés alatt…)

De minden változik, a babák lassan csak elkezdenek önállósodni, és akkor majd beindul a gépezet. Stay tuned.

Nem, nem Disk Queue Length, az a múlt. Ezért.

Apró, de hasznos tudnivaló.
Mentésből visszaállított adatbázisok klasszikus problémája, hogy hiába van a visszaállított adatbázisban Józsi nevű felhasználó, és hiába van a szerverszintű loginok között Józsi, a kettő nem akar egymásra találni.
Régebben az sp_change_users_login-t használtuk a párkeresésre.

SQL 2005-től működik az ALTER USER WITH LOGIN is, ez ráadásul az előbbivel ellentétben NEM csak standard loginokra működik.

ALTER USER user_name WITH LOGIN = login_name

Az sp_change_users_login viszont egyszerre sok felhasználót is tud párosítani, így nem érdemes teljesen elfelejteni.

Habár nem értek az Analysis Serviceshez (még, az SSIS után ez a következő falat), de ez a cikk igen érdekes. Nem annyira az SQL-es, inkább az operendszeres vonatkozásai miatt.
Az átlagfelhasználó az új Windowsokból csak az új shellt tapasztalja meg, meg, hogy több ram kell nekik. Ennek ellenére a háttérben keményen fejlesztik a rendszer magját, csak az nem annyira látványos, még, ha igen hasznos is.
A cikkben arról beszélnek, hogy a Windows 2008-ban új memóriakezelő van, ami sokkal jobban kezeli a sok, apró memóriafoglalást. Ez azonnal látszik, ha az Analysis Services-szel dolgoztatunk fel jelentős mennyiségű adatot, a 2008-on kapásból dupla olyan gyors lett a feldolgozás (tudom, minden teszt csal, de nem hiszem, hogy nagyot hazudnának).
A szerviznek meg lehet mondani, hogy előre allokálja le a memóriát, így 2003-on is hasonló teljesítményt érhetünk el, mint 2008-on.

Az egészből számomra az az érdekes, hogy gyorsabb Windows 2008-ban a memóriakezelés, amiből bármilyen app profitálni fog. Megvettük. :)