Archive for May, 2012

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

Wednesday, May 30th, 2012

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

SQL Server 2012 újdonságok – 5. A THROW parancs

Tuesday, May 29th, 2012

Az SQL Server 2005-ben bevezetett TRY/CATCH hibakezelés óriási előrelépést jelentett az átlátható TSQL hibakezelés terén. Más nyelvekhez viszonyítva egy valami azonban hiányzott a képből, a THROW, amivel ki lehet váltani egy kivételt (exceptiont). Volt persze egy hasonló, a RAISERROR, ám ez kicsit más, mint a többi programnyelv throw-ja. A throw ugyanis általában kétféle dolgot tud. Az egyik, hogy feldob egy kivételt, a másik, hogy egy elkapott kivételt továbbdob. Ez utóbbi re-thrownak hívják, és azért nagyon hasznos, mert try-catch blokkban le tudjuk kezelni az eredeti hibát, pl. visszagörgetjük a tranzakciót, naplózunk vagy bármi egyéb akciót hajtunk végre, majd a catch ág végén újra feldobhatjuk az eredeti hibát. Így a kliens alkalmazás vagy SQL hívó pontosan megkapja az eredeti hiba részleteit, például a kivétel sora nem a catch ágba rakott (re)throw-ra mutat, hanem az eredeti hibát kiváltó SQL sorra.
A THROW tehát hasonló helyeken használatos, mint a RAISERROR, de van közöttük pár fontos különbség. A legfontosabbak: a THROW nem tud rendszer kivételeket generálni (SQL Server belső hibaüzeneteket), nem használja a sys.messages táblát (ebben vannak az előre definiált hibaüzenetek) és mindig 16-os severity levelű kivételeket dob.
Az alábbi példában egy update az, ami hibát dobhat, ha beleütközik egy idegen kulcsba. A kivételt elkapjuk, lelogoljuk, majd újradobjuk a kliens részére. A re-throw miatt a hibanaplózó logika nem szól bele a kliens által kapott hibarészletekbe.

BEGIN TRY
	--Ez hibázhat
	update HumanResources.Employee 
	set BusinessEntityID = -1 
	where BusinessEntityID = 2;
END TRY
BEGIN CATCH
	--Naplózzuk a hibát
	INSERT INTO 
	[dbo].[ErrorLog]
    ([ErrorTime]
    ,[UserName]
    ,[ErrorNumber]
    ,[ErrorSeverity]
    ,[ErrorState]
    ,[ErrorProcedure]
    ,[ErrorLine]
    ,[ErrorMessage])
	VALUES(SYSDATETIME(), 
	SUSER_NAME(),
	ERROR_NUMBER(),
	ERROR_SEVERITY(), 
	ERROR_STATE(), 
	ERROR_PROCEDURE(), 
	ERROR_LINE(), 
	ERROR_MESSAGE());
  
  --Újradobjuk a kivételt a kliens részére
  THROW;

END CATCH

A kliens ezt kapja:

Msg 547, Level 16, State 0, Line 3
The UPDATE statement conflicted with the REFERENCE constraint "FK_EmployeeDepartmentHistory_Employee_BusinessEntityID". The conflict occurred in database "AdventureWorks2012", table "HumanResources.EmployeeDepartmentHistory", column 'BusinessEntityID'.

Látható, hogy ez az eredeti hiba, a 3. sorra hivatkozik, nem a 29.-re, ahol valójában a THROW van. Az errorlog táblánkba ugyanez az információ kerül be:

Nyilván nem csak újradobásra jó a THROW, hanem olyan helyeken is, ahol eddig RAISERROR-ral dobtunk fel saját hibát.
Ez:

RAISERROR(N'Az alkalmazott nem törölhető, mert vannak hozzá kapcsolt élő feladatok', 16, 1);

így néz ki THROW-val:

THROW 50000, N'Az alkalmazott nem törölhető, mert vannak hozzá kapcsolt élő feladatok', 1;

A hívó mindkét esetben ezt kapja:

Msg 50000, Level 16, State 1, Line 1
Az alkalmazott nem törölhető, mert vannak hozzá kapcsolt élő feladatok

Összegezve, ha saját hibákat szeretnénk a kliensek felé közvetíteni, illetve, ha meg kell tartani az eredeti hibáról szóló információkat, akkor a THROW-t érdemes használni. Speciális esetekben, pl. nem 16-os szintű hibákhoz, vagy ha a sys.messagesben előre definiált hibaüzenetet kell feldobni, akkor továbbra is elérhető a RAISERROR.
Egyébként az a tény, hogy a THROW nem használja a sys.messages táblát igen fontos lépés, mert így könnyebb költöztetni az adatbázist másik szerverre, nem kell átvinni egyedi hibaüzeneteket a master táblák között. Ez kapcsolódik az itt nem tárgyalt, SQL 2012 újdonság Contained Databases újításhoz is.

SQL brossúra akció

Monday, May 21st, 2012

Kővári Attilával és Bitemo Erikkel közösen írtunk egy SQL Server 2012 anyagot, amelyet a termékbemutató konferencián kaptak meg a résztvevők. Ebben tömören összefoglaltuk a szerintünk legfontosabb újdonságokat, mindenki a saját területén.

Attilával meghirdetünk egy akciót: aki 2012. június 15-ig ír nekem vagy neki, hogy szeretne egy példányt kapni az anyagból, annak ingyen, mindenféle kötelezettség nélkül elküldjük azt.
Értelemszerűen a levélben legyen benne a név és cím.

Az akciót nem az MS szervezi, tehát a kérések hozzám és Attilához futnak be, nem kaptok majd office ajánlatokat az MS-től akció után. :)

Az akció miértjéről Attila bővebben írt, érdemes szétnézni nála. Nálam is hasonló a helyzet, csak annyi a különbség, hogy a borítékot nem a gyerekek fogják felcímezni, mert akkor senki nem kapná meg, max. a postás a szívinfarktust. :)

Rss2Mobi: Google Reader -> .mobi

Monday, May 7th, 2012

Mióta van Kindle DX-en nem vagyok hajlandó képernyőről olvasni (pedig 32″-os, van rajta hely).
Amit rá tudok tolni Kindle-re, arról tudok, másról nem.
Sima könyveket, whitepaperöket Calibre-vel töltök fel rá.
Az msdn és technet magazinokhoz nemrég írtam (mármint nem én írtam, a csak itt a blogban írtam róla) egy kis letöltő programról.
Ami hiányzott még, az a blogok olvasása Kindle-lel.
Az rss2mobi egy kis python programocska, amivel le lehet tölteni a Google Readerbe betárazott feedeket. Egyelőre még nem teljesen komfortos a kimenete (sok lap a tartalom, amire nincs szükségem), de határozottan jó, ha van kis szabad időm, akkor olvashatok némi blogot. Kicsit majd átszabom, legalább látok pythont. Like. :)

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

Monday, May 7th, 2012

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