Soci (Soczó Zsolt) szakmai blogja

2016.06.19.

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

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

2015.06.24.

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

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

2015.06.16.

Intra query deadlock

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.

2015.03.12.

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

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.

2014.05.19.

SQL Server Change Tracking

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

2014.05.02.

Snapshot isolation level

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.

2014.03.19.

SQL Server magas CPU használat nyomozás és megoldás

Az utóbbi hetekben szinte minden nap SQL Servert hegesztek valahol. Leírom az összes eset tanulságát, lássuk a legutóbbit.
Adott egy igen magas forgalmú SQL Server, 1000 batch/sec körüli a terhelése. A lekérdezések derekasan optimalizálva voltak a megrendelő által, másképp nem is bírta volna produkálni a szerver ezt az áteresztő képességet (de gáz ez a szó, nem egy intim betétről írok).
A lassú lekérdezések listájában volt egy gyanúsan egyszerű, de rengetegszer meghívott lekérdezés. Ez nem volt indokolt, ezért ezt most már csak egyszer hívják meg a kliensben, aztán eltárolják az eredményét. Ezt jó észben tartani, hiába 2 lapolvasásos, seekes egy kveri, ha nagyon sokszor hívják meg, le tudja ez is fogni a szervert.
Tovább nézve látszott, hogy egy olyan trigger belseje vitte el az időt, amit még én javasoltam nekik 1-2 éve. :) A trigger feladata eladásoknál módosítani a készletet, így nem kell szummákkal kiszámítani listázáskor az aktuális készletet. Denormalizált adatokat frissített. Ezzel azt lehet majd tenni, hogy a forrástábla nem minden oszlopa módosítja a készletet, így egy IF UPDATE(…)-tel le lehet majd csökkenteni a számítások számát.
A következő pont a recompile-ésönök vizsgálata volt. A legtöbb lekérdezés ad-hoc queryként ment be, a procedure cache kihasználása gyenge volt, 60% körüli. Mivel a hibajelenség a nagy CPU terhelés volt, gyanús volt, hogy a sok fordítás-tervgenerálás eszi a procikat (8 mag, 16 logikai proc 90%-on).
A lekérdezéseket megvizsgálva kiderült a gyenge cache reuse oka: a batch-ek elejére kommentek voltak írva, amelyek kliensenként egyediek voltak. Így ugyanaz az ad-hoc query sok százféle verzióban is bement a szerverre, ami így nem tudta hatékonyan cachelni.
A kommenteket kivéve a plan reuse felment 90% fölé. :)
A harmadik dolog még egy update statistics with fullscan volt. A statisztikák rendkívül fontosak az SQL Server működéséhez, aki nem hallott még róla olvasson utána, megéri. Normál esetben, nagy tábláknál csak mintavételezve frissíti a statisztikákat a szerver, érthető módon spórolva az erőforrásokkal. Viszont egyenetlen eloszlású oszlopok esetén ez rossz hisztogramokat eredményezhet, ami miatt rossz tervet fog generálni a szerver. Ezen segíthet, ha időnként futtatunk full scanes update statisticset is. Például hétvégenként. Erre van egy nagyszerű kis sql csomag, hamarosan írok róla.

Zárásként tanulságul, az SQL kommentek messze nem olyan kis ártatlan teremtések, mint gondolnánk.

Update: az elfelejtettem leírni, hogy az eredeti 90%-ról lement 50%-ra a procihasználat.

2012.08.30.

SQL Server mirroring monitorozása

Ha a mirroringgal gond van, arról fontos azonnal tudni. Két esetet szoktunk figyelni. Az egyik a state change, azaz ha valamiért átvándorol a kiszolgálás az egyik lábról a másikra, illetve, ha feltorlódnak a tranzakciók valamelyik oldalon.

A monitorozásra vannak beépített dolgok, ezekről itt lehet bővebben olvasni, itt szinte minden infó megvan az alertezés felépítéséhez.
Két problémás pontba akadtunk bele. Az előbbi cikkben a WMI események hatására csak számok jöttek vissza státuszként, ezek nem túl beszédesek egy emailben. Ezeket elég könnyű visszafejteni stringekké:

declare @msg nvarchar(4000) = 'State of $(ESCAPE_SQUOTE(WMI(DatabaseName))) database changed to ''';
declare @state nvarchar(50) = '$(ESCAPE_SQUOTE(WMI(State)))';
declare @newStateString nvarchar(100) =
case @state
when '2' then 'Synchronized Principal without Witness'
when '7' then 'Manual Failover'
...
end;

