Soci (Soczó Zsolt) szakmai blogja

2011.01.06.

How to Share Data Between Stored Procedures

Felírom magamnak, hasznos lehet.
http://www.sommarskog.se/share_data.html

2010.05.13.

Szótöredék keresés SQL Serveren – furcsának ható hiba

Az alábbi kérdezték tőlem pár perce:

SELECT   FROM [table1] WHERE  like '%rekes%'

A rekesz szót nem találja meg, ha magyar a collation az oszlopon. Ez természetes, a kettős betűket ezzel a logikával kezeli a szerver, azaz egy betűnek tekinti őket. A where-be kell egy collation cast, mondjuk latin1-re, amiben nincsenek kettős betűk. Mondjuk ezután nem fog indexet használni a szerver, de a kezdő % miatt eleve nem használva.
Az alábbi példában az egyikkel collationnel megtalálja, másikkal nem.

SELECT   FROM [table1] WHERE  like '%rekes%'
--  collate Hungarian_CI_AS
collate Latin1_General_CI_AS

Lehet csinálni indexelt, számított oszlopot is más collationnel, és arra szűrni, az gyorsabb lesz.

2010.01.27.

SQLCLR deplyment hiba

Filed under: .NET,Adatbázisok,ADO.NET,SQL Server 2005,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 10:24

Error: Incorrect syntax near valami.
Akkor jön elő, ha az SQLCLR assemblyt és benne a függvényeket akarja az VS deployolni. Több oka lehet, most az volt, hogy egy .NET oldalon double-t visszaadó függvény véletlenül így lett deklarálva:

[SqlFunction(…, TableDefinition = “Datum datetime, Szazalek double”)]

Mi a hiba benne? SQL Serverben nincs double, csak real és float. Ráadásul a C# float az az SQL real és a C# double az SQL Server float (kb.). :)

Az előbbi helyesen:

[SqlFunction(…, TableDefinition = “Datum datetime, Szazalek float”)]

Miért kellett SQLCLR függvényt írni? A futó aggregálások (én legalábbis nem tudok jobbat kurzor nélkül) o(n2)-es algoritmusok, ezt CLR-ben könnyen meg lehet írni o(n)-re. Pl:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class UserDefinedFunctions
{
internal class Result
{
public long RowId;
public double CumDollarGain;
public double TopDollarGain;
public double DollarDrawDown;
}

//Input table: create table #trades(RowId long, DollarGain money, other columns possible)
[SqlFunction(FillRowMethodName = “FillRow”, DataAccess = DataAccessKind.Read,
TableDefinition = “RowId bigint, CumDollarGain float, TopDollarGain float, DollarDrawDown float”)]
public static IEnumerable Cumul()
{
using (var conn = new SqlConnection(“Context connection=true”))
{
using (var cmd = new SqlCommand(“select RowID, DollarGain from #trades”, conn))
{
var res = new List();
conn.Open();
double cumulPrice = 0, topPrice = 0, drawDawn = 0;

using (SqlDataReader r = cmd.ExecuteReader())
{
int idCol = r.GetOrdinal(“RowID”);
int gainCol = r.GetOrdinal(“DollarGain”);

while (r.Read())
{
var price = r.GetDouble(gainCol);

cumulPrice += price;
topPrice = Math.Max(price, topPrice);

drawDawn += price;
drawDawn = Math.Min(drawDawn, 0);

res.Add(new Result
{
RowId = r.GetInt64(idCol),
CumDollarGain = cumulPrice,
TopDollarGain = topPrice,
DollarDrawDown = drawDawn
});
}
return res;
}
}
}
}
public static void FillRow(object obj,
out long id,
out double cumDollarGain,
out double topDollarGain,
out double dollarDrawDown)
{
var r = (Result)obj;
id = r.RowId;
cumDollarGain = r.CumDollarGain;
topDollarGain = r.TopDollarGain;
dollarDrawDown = r.DollarDrawDown;
}
};

Sajnos nem lehet átpasszolni a megnyitott SqlDataReadert a két metódus között, ezért kénytelen az ember letárolni az eredményhalmazt. Persze pár ezer sornál ez nem gond.

2010.01.21.

Parallel.ForEach in action

Filed under: Adatbázisok,ADO.NET,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 23:20

Imádom, nagyon ügyes dolog. Csak azért nem hajtotta ki jobban a procikat, mert már nem győzték a diszkek.
Érdekes problémaként még az jött elő, hogy több mint 100 szálat indított a Parallel, mert úgy érezte ez jó lesz, de az Sql Server connection poolja alapban max. 100 kapcsolatot engedélyez, így egyes szálak bedugultak. Lejjebb lehet venni a szálak számát, vagy feljebb a poolt. Nekem 80 szál elég volt, úgyse győzte már az SQL Server az adatokat.

2009.10.12.

Random orderby Linq és EF használatával

Az SQL Serveren szokásos orderby newid() szerveroldali megoldást csak linq to SQL esetén tudjuk kihasználni, szerveroldali függvénnyel. Az EF-ben az ilyesmi nem megy:

… orderby Guid.NewID() …

Ez az EF verzió még nem tudja lefordítani szerveroldali kifejezéssé az orderby kifejezését.
Viszont a random rendezést át lehet nyomni kliensoldalra, ha a lekérdezést “materializáluk” (AsEnumerable) előbb:

Random rnd = new Random();
(from s in ATSEntities.Instance.Symbol
select s).AsEnumerable().OrderBy(o => rnd.Next()));

Nem guidot használtam, hanem Randomot, az kisebb költségű, és az én célomra nem baj, ha csak pszeudo-random a sorrend.

2009.08.04.

Deadlock bulk load során

Párhuzamos bulk betöltések esetén deadlockolhatnak egymással a másoló szálak, főleg, ha vannak FK-ek a táblákon.

Sokféle megoldás adható a problémára.
1. Átmeneti táblába töltünk, amin nincsenek indexek és fk-k.
2. Lekezeljük a deadlockot, és ismétlünk.
3. tablock-kal töltünk be, ezzel azonban súlyosan visszavethetjük a párhuzamosságot.
Ezt ki lehet kényszeríteni központilag is:
EXEC sp_tableoption ‘Tick’, ‘table lock on bulk load’, ‘1’

Nekem ez jó volt, mert nálam az adatforrás volt lassú 1 szálon, ezért kellett 40, az SQL betöltések sorosítva is elég gyorsak, és nincs deadlock.

2009.07.24.

Reporting Services 2008 for Custom Aggregation

Filed under: Adatbázisok,SQL Server 2005,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 10:06

A RS 2008-ban ez más, mint 2005-ben, itt leírják miért és hogyan.

Ha valakit tud tippet arra, hogyan lehet _saját_ _running_ aggregate-eket írni, az érdekelne. Nem sikerült megbízható módon megcsinálnom, működik, de nem mindig.

2009.07.20.

Histogramgenerálás SQL-ből

SQL magazinból.
Fenn van az SQL Magazine 2000-2004 között itt az msdnen, jó cikkek vannak benne.

2009.07.15.

Scatter riportok a Reporting Servicesben

Filed under: Élet,SQL Server,SQL Server 2005,SQL Server 2008,Szakmai élet,Tőzsde — Soczó Zsolt @ 12:07

A legtöbb riport item az RS-ben intuitív, na, ez nem az. Ezzel a chart fajtával nagyon jól lehet szemléltetni nagyszámú minta eloszlását, jól látszik pl. hol tömörödnek az értékek csoportokba (clusterekbe).

Nekem pl. intraday trade-ek elemzésére kiváló (példaként itt a rendszerem egyik riportja, a pötyösek a scatterek), mivel több mint ezer trade történik 2-3 évnyi adat tesztelése során, amelyek teljesítményét jól lehet vizualizálni a scatter chart segítségével valamely változó függvényében.

A chart beélesztésében ez segített.

2009.07.02.

Automatikus kurzor takarítás

Na, ezt nem ismertem, pedig már 10 éve nyüvöm az SQL Servert.

2009.04.30.

Read-only filegroup, hogy ne lock-oljon az SQL Server? Nem.

Az egyik MCP vizsgán céloztak arra, hogy ha egy adatbázis valamely filegroupját read only-vá tesszük, akkor nem fog lockokat rárakni a szerver, hisz minek, úgyse lesznek módosító folyamatok.

Nos, ez elméletileg így lehetne, de nem így van. Ha az egész adatbázis read only, akkor viszont tényleg nem lockol, ami adhat némi teljesítmény nyereséget.

Másra azért jó:

Read-only filegroups, They provide you the following three benefits:

1. Can be compressed (using NTFS compression)
2. During recovery you don’t need to apply logs to recover a read-only file group
3. Protection of data from accidental modifications

2009.04.27.

A márciusi BI konferencia anyaga a weben

Erről elfelejtettem írni, online minden anyag, így a screencastok is – az én részem a Reporting Services volt.

Az utóbbi időben sokat használom a Reporting Servicest a kereskedő algoritmusaim teszteredményeinek megjelenítésére, és egyre jobban szeretem.

Az Entity Frameworköt is gyúrom, na erről már vegyesebb véleményem van, de egyelőre még korai lenne világgá kürtölni, ha már jobban kiismertem, majd írok róla.

2009.04.12.

Előadásom az Architect Akadémián – SQL Server architect szemmel

Kicsit későn szólok, de ha valakit érdekel, még jelentkezhet, április 15-én lesz.
3×1 órában beszélek arról, hogyan lehet bevetni az SQL Serverek (2000-2008) okosságait egy új alkalmazásarchitektúra kidolgozása során. A cél nem annyira mélységi, mint szélességi bemutatása annak, mit lehet kihozni az SQL Serverből. Igyekszek olyan dolgokról is beszélni, amiről ritkán esik szó (pl. Query Notification, Service Broker), világnézet tágítás végett. Szeretném megmutatni, hogy az SQL Server nem egy egyszerű CRUD adatbázismotor, ahogy sajnos nagyon sokan használják.

2009.03.25.

Vigyázni a profilerrel!

Filed under: Adatbázisok,SQL Server 2005,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 13:43

Az egyik folyó munkámban profilerrel szerettünk volna szétnézni egy szerveren, hogy lássuk, kik a lassú lekérdezések. Eddig ez minden cégnél zökkenőmentesen ment, mondjuk egy min. 5000-es read filter mellett szépen jött a lista. Esetünkben azonban a profiler bekapcsolása után gyakorlatilag elérhetetlenné vált az sql server, de olyannyira, hogy még be se tudtunk rá lépni, újra kellett indítani. Először azzal ideologizáltuk meg a dolgot, hogy lassú volt a kapcsolat a profilert futtató gép és az sql server között, de ugyanígy lefagyott akkor is, ha egy azonos LAN-on levő gépről futott a kliens.
Esetünkben nem egy sima terhelési minta kellett, hanem kellettek az XML Planek is, gondolom ez feküdte meg a gyomrát, ezek előállítása.
Kevésbé megterhelő módja a valódi planek kinyerésének a management view-k használata. A következő lekérdezés visszaadja a hangoláshoz számomra fontos infókat:

SELECT top 500
OBJECT_NAME(st.objectid) object_name, 

SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset 
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END 
- QS.statement_start_offset)/2) + 1) statement_text,

qs.last_execution_time, 
qs.execution_count, 

qs.total_worker_time, 
qs.total_worker_time / qs.execution_count agv_worker_time,
qs.last_worker_time,

qs.total_logical_reads,
qs.total_logical_reads / qs.execution_count avg_logical_reads,
qs.last_logical_reads,
qp.query_plan as query_plan_text,
xp.query_plan
--into tempdb.dbo.Plans
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, 
qs.statement_start_offset, qs.statement_end_offset) AS qp
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) xp
order by total_worker_time desc

Az utolsó oszlop az xml plan, csak rá kell kattintani, és máris látszik grafikusan a terv. Szenzációsan kényelmes.

2009.03.18.

Supporting SQL Server 2008: The system_health session

Filed under: Adatbázisok,SQL Server 2005,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 14:36

Az extended event-ökre épülő dolog, ez, amelyet problémák esetén érdemes megnézni a szokásos logok mellett:

SELECT CAST (xest.target_data AS XML)
FROM sys.dm_xe_session_targets xest 
JOIN sys.dm_xe_sessions xes ON
xes.address = xest.event_session_address 
WHERE xes.name = 'system_health';

A háttérben fut egy extended event session, ami a gázosabb helyzetekben logol. Igaz, csak korlátozott méretekben és memóriában, de lehet benne hasznos infó.
Ezekről van benne infó:
* The sql_text and session_id for any sessions that encounter an error with severity >=20
* the sql_text and session_id for any sessions that encounter a “memory” type of error such as 17803, 701, etc (we added this because not all memory errors are severity >=20)
* A record of any “non-yielding” problems (you have sometimes seen these in the ERRORLOG as Msg 17883)
* Any deadlocks that are detected
* The callstack, sql_text, and session_id for any sessions who have waited on latches (or other interesting resources) for > 15 seconds
* The callstack, sql_text, and session_id for any sessions who have waited on locks for > 30 seconds
* The callstack, sql_text, and session_id for any session that have waited for an extended period of time for “external” waits or “pre-emptive waits”.

Jó tudni még, hogy az sql log könyvtárában vannak trc fájlok is, amelyek meg a már 2005 óta létező, háttérben futó default trace nyomait rögzíti. Ebben is lehetnek értékes morzsák hibakereséshez.

Jó cikk az SQL Server 2008 Extended Eventökről

Filed under: Adatbázisok,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 14:25

Advanced Troubleshooting with Extended Events
Ez működik Expressen is, és nagyon durva dolgokat lehet vele csinálni, de nem egyszerű.

2009.03.17.

SQL Server: Lock Pages In memory on 64 bit platform

Filed under: Adatbázisok,SQL Server 2005,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 23:10

Yes, we do recommend to turn on Lock pages in memory so that OS doesn’t page SQL Server out. On 64 bit you only need to grant the right “Lock Pages in Memory” to the SQL account for SQL Server to utilize this feature.

Kapcsolódik még.

Ha ez a hiba előjön főleg nézzetek utána:
“A significant part of sql server process memory has been paged out. This may result in performance degradation”

De már megyek is vissza a munkához, mert igencsak beindult az élet, egyszerre 6 cég számára futnak munkáim, és most adok ajánlatot a jövő héten egy újabb, várhatóan elég nagy volumenű feladatra. Igaza van Marcellnak, válság más cégnél van, nálunk miért lenne? Ezzel nem akarom nagyképűen lebecsülni sok ember nagyon is valós gondját kölcsönproblémák és egyáltalán a megélhetés miatt, csak azt akarom megerősíteni, hogy attól, hogy a gazdasági folyamatok nem a helyükön vannak, attól még megy az élet, folyik az építkezés sok helyen. Van egy ismerősöm, aki kereskedelemből él, és mondta, hogy a ő cégére is igen nehéz idők járnak, nem tudja, nyáron is létezni fog-e még a cége.
Nehéz kikapcsolni a sok negatív hangot, ami az utóbbi hónapokban a csapból is folyik, nem tesz jót az ember motivációjának. De jön a tavaszi szél, remélem ez sok mindenben új vizet áraszt. Az amerikai tőzsde már mocorog (de még messze nincs vége a medveszezonnak), akkor talán 1 év múlva már nálunk is fog. És utána talán a magyar gazdaság is megindul.

2009.03.12.

Mikor építhetünk a sorok sorrendjére SQL Serverben?

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

2009.02.25.

Snapshot elszigetelési szint a SQL Server 2005-től

Filed under: Adatbázisok,SQL Server 2005,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 12:17

Eléggé elfeledett téma ez az SQL Serverben, pedig önmagában ez az új szolgáltatás eladta volna az SQL 2005-öt.
Update: korábban itt egy url volt a technetre, de onnan lekerült a cikk, ezért feltöltöttem ide:

Bevezető az elszigetelési szintekbe
Az SQL Servernek biztosítani kell a párhuzamosan futó adatmódosító és olvasó folyamatok békés egymás mellett élését. A párhuzamos műveletek egymásra hatását teljesen meg lehetne akadályozni, ám ez súlyosan visszavetné az adatbázis kiszolgálási sebességét. Ehhez egyszerűen csak be kellene rakni egy kapuőrt, aki addig nem enged be egy új tranzakciót, míg az éppen futó konklúzióra nem jutott, azaz committal vagy rollback-kel véget nem ért. Könnyen belátható, hogy ezzel kiirtanánk minden párhuzamosságot az adatbáziskezelőből, egyfelhasználósra degradálnánk.
A káosz és a sorosított tranzakciók között vannak kompromisszumos közbenső szintek, ezek az elszigetelési szintek, isolation level-ök. Könnyű elképzelni, hogy két író műveletet, amelyek ugyanarra az erőforrásra, pl. tábla sorra vonatkoznak, nem lehet párhuzamosítani. Így az elszigetelési szintek a párhuzamos író és olvasó folyamatok közötti áthatást tudják szabályozni. Egészen pontosan, az elszigetelési szintek a selectekre hatnak. Az insert, update, delete-re nem, ezt érdemes a fejünkbe vésni már az elején.

A kérdések a következők:
1. Láthat-e egy select olyan adatokat, amelyeket egy másik, nyitott tranzakció éppen módosít (dirty records)?
2. Elvárható-e, hogy egy tranzakcióban a kiolvasott adatokat ugyanazon selecttel újra kiolvasva pontosan ugyanúgy néznek ki a sorok, mint korábban, vagy megváltozhatnak a két olvasás között, azaz, megismételhető-e az olvasás (repeatable read)?
3. Mi van, ha az előbbi olvasások között új sorokat szúrnak be a táblába? Láthatóvá válnak-e ezek a szellem sorok a második olvasásra (phantom records)?

Az SQL Server mindhárom kérdésre ad válaszokat, már idétlen idők óta.

A READ UNCOMMITTED elszigetelési szinten futó select láthatja az éppen módosítás alatt álló sorokat, amelyeket lehet, hogy egy másodperc múlva már vissza is görgetnek.
A READ COMMITTED alapértelmezett szinten nem látunk piszkos rekordokat, de belefuthatunk a nem megismételhető olvasás és a fantom sorok problémájába.
A REPEATABLE READ szinten garantálják az olvasások megismételhetőségét, de fantom sorok még így is megjelenhetnek.
A SERIALIZABLE szint az összes olvasási anomáliára gyógyír, cserébe a párhuzamosság nagyon erősen lecsökken.
Ezen szinteket a zárolások idejének és módjának variálásával képes előállítani a szerver, a részletekről egy régebbi cikkemből érdemes tájékozódni.
http://soci.hu/publications.aspx
Jól látható, hogy az elszigetelési szintek megfelelő megoldást adnak a különböző élethelyzetekben fellépő párhuzamossági problémákra. Vagy mégsem? Ha így lenne, nem jött volna létre ez a cikk.

