Archive for the ‘SQL Server 2005’ Category

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.

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.

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 magas CPU használat nyomozás és megoldás

Wednesday, March 19th, 2014

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.

SQL Server mirroring monitorozása

Thursday, August 30th, 2012

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.

Megoldás a deadlockos cikkhez

Friday, July 20th, 2012

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

Friday, July 20th, 2012

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.

Furcsa deadlockok

Thursday, July 19th, 2012

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:

Érdekes SQL programozási hibák

Wednesday, April 27th, 2011

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.

option(recompile) nem mindig működik

Thursday, April 14th, 2011

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. :)

Adding External References to SQL CLR Projects

Friday, February 25th, 2011

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

SQL Server Profilerben TSQL hibák vizsgálata

Wednesday, February 16th, 2011

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?

SQLXMLBulkload

Monday, February 7th, 2011

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

How to Share Data Between Stored Procedures

Thursday, January 6th, 2011

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

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

Thursday, May 13th, 2010

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.

SQLCLR deplyment hiba

Wednesday, January 27th, 2010

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.

Random orderby Linq és EF használatával

Monday, October 12th, 2009

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.

Deadlock bulk load során

Tuesday, August 4th, 2009

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.