set @msg += @newStateString + ''' on $(ESCAPE_SQUOTE(WMI(StartTime)))';

EXEC msdb..sp_send_dbmail @profile_name='SQL Server 2008 Mirroring Notifications',
@recipients='foo@bar.com',
@subject= 'DB Mirroring Alert',
@body=@msg;

Lehetne elegánsabban is lookup táblával, de a célnak ez megfelelt.

A másik kérdés macarásabb. Egy job percenként mintavételezi a mirror queue-jainak a mértét, és ezeket beírja egy rendszertáblába. Ha valamelyik túlmegy egy megadott határértéken, akkor ez beírja az event logba, onnan egy alert ki tudja olvasni, és emailt küldeni róla. Csak éppen a lényeg nincs ebben benne, melyik adatbázissal van a baj. Ez bug, ez van.
Hogy valami képünk mégis legyen már email alapján mi a gond, az alábbi jobot futtatjuk le a threshold alert alapján:

EXEC msdb..sp_send_dbmail @profile_name='SQL Server 2008 Mirroring Notifications',
@recipients='foo@bar.com',
@subject= 'DB Mirroring Alert',
@execute_query_database = 'msdb',
@query = '
select * from (select distinct(database_id) id from dbo.dbm_monitor_data) d
cross apply
(select top 5 cast(DB_NAME(database_id) as nvarchar(20)) db, local_time, redo_queue_size, send_queue_size
from dbo.dbm_monitor_data
where ((send_queue_size > 0 or redo_queue_size > 0) and local_time > GETDATE() - 3  and database_id = d.id)) t
order by db, local_time desc'

Ez az utóbbi 3 nap torlódásait küldi el emailben, ebből már látszik, melyik db akadt el.

2012.07.20.

Megoldás a deadlockos cikkhez

Heuréka! :)

Előzmény.

Az előzményben látható első deadlock lenyomata xmlben így néz ki:

<deadlock-list>
	<deadlock victim="process3c3dc8">
		<process-list>
			<process id="process3c3dc8"
					 taskpriority="0"
					 logused="236"
					 waitresource="KEY: 6:72057594959101952 (8194443284a0)"
					 waittime="8196"
					 ownerId="724347"
					 transactionname="user_transaction"
					 lasttranstarted="2012-07-17T13:30:06.643"
					 XDES="0x9dec3b0"
					 lockMode="U"
					 schedulerid="3"
					 kpid="2744"
					 status="suspended"
					 spid="85"
					 sbid="0"
					 ecid="0"
					 priority="0"
					 trancount="1"
					 lastbatchstarted="2012-07-17T13:30:06.643"
					 lastbatchcompleted="2012-07-17T13:30:06.643"
					 clientapp=".Net SqlClient Data Provider"
					 hostname="AlmaDEVWEB02"
					 hostpid="1312"
					 loginname="BuildAgentUser"
					 isolationlevel="repeatable read (3)"
					 xactid="724347"
					 currentdb="6"
					 lockTimeout="4294967295"
					 clientoption1="673316896"
					 clientoption2="128056">
				<executionStack>
					<frame procname="AlmaDev.dbo.spExecuteTransaction"
						   line="136"
						   stmtstart="7378"
						   stmtend="7554"
						   sqlhandle="0x0300060069e52875f254de0091a000000100000000000000">
						SELECT @toBalance = Balance FROM Account WITH (UPDLOCK) WHERE Id = @toAccountID
					</frame>
				</executionStack>
				<inputbuf>
					Proc [Database Id = 6 Object Id = 1965614441]
				</inputbuf>
			</process>
			<process id="processbe41288"
					 taskpriority="0"
					 logused="236"
					 waitresource="KEY: 6:72057594959101952 (8194443284a0)"
					 waittime="2505"
					 ownerId="724314"
					 transactionname="user_transaction"
					 lasttranstarted="2012-07-17T13:30:06.610"
					 XDES="0x9af4e80"
					 lockMode="X"
					 schedulerid="2"
					 kpid="4716"
					 status="suspended"
					 spid="108"
					 sbid="0"
					 ecid="0"
					 priority="0"
					 trancount="2"
					 lastbatchstarted="2012-07-17T13:30:06.610"
					 lastbatchcompleted="2012-07-17T13:30:06.610"
					 clientapp=".Net SqlClient Data Provider"
					 hostname="AlmaDEVWEB02"
					 hostpid="1312"
					 loginname="BuildAgentUser"
					 isolationlevel="repeatable read (3)"
					 xactid="724314"
					 currentdb="6"
					 lockTimeout="4294967295"
					 clientoption1="673316896"
					 clientoption2="128056">
				<executionStack>
					<frame procname="AlmaDev.dbo.spExecuteTransaction"
						   line="138"
						   stmtstart="7556"
						   stmtend="7830"
						   sqlhandle="0x0300060069e52875f254de0091a000000100000000000000">
						UPDATE Account SET
						Balance += (@amount - @commissionAmount)
						WHERE Id = @toAccountID
					</frame>
				</executionStack>
				<inputbuf>
					Proc [Database Id = 6 Object Id = 1965614441]
				</inputbuf>
			</process>
		</process-list>
		<resource-list>
			<keylock hobtid="72057594959101952"
					 dbid="6"
					 objectname="AlmaDev.dbo.Account"
					 indexname="PK_Account"
					 id="lockc99fc00"
					 mode="U"
					 associatedObjectId="72057594959101952">
				<owner-list>
					<owner id="processbe41288"
						   mode="U"/>
				</owner-list>
				<waiter-list>
					<waiter id="process3c3dc8"
							mode="U"
							requestType="convert"/>
				</waiter-list>
			</keylock>
			<keylock hobtid="72057594959101952"
					 dbid="6"
					 objectname="AlmaDev.dbo.Account"
					 indexname="PK_Account"
					 id="lockc99fc00"
					 mode="U"
					 associatedObjectId="72057594959101952">
				<owner-list>
					<owner id="process3c3dc8"
						   mode="S"/>
				</owner-list>
				<waiter-list>
					<waiter id="processbe41288"
							mode="X"
							requestType="convert"/>
				</waiter-list>
			</keylock>
		</resource-list>
	</deadlock>
</deadlock-list>

Figyeljük meg, hogy mindkét processz a “KEY: 6:72057594959101952 (8194443284a0)” kulcson várakozik, ezen vesztek össze.

Viszont az xmlben nem az van, mint a képen!!!

Az xml vége felé látszik ki-mit ownol. owner-list/owner elemek:

		<resource-list>
				<owner-list>
					<owner id="processbe41288" mode="U"/>
				</owner-list>
				<waiter-list>
					<waiter id="process3c3dc8" mode="U" requestType="convert"/>
				</waiter-list>
			</keylock>
				<owner-list>
					<owner id="process3c3dc8" mode="S"/> <!-- Ez nem U !!! -->
				</owner-list>
				<waiter-list>
					<waiter id="processbe41288" mode="X" requestType="convert"/>
				</waiter-list>
			</keylock>
		</resource-list>

A második processz nem U lockot tart az erőforráson, hanem S-t, Shared lockot! De miért U látszik a képen? A bugos kutya életbe!
Így már kristálytiszta a dolog. Azt nem értettem, hogy tudott kiadni az SQL Server két U lockot ugyanarra az erőforrásra. Sehogy, az elmélet jó. Csak az SSMS bugos.

Tehet, mit látunk itt tképpen? Az egyik processz tart U lockot a közös erőforrásra (owner id=”processbe41288″ mode=”U”), a másik pedig egy Shared lockot (owner id=”process3c3dc8″ mode=”S”). Miért shared az egyik? Mivel valamelyik korábbi sorban (ez nem látszik az xmlből, már lefutott) lemaradt a with(updlock) a select utáni tábla mögül.
Ez az alapállás. Aztán az egyik Update lockra akar konvertálni (waiter id=”process3c3dc8″ mode=”U” requestType=”convert”, ez nem teljesen világos miért, mivel eleve az van neki. A másik Sharedről eXclusive-ra akar konvertálni. Ez már a nem megy, mivel az X nem kompatibilis az U-val, így az nem adható ki, a második processznek várnia kell, de mivel az első által kért U sem a U-val, így összeakadnak.
Míg most sem teljesen tiszta minden, az XML-ben sem látni az összes részletet, pl. miért van az U-U konverzió, de a megoldás viszonylag 100%-ra a következő: át kell nézni a beteg tárolt eljárást, és meg kell keresni benne olyan select-et, amely az Account táblát nem updlockkal érik el. Ezekre fel kell rakni az updlockot, és valószínűleg kiesik a deadlock.
Tanulság: nem elég a deadlock graph grafikus nézetét megnézni, bele kell kukkantani az xml-be is, ha valami nem logikus.
Ezt tudva már más is találkozott ezzel: http://rusanu.com/2010/05/12/the-puzzle-of-u-locks-in-deadlock-graphs/
Szóval még nem teljesen tiszta minden, de az IT már csak ilyen, félinformációk alapján is tudni kell hibát keresni.

Ártatlan kis 1 lapos lekérdezések

Csalóka az adatbázis. Sokan félreértik, mire való, és a 90-es évek stílusában minden vackot rábíznak, ettől persze megfekszik szegény. A félreértés onnan ered, hogy az MS akkor azt mondta, spben kell megírni az üzleti logikát (mivel a VB használhatatlan volt, a C++ meg a legtöbb ember kezében csak bug gyártásra jó). A Delphi ezért élte a fénykorát.
Na szóval, amikor szegény dbre sok üzleti logikát bíznak, akkor mivel őbenne nem lehet a hagyományos módon cache-elni mint egy appszerveren, így az összes piszlicsáré, a büdös életben nem változik de legyen a dbben mert az jó adatot is táblákból kell kiolvasni. Pl. az enumok értékét, amit azért nem szoktunk megváltoztatni a dbben, mert ehhez úgyis a programozott enum értékeket is át kéne írni, ráadásul a kettőt egyidőben szinkronban.
Így aztán a tízmilliós vasú SQL Server olyan dolgokon dolgozik, ami nem az ő feladata. 1 lapolvasás nem a világ, és különben is memóriában vannak a pici táblák, nem jelent ez nagy terhelést.
Valódi cacheből, appszerveren felolvasni egy pl. egy stringet pár nanonsecundum.

Nézzük meg ezt SQL Serveren belülről:

declare @i int = 1000000, @a varchar(200)

while (@i > 0)
begin
	select @a = s.Value from Setting s where s.Name = 'TestSetting1';
	set @i-=1;
end

A Setting tábla két soros, és rendes voltam, raktam clustered indexet a Name oszlopra. 15mp-ig tart a ciklus, a select nélkül kevesebb, mint 1 mp (kb. 800 millisec).
Azaz a select általi kiolvasás kb. 15us, míg ugyanez az appszerveren nanosec nagyságrendű. Nem mindegy.
Ha már ennyire szerelmesek vagyunk az adatbázisba, akkor még azt meg lehet tenni, hogy paraméterként adjuk át neki ezeket a well-known beállításokat.

Pl.

create proc Teszt1
as
declare @a varchar(200)
select @a = s.Value from Setting s where s.Name = 'TestSetting1';
go

create proc Teszt2
@par varchar(200)
as
declare @a varchar(200) = @par
go

declare @i int = 1000000, @a varchar(200)
while (@i > 0)
begin
	exec Teszt1;
	set @i-=1;
end
go

declare @i int = 1000000, @a varchar(200)
select @a = s.Value from Setting s where s.Name = 'TestSetting1';
while (@i > 0)
begin
	exec Teszt2 @a;
	set @i-=1;
end

28 vs 12 mp, látszólag nem nagy deal, de egy terhelt szervernél minden gramm számít.

Ps. sok cégnél láttam ilyeneket, senki ne vegye magára.

2012.07.19.

Furcsa deadlockok

Mostanában valahogy utolérnek a code review jellegű munkák, amiket egyébként nagyon szeretek, mivel ezek általában intenzív, agyalós nyomozások szoktak lenni, amit imádok.
Az előzőben deadlockok jelentek meg egy SQL Server alapú rendszerben, nagyobb terhelés esetén. A deadlockokat általában elég egyszerű megfogni, az SQL Server Profiler Deadlock graph tálcán kínálja az összeakadt folyamatokat és erőforrásokat. A legtöbb deadlock vagy conversion deadlock, amelyek repeatable read izolációs szinten végrehajtott select-update vált ki, vagy a más sorrendben elért táblák miatt előálló deadlock.
Esetünkben a selectek ki voltak egészítve with(updlock)-kal, amely pont a konverziós deadlockot hivatott megakadályozni (nem kell a shared lockot átkonvertáltni update lockká, mivel már a select is update lockot használ).
No, de ami most más volt mint szokott, hogy általam eddig nem látott volt a deadlock graph. Megörökítettük az utókornak, íme (shift clickkel kell megnyitni, így külön ablakban nyílik meg, így össze lehet nézni a szöveggel):

Mi a furcsa benne? Középen van a két erőforrás, amely esetünkben ugyanaz a tábla. Ez még ok, de a HoBtId is ugyanaz, azaz ugyanarról a sorról van szó.

Update: ez marhaság. a HobtID csak azt mondja meg, hogy azonos allokációs egység, heap vagy index (btree). De az, hogy ugyanarra a sorra várakoznak igaz, a waitresource=”KEY: 6:72057594959101952 (8194443284a0)” attributumból látszik, de ez nem látszik a grafikus nézeten.

Még ez is rendben van. Nézzük meg, milyen lockok vannak kiosztva! A bal és a jobb oldali processz is update lockot tart az erőforrásokon, amelyek a korábbiak alapján úgy néz ki egy erőforrást reprezentálnak. Na, itt van a bibi. Két update lock nem kompatibilis, pont erre találták ki őket, hogy ne szeressék egymást. Akkor hogy tud kiosztani a szerver mégis ilyeneket? Igazából ez a talány, utána már természetes, nem tudnak fogni még egy update és egy exclusive lockot a közös sorokra. Mi a túró történik itt?

Nekem van egy bűnbakom, aki ezt a furcsaságot csinálja, de csak pár nap múlva írom le, amikor már többet tudok róla, hogy a módosítások után megjavult-e a rendszer.

Aki látott már ilyet, kérem ossza meg velünk.

Zárásul csemegének itt van egy nagyobb gráfocska, amikor többen is összeakadnak:

2012.06.04.

SQL Server 2012 újdonságok – 7. Új TSQL függvények

Filed under: Adatbázisok,SQL Server,SQL Server 2008 R2,Szakmai élet — Soczó Zsolt @ 22:35

Számos új függvény teszi egyszerűbbé a mindennapi TSQL programozást. A függvények egy része az Excel vagy az Access függvényeihez hasonló, megkönnyítve az ezeken írt alkalmazások átírását SQL Server 2012-re.
Számos új dátumkezelő függvényünk van. Az eomonth visszaadja egy adott dátumhoz tartozó hónap utolsó napját (kezelve szökőéveket, stb.). A második paraméterével át lehet térni egy másik hónapra is:

declare @currentdate datetime = '20120225';
select 
eomonth (@currentdate) as 'EOMMonth',
eomonth (@currentdate, -2) as 'EOM2MonthsEarlier',
eomonth (@currentdate, 1) as 'EOM1MonthLater';

Dátum összerakása a komponenseiből:

declare @Year int = 2011;
declare @Month int = 2;
declare @Day int = 29;

select datefromparts(@Year, @Month, @Day) AS Result;

Hasonló függvények készültek a többi típushoz is: datetimefromparts, datetime2fromparts, datetimeoffsetfromparts, smalldatetimefromparts, timefromparts.
A típusok közötti konverzió sokkal egyszerűbbé vált. Egyrészt most már van try_convert, ami az eddigi convert függvénnyel ellentétben nem dob hibát, ha nem sikerül a konverzió, hanem nullt ad vissza:

select TRY_CONVERT(float, 'test') F
, TRY_CONVERT(datetime2, '2012-05-15') D1
, TRY_CONVERT(datetime2, '2012-05-15.') D2
, TRY_CONVERT(datetime2, '2012-05-45') D3
, TRY_CONVERT(datetime2, '25/04/2020', 103) D4;	--British/French: dd/mm/yyyy

A .NET-ből ismert alaptípusok Parse, TryParse és ToString metódusát is kivezették, így ezekkel gazdag, szabályozható formátumú és kultúrafüggő értelmezési és formázási lehetőségek jelentek meg az SQL Serverben.

SELECT PARSE('7/17/2011' AS DATE USING 'en-US') AS dt1,
PARSE('2011.07.17.' AS DATE USING 'hu-HU') AS dt2;

Az en-US angol nyelv, USA-ban értelmezve, a hu-HU magyar nyelv, Magyarországon értelmezve.
A format függvény segítségével a .NET-es típusok ToStringjét hívhatjuk meg, amivel rendkívül egyszerű összetett formázásokat is megvalósítani:

declare @Price decimal(19,4) = 13542.1251;
declare @Date datetime2(0) = '20120425 15:13:00';
select 
	--Pénzösszegek
	format(@Price, 'C4', 'hu-hu') HunPrice, --13 542,1251 Ft
	format(@Price, 'C4', 'en-us') UsPrice,  --$13,542.1251	
	--Dátumok
	format(@Date, 'D', 'hu-hu') HunDate,    --2012. április 25.
	format(@Date, 'D', 'en-us') UsDate,     --Wednesday, April 25, 2012 
	format(@Date, 'd', 'hu-hu') HunDate2,
	format(@Date, 'd', 'en-us') UsDate2,
	format(@Date, 'f', 'hu-hu') HunDate3,
	format(@Date, 'f', 'en-us') UsDate3,
	--Custom format string
	format(@Date, 'yyyy.MM.dd') HuncutDate3,
	--Számok formázása
	format(@Price, 'F2', 'hu-hu') HunNumber,
	format(@Price, 'F2', 'en-us') UsNumber,
	format(@Price, '00000', 'en-us') LeadingZerosNumber,
	format(@Price, 'N2', 'hu-hu') HunGroupedNumber,
	format(@Price, 'N2', 'en-us') UsGroupedNumber;

Az eddig case-ekkel megvalósított logikai kifejezések egy részét egyszerűbben is meg tudjuk fogalmazni az iif és a choose függvényekkel:

select 
a = iif(1=1, 'cica', 'kutya'),	--cica
b = iif(1=2, 'cica', 'kutya'),      --kutya
n = iif(null = 1, 'cica', 'kutya'); --kutya

A choose az n. argumentumot adja vissza, 1-gyel kezdődő sorszámmal:

select 
choose(1, 'a', 'b', 'c') as 'first', 
choose(2, 'a', 'b', 'c') as 'second';

A concat függvénnyel szövegeket lehet egybefűzni. Abban különbözik a + operátortól, hogy mivel a paraméterei stringek, ha más típus megy be paraméterül, az automatikusan stringgé konvertálódik, így biztosan stringként kerül összefűzésre . A másik különbség, hogy a NULL paraméterek helyett üres stringet helyettesít be, míg a + operátor alap set opciók esetén NULL-t ad vissza.

declare @PurchaseOrderNumber int = 123;
select concat('PO', @PurchaseOrderNumber) PO1, 
concat('PO', NULL, @PurchaseOrderNumber) PO2;

Bár az adatok formázása általában nem az adatbázis, hanem a megjelenítési réteg feladata, ennek ellenére sokszor kellett eddig is például számokat vagy dátumokat formázni SQL-ben is. Az új függvényekkel a .NET formázások teljes repertoárja elérhető az SQL programozók részére is.

2012.05.30.

SQL Server 2012 újdonságok – 6. Spatial fejlesztések

Geometriai adatok tárolása és kezelése az SQL Server 2008-ban jelent meg. A különböző alakzatok adatait nem csak tárolni képes a szerver, hanem sokféle műveletet végezni rajtuk, és képes indexelni a síkbeli és a föld felületén értelmezett görbült adatokat, így például két alakzat közötti távolságkeresést nagyon gyorsan tudja végrehajtani.
Az SQL Server 2012-ben a következő főbb fejlesztések történtek (nem teljes lista):

  • A földgömb felszínén értelmezett geography típus metódusai most már mindent tudnak, amit a geometry típus (eddig egyes metódusok nem voltak rajta implementálva).
  • Az a korlátozás, hogy egy alakzat nem nyúlhat át a másik félgömbre megszűnt
  • Új alakzatok jelentek meg (CircularString, CompoundCurve, CurvePolygon). Ezen íves alakzatok járulékos előnye, hogy a Buffer metódus mellett, amely képes körbevenni egy alakzatot egy másik (elnagyoltabb és távolabbi) alakzattal, megjelent a BufferWithCurves, amely nem csak egyenes vonalakkal dolgozik, így sokkal kevesebb alakzatból létre tudja hozni a körvonalat.
  • Részletes hibaüzenetek, ha nem valid egy alakzat (nem zárt, stb.).
  • Sok új metódus, többek között:
    • ShortestLineTo: legrövidebb út két alakzat között egyenesként visszaadva.
    • Új aggregáló függvények: UnionAggregate, EnvelopeAggregate, CollectionAggregate, ConvexHullAggregate. Ezek igen hasznosak, amikor sok kis darabból nagyobb alakzatokat összerakni.
  • A belső számítások eddig 27, most már 48 bites pontossággal dolgoznak.
  • Auto grid. Az alakzatok indexeléséhez a síkot felosztják egyre kisebb négyzetekre, és rögzítik, hogy az alakzat mely négyezeteket fedi le. Eddig nekünk kellett megadni a maximum 4 szint felbontását (hányszor hányas négyzetrácsra fektessék rá az alakzatot). Most már automatikusan képes dönteni a szerver a felbontásról, ráadásul olyan jól, hogy az indexelt keresések általában jelentősen gyorsabbak lesznek, mint a korábbi verzióban.
create spatial index idxgeog
on table(column) using GEOGRAPHY_AUTO_GRID;
  • Tömöríthető spatial indexek: kb. felére nyomhatók össze a spatial indexek, 5-10% CPU veszteség árán.
  • A leggyakoribb feladatra, amikor alakzatok között kell legkisebb távolságot számolni (pl. útvonaltervezésnél) 10-30-szoros gyorsulás érhető el az SQL 2008-hoz képest, ha a lekérdezésünket úgy írjuk meg, hogy az optimizer az ún. Nearest Neighbor Query Plan-t tudja használni.
  • sp_help_spatial_geography_histogram és sp_help_spatial_geometry_histogram tárolt eljárások az adatok eloszlását mutatja meg (az SQL Server Management Studio Spatial fülén vizuálisan is).

Íme egy Nearest Neighbor Query Plan-t használó lekérdezés:

DECLARE @g geography = 'POINT(18.919358	47.460025)';	--Budaörs
SELECT TOP(10) geom.ToString(), 
geom.STBuffer(5), 
Telepules, Utca, Hazszam1
FROM [HungarySpatial].[dbo].[Cim] 
--with(index (idx_Cim_Spatial_1))
with(index (idx_Cim_Spatial_Uj))
WHERE geom.STDistance(@g) IS NOT NULL
ORDER BY geom.STDistance(@g);

--Index nélkül: 280mp
--2008-as index-szel 13mp
--2012-es index-szel 0.1mp!

--Régi index
CREATE SPATIAL INDEX idx_Cim_Spatial_1 ON Cim (geom)
USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM), 
CELLS_PER_OBJECT = 16)

--Új, automatikus felbontású spatial index
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

CREATE SPATIAL INDEX idx_Cim_Spatial_Uj
ON Cim (geom) 
USING GEOGRAPHY_AUTO_GRID;

A spatial támogatás egy gyakran mellőzött része az SQL Servernek, pedig még az ingyenes Express is támogatja. Az Entity Framework kliensoldalról könnyen elérhetővé teszi a térbeli adatokat, így egyszerűen írhatunk izgalmas és hasznos alkalmazásokat, amelyek térbeli adatokkal dolgoznak (legközelebbi célállomás, térkép vizualizáció, stb.)

2012.05.07.

SQL Server 2012 újdonságok – 4. FORCESEEK, FORCESCAN hintek

A FORCESEEK hint SQL Server 2008 óta létezik. Ezzel azt lehet súgni a query optimizernek, hogy egy lekérdezés kiértékelése során inkább használjon nonclustered index seeket, table vagy clustered index scan helyett. Ez ekkor hasznos, ha egy paraméterezett lekérdezés a tipikus paraméterekre kevés sort ad vissza, így a nonclustered index seek az optimális adatelérő stratégia, de időnként becsusszannak olyan paraméterek is, amelyek scant igényelnek, mert már nem éri meg a sok indirekt adatelérés, ami a nonclustered index seek velejárója. Ilyenkor a szerver helyesen scant választ, azaz inkább lineáris kereséssel végigmegy az egész táblán. Mivel a szerver eltárolja és újrahasznosítja a végrehajtási terveket, ha pont elsőre egy ilyen terv generálódott, akkor a további lekérdezéseket is ezzel hajtja végre. Ez azonban nem optimális sebességet ad a tipikus, pár sort visszahozó lekérdezésekre. Ezt a bizonytalanságot tudja stabilizálni a FORCESEEK hint. A hint hatására szívesebben használja a nonclustered index seeket az SQL Server. Ez rossz hatással lesz az atipikus, ritkán beeső, de sok sort visszaadó lekérdezésekre, de a tipikus, kevés sort visszaadókra stabilabb tervet és válaszidőket kapunk.
SQL Server 2012-ben a FORCESEEK újdonsága, hogy meg lehet adni egy összetett index oszlopait vagy azok egy részhalmazát, hogy pontosabban specifikáljuk, mely index oszlopokat szeretnénk, ha használná az optimizer.
Nézzünk egy példát. Van egy új akciója az internetes kutyatápboltnak, a Bodri májkonzerv, amely 2-es SpecialOfferID-val fut, és szeretnénk másodpercenként frissítve látni egy 50 colos plazma képernyőn azokat az megrendeléseket, amelyben 10-nél több dobozzal rendeltek. A lekérdezésnek nagyon hatékonynak kell lenni, de nem akarunk új indexet létrehozni a táblákon.
Adott egy ilyen index a SalesOrderDetail táblán:

CREATE NONCLUSTERED INDEX IX_Comp1
ON Sales.SalesOrderDetail (OrderQty, SpecialOfferID, UnitPrice);

A lekérdezés így néz ki:

select * from [Sales].[SalesOrderDetail]
where OrderQty = 10 and SpecialOfferID = 2;

Az SQL Server által választott végrehajtási terv NEM használja a fenti indexet, Clustered Index Scant használ:

A lekérdezés becsült költsége 1.18mp, és 1240 logikai IO művelettel, 8 kByte-os lap olvasásával oldotta meg a szerver (végignézte a teljes táblát).
A FORCESEEK eddig is ismert alakjával rávehetjük az indexünk használatára:

select * from [Sales].[SalesOrderDetail]
with(forceseek)
where OrderQty = 10 and SpecialOfferID = 2;

A Properties ablakban megnézve azonban látszik, hogy az Index Seek műveletben csak az OrderQty oszlopra használta ki az indexet, pedig a lekérdezésben benne van egy másik szűrt oszlop is:

Seek Keys[1]: Prefix: [AdventureWorks2012].[Sales].[SalesOrderDetail].OrderQty = Scalar Operator((10))

Ennek becsült költsége 1.91mp, azaz a FORCESEEK hinttel csak rontottunk a dolgon, az IO is felment 2365-re. Ennek oka, hogy mivel csak az OrderQty-re szűrt az indexben, a szűrés után még elő kell venni az adatsorokat (Key Lookup, 768 sor), és azokat tovább szűrni a SpecialOfferID = 2 feltételre (Filter operátor).

Az SQL Server 2012-ben kibővített FORCESEEK-kel (amúgy 2008R2 SP1-ben is benne van) meg lehet mondani a szervernek, hogy márpedig próbálja meg használni az indexet mindkét oszlop szűrésére:

select * from [Sales].[SalesOrderDetail]
with(forceseek (IX_Comp1 (OrderQty, SpecialOfferID)))
where OrderQty = 10 and SpecialOfferID = 2;

A végrehajtási tervből eltűnt a Filter, ami jó jel, az Index Seek szűrése pedig magában foglalja mindkét oszlopot:

Seek Keys[1]: Prefix: [AdventureWorks2012].[Sales].[SalesOrderDetail].OrderQty, [AdventureWorks2012].[Sales].[SalesOrderDetail].SpecialOfferID = Scalar Operator((10)), Scalar Operator((2))

Az így kapott lekérdezés becsült költsége továbbra is 1.91mp, azonban az IO leesett 4-re! Az eredeti 1240 helyett 4 lett az IO, azaz több mint 300-ad részére csökkent!
A végső verdiktet az mondja ki, hogy ha megmérjük a tényleges végrehajtási időket. 10000 végrehajtás alapján az első, hint nélküli lekérdezés ideje 238mp, az egyszerű hinttel 38mp, az új hinttel 2mp.
120-szoros gyorsulást értünk el az új hint formátummal!

A FORCESCAN teljesen új hint, ezzel scanre vehetjük rá a szervert akkor is, ha seekelne magától. Ennek haszna akkor van, ha tudjuk, hogy a lekérdezés sok sor érint, ezért a seek nem lenne optimális, így mindenképpen scant akarunk. időnként miért választ az SQL Server mégis seeket? Vagy, mert nem frissek a statisztikái, ezért kevés sort vár el, vagy, mert valami oknál fogva nem tudja jól megbecsülni a várható sorok számát. Főleg join-os lekérdezéseknél nehéz a dolga, mert az oszlopszintű eloszlási statisztikáit ilyenkor nem lehet pontosan használni.
Az ilyen eseteket könnyű megismerni, mert ilyenkor Index Seek-et használó Nested Loop Joinokat hajt végre a szerver sok ezer vagy millió soron, Hash Join helyett. Ezekben az esetekben a FORCESCAN hinttel rá lehet venni, hogy inkább ne használjon indexet, ne szaladjon neki sokszor a táblának, hanem inkább egyszer menjen végig az egész táblán, mert a még mindig kisebb költségű.
Nem csak joinos lekérdezéseknél, hanem sima szűréseknél is előfordulhat, hogy rosszul becsüli meg a szerver a feldolgozandó sorok számát, így seekel scan helyett, akkor stabilizálhatjuk a tervet, hogy mindig scant használjon a FORCESCAN hinttel.
Összegezve, ha tudjuk, hogy a seek vagy a scan előnyös a lekérdezésünknek, akkor határozottá tehetjük a várakozásunkat a szerver felé, ezáltal kiszámíthatóbb tervet, így kiszámíthatóbb válaszidőket kapunk.

Update a cikkhez: amikor a fenti demót írtam, akkor még csak RC1 volt. Miután upgradeltem az RTM-re, hirtelen magától is tudta a jó tervet, nem kellett hint. Vagy okosodott, vagy csak nem voltak frissek a statisztikák, amikor a demót írtam. Az elv mindenesetre látszik a cikkből, és örülünk, ha a szerver okos. :)

2011.04.27.

Érdekes SQL programozási hibák

Előző héten két érdekes hibát láttam ügyfeleknél.
Az egyikben láttam, hogy a lekérdezés végrehajtási tervében konverzió van, datetime -> integer, emiatt scan volt seek helyett, azaz lassú volt a lekérdezés.
Ami fura volt ebben, hogy egy izeID oszlop volt összehasonlítva egy datetime értékkel. Az ID-k tipikusan intek, megnézve a táblát tényleg az volt. Volt egy lokális változó, ami copy-paszta miatt datetimera sikerült int helyett, és ezzel írták tovább a where szűrést. A lekérdezés már 2 éve ment élesben. :)
Ami érdekes volt benne, hogy funkcionálisan jól működött, a számok szépen konvertálódtak dátumokká és vissza, nem volt vele gond, csak lassú volt az egész. Bizarr hiba.

A másik esetben egy ilyen lekérdezés volt:

declare @a int
while valami
begin
  select @a = oszlop from tabla where ...
end

Furcsa volt, hogy a @a furcsa módon tartalmazott értéket akkor is, amikor nem érintett sort a select a where miatt.
Jobban belegondolva ez nem is fura, hisz a select NEM ad értéket a változónak, ha nem érint sort, és NEM is nullázza ki. Első iterációkor a @a null, hisz nem kapott értéket, a második iterációnál meg benne volt az első futás eredménye, ami már nem null volt, bár azt várták. Nem nagy hiba, de időrabló tud lenni.

2011.04.14.

option(recompile) nem mindig működik

Hirtelen egymás utáni napokon 3 cégnél égetett be az SQL Server 2005-től létező option(recompile), miután nem működik.

A hint célja az lenne, hogy a konkrét paraméterértékek ismeretében kérünk egy újrafordítást, így az optimizer ki tud dobni felesleges ágakat a lekérdezésből, ezzel nagyon hatékony terveket tud létrehozni egyes speciálisabb lekérdezésekhez.
Pl. gyakori, hogy a paraméterre csak akkor kell szűrni, ha nem null, ha null, jöjjön vissza minden sor:

… where @param is null or oszlop = @param

Ez alapban scan lenne, lassú. Az option(recompile) hatására azonban ha a @param null, akkor teljesen kiesik a sor, ha nem null, akkor meg leegyszerűsödik erre:

… where oszlop = @param

Ez meg már jó kis gyors seek lesz, megfelelő index esetén.

Mi itt a gond? Csak az, hogy ez sok verzión NEM működik. Pedig működött. :)

Az ok a következő. SQL 2005-ben még nem működött. 2008-ra megcsinálták, ment, csakhogy egy igen durva bugot is beleszereltek: ha többen hajtanak végre ilyen hintelt lekérdezést, akkor összekeveredhetnek az eredményhalmazok. Ez az innye, bazmeg típusú bug. Gyorsan ki ki kommenteltek pár sort, mivel megvarrni meg nem olyan egyszerű, mint elrontani. Így ez most nem megy az újabb SQL Servereken.
Erland barátunk szerint az R2 RTM NEM tartalmazza még a javított verziót (magyarul helyesen működik, nem nem gyorsít).
Viszont ez az R2 CU1 már javítja.
Ezen doksi szerint már 2008-hoz is van javítás, a CU5-ben.

Már csak le kéne cseréni a cégeknek a 2005-öt. :)

2011.03.03.

Insert… select … minimálisan logolt módú az SQL 2008-ban

Filed under: Adatbázisok,SQL Server 2008,SQL Server 2008 R2,Szakmai élet — Soczó Zsolt @ 19:56

Ha az insert table with (tablock) módon írjuk a lekérdezést, és az heap, akkor bulk insert lesz a sima insert. Nem kevés idő megtakarítást jelent ez.

2011.02.25.

Adding External References to SQL CLR Projects

Ha egy SQLCLR eljárásból kell meghívni valamilyen külső komponenst.
A trükk az, hogy a külső assembly-t is be kell telepíteni az SQL serverbe CREATE ASSEMBLY-vel, mivel az csak onnan hajlandó betölteni assemblyket (pár fw. alap dllt kivéve).

2011.02.16.

SQL Server Profilerben TSQL hibák vizsgálata

SQL tanfolyamot tartok egy cégnél (.NET 4-et és Design Patternst meg egy másiknál :). Az oktatások arra jók, hogy mindig rájövök mit nem tudok, így aztán van módom újat tanulni. Azt meg általában szeretek. :)

Na szóval, ha a szerver oldalon történik egy exception valamilyen sql parancs végrehajtási hiba következtében, akkor ez szépen látszik a profilerben, csak az nem, mi váltotta ezt ki.
Most lapozgattam az Inside Microsoft SQL Server 2005: Query Tuning and Optimization könyvet, és ott belefutottam a megoldásba.
Nem csak az Errors And Warnings: Exception eseményt kell bekapcsolni, hanem a User Error Message-et is.
.
A képen látható, az EventSequenceből szépen összerakható az események egymásutánisága.
Ez megint egy olyan apró kérdés volt, amit 5 perc guglizás megold, de hát nem ilyenekkel van tele az ember feje?

2011.02.07.

SQLXMLBulkload

Már el is felejtettem, hogy van ilyen. Öregszem. :)

Newer Posts »

Powered by WordPress