Soci (Soczó Zsolt) szakmai blogja

2012.05.21.

SQL brossúra akció

Filed under: Adatbázisok,Felhívás,SQL Server,SQL Server 2012,Szakmai élet — Soczó Zsolt @ 07:22

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

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

2012.04.18.

SQL Server 2012 újdonságok – 3. Paging

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

A legtöbb alkalmazás tartalmaz olyan funkciót, amelyekben rendezett, nagy eredményhalmazokat kell kisebb darabokban, lapozva megjeleníteni. Gondoljunk pl. a keresés funkcióra, amikben ha sok eredmény jönne vissza, nem zúdítjuk azt az alkalmazás nyakába, csak megmutatjuk az első például 10 eredményt, majd lehetőséget biztosítunk a felhasználóknak, hogy megnézhessék a 11-20. eredményt, stb.
Általában ezt nagy eredményhalmaz egy kis részének megjelenítését hívják lapozási feladatnak. Korábbi SQL Server verziókban erre többféle megoldást is használtunk. Lehetett alkalmazni szerveroldali kurzorokat, trükkös egymásba ágyazott ORDER BY-os és TOP-os lekérdezéseket, SQL Server 2005-től a ROW_NUMBER sorszámgeneráló függvényt WHERE megállási feltétellel kombinálva, ínyenceknek CTE-be csomagolva. Ahogy haladtunk előre az újabb SQL Server verziók felé, úgy lett egyre egyszerűbb ilyen lekérdezéseket írni.
Az SQL Server 2012-re kialakult az a formátum, aminél már valószínűleg nem lehet tovább egyszerűsíteni a feladat megoldását.
Az ORDER BY kiegészült a lapozást lehetővé tevő új elemekkel: ORDER BY OFFSET n ROWS és FETCH NEXT n ROWS ONLY. A n érték lehet változó vagy paraméter is, így paraméterezett tárolt eljárásokban is kényelmesen használható a lapozás.
Például az AdventureWorks2012 adatbázis Person táblájából ha leválogatjuk az A betűvel kezdődő keresztnevű személyeket, akkor 2014 sort kapunk vissza:

SELECT FirstName, LastName, BusinessEntityID
FROM [AdventureWorks2012].[Person].[Person]
where FirstName like 'A%'
order by FirstName, LastName;

Az első 33 sor:

Ennyit nem biztos, hogy a felhasználók nyakába szeretnénk önteni, de könnyedén visszaadhatjuk lapozva is az eredményeket:

SELECT FirstName, LastName, BusinessEntityID
FROM [AdventureWorks2012].[Person].[Person]
where FirstName like 'A%'
order by FirstName, LastName
offset 20 rows
fetch next 10 rows only;

A lapozott eredményhalmaz:

Látható, hogy Aaron Foster található az első sorban, ő volt a 21. az eredeti eredményhalmazban.
A lekérdezés végrehajtási terve közel azonos egy TOP operátoros lekérdezés végrehajtási tervével (ami természetes, hisz egy top művelet van a háttérben, csak előtte van egy skip is), így a már ismert elvek alapján indexekkel könnyen optimalizálható a SELECT végrehajtása.

2012.03.30.

SQL Server 2012 újdonságok – 2. Sequence

Filed under: Adatbázisok,SQL Server,SQL Server 2012,Szakmai élet — Soczó Zsolt @ 09:08

Update:
A cikkben NEM folytonos sorszámgenerálásról írok, mint pl. amit számla sorszámozásnál használnak, hanem pusztán egyedi kulcsok generálását, amik nem ismétlődnek, monoton nőnek, de nem folytonosak.

