Archive for the ‘SQL Server 2014’ Category

Nagy táblák joinolása

Tuesday, November 22nd, 2016

Egyik folyó munkámban több tízmilló soros táblákon végzett joinokat kellett optimalizálni. Általában ez nem kihívás, mert szinte mindig vannak szűrési feltételek, amelyeket kellő közelségbe víve a táblákhoz és rendes indexekat alápakolva már csak pár ezer joint kell végrehajtani, ami gyors lesz.
De most tényleg össze kellett joinolni sok millió sort, szűrés nélkül.
Mit lehet ezzel kezdeni? Sajnos itt már eléggé behatárolt területen mozgunk. A normál indexelős megoldások nem segítenek, mivel minden táblát teljes egészében be kell járni (nincs where).
Ráadásul ha *-os a select, akkor a cover NC index se játszik, hogy legalább az IO csökkenne.
Merge joinra lehet játszani clu indexekkel, de azért ez korlátos terület sok tábla esetén, illetve párhuzamos tervek esetén magától nem fog merge joint használni (itt írnak egy trace flagről, amivel mégis rá lehet venni).
Mit lehet tenni. Egyik lehetőség előre elkészíteni a join indexelt view-ban. Erre ügyesen ráharap az optimizer, ha van olyan join amit aztán többször futtatunk, akkor megéri ez a denormalizálás.
Ha viszont van újabb szerverünk (2016), akkor van sokkal durvább lehetőség: Columnstore index.
Az a baj ugye a nagy joinnal, hogy akárhogy is trükközünk, ez nagy meló a prociknak és az IO alrendszernek (vinkóknak). Az indexed view ezt úgy oldja meg, hogy egyszer kell megcsinálni, aztán sokszor élvezni az előre összepakolt adatokat.
A columnstore viszont (dióhéjban) azért piszok gyors mert:
1. 5-10-szeresen tömörítve tárolja az adatokat, kevesebb IO, illetve a memóriában a buffer cache-t is jobban ki tudja használni (mintha több RAM-unk lenne)
2. Képes az adatok csak egy részét felolvasni, ha csak kevés oszlop kell (select *-on ez nem segít persze)
3. Képes batch módban belülről párhuzamosan végrehajtani a műveletek egy részét (ez nagyon durván megdobja)
4. Képes a sorok egy részét felolvasni where feltétel alapján, mivel minden 1m sorhoz (szegmens) nyilván tarja az adott oszlop min és max értékét
5. Le tud nyomni operátorokat (pl. sum) a storage engine-be, így nem kell adatokat passzolgatni a rétegek között.

No, lássuk a medvét. Létrehoztam két másolatot egy 100 millió soros táblából. A tesztgép egy két éves laptop 2 core-ral és 8G RAM-mal, SSD-vel. Nem egy szerver.
A két táblát a kulcsai mentés join-olom, így mind a 100 millió sort végig kell néznie, és ennyi találat is lesz.

Először sima Clu index:
create clustered index IX_Clu1 on B1(Id)
create clustered index IX_Clu2 on B2(Id)

select count(*) from B1 join B2 on B1.Id = B2.Id

SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 18 ms.

(1 row(s) affected)
Table ‘B1’. Scan count 5, logical reads 1141262, physical reads 6,
read-ahead reads 1138814, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table ‘B2’. Scan count 5, logical reads 1140956, physical reads 4,
read-ahead reads 1138821, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
Table ‘Workfile’. Scan count 896, logical reads 480256, physical reads
2688, read-ahead reads 477568, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

SQL Server Execution Times:
CPU time = 477262 ms, elapsed time = 377318 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

377 másodperc.

Jöhet a columnstore index:
create clustered columnstore index IX_CStore1 on B1
create clustered columnstore index IX_CStore2 on B2

select count(*) from B1 join B2 on B1.Id = B2.Id

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.

(1 row(s) affected)
Table ‘B2’. Scan count 4, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 105018, lob physical reads 0,
lob read-ahead reads 0.
Table ‘B2’. Segment reads 103, segment skipped 0.
Table ‘B1’. Scan count 4, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 104998, lob physical reads 0,
lob read-ahead reads 0.
Table ‘B1’. Segment reads 102, segment skipped 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

SQL Server Execution Times:
CPU time = 79920 ms, elapsed time = 27834 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

377 sec vs. 28 sec. Azért ez masszív különbség. :)

Érdekességképpen megnéztem NC Columnstore index-szel is, úgy 60 sec jön ki. Ez se rossz.

A jövő héten lehet ki tudjuk próbálni egy nagyobb géppel is, kíváncsi vagyok, ott mit tudunk vele kihozni.

Ha esetleg valakinek vannak már gyakorlati sikerei, érdekelnek a számok.

SQL Server memória hiány miatti lassú lekérdezés

Sunday, June 19th, 2016

Érdekes hibába futottam bele mostanában. A lapotopomban csak 8G RAM van, így az azon futó SQL servernek nem sok marad, amikor a Chrome, a Visual Studio 2015 és más memóriazabáló alkalmazások elkezdenek terjeszkedni.
A probléma az volt, hogy egy olyan lekérdezés, amely Sortot tartalmazott a szokásos 2-3 mp helyett fél-egy percig futott. A végrehajtási tervben volt egy figyelmeztetés, hogy a sort operátor kénytelen kipakolni a rendezést a tempdb-be (spill). De ettől még nem kellett volna ilyen lassúnak lennie. Csak 3500 sorról volt szó, ez azért belefért volna memóriába.

A select * from sys.dm_exec_query_memory_grants lekérdezésből kiderült, hogy a lekérdezés sortjának kellett volna 10M memória, de nem tudott annyit kapni, ezért várakozott, és aztán 20 másodperc múlva egyszer csak timeout lett, és kényszeredetten csak nekifogott végrehajtani a sortot a tempdbben.

Azaz egy egy nem szokványos lassú lekérdezés volt, nem sok lapolvasással járt, mint a tipikus rosszul optimalizált lekérdezések, hanem a memory grantre várt.
A megoldás az lett, hogy beállítottam 1G min memory-t az SQL Servernek, így már kiszámíthatóan jól érzi magát.