A Snapshot elszigetelési szint alapjai
Az előző szintek közös ideológiai és technológiai jellemzője, hogy zárolássokkal védik meg egymástól a párhuzamos folyamatokat. Ez elvileg korrekt megoldást ad, de időnként túlságosan lekorlátozza a szerver párhuzamosságát.
Jön egy folyamat, indít egy tranzakciót, amiben nekiáll módosítani egy tábla felét. A módosítás mondjuk 5 percig tart. Ez idő alatt READ COMMITTED vagy szigorúbb szinten senki nem tudja olvasni a tábla módosítás alatt álló részeit, mert zárolás alatt állnak. Miért nem lehet azt tenni, hogy a select visszakapja a sorok módosítás előtti állapotát?
Hasonlóan, ha fut egy nagyobb analitikai lekérdezés vagy riport, ami intenzíven olvassa a tábla tartalmát, akkor miért nem engedik módosítani közben a sorokat, úgy, hogy a select az adatok módosítás előtti állapotát lássa? Miért ne? Erre találták ki a snapshot elszigetelési szintet.
Snapshot esetén nem zárolássokkal dolgozik a szerver, hanem ún. sorverziózással. Amikor egy SNAPSHOT izolációs szinten futó folyamat adatokat kérdez le, nem rak a szerver csak olvasható (shared) zárakat a sorokra. Ehelyett, ha jön író folyamat, a módosított sorok módosítás előtt állapotát letárolja a tempdb-ben, az ún. version store-ban, verziótárban. Ha az olvasó folyamat vagy akár más folyamatok is újra a kérdéses adatokat select-álják vissza, a verziótárból szedi össze a szerver a módosított sorok korábbi állapotát. Ha többen is nekilátják módosítani ugyanazt a tartományt, akkor többféle verzió is keletkezik a tempdb-ben, amelyek láncolt listában tárolódnak, és lekérdezéskor ebből keresi ki a szerver a szükséges verziót.
Mit nyerünk és mit vesztünk, hogy áll a zárolás kontra sorverziózás vetélkedő? A verziózás nyilvánvaló (óriási) előnye, hogy az olvasók nem blokkolják az írókat és vica versa. Végül is ezért vezették be a szintet. Sajnos viszont az is nyilvánvaló, hogy a módosítások lassabbak lesznek, mivel verziózni kell minden módosított sort. A lekérdezések is lassabbak lesznek, mert ha vannak nyitott író tranzakciók, utána kell nézni a soroknak a tempdb-ben is, hátha van korábbi verziójuk.
Másfelől viszont verziózás esetén nem kell shared lockokat rakni az olvasott sorokra vagy lapokra, így ez a költsége meg kiesik. A kettő közötti döntéshez pontosabban meg kell ismernünk a snapshot elszigetelés belső működést, vágjunk hát bele a részletekbe.
Snapshot üzemmódok bekapcsolása
Kétféle módon használhatjuk a snapshot elszigetelési szintet, utasítás és tranzakció szinten is, fontos megérteni a pontos különbséget a kettő között.
Egyik snapshot üzemmód sincs alapban bekapcsolva az adatbázisokon, azaz az SQL 2005-ös és 2008-as adatbázisok is zárolással működnek, hasonlóan az SQL Server 2000-hez. Bekapcsolásuk ALTER DATABASE-zel lehetséges:

alter database SnapDB
set allow_snapshot_isolation on

illetve

alter database SnapDB
set read_committed_snapshot on

Bekapcsolás után erősen megnőhet a tempdb mérete és terhelése, erről hamarosan részletesen írok. Minden egyes tábla sora, amelyen update vagy delete műveletet futtatunk, kap egy 14 bájtos plusz adattagot, egy mutatót, azaz ennyivel lesz hosszabb minden módosított sor. Ettől laptörések (page split) következhetnek be, ami töredezetté teheti a táblát, így bekapcsolás és némi használat után érdemes megfigyelni az érintett táblák töredezettségi szintjét, de defragmentálni, ha indokolt.

Snapshot Isolation (SI)
Ha az allow_snapshot_isolation be van kapcsolva, akkor a tranzakciókban átválthatunk SNAPSHOT elszigetelési szintre, amitől tranzakció-szintű sorverziózást kapunk.
Alapállás:

create table Gyumolcs (id int, nev nvarchar(50))
insert Gyumolcs values(1, N'Alma')

“A” kapcsolaton végrehajtjuk a következőt:

begin tran
update Gyumolcs set nev = N'Körte' where id = 1	

Látható, a tranzakció nyitva maradt. “B” kapcsolaton:

set transaction isolation level snapshot
begin tran
select * from Gyumolcs
-- 1 Alma

Átváltottunk snapshot elszigetelési szintre, és felolvassuk a táblát. Az első megfigyelés, hogy nem blokkolódik az olvasó (B) tranzakció, hanem azonnal visszatér a sor módosítás előtti állapotával, azaz az Almával. Snapshot nélkül a select várakozna az első tranzakció végére, commit estén a módosítás utáni állapotot látnánk, rollbacknél az előttit.
Zárjuk le az első tranzakciót, véglegesítve a módosítást:

Commit	

Adjuk ki még egyszer a selectet a második, még nyitott tranzakcióban:

select * from Gyumolcs
-- 1 Alma

Kimenet: továbbra is Alma. Miért? SNAPSHOT szinten tranzakció-szintű sorverziózást kapunk, azaz garantáltan ugyanazt az adatok kapjuk vissza második olvasásra is, amit a tranzakció elején. REPEATABLE READ, sorverziózással.
Zárjuk le a második tranzakciót is egy committal. Ekkor nem történik adatbázisműveletet, hisz csak olvastunk ebben a tranzakcióban, de a verziótár tudja, hogy lehet takarítani a korábban eltárolt sort, nincs már senkinek szüksége rá. Ebből következik, hogy hosszú ideig nyitott tranzakciók miatt igen nagyra tud nőni a verziótár (a tempdb), így erre fokozottan kell ügyelni az adatelérő réteg tervezésénél.
Tovább vizsgálódva nézzük meg, mi a helyzet a fantomsorokkal?
A másodikon kapcsolaton:

set transaction isolation level snapshot
begin tran
select * from Gyumolcs
-- 1 Körte

Kimenet: Körte, az előző teszt módosításának végeredménye.

Az első kapcsolaton szúrjunk be egy új sort:

begin tran
insert Gyumolcs values(2, N'Barack')	
	select * from Gyumolcs
-- 1 Körte

A második kapcsolaton megismételve a selectet továbbra is csak a Körte sor látszik, az új Barack nem. Ez akkor is így marad, ha commitoljuk az első tranzakció beszúrását. Azaz látjuk, hogy a SNAPSHOT elszigetelési szint véd a fantom sorok ellen is. Ez azért nagy szám, mert ugyanazokat a tranzakcionális garanciákat kapjuk SNAPSHOT szinten, mint a zárolós működési módban a legszigorúbb SERIALIZABLE szinten, mégis, a folyamatok párhuzamosságát sokkal kevésbé korlátozva. Ennek nyilván a verziótár állandó frissítésével fizetjük meg az árát.
Rendben, a módosítások és a frissítések jól megférnek egymás mellett, és garantálja nekünk a szerver, hogy a lekérdezések az őket magába foglaló tranzakció kezdetéhez képesti legfrissebb adatot adja nekünk vissza. Ez adatelemzési szempontból óriási előny lehet, azonban a módosításokat kicsit megnehezíti. Miért?
Hajtsuk végre a következő scriptet az B kapcsolaton keresztül:

set transaction isolation level snapshot
begin tran
select * from Gyumolcs 
where id = 1
-- 1 Körte

Látjuk a Körte sort, eddig semmi meglepő.
A kapcsolaton módosítjuk B kapcsolaton leválogatott sort:

begin tran
update Gyumolcs set nev = N'Narancs' where id = 1	

A Körte most már Narancs, de még nincs véglegesítve a tranzakció. B folyamat újraolvashatná a sort, és látná az eredeti állapotot, az Körtét, ezt már tudjuk. De B most módosítani akarja ugyanazt a sort!

update Gyumolcs 
set nev = N'Narancs' where id = 1
-- Várakozik ...

Mit tehet ilyenkor a szerver? Olvasás esetén elővehette az eredeti verziót, de ha most is ezt tenné, akkor a tranzakció végén fejbe vágná a két tranzakció egymás módosítását, azaz a sor végső állapotát az döntené el, hogy melyik tranzakció végzett később. Ezt a problémát lost update-nek nevezzük, elveszett módosításnak. A cikk elején felsorol anomáliák között azért nem szerepelt ez, mert ez csak optimista, nem zárolós sémák esetén jelentkezik, a zárolós-pesszimista üzemmódnál nem.
No, az SQL Server úgy kezeli ezt az esetet, hogy megvárakoztatja a második módosító tranzakciót. Ha az első egy committal ezek után eldönti, mit akar, a második megkapja a magáét:

commit	

Msg 3960, Level 16, State 4, Line 1
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.Gyumolcs’ directly or indirectly in database ‘SnapDB’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Logikus lépés, valahogyan tudatni kell a második tranzakcióval, hogy megelőzték. A tranzakciót már le se kell zárni, mert ezt megtette helyettünk a szerver. Sőt, nem is szabad, hisz nincs értelme, ezt is gyorsan megtapasztaljuk:

	commit

Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Azaz az első tranzakció sikeresen módosított a sort. Mit tehet a vesztes tranzakció? Ott van a tipp a hibaüzenetben, újra lehet próbálkozni. Másodikra talán mi leszünk a gyorsabbak. Ha belegondolunk, a zárolós-pesszimista üzemmód esetén is vannak hasonló esetek, ott deadlockok esetén kell ismétlő logikát berakni az alkalmazásba, itt pedig a 3960-as hibát kell speciálisan lekezelni (az adatelérő rétegben, vagy egy TSQL TRY-CATCH-ben).
Ez a hiba valamelyest segít dönteni abban, hogy érdemes-e átváltanunk erre az új elszigetelési szintre. Ha kevés módosítási konfliktust élünk meg, azaz sok az olvasó folyamat, de kevés az módosító, akkor a SNAPSHOT jó választás, nagyon sokat segíthet a párhuzamos végrehajtásban, így a szerver áteresztő képességében (hány tranzakciót tud végrehajtani egységnyi idő alatt).
Viszont sok párhuzamos író folyamat esetén (pl. sok forrásból táplálkozó adatgyűjtő rendszer) a régi zárolós üzemmód a megfelelőbb. Ebben az esetben viszont a deadlockokat kell ismétléssel lekezelni, csak ezekből várhatóan kevesebb lesz, így erre az oldalra billen el a mérleg nyelve.

Read Committed Snapshot Isolation (RCSI)

RCSI esetén nem kell explicit átváltani SNAPSHOT szintre set transaction isolation level paranccsal, hanem az eddigi alapértelmezett READ COMMITTED szint működése alakul át verziózottá. Azaz anélkül, hogy a kisujjunkat is meg kellene mozgatni, máris élvezhetjük a megnövekedett párhuzamosságot (meg a megnövekedett tempdb-t :).
A SI és az RCSI között azonban alapvető különbség van: míg SI estén láttuk, hogy az olvasó tranzakció kezdetéhez képest kapunk vissza konzisztens adatokat, ismételt olvasási garanciával, addig RCSI esetén a select utasítás kezdetéhez képest kapunk vissza egységes adatokat. Azaz nincs ismételt olvasási és fantom sorok elleni garancia.
Nézzük meg az első példánkat, ezúttal RCSI szinten. Hogy teljesen tiszta legyen a kép, kapcsoljuk ki a sima SI-t:

alter database SnapDB
set allow_snapshot_isolation off

Majd váltsunk át az RCSI üzemmódba a következő paranccsal:

alter database SnapDB
set read_committed_snapshot on

A parancs csak akkor hajtódik végre, ha csakis ezt a parancsot végrehajtó kapcsolat van benn az adatbázisban. Ügyeljünk, hogy a Management Studio Object Browsere is nyitva tart egy kapcsolatot az adatbázisra, ha annak belseje ki van nyitva a fában. Zárjuk be a fa csomópontját, és felszabadul a kapcsolat.
Bekapcsolás után hajtsuk végre a korábbi tesztünket ezen a szinten:

Conn1:

1.
create table Gyumolcs (id int, nev nvarchar(50))
insert Gyumolcs values(1, N'Alma')	

Conn2:

2.
begin tran
select * from Gyumolcs --1 Alma

Conn1:

3.
begin tran
update Gyumolcs set nev = N'Körte' where id = 1	

Conn2:

4.
select * from Gyumolcs --1 Alma

Conn1:

5.
commit	

Conn2:

6.
select * from Gyumolcs -- 1 Körte
Commit

Látható, hogy a nyitott módosító tranzakció esetén (3) a módosítás előtt adatot kapjuk vissza (4), éppúgy, mint a SI esetén. Ha azonban a módosítás véglegesítésre került (5), az olvasó folyamat már a módosítás utáni állapotát látja a sornak (6). Ez az alapvető különbség az RCSI és az SI között.
RCSI esetén nincs konfliktus update esetén, a második módosító folyamat ugyanúgy blokkolódik, mint SI esetén, azonban az első író commitja után a második is sikeresen végrehajtja az update-jét.
Azaz RCSI esetén számíthatunk fejbe vágott módosításokra, nem megismételhető olvasásokra és fantom sorokra. Cserébe viszont jelentősen kevesebb adat kerül a verziótárba, hisz nem kell olyan hosszú ideig megtartani a módosítás előtti állapotokat. Megint valamit-valamiért.
A verziótár más felhasználásai
A tempdb-ben kialakított verziótár elsődlegesen a Snapshot szintek kedvéért került kialakításra. Ám ha már elkészült, más szolgáltatást is építettek rá.
A triggerek már eddig is egyfajta verziózott módon működtek, hisz pl. a deleted tábla a sorok módosítás előtti állapotát mutatta. Ezt SQL Server 2000-ben a tranzakciós napló visszaolvasásával állították elő. A megoldás nagy hátránya volt, hogy az egyirányú írásra optimalizált tranzakciós naplót időnként vissza kellett olvasni, ami miatt megnövekedett a HDD fejmozgások száma, így lecsökkent a tranzakciós log írási sebessége.
SQL 2005-től kezdve a triggerek inserted és deleted virtuális tábláit a verziótár segítségével szintetizálják. Akkor is, ha egyik snapshot üzemmód sincs bekapcsolva. Ettől megszűnik a logra gyakorolt kedvezőtlen hatás, de megnő a tempdb terhelése.
A verziótár másik jelentősége az online indexműveleteknél jön a képbe. SQL 2005-től az indexműveleteket (create, drop, rebuild) az érintett tábla zárolása nélkül is végrehajtható, ezek az online műveletek. Például:

alter index all on Production.Product
rebuild with (online = on);

Amikor elkezdődik az új index felépítése a szerver belül “átvált” SNAPSHOT izolációs szintre, így a meglévő index adatait a tranzakció, az index építés kezdetéhez képest konzisztens módon képes átmásolni. Miközben építi fel az új, párhuzamos indexfát közben más kapcsolatokon keresztül módosíthatják az alaptáblát és az indexet is. A módosítások egyszerre módosítják az eredeti indexet és az épülőt is. A verziótároló a tempdb-ben közben szépen rögzít minden változást, így a módosítások nem kerülnek duplán átvezetésre.
Ami számunkra ebből kívülről látható lényeg, hogy az indexművelet alatt is elérhető a tábla és az index.
A Multiple Active Resultset (MARS) nevű harmadik technológia az, ami még kihasználja a verziótárolót, ám ennek működését most nem részletezem.

Verziótár takarítás, monitorozás

Ha indokolatlanul nagynak tűnik a tempdb vagy valamelyik snapshot szint bekapcsolása után szeretnénk látni mennyire intenzíven használja a szerver a verziótárat, többféle úton is kutakodhatunk.
Ha arra vagyunk kíváncsiak hány sor van a verziótárban, futtassuk le a következő parancsot:
select count(*) from sys.dm_tran_version_store
SI tranzakciók esetén a tranzakció végén, RCSI tranzakcióknál a select lefutása után már nincs szükség a módosított sorok verziótörténetére. Ezért percenként lefut egy háttérszál a szerverben, amely kitörli a már nem szükséges sorokat.
Ha a tempdb kifutna a számára behatárolt helyből, akkor a takarító azonnal elindul, hátha fel tud szabadítani elég helyet, így nem kell megnövelni az adatbázist. Tisztára olyan ez, mint a .NET Framework Garbage Collectora, csak az a memóriát söprögeti.
A Version Generation Rate és a Version Cleanup Rate teljesítményszámlálók segítségével durván láthatjuk mekkora a sürgés-forgás a tárban.
Az Update conflict ratio túl nagy értéke azt sugallja, lehet, hogy érdemes visszatérni a zárolós üzemmódhoz, mert túl sok az író folyamat.
A verziótár halála egy idétlen tranzakció, amely – általában hibakezelési hiba miatt – túl hosszú ideig fut. Azaz elfelejtik lezárni a tranzakciót, de az adatbáziskapcsolatot nyitva hagyják. Egy ilyen tranzakció a zárolásos üzemmód esetén hosszú blokkolási láncokat, így akár az egész adatbázisra épülő alkalmazás leállását okozhatta. Most nem áll meg az élet, de lehet, hogy a tempdb nagyon nagyra nő, és belassul az adatbázis. A Longest Transaction Running Time nevű számlálóban meg lehet nézni, van-e valaki beragadva. Ha itt meglátunk mondjuk 1800 másodpercet, azaz fél órát, miközben tudjuk, hogy a tranzakcióink 1 percen belül lefutnak, akkor sejtjük, hogy valaki beragadt. A bűnöst a következő lekérdezéssel érhetjük tetten:

select elapsed_time_seconds, session_id 
from sys.dm_tran_active_snapshot_database_transactions
elapsed_time_seconds session_id
-------------------- -----------
848                  52

Az 52-es session (kapcsolat) már 848 másodperce tollászkodik, ha nem indokolt a munkája, kilőhető.
Használjuk vagy sem?

Zárásul nézzük meg, milyen szempontok alapján tudunk választani a kétféle snapshot elszigetelés között, illetve mire számítsunk, ha úgy általában átállunk a zárolós üzemmódról valamelyik verziós üzemmódra.

Általánosságban a RCSI-on érdemes először elgondolkodni, mert:
• Kevesebb helyet igényel a tempdb-ben mint a SI
• Használható elosztott tranzakciókban is, a SI nem
• Nem lesznek update konfliktusok
• Nem kell átírni az alkalmazásokat. Egyszerűen be kell kapcsolni az adatbázison RCSI-t, és máris minden alkalmazás élvezi az előnyeit.

Mikor érdemes ezek után az SI-t bevetni?
• Ha általában kevés a párhuzamosan futó módosító folyamat, így kicsi az update konfliktusok valószínűsége
• Olyan lekérdezéseket, riportokat kell futtatni, amelyeknek fontos az időpont-konzisztencia. Magyarul ha egy tranzakcióban többször is át kell gyúrni az adatokat és fontos, hogy közben az adathalmazunk stabil legyen, akkor ezt csak a SI tudja biztosítani, a RCSI nem. Például a tranzakcióban először aggregáló lekérdezéseket futtatunk egy táblán, aztán a részletes adatokat válogatjuk le. SI nélkül inkonzisztens lehetne a kimenet, azaz a részletes adatok nem vágnának egybe az aggregált eredményekkel.

Vessük most össze a verziós üzemmódokat a zárolós üzemmódokkal.

A verziózás előnyei a zárolással szemben:
• A select nem rak csak olvasható (shared) zárakat a táblára, így az írók és az olvasók nem akadályozzák egymást (ez a legfőbb érv a verziózás mellett)
• A selectek időben konzisztens képet adnak vissza (SI és RCSI esetén másképp, lásd korábban)
• Sokkal kevesebb zárat (lockot) kell nyilvántartani a szervernek, ami jelentős erőforrást takarít meg
• Kevesebb zár-eszkaláció történik (amikor sok sor vagy lap szintű zárat átkonvertál tábla szintű zárrá a szerver, hogy erőforrásokat szabadítson fel)
• Kisebb a deadlockok valószínűsége

Természetesen vannak hátrányai is a verziózásnak, másként automatikusan ezt használnánk. Ezek a következők:
• A selectek lelassulhatnak, ha hosszú verzió-láncokat kell végignézni sok nyitott tranzakció miatt
• A tempdb-ben helyet kell neki biztosítani
• Az adatmódosító műveletek verziókat fognak generálni a verziótárolóban, még akkor is, ha nincs közvetlenül rájuk szükség. Azaz lassulnak az adatmódosítások.
• Minden sor ami a verziózás bekapcsolása után módosult 14 bájttal hosszabb lesz (mutató a verziótár megfelelő bugyrára)
• Az update lassabb lesz a verziótárolás miatt
• SI esetén az update konfliktusba kerül párhuzamos folyamatok update-jeivel, amit le kell kezelni az alkalmazásnak
• Ügyelni kell a tempdb méretére, nehogy megszaladjon.

Összefoglalva elmondható, hogy a snapshot elszigetelési szintek megjelenése hatalmas fegyvertény, amellyel sok párhuzamosan működő rendszer működését lehet drámaian felgyorsítani, illetve nagyban megkönnyíti olyan alkalmazások átírását más adatbáziskezelő rendszerről, amely verziózással működik.

2009.02.23.

.NET teljesítményhangolási tapasztalatok 6.

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.

« Older PostsNewer Posts »

Powered by WordPress