A sorszámgenerálás igen általános feladat adatbázisalapú alkalmazásokban. Az egyik leggyakoribb eset, amikor egész számokra épített mesterséges elsődleges kulcsoknak kell értéket generálni. Erre eddig a leggyakrabban alkalmazott megoldás az IDENTITY-vel megjelölt oszlop használata volt. IDENTITY-nél az oszlopon kijelölt sorszámgenerálás és az insert művelet össze volt ragadva. A SEQUENCE nevű új adatbázis objektum segítségével mindentől teljesen független sorszámgenerátorokat hozhatunk létre, amelyeket bármikor meghívhatunk, nem csak insertekhez.
Miért jó ez a sorszámgenerálás és a felhasználása elválasztása? Képzeljük el azt az estet, hogy el kell menteni megrendeléseket. Van 1000 megrendelés fejlécünk (Order Header), és mindegyikhez van átlagban 5 megrendelés tétel (Order Detail). IDENTITY -t használva egyesével be kell szúrni a fejléc sorokat, visszavezetni az adatelérő rétegbe a generált identity értéket, átírni az adott fejléc alá tartozó megrendelés tételek szülő fejlécre mutató idegen kulcsát a kapott értékkel, majd beszúrni a megrendelés tételeket. A megrendelés tételeket be lehet szúrni egy batch-ben, azaz egy körülfordulás alatt. Azonban a fejléc sorok identity-jének visszavezetése miatt 1000 külön batch-et kell beküldeni a szülő sorok beszúrásához, majd további 1000-et a gyerek sorok kedvéért. Ez összesen 2000 hálózati körülfordulás, pedig már kötegeltük a gyerekek beszúrását, e nélkül 1000+5000=6000 körülfordulás lenne. Ha egy hálózati körülfordulás 10ms (ez realisztikus, és magában foglalja az insert idejét is), akkor a 2000 művelet 20 másodperc alatt fut le.
Ha azonban SEQUENCE segítségével generáljuk a kulcsokat, akkor az adatelérő réteg minden további nélkül lekérhet egy nagyobb sorszámtartományt, azaz nem 1-gyel lépteti előre a SEQUENCE-t, hanem pl. 10000-rel. Így az az adatelérő réteg kap 10000 sorszámot, amit más garantáltan nem kap meg, így ezzel ő gazdálkodhat. Mit fog tenni egy okos az adatelérő réteg? Előre kiosztja minden szülő sornak a kulcsértéket a kapott tartományból, utána állítja a gyerekeket, és 1 azaz egy batch-ben képes beszúrni az összes szülő és gyerek sort! Praktikusan ez azt jelenti, hogy valószínűleg 1 mp-en belül beszúrásra kerül mind a 6000 sor.
Óriási nyereség ez, amelyet a fejlettebb Objektum Relációs Mapper-ek (pl. NHibernate) HiLo stratégiaként ismernek, és tudnak SEQUENCE-t használni a kiosztott id-k (elsődleges kulcsok) kezelésére.
A megoldás kulcsa tehát az, hogy még a sorok beszúrása előtt tudunk azoknak id-t generálni, ezáltal extra optimalizálási lehetőségeink vannak. Minek köszönhető ez? Annak, hogy az id generálás és a beszúrás műveletek nincsenek összekötve időben, köszönhetően a SEQUENCE-eknek.
Mivel a SEQUENCE által generált sorszámot tetszés szerint használhatjuk, készíthetünk vele például táblák között megosztott id generátort is, így pár tábla úgy kap elsődleges kulcsot, hogy a táblák között nézve se lesz ütközés közöttük.
Nézzünk pár példát rá.
Integer alapú SEQUENCE létrehozás, amely 1-től indul és 1-esével lépked:

create sequence HumanResources.Sequence1 as int
start with 1
increment by 1;

A következő sorszám lekérése:

select next value for HumanResources.Sequence1;

Trükkösebb SEQUENCE-ek is vannak, amelyek miután elérek egy felső határt, átfordulnak, és újrakezdik a számlálást:

create sequence HumanResources.SequenceWithCycle
as int
start with 1
increment by 1
minvalue 1
maxvalue 5
cycle;

Miután ellépkedett 5-ig, újra 1 lesz a következő generált érték. A cycle kulcsszó nélkül hibát kapnánk az 5 után meghívott next value-ra:

The sequence object ‘SequenceWithCycle’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Hogyan implementálták a SEQUENCE-t, hogy elég gyors legyen? Ha tisztán memóriában növelgetnék a sorszámot, akkor a sorszám a szerver újraindulása után újraindulna, ezért mindenképpen kell valamilyen tartós tároló (diszk, sql tábla, stb.) mögé. Lehetne azt csinálni, hogy amikor leállítják a szervert, akkor kiírnák az számláló állását, majd újraindítás után visszaolvassák azt. Ezzel viszont az a baj, hogy ha váratlanul lehal a szerver processz, vagy elmegy az áram, akkor nem lesz kiírva az utolsó érték, így legközelebb újra kioszt már kiadott sorszámokat, ami nyilvánvaló logikai hibákat okozna.
Lehetne minden sorszám letépése után kiírni az aktuális értéket, de ez meg nagyon lassú lenne. A SQL Server által választott megoldás kompromisszum a két oldal között, egyfajta cache-elés.
Memóriában tárolja sorszámot, de úgy, hogy egyszerre lekér egy nagyobb tartományt diszkről, és azt osztogatja ki, tisztán memóriában. Mikor elfogyott a tartomány, akkor megint növel egy nagyobb harapást rajta, kiírja a diszkre, és elkezdi újra memóriából osztani. Mi történik, ha elhasal a szerver? Csak annyi, hogy pár sorszám, ami még nem került kiosztásra a lekért tartományból kimarad, és elkezd egy új tartományt osztani a szerver. Luk lesz a sorszámokban, de ez nem okoz problémát, se az IDENTITY-nél, sem a SEQUENCE-nél nem építhetünk a folytonos sorszámokra, csak azt garantálják, hogy kétszer nem adják ki ugyanazt a sorszámot.
A SQUENCE létrehozásakor meg lehet adni, mekkora tartományt használjon cache-ként:

create sequence HumanResources.SequenceWithLargerCache
as int
start with 1
increment by 1
cache 100;

Kis cache esetén kicsi a valószínűsége a luknak, nagy cache esetén gyorsabb a sorszámosztás. Értelemszerűen intenzív SEQUENCE használó programoknál érdemes nagy cache értéket használni, de érdekes módon pár 10-es méret után már nem érhetünk el jelentős nyereséget.
A SEQUENCE-eket nem csak egyesével lehet léptetni, például a korábban leírt HiLo id generátor adatelérő stratégia egyszerre pár száz vagy ezer sorszámot kér magának, amivel aztán maga gazdálkodik. Tetszőleges számú előreléptetést így kell kérni:

declare @RangeFirstValue sql_variant ;
declare @RangeLastValue sql_variant ;

exec sp_sequence_get_range
@sequence_name = N'HumanResources.CounterSeq'
, @range_size = 50
, @range_first_value = @RangeFirstValue output
, @range_last_value = @RangeLastValue output;

SELECT @RangeFirstValue, @RangeLastValue;

Kimenet: 2336 2385

A SEQUENCE egyébként más adatbázisokról való áttéréskor (Oracle, Firebird) az egyik legproblémásabb pont volt, SQL Server 2012-től ez is megoldódott.
A másik problémás pont a soronként működő triggerek, de ebben nem lépett előre a 2012.

2012.03.28.

SQL Server 2012 újdonságok – 1. File Table

Filed under: Adatbázisok,SQL Server,SQL Server 2012,Szakmai élet — Soczó Zsolt @ 13:38

Aki még emlékszik rá, valamikor 2004 táján volt egy olyan ötlet, hogy az NTFS valamiféle adatbázis irányba megy el, így a Vista mögött WinFS néven lesz valami hibrid tároló. Még demóztam is annak idején, de aztán nem lett belőle semmi.
Most, 2012-ben az FileTable SQL Server újdonságot látva WinFS feelingem támadt. Lássuk, mi ez?