Egy rendes szerveren valószínűleg ritkább az ilyen memória kényszer, de azért jó tudni róla.

2 karakter, és máris más az execution plan

Wednesday, June 24th, 2015

Az eredeti lekérdezésben az ORDER BY így nézett ki: order by BDT.
Ez a számított oszlopra vonatkozott, azért a szervernek meg kellett oldania a rendezést egy külön lépésben. A b.BDT után viszont már tudja használni az alatta levő index rendezettségét, így nem kell rendeznie. 4x teljesítménynövekedést okozott ez a 2 karakter.

select
dateadd(second, @dtModifier, BDT) BDT,
cast(O as real) O, 
cast(H as real) H, 
cast(L as real) L,
cast(C as real) C,
V
from dbo.Bar b with (nolock)
where b.TickerID = @TickerID
and b.BDT >= @StartDate
and I = @I
order by b.BDT

Intra query deadlock

Tuesday, June 16th, 2015

Ma láttam egy újfajta deadlockot, amiben az SQL Server által párhuzamosan végrehajtott lekérdezés szálai akadtak össze. Azaz ugyanaz a PID akadt össze magával.

Először arra gondoltam, lefogom 1 szálra a lekérdezést maxdoppal, de szerencsére nem volt jól optimalizálva, így jól fel lehetett gyorsítani. 240000 lapolvasásról 4-re. :) Így már magától soros lett a plan, volt deadlock, nincs deadlock.

Bob is írt már erről.

Exam 70-459: https://www.microsoft.com/learning/en-in/exam-70-459.aspx done

Friday, May 22nd, 2015

Na, ez is megvan. :)

Ez is upgrade, két vizsgát tartalmazott, és ebben már volt 2014-es tartalom. Két témakört érintettek, az egyik az In-Memory DB (mi más?), a másik a delayed durability. Ja, és a clustered columnstore index is előjött egy kérdésben.

Szokás szerint volt pár nem jól definiált kérdés, ezeknél már kommenteztem, mert utálom, hogy ennyire nem nézetik át a kérdéseket.

Az in-memory-snál elég sokat elidőztem, mivel belementek, hogy milyen izolációs szintek vannak benne, és melyik alkalmas az adott feladatra, amit a scenarióban leírtak. A scenariók itt is km hosszúak, kicsit be is tojtam az elején, hogy nem lesz elég időm. De kiderült, hogy sok nem scenarió alapú kérdés is volt, azokat gyorsan meg lehet válaszolni.

Csak az inmemory-s rész a maga kb. 5 kérdésével elvitt vagy 20 percet, de a többi már emészthetőbb volt.

Idén már csak két tervem van, az MVC vizsga és a BI-os SQL rész. Az MVC-t valszeg letolom jövő héten még, amíg tart a second shot, ki tudja, hátha egyszer megbukom, nyugalmat ad, hogy nem dobok ki 75EUR-t az ablakon.

SQL Server 2014 SP1 letölthető

Wednesday, May 20th, 2015

Innen.

Itt a fixek listája. Átszaladva a listán a túlnyomó többsége fix, nem sok új dolog van benne. Érdekes, hogy az in memory db részhez nincs benne fix. Kevesen használják még…

SQL Server 2016 In-Memory OLTP

Friday, May 15th, 2015

Úgy tűnik 2016-ra felnő, 1.0-ssá az In-Memory OLTP az SQL Serverben. Gondolkodik valaki a bevezetésén (úgy értem 14-ben) ?

Exam 70-457 Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 done :)

Friday, May 15th, 2015

Ma volt egy szabad napom, gyorsan lenyomtam ezt is.

A vizsga a szokásos volt, 0,8-e normál, rendes kérdések, 20% nehezen megragadható marhaság. 50 kérdés volt, ezzel le van tudva a 70-461 és a 70-462. 700 ponttól lehet átmenni, nekem 833 volt a 461-es rész, 900 a 462-es. Látszik, hogy developer agyam van. :)

Ha jól emlékszem nem volt benne egyetlen 2014-es kérdés sem, ez számomra szomorú. Valójában én a 2014 vizsgákat akartam letenni, de nincsenek most ilyenek. Gondolom majd a 2016-osnál lesznek új vizsgák, nem akarják lejáratni két évente a certeket.

Ami érdekes volt, eléggé rámentek a Windowing Functionökre, erre érdemes mindenkinek gyúrni (egyébként is hasznosak). De csak a 2005-ös szinten kérdezték, nem mentek bele a 2012-es újdonságokba (framing), pedig itt vannak az igazi csemegék.

A következő lukas napon jön a 459, abban már tényleg várhatóak 2014-es kérdések, majd megírom, mire kíváncsiak.

SQL teljesítményoptimalizálás – imádom

Thursday, March 12th, 2015

Az elmúlt 3 hétben egy fejlesztési projektből kicsípve 3 napot három cégnél is SQL Server teljesítményoptimalizáltam (és még vannak cégek a queue-ban, ilyen erős évindulásom még soha nem volt :). Azt kell mondjam, ez a legkedvesebb munkám mindenek felett. 1 nap alatt általában igen látványos eredményeket lehet elérni, eddig még soha nem csalódtak bennem ügyfelek.

Az áprilisom és a májusom teljesen tele van már, de ha valakinek hasonlóra van igénye, jelezze nekem, ha becsúszik valahol egy luk, egy napra lehet el tudok ugrani. Júniustól egyelőre még laza a naptáram, oda könnyebb tervezni.

A teljes üzleti transzparencia jegyében, és hogy könnyű legyen kalkulálni leírom az óradíjamat: 20000 Ft + ÁFA / óra. Azaz egy nap igen intenzív (lóg a nyelvem a nap végére tényleg, nagyon intenzív gondolkodást igényel a munka) optimalizálás 160e + ÁFA. Valaki erre biztos azt mondja, ez sok, megértem. Valaki viszont, aki már találkozott 30-40 ezres konzulenssel, annak ez olcsónak számít, tudva, hogy nem szoktam az ügyfeleknél húzni az időt, hanem nyomom a munkát nagy erővel. Egy nap alatt ki lehet végezni legalább 5-15 top lekérdezést, amitől nagyon meg szoktak könnyebbülni a szerverek. Azaz ennyi pénzből garantáltan jelentősen fel fog gyorsulni a szerver. Nyilván csodát nem tudok tenni, ha egy alkalmazás több ezer apró lekérdezéssel old megy egy feladatot, akkor a network roundtrip idején nem tudok segíteni, hiába gyorsítok fel egy lekérdezést pár msra. Vagy ha össze kell szummázni egymilliárd sort, az nehéz gyorsan megcsinálni. Azaz architekurális problémákon nem tud segíteni a db oldali hangolás.

A másik, amire fel kell készülni, hogy néha módosítani kell a hívó kódokon is, illetve időnként módosítani kell az adatbázis szerkezetét is. Én demonstrálom, mivel jár, ha ezt megteszi az ügyfél, aztán a döntés az övé, meglépi-e? Mivel ezeket nem lehet azonnal meglépni, amikor ott vagyok, gyakori, hogy az éles rendszerbe bevezetett módosításokat még egyszer át kell néznem. Ezt már legtöbbször TeamViewerrel vagy RDP-vel szoktam itthonról megtenni, mivel ez 1-2 óránál már nem visz el többet, nem éri meg ennél többet utazni miatta.

Hogy hatékony legyen a munka annyit szoktam kérni, hogy legyen jogom monitorozni az éles szervert, és legyen egy tesztszerver, ami egy restorolt adatbázist vagy adatbázisokat tartalmaz az éles rendszerről.
Ezen dokumentáltam be tudom mutatni, hogy ha az éles szerveren végrehajtják azokat a változtatásokat, amiket javaslok, akkor mennyivel lesz gyorsabb a rendszerük.

A munka része még, amikor átbeszéljük a fejlesztők vagy üzemeltetők fejében felmerült kérdéseket.

Az optimalizálási munkának egy hátránya van: mivel mindig az ügyféllel együtt végzem a munkát, és közben részletesen elmondom, mit és miért csinálok, általában már nem hívnak legközelebb, mivel kitanulják, mit kell tenni a lassulás esetén. :)

Ps. jövő héten lejár a 25%-os, 150e-es TDD tanfolyam akció, utána már csak ősszel fogok indítani tanfolyamot, mivel kilátásban van egy hosszabb projektem, így nem lesz rá időm. Aki akar, most szálljon fel a vonatra.

FORCESEEK hint – szeretem

Thursday, March 5th, 2015

Mindig imádom, ha valami geekséget be tudok vetni a gyakorlatban. SQL Server 2012-ben jött be az az újdonság, hogy a FORCESEEK hintnek meg lehet adni egy index oszlopait is.

A tanfolyamok kedvéért lehet találni példákat, aminek segít egy hint, de végre találtam valamit, ami élőben is demonstrálja a dolgot.

Az alábbi lekérdezés 1 perces tőzsdei adatokban keres adathiányt. Az AllTH összeszedi minden napra a nyitvatartási időket.

;with AllTH as
(
	select 
	D, 
	dateadd(DAY, 1, D) D1,
	cast(DATEADD(HOUR, DATEPART(HOUR, OpeningTime), DATEADD(MINUTE, DATEPART(MINUTE, OpeningTime), cast(D as datetime2))) as datetime2) ExpectedOpeningTimeAsDate, 
	cast(DATEADD(HOUR, DATEPART(HOUR, ClosingTime), DATEADD(MINUTE, DATEPART(MINUTE, ClosingTime), cast(D as datetime2))) as datetime2) ExpectedClosingTimeAsDate, 
	OpeningTime, ClosingTime from TradingHoursFlat
	where TickerId = @thTickerId
	and D >= (select min(TradeTime) from Tick where TickerID = @tickerId and tradetime > dateadd(day, -180, getdate()))
	and D < dateadd(day, -1, GETDATE())
),
FilteredBars as
(
	select cast(TradeTime as datetime) TradeTime,
	t.D, t.OpeningTime, t.ClosingTime
	from AllTH t
	join Tick b
	with(forceseek (IX_Natural_Key (TickerId, TradeTime)))
	--on t.D = cast(TradeTime as date) 
	on TradeTime between t.D and t.D1
	--on TradeTime between t.D and t.D+1
	where b.TickerID = @tickerId
	--and cast(TradeTime as time) between t.OpeningTime and t.ClosingTime
	and tradetime between t.ExpectedOpeningTimeAsDate and t.ExpectedClosingTimeAsDate
),
T as
(
	select a.D, min(TradeTime) ActualOpeningTime, max(TradeTime) ActualClosingTime from FilteredBars b 
	right join AllTH a
	on a.D = cast(TradeTime as date)
	group by a.D
), U as
(
	select a.D, ExpectedOpeningTimeAsDate, ActualOpeningTime, ExpectedClosingTimeAsDate, ActualClosingTime,
	DATEDIFF(MINUTE, ExpectedOpeningTimeAsDate, ActualOpeningTime) OpeningGap,
	DATEDIFF(MINUTE, ActualClosingTime, ExpectedClosingTimeAsDate) ClosingGap
	from T
	right join AllTH a
	on a.D = cast(t.ActualOpeningTime as date)
)
,V as
(
	select * from U
	where OpeningGap > @tolerance or ClosingGap > @tolerance
	or ActualOpeningTime is null or ActualClosingTime is null
)
select 
ROW_NUMBER() over(order by D) Id,
ExpectedOpeningTimeAsDate ExpectedOpeningTime,
ActualOpeningTime,
ExpectedClosingTimeAsDate ExpectedClosingTime,
ActualClosingTime,
case when ActualOpeningTime is null then 'MissingDay' else 'MissingIntradayData' end GapKind 
from V
order by D

A hivatkozott Tick táblában ebben a pillanatban ennyi sor van: 61646572157. Nem írtam el, ez 61 milliárd sor!

Itt van az SQL 2012-es hint barátunk:
join Tick b with(forceseek (IX_Natural_Key (TickerId, TradeTime)))

Furcsa módon hint nélkül a becsült plan 60x olcsóbb, de mégis, a hinttel rákényszerített (számomra, aki ismeri az adatokat logikus) plan sokkal gyorsabban fut le.
Ha nem írom ki az oszlopokat, vagy csak foreceseek vagy forcessek + index név nem veszi rá, hogy az én tervemet valósítsa meg.

Ezzel nem azt sugallom, hogy hinteljetek mint az állat, csak azt, hogy egyes határesetekben jól jöhet, ha tudunk róla.

Egy tipp még. Vigyázni kell, hogy ne keverjük a datetime2 különböző hosszúságú változatait, mert ezeket implicit konvertálni fogja a szerver, megint elesve a seekektől.

SQL fejtörő 7. – megoldás

Sunday, February 22nd, 2015

Kérdés: meg tudom-e nézni egy hosszan futó SQL DML lekérdezésnél, hogy a mögötte futó operátorok (join, index seek, stb.) hány százaléknál járnak?

SQL Server 2014-től igen!

Nézzük a következő (buta) lekérdezést:

SET STATISTICS PROFILE ON;
GO
--Next, run your query in this session
select * from [dbo].[Nums] n1 cross join dbo.Nums n2;

Ez egy lassú, sok soros cross join, csak demó célra. A vizsgálandó lekérdezést olyan sessionben kell futtatni, amiben a STATISTICS PROFILE be van kapcsolva.

Így néz ki a végrehajtási terve:

CrossJoinPlan

A terv fontos a következőkhöz, azért raktam ide. És most jön a lényeg: sys.dm_exec_query_profiles. Ő mutatja meg, belül mi zajlik:

SELECT  
   node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count, 
   CAST(SUM(row_count)*100 AS float) / SUM(estimate_row_count) PercentComplete
FROM sys.dm_exec_query_profiles 
--WHERE session_id=54
GROUP BY node_id,physical_operator_name
ORDER BY node_id;

A végrehajtás kezdetén így néz ki a kimenete (ha valaki tud tippet adni, hogy lehet ezt SyntaxHighlighterrel jól formázni megköszönöm):

node_id	physical_operator_name	row_count	estimate_row_count	PercentComplete
0	Parallelism		1320422		4294967295		0.030743470422631
1	Nested Loops		1323284		4294967296		0.0308101065456867
2	Clustered Index Scan	15		100000			0.015
3	Table Spool		1323284		1410065408		0.0938455757082157
4	Clustered Index Scan	400000		400000			100

Egy perccel később:

node_id	physical_operator_name	row_count	estimate_row_count	PercentComplete
0	Parallelism	15917761	4294967295	0.370614254002137
1	Nested Loops	15920504	4294967296	0.370678119361401
2	Clustered Index Scan	161	100000	0.161
3	Table Spool	15920504	1410065408	1.12906138322911
4	Clustered Index Scan	400000	400000	100

Mit jelen ez? A terv alapján van két clustered index scanünk. Az egyik felolvasta a táblában található 400000 sort, azzal ő végzett is. Aztán a cilusos join elkezdi hozzávenni a tábla sorait, minden sorhoz 400000-et. Az egész eredménye belekerül a Table Spoolba, majd csurog ki a Parallelism operátoron keresztül (ami Gather Stream műveletet hajt végre, azaz összegyűjti a több szál által összerakott sorokat egy streambe, mivel párhuzamos a terv).

Lássuk be, marha érdekes látni, hogy áll belül egy nagyobb DML művelet, nem?

Delayed Durability sebességnövekedés

Sunday, January 11th, 2015

Kipróbáltam az SQL Server 2014-ben megjelent Delayed Durability-t többféle scenarioban, ezzel a scripttel.
Laptopon, SSD háttérrel de kevés RAM-mal (8G) nem igazán okozott sebességnövekedést. Valaki használja már ezt élőben, rendes diszkekkel? Érdekelne, ott mennyit javít.

Hosszú műveletek állapotának megfigyelése SQL Serveren

Monday, December 22nd, 2014

Már fél napja fut egy alter index reorganize, honnan tudhatom, mennyi van még hátra?

Innen.

Az infó a sys.dm_exec_requestsben van. Ha még csak 0%-on áll a művelet, akkor nem jön vissza sor, pár perc kell ezeknek a lassú parancsoknak, hogy beinduljanak.

AlwaysOn ReadOnly replika olvasás

Monday, July 28th, 2014

Hangyál Zoli hívta fel a figyelmem egy finomságra. AlwaysOn, ReadOnly replika, szinkron kapcsolat. Egy adott módosítás hatása látszik-e azonnal a replikán, ha a primary-n a tranzakció commitról kaptunk visszajelzést?

SQL Server Change Tracking

Monday, May 19th, 2014

Ezt a cikket a technetben lehetett olvasni régebben, de kezdenek kiesni a régebbi tartalmak, azért bemásoltam ide a blogba is. Mivel tele van képekkel és táblázattal, inkább átkonvertáltam pdf-re, és nem htmlesítettem.

A teljes cikk itt olvasható:
SQL Server 2008 Change Tracking

Snapshot isolation level

Friday, May 2nd, 2014

Egy korábbi, 2008-ban írt cikkemet emeltem be ide, mivel láttam, hogy többen meg akarták nézni nálam, de nem sikerült, mert a technetre mutató küldő link már nem élt.

A snapshot szint az In-Memory tábláknál is fontos, így érdemes elolvasni a cikket, mivel a következő 2014-es cikk a párhuzamosságról fog szólni, ahol már ismertnek tételezem a snapshot ismeretét.

SQL Server 2014 újdonságok – 4. In-Memory OLTP – range indexek működése

Wednesday, April 30th, 2014

A korábbi részekből láttuk, hogy a memória táblák sorait nem IAM page-ek vagy clustered index tartja egyben, hanem egy index, amit a primary key mögé kell létrehozni. Ez lehet hash index is, de lehet egy nonclustered index is. Ezt memória tábláknál range indexeknek is szokták hívni, mivel ezzel lehet kisebb-nagyobb operátorokkal tartományokra is keresni, a hash index csak point lookupra jó.
Aki jól ismeri a nonclustered indexeket diszk tábláknál, annak azonnal beugorhat, hogy a nonclu index csak kevés sor esetén szokott hatékony lenni, sok sor esetén csak akkor, ha miden adat benne van az indexben. A fő időt a nonclustered index végéből a valódi adatokra átnavigálás viszi el, ezt hívák bookmark lookup vagy key lookupnak a planben.
Memória indexek esetén NINCS ez az overhead. Minden nclu index cover indexnek tekinthető, amint elértünk a levélszintre, azonnal “ott” van az adat. Mivel a sorok nincsenek lapokba szervezve, nincsenek page latch-ek sem.
Hogyan vannak szervezve a memória táblák nonclustered indexei?
Nyilván ez is egy fa, a normál B-Tree módosítása, Bw-Tree a neve. Aki mélységeiben akarja tanulmányozni, itt egy link az MS Research cikkhez.
A cél itt is a lock és latch mentes kialakítás volt. Az indexlapok nem fix méretűek, hanem változóak. Ha egyszer elkészült egy lap, az többé nem módosul. A hash indexeknél láttuk, hogy a lock free adatstruktúrák egyik implementációja, amikor nem módosítanak valamit, immutable az objektum, mert ahhoz szinkronizálni kellene a hozzáférést, hanem készítenek egy új példányt, majd az eredetit egy mozdulattal kicserélik az újra. Ezt az elvet használják a BwTreeben is.

BwTree

Az ábrában az a trükk, hogy az index lapok NEM egymás címét tartalmazzák, hanem a másik lap sorszámát a bal oldalon látható Page Mapping Table-ben. Azaz beraktak egy indirekciót a fa lapjainak összeláncolásába. Így már lehet azzal játszani, hogy sose módosítanak egy index lapot, hanem készítenek egy újat, és azt beláncolják a Page Mapping Table-be. Ez a már ismert CompareExchange felhasználásával.
A root lap láthatóan 3 irányba ágazik el. A 10 index kulcs alattiak a 3-as lapon lesznek, a 11-20 közöttiek a 2-esen, és a 21-28 közöttiek a 14-esen. Látható tehát, hogy minden index bejegyzés egy kulcs értéket és egy lapszámot tartalmaz, ami a következő szintre visz le.
A levélszintű lapok már közvetlenül a sorokra mutatnak. Ha egy kulcs értékhez több sor is tartozik, azok össze vannak láncolva a hash indexnél látható módon. (Diszk tábláknál a clustered indexeknél ha nem egyedi az index kulcs, akkor egy 32 bites számot generálnak mellé, hogy egyedivé váljon. Ott nincs ilyen láncolás, ezért kell ez.)

Közbenső szinten tehát úgy módosítanak egy index lapot, hogy lemásolják, módosítják, majd kicserélik a Page Mapping Table-ben a régit az új verzióra. Ez lock-free, de eléggé erőforrás igényes. Levélszinten ezt már nem lehet eljátszani, mert túl nagy lenne a költsége. Ott más trükköt használnak. Amikor módosul egy kulcsérték, akkor beraknak egy delta lapot az index lap elé. Ez a lap azt írja le, hogy az index lapon levő információt milyen módosítások értek. Az ilyen index lap picike, hisz csak egy kulcsot tartalmaz, a módosítás jellegét (insert vagy delete), illetve mutatót a “módosított” lapra.

Az alábbi ábrán egy insert majd időben utána egy delete lenyomata látszik.

BwTreeUpdate

Először beszúrtak egy 50-es index kulcsú sort. A Page Mapping Table a művelet előtt a fekete nyíl mentén az eredeti lapra mutatott. Az insert után beláncolják az index lap elé a pirossal jelzett delta rekordot. Így összerakható az infó, hogy az index lapon látható információkon kívül még van egy új sorunk.
Ha utána törlik a 48-as kulcsú sort, akkor újra beláncolnak egy delta rekordot, ezt mutatja a lila nyúl.
Mi a poén ebben? A delta lapokat van idő összerakni, nem kell kapkodni, lockolni. Amikor kész, rámutat a következő index lapra, ami vagy egy delta page, vagy egy igazi index lap, akkor már csak egy CompareExchange, és a Page Mapping Table máris az új lapunkra mutat, beláncoltuk a listába. Ha megelőztek, semmi gond, nekifutunk még egyszer. Nyilván ezért hívják ezt optimista megközelítésnek, mert arra számítunk, kevés ütközés lesz, így megéri nekik néha újragenerálni adatstruktúrákat, jobban, mint állandóan várni a másikra.

Az update-et egy insert majd egy delete-ként valósítanak meg, az két delta rekordot eredményez.

A sok delta rekord persze jól belassítaná a műveleteket, ezért azokat gondozni kell. Ha egy művelet eredményeképpen 16-nál hosszabbá válik a delta rekord lista, akkor a változások hatását az eredeti index lapra összepakolják egy új index lapra, majd kicserélik a régi lapot az újra. A régi lapokat elviszi a Garbage Collector (erről majd későbbi részben írok, a poros sarkokkal együtt).

Ha egy lap mérete eléri a 8k-t a sok insert miatt, akkor azt kettévágják (lehetne nagyobb, nincs 8ks lapméret mint a diszktábláknál, csak kiegyensúlyozatlan lenne a fa, ha túl sok sor lenne egy lapon). Ezt két atomi CompareExchange-dzsel valósítják meg.

Ha viszont a törlések miatt a lap mérete 800 byte alá esik, vagy már csak 1 sort tartalmaz, hozzácsapják a szomszédhoz a sorait, ezt is két atomi lépésben.

Összegezve, ha nagyon gyors point lookup kell, azaz egyedi vagy nagyon jól szóró kulcs mentén kell felolvasni sorokat, akkor egy jó bucket counttal megáldott hash index lehet az optimális megoldás, mivel a hash indexnek nincs ez a módosítási overheadje.

Ha viszont tartományra kell keresni, vagy nem nagyon egyediek az értékek, akkor a range index lesz hatékony.

A következő részben a párhuzamos módosítások szervezését nézzük meg (optimistic concurrency).

SQL Server 2014 újdonságok – 3. In-Memory OLTP – hash indexek belső kezelése

Monday, April 28th, 2014

Az előző rész indexére alapozva nézzünk meg két lekérdezést, hogyan tudja támogatni a hash index.
Először egy pontszerű lekérdezés, egyenlő operátorral:

select * from Person.Person_InMem
where ModifiedDate = '20120112'

Végrehajtási terv:
HashIndeSeek

Szépen használja az indexet, seekel.

Kisebb operátor esetén:

select * from Person.Person_InMem
where ModifiedDate < '20120112'

HashIndexScan

Kénytelen végignézni az összes sort, mivel a hash indexekkel nem lehet csak egyenlőségre keresni, ezért a scan operátor a tervben.

Az összetett hash indexek nem használhatók kevesebb oszlopra szűrésre, még akkor sem, ha az első oszlopokból válogatunk. Azaz egy FirstName, LastName összetett hash index csak egyszerre erre a két oszlopra képes seekelve szűrni, csak a FirstName-re nem, mivel a hash a két értékre együtt került kiszámítása, külön nem értelmezhető.

Hogyan tárolja a szerver a hash indexeket?

InMemoryTableRow

Az ábrából látszik, hogy minden sor tartalmaz n * 8 byte-os pointert, minden egyes ilyen pointer egy indexehez tartozik.
A hash függvény alapján kiadódik egy hash érték, ezzel beleindexelnek a hash indexbe, ami egy sima lineáris tömb. A tömb adott eleme azaz egy vödre rámutat egy sorra. De mivel több sornak is lehet azonos a hash-e az adott index kulcsa alapján, ezeket láncolják hozzá az előző sorhoz az index pointeren keresztül. Nézzük ezt meg egy ábrán:

HashIndices

A bogotai Susannak és a bécsi Susannak is ugyanaz a hash-e, hisz azonos az értékük. Az 5-ös vödör rámutat az egyik sorra, majd onnan a sorból az első index mutató keresztül lehet továbbmenni a következő sorra (láncolt lista).
A 4-es Name hash esetén a két Gregnek és Jane-nek azonos a hashe, azért ők hárman lesznek összeláncolva.
A City oszlopon létrehozott hash index 6-ot rendelt hozzá Liszabonhoz, Bogotához és Bécshez is, ezért ők hárman vannak összeláncolva.

Az ábrából látható, hogy a hash indexek nagyon hatékonyak, ha rövid a láncolt listájuk, de mi a helyzet a módosítással? Miért nem akadnak össze a szálak, amikor pl. törölnek egy sort a láncolt listából? Hogyan oldják ezt meg lock nélkül?

Minden műveletet a korábban már említett Compare Exchange-zsel oldanak meg. Ez egy 3 argumentumú művelet, a .NET doksiból:

public static T CompareExchange<T>(
	ref T location1,
	T value,
	T comparand
)

Összehasonlítja a location1-et a comparanddal, és ha egyeznek, akkor value-t belerakja location1-be. A visszatérési érték a location1 eredeti értéke, ez azért kell, hogy tudjuk, sikerül-e berakni value-t location1-be. Mindezt atomi módon, a processzor támogatásával végzi a gép.

A következő rész natív spekuláció, doksiban nem láttam kirészletezve.

Tegyük fel két versengő folyamat is be akar szúrni egy-egy sort úgy, hogy mindkét sor ugyanabba a vödörbe esik. Tegyük fel a vödörben már van egy sor. Akkor ennek a sornak a következő sorra mutató pointere null lesz.
Bejön az első folyamat. Létrehozza az új sort a levegőben. Ránavigál a hash index vödörből az első sorra, majd végrehajt kb. egy ilyen kódot:

CompareExchange(ref első sor következő pointere, a beszúrandó sor címe, null)

Ha ez a folyamat volt a gyorsabb, akkor az első sor következő pointerét nullként látta, tehát ő tudja hozzárendelni a sorát a next pointerbe, ő tudta magát beláncolni a sorba.
A lassabb folyamatnak már nem nullt lát localtion1-ben, azaz az első sor next pointerében, ezt észreveszi abból, hogy a CompareExchange visszatérési értéke nem null. Ilyenkor mit kell tennie? Ránavigálni a következő (a másik által az előbb beszúrt sorra), és ott újra bepróbálkozni. Előbb-utóbb csak ő is első lesz.
Miért nagyon gyors ez a folyamat? Mert egyáltalán nincs zárolás nem hogy nagyobb felbontással, lap szinten, de még sor szinten sem.
A törlések kezelése már macerásabb lenne, ha így akarnák megoldani. Csakhogy a törlések teljesen másképp mennek, mivel a snapshot izolációs szint miatt meg kell tartani a sor korábbi verzióját is, nem lehet csak úgy kitörölni. Ennek megvalósításáról is lesz szó a párhuzamosságról szóló részben, de előtte még megnézzük a normál indexek hogyan működnek, a következő részben.

SQL Server 2014 újdonságok – 2. In-Memory OLTP – hash indexek

Tuesday, April 22nd, 2014

A memóriatáblákhoz kötelező minimum 1 indexet létrehozni, és ez az index egy hash index kell legyen. Mivel kötelező rajtuk egy primary key is, ezért az első indexen nem kell sokat gondolkodni, az a pk mögötti index lesz.
A hash index hasonló mint a .NET Dictionary vagy bármely más rendszer hash táblája. Az index értékekből hasht képeznek, az azonos hash-ű sorokat összeláncolják pointerek mentén. Az adatok NEM lapokon vannak elhelyezve, mivel a lapok a diszk adatok miatt voltak kialakítva a normál táblák esetén. Mivel nincsenek lapok, nem veszekednek a szálak a lapok latch-elésén se (mint a .NET lock kulcsszó), így sokkal nagyobb párhuzamosságot érnek el, mint a korábbi struktúra esetén. Diszk tábláknál ha egy lapon sokan matatnak egyszerre, be kell várják egymást. Itt minden sor szinten van tárolva, azaz sokkal finomabb felbontású régiókat kell védeni, ráadásul a módosítás sose helyben történik, de erről majd a konkurenciáról szóló részben írok.

Nincsenek GAM, SGAM, PFS, IAM és más egyéb szokásos adatösszetartó lapok, csakis az indexek tartják egyben egy tábla sorait. Ezért kell minimum 1 index a táblára.
További indexeket is létre lehet hozni, összesen max. 8-at. Azért ennyit, mert az ütköző hash-ek miatt össze kell láncolni a sorokat, így minden sorba be van építve fixen hash index számú pointer a láncoláshoz, de be kellett határolni a számukat, ne fogjanak el túl sok helyet. Így néz ki egy sor, a végén vannak a láncolt listákhoz a pointerek :

InMemoryTableRow

Az első mezőkről majd a párhuzamosság kezeléséről (concurrency) szóló részben írok.

A hash indexnél fontos a BUCKET_COUNT, ez szabályozza mennyire szórjon a hash függvény. Ha túl kicsire vesszük, hosszú láncolt listák alakulnak ki egy bucketen belül. Ha túl nagy, feleslegesen eszi a memóriát, és lassítja a scaneket, mivel minden bucketet végig kell néznie scan során. Mivel 64 biten dolgozunk, egy pointer 8 byte, így hash index mérete BUCKET_COUNT * 8 byte, csak mindig a legközelebbi, 2 hatványra kerekítik belülről. Azaz 1000-et megadva 1024 vödör keletkeze, ami 8192 byte (ez persze a gyakorlatban nagyon pici lenne, nem lenne hatékony index). Érdemes akkorára venni, mint ahány különböző értéke van a kulcsnak * 1-2. Érhető, hisz így nem nagyon lesz sokelemű a láncolt lista egy bucketen belül, kevés lesz az ütközés.
Primary keynél könnyű a helyzet, mivel egyedi értékeket tartalmaz. Ha a tábla például 3 millió sort tartalmaz, és várhatóan a közeljövőben se nő mondjuk 5 millió fölé, akkor egy hasonló nagyságrendű BUCKET_COUNT jó lehet. Később, ha sokkal több sor lesz, majd újra létrehozzuk a táblát más BUCKET_COUNT-tal. Ebből látszik, hogy a normál táblákkal szemben itt nem annyira önhangoló minden, mint megszoktuk.
Normál oszlopoknál egy select count(distinct oszlop) lekérdezéssel könnyű megnézni, mennyi egyedi érték van a táblában. Ha mondjuk 100 különböző érték van egy 5000 soros táblában, akkor egy nagy BUCKET_COUNT esetén is csak 100 bucketben lesz érték, azaz a bucketekben átlagban 50 sor lesz, amiben már csak lineárisan lehet keresni. Magyarul, erre nem jó a hash index, ide sima fa alapú indexre lesz szükség, mivel azt is létre lehet hozni a memória táblákon.

Nézzük meg a gyakorlatban!

Hozzunk létre egy memória táblát az AdventureWorks Person táblája adataival:

CREATE TABLE [Person].[Person_InMem]
(
	[BusinessEntityID] [int] NOT NULL,
	[PersonType] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[NameStyle] [bit] NOT NULL CONSTRAINT [DF_Person_InMem_NameStyle]  DEFAULT ((0)),
	[Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Person_InMem_EmailPromotion]  DEFAULT ((0)),
	[rowguid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Person_InMem_rowguid]  DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_InMem_ModifiedDate]  DEFAULT (getdate()),

CONSTRAINT [PK_Person_InMem_BusinessEntityID] PRIMARY KEY NONCLUSTERED HASH 
(
	[BusinessEntityID]
)WITH ( BUCKET_COUNT = 1000)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

INSERT INTO [Person].[Person_InMem]
           ([BusinessEntityID]
           ,[PersonType]
           ,[NameStyle]
           ,[Title]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Suffix]
           ,[EmailPromotion]
           ,[rowguid]
           ,[ModifiedDate])
SELECT [BusinessEntityID]
      ,[PersonType]
      ,[NameStyle]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[EmailPromotion]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [Person].[Person]

Az eredeti táblából kihagytam az xml oszlopokat, azok nem támogatottak memória táblákban. Hash bucket számosságnak 1000-et adtam meg. A táblában 19972 sor van. Mivel primary key-ről van szó, ugyanennyi különböző érték van benne. De csak 1024 bucketünk van, azért átlagosan kb. 20 sor kerül egy bucketbe. Nézzük meg, igaz-e az elmélet, az új dm_db_xtp_hash_index_stats nézettel:

SELECT 
   object_name(hs.object_id) AS 'object name', 
   i.name as 'index name', 
   hs.total_bucket_count,
   hs.empty_bucket_count,
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',
   hs.avg_chain_length, 
   hs.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats AS hs 
   JOIN sys.indexes AS i 
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id
where object_name(hs.object_id) = 'Person_InMem'
object name    index name                        total_bucket_count   empty_bucket_count   empty_bucket_percent   avg_chain_length     max_chain_length
-------------- --------------------------------- -------------------- -------------------- ---------------------- -------------------- --------------------
Person_InMem   PK_Person_InMem_BusinessEntityID  1024                 0                    0                      19                   38

Látható, hogy az átlagos lánc hossza egy bucketben 19, de van legalább egy 38 hosszú is, mivel a hash függvény nem teljesen egyenletes.
Jó ez nekünk? Nagyon nem. Ideális esetben 1-2 tétel van csak egy bucketben, de 10 fölött már erősen érezhető lesz a teljesítményveszteség. Látható, hogy az üres bucketek (áttérek a vödör szóra, de elég hülyén hangzik) száma 0. Ez is probléma, “nincs hely” az új sorok részére, azaz még nagyobb torlódás lesz később. Az útmutató szerint az üres vödrök száma legalább 33% kell legyen, hogy legyen hely a táblának növekedni.

Próbáljuk meg magasabbra venni a vödrök számát, mondjuk 10000-re. Mivel memória tábláknál nincs alter table, újra létre kell hozni, más paraméterekkel, majd újratölteni. Ez után a hash statisztikák:

object name    index name                        total_bucket_count   empty_bucket_count   empty_bucket_percent   avg_chain_length     max_chain_length
-------------- --------------------------------- -------------------- -------------------  ---------------------- -------------------- --------------------
Person_InMem   PK_Person_InMem_BusinessEntityID  16384                4933                 30                      1                   8

Na, ez már sokkal jobban néz ki. 16384 vödör van (10000 után ez volt a következő 2 hatvány), 30%-a a vödröknek üres, átlagosan 1 elem van minden vödörben (nyilván kicsit több, mivel 19000 sor van 16000 vödörre).

De nem mindig lehet csak a vödrök számával ilyen szép helyzetet előállítani, ha túl sok ismétlődés van az adatokban. Hozzunk létre egy második indexet is, ezúttal a FirstName oszlopra:

SELECT count(distinct FirstName) FROM [AdventureWorks2012].[Person].[Person]; --1018

CREATE TABLE [Person].[Person_InMem]
(
	[BusinessEntityID] [int] NOT NULL,
	[PersonType] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[NameStyle] [bit] NOT NULL CONSTRAINT [DF_Person_InMem_NameStyle]  DEFAULT ((0)),
	[Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Person_InMem_EmailPromotion]  DEFAULT ((0)),
	[rowguid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Person_InMem_rowguid]  DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_InMem_ModifiedDate]  DEFAULT (getdate()),

CONSTRAINT [PK_Person_InMem_BusinessEntityID] PRIMARY KEY NONCLUSTERED HASH 
(
	[BusinessEntityID]
)WITH ( BUCKET_COUNT = 10000)
,
INDEX [ix_FirstName] NONCLUSTERED HASH
(
	[FirstName]
) WITH ( BUCKET_COUNT = 1000)
)
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

De nem jön létre az index, mert:
Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.

Ez van, a hasht gyorsan kell tudni előállítani, ebben a verzióban legalábbis nem mentek bele, hogy a nyelvek nemzeti sajátosságait kihasználva is képezzenek hasht. Ezekre lehet majd rendes fa alapú indexet létrehozni, de most koncentráljunk még a hash indexekre.

Lássuk a dátum oszlopon:

SELECT count(distinct [ModifiedDate]) FROM [AdventureWorks2012].[Person].[Person] --1290

CREATE TABLE [Person].[Person_InMem]
(
	[BusinessEntityID] [int] NOT NULL,
	[PersonType] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[NameStyle] [bit] NOT NULL CONSTRAINT [DF_Person_InMem_NameStyle]  DEFAULT ((0)),
	[Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Person_InMem_EmailPromotion]  DEFAULT ((0)),
	[rowguid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Person_InMem_rowguid]  DEFAULT (newid()),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_InMem_ModifiedDate]  DEFAULT (getdate()),

CONSTRAINT [PK_Person_InMem_BusinessEntityID] PRIMARY KEY NONCLUSTERED HASH 
(
	[BusinessEntityID]
)WITH ( BUCKET_COUNT = 10000)
,
INDEX [ix_ModifiedDate] NONCLUSTERED HASH
(
	[ModifiedDate]
) WITH ( BUCKET_COUNT = 1000)
)
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

Dátum oszlopra már megy a hash index.

A hash statisztikák:

object name    index name       total_bucket_count   empty_bucket_count   empty_bucket_percent   avg_chain_length     max_chain_length
-------------- --------------- -------------------- -------------------  ---------------------- -------------------- --------------------
Person_InMem   ix_ModifiedDate                 1024                 262                      25                   26                  143

Csapnivalóak az eredmények. Bár van 25% szabadon, azaz a vödrök számát nem lőttük alá, mégis átlagosan 26 sor van egy vödörben. Miért? Mert minden dátum kb. 20-szor szerepel a kulcsban, nem elég egyedi az oszlop. Az ilyenekre nem igazán hatékony a hash index.
A következő részben megnézzük, hogyan tárolja és hogyan használja a hash indexeket az SQL Server.

Memória táblák meghajtása OR mapperrel

Tuesday, April 22nd, 2014

Az SQL Server 2014-es memóriatáblákat csak akkor lehet a lehető legközvetlenebb módon elérni, ha natív kódra fordított tárolt eljárásokkal érjük el. Az OR mapperek viszont alapban sima, dinamikus SQL-eket generálnak. Ha így akarunk CRUD műveleteket végrehajtani egy memória táblán, akkor az átmegy a szerver interop rétegén, ami jelentősen belassítja azt. Többszörösére. Emiatt -jelen tudásom szerin-, ha OR mapperel akarjuk meghajtani a memória táblákat, akkor saját spket kell írni. Kipróbáltam a dolgot, de falakba ütköztem. Ugyanis az OR mapperek elvárják, hogy pl. egy update után visszaadja a szerver az xxx rows affected information message-et, ebből tudja az OR mapper, hogy sikerült a sor módosítása. Ha nem jön vissza semmi, akkor azt hiszi, hogy az optimista konkurencia ellenőrzés miatt nem sikerült a sor módosítása (azt hiszi valaki közben módosította vagy törölte a sort), így exceptiont dob. Viszont a memória táblás update NEM adott vissza XXX rows affected üzenetet, így az OR mapper exceptiont dob (NHibernate.StaleObjectStateException és ).
A megoldás tehát valószínűleg az lehet, hogy ki kell kapcsolni az OR mapper optimista konkurrencia ellenőrzőjét.
Egyelőre NHibernate-nél nem találtam megoldást rá, legalábbis a Conf ORM-es “code first” mapping módszerrel. EF-en még nem próbáltam.
Mindenesetre kiírtam msékhez a kérdést, ha jön válasz, megírom.