Nagy fájlok és a relációs adatok együttes tárolása gyakran visszatérő feladat. Pl. egy website fényképeket publikál, amelyekhez le kell tárolni a fényképeket mint nagytömegű bináris adatot, és hozzá tucatnyi metaadatot a kép méretéről, készítőjéről, stb. A metaadatok könnyen letárolhatók relációs sémában, de a fájl adatok mindig is problémát jelentettek, mivel a relációs adatbáziskezelők nem nagyméretű adatok tárolására tervezettek, hanem sok kicsire.
Az SQL Server 2008-ban bevezetett FILESTREAM attribútum segítségével már tudtunk nagytömegű adatokat hatékonyan tárolni, amely látszólag adatbázisban tárolja az adatokat, valójában fájlrendszerben, a tranzakcionális épség megtartása mellett. Az így tárolt adatokat el lehet érni SQL SELECT paranccsal is, mintha relációs adat lenne, de tranzakcionális fájl apin keresztül is, amely hatékonyabb elérést biztosít. Utóbbi viszont csak tranzakció megnyitása után engedélyezte a kipublikált fájl megosztás elérését, így azt azt az arre fel nem készített alkalmazások közvetlenül nem tudták elérni fájl apival (CreateFile függvény, .NET FileStream osztály, stb.).
Az SQL Server 2012-ben bevezetett FileTable a FILESTREAM funkciót fejleszti tovább. Belül FILESTREAM alapon látszólag táblában, valójában fájlokban tárolja az adatokat, kívülről viszont teljesen úgy lehet elérni őket, mintha egy fájlmegosztás fájljait és könyvtárait látnánk. A FileTable adatok eléréséhez nem kell (de lehet) tranzakciót nyitni, így közönséges módon, mintha tényleg fájlokat látnánk elérhetőek az adatok.
Közelebbről, a FileTable valamely sora egy fájlt vagy egy könyvtárat képes tárolni. Az adatok a fájlrendszerhez hasonlóan hierarchikusan vannak elrendezve. Belül contraintekkel és triggerekkel biztosítják, hogy az igazi fájlrendszerhez hasonlóan működjön. 10 fájl attribútumot is tárolnak (hidden, readonly, stb.). Tartalmaz egy fájl típust azonosító oszlopot is, hogy a FullText kereső tudja, milyen tartalom van benne (rtf, doc, stb.)
SQL szemszögből az adatok elérhetők SQL parancsokkal is, és a backup/restore is kezeli, azaz teljesen integráns része lett az SQL Servernek, míg kívülről teljesen az az élményünk, hogy fájlrendszert látunk.
Nézzünk egy egyszerű példát FileTable létrehozására:

alter database AdventureWorks2012
ADD FILEGROUP FsGroup CONTAINS FILESTREAM;

alter database AdventureWorks2012
ADD FILE (NAME = Fs, FILENAME = 'c:\data\filestream1')
TO FILEGROUP FsGroup;

ALTER DATABASE AdventureWorks2012
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'gyoker');

CREATE TABLE FtStore AS FileTable
WITH (FileTable_Directory = 'Ft',
	FileTable_Collate_Filename = Latin1_General_100_CI_AS);

Intézőben közönséges megosztásként látszik a FileTable, létrehozhatunk benne könyvtárakat és fájlokat:

File Table in Windows Explorer

File Table in Windows Explorer

Így érhető el a FileTable SQL oldalról:

SELECT * FROM [AdventureWorks2012].[dbo].[FtStore];

Az eredményhalmaz ketté van vágva a könnyebb olvashatóság kedvéért:

FileTable from SQL side

FileTable from SQL side

FileTable from SQL side

FileTable from SQL side

A FILESTREAM vagy FileTable adatokat igazán hatékonyan a megosztáson keresztül, fájlkezelő műveletekkel lehet jól kiaknázni, mivel ilyenkor az adatok nem kerülnek be a Buffer Cache-be, így nem tolják ki onnan a már cache-elt kisebb relációs adatokat.

Egyetlen fura pont van csak az egészben, hogy notepadban nem lehet megnyitni és elmenteni a FileTableben tárolt szövegeket, mert a notepad Memory Mapped File apival nyitja meg a szöveget, amit nem támogat a FileTable redirectora… Csak sima CreateFile api vagy .NET-ből a FileStream az, amivel meg lehet nyitni a benne levő dolgokat. A WordPad például nem trükközik, nem használ Memory Mapped File-t.

« Older Posts

Powered by WordPress