Enhancing SQL Server 2008 Performance: Merging Commands & Data Compression

2. SQL Server 2008 { Áttekintés 2. rész} Soczó Zsolt ASP.NET MVP, MCSD, MCDBAzsolt.soczo@gmail.comResearch EngineerQualification Developement

3. Tartalom • MERGE utasítás • Komponálható DML • GROUPING SETS • Adattömörítés • Csillag Join és Bitmap Filter • Sparse oszlopok • Filtered index • Full Text Kereső • Megbízható függőségek • Profiler Deprecation események • Apróságok

4. MERGE utasítás • INSERT/UPDATE/DELETE egy művelettel • Gyorsabb, mint a külön I/U/D – 1x megy végig a táblákon • Adatbetöltésekre • UPSERT eljárásokhoz • Szinkronizáló alkalmazásokhoz • A műveletek egy tranzakcióban futnak merge into CelusingForrasonForras.Id = Cel.Id when matched then update set Cel.Adat = Forras.Adat, ... when target not matched then insert (Id, Adat, ...) values(Forras.Id, Forras.Adat, ...) when source not matched then delete; merge into CelusingForrasonForras.Id = Cel.Id when matched and (Forras.OszlopN <> Cel.OszlopN) then update set Cel.Adat = Forras.Adat, ... when target not matched then insert (Id, Adat, ...) values(Forras.Id, Forras.Adat, ...) when source not matched then delete;

5. Komponálható adatmódosítások • Adatmódosító műveletek által érintett sorok felhasználása „virtuális” táblaként • Későbbi verzióban nem csak insert lehet a művelet célja insert into Egyiktábla(Oszlop)  select Oszlop1 from (update MásikTábla Set Oszlop1 = Újérték output inserted.Oszlop1) as d; 

6. {MERGE, Komponálható DML} demó

7. GROUPING SETS I. • A GROUP BY kibővítése • Több feltétel szerinti csoportosítás • Egy eredményhalmazt ad vissza • (több GROUP BY és UNION ALL-lal helyettesíthető) • Egyszerűsíti a többféle szempont szerint aggregálásokat • Gyorsabb mint a UNION-os megoldás • Újrahasznosítja a részeredményeket • Egyszer megy végig a forrásadatokon SELECT C1, C2, … , Cn, Agg(M1), … , Agg(Mk)FROM T GROUP BY GROUPING SETS ((G1), (G2), … , (Gx))

8. GROUPING SETS II. Év, negyedév Idő- szaki összes Év, negyedév, ország összes SELECT D.CalendarYear, D.CalendarQuarter, T.SalesTerritoryCountry, SUM(F.SalesAmount) AS SalesAmount FROM dbo.FactResellerSales F INNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey INNER JOIN dbo.DimSalesTerritory T ON F.SalesTerritoryKey = T.SalesTerritoryKey GROUP BY GROUPING SETS ( (CalendarYear, CalendarQuarter, SalesTerritoryCountry), (CalendarYear, CalendarQuarter), (SalesTerritoryCountry), ()) ORDER BY CalendarYear, CalendarQuarter, SalesTerritoryCountry Ország Országonkéntiösszes Teljes összeg

9. Adattömörítés I. • Táblák és indexek tartalmát tárolja tömörítve • Elsősorban adattárházak tény tábláihoz (mert a módosításokat kicsit lassítja) • Lehetségesen gyorsabb lekérdezések • IO intenzív lekérdezések esetén • Kevesebb IO „költség” • De több CPU „költség” • 2-7-szeres tömörítés • A többi szolgáltatás számára észrevétlen

10. Adattömörítés II. • Sor tömörítés • fix adatok változó hosszúságú kódolása (int, decimal, stb.) • Lap tömörítés • Prefixenkódolás • Adatszótár használata (hashtable-szerű működés) • BLOB adatok (varchar(max) stb.) nem tömörítettek • Megoldások: saját függvény, saját típus, alkalmazás tömörít, FILESTREAM tömörített NTFS-en Anchor sor 

11. Adattömörítés III. • Mennyi nyereség várható? • Bekapcsolás táblára • Indexre exec sp_estimate_data_compression_savings 'Séma', 'Tábla', Index id, Partíció, 'row' vagy 'page' alter table Tába rebuild with (data_compression = page vagy row) alter index Indexnév on Tábla rebuild with (data_compression = page vagy row);

12. Csillag JOIN és Bitmap Filter • Új, belső módszer csillagtáblákra épülő JOIN-ok gyorsítására • Hatalmas, n x 100 millió soros táblákra is • Bloom Filter implementáció • Feltételek • Egyoszlopos, egyenlőség alapú JOIN • Integer a legjobb, in-rowoptimization • Csak párhuzamos végrehajtási tervben • A Tény tábla legyen a legnagyobb • Sok szál és memória kell hozzá

13. Sparse oszlopok • “Sparse” attribútum az oszlopon • Tárolás optimalizálás: 0 bájt a NULL értékek tárolása • NULL tömörítés a TDS (drót) szinten • Az alkalmazások nem látnak belőle semmit • Max. 30000 egy táblán • XML-ként is láthatók és módosíthatók a sparse oszlopok (SparseColumnSet) • Gyorsabb lehet mint a többtáblás (Entity-Attribute-Value) design Entity-Attribute-Value Sparse

14. Filtered index áttekintés • Az adatok egy {részhalmazára}épített nonclustered index • Kicsi index, kicsi IO • Sparse oszlopokkal rendelkező táblákra is kiváló, csak a tényleges (nem NULL) adatokat indexelve create nonclustered index idx_BOF on Production.BillOfMaterials (ComponentID, StartDate) where EndDate IS NOT NULL

15. { Sparse oszlopok, Filtered Index, Powershell integráció } demó

16. Full Text kereső • Full-Text motor és az indexek teljesen integráltak • Katalógus, az index és a nem indexelendő szavak listája (stoplist) az adatbázisban lakik • A motor is az adatbázisban fut! (a szótördelés nem) • Jobb teljesítmény • Vegyes lekérdezések (FullText& Relációs) gyorsabbak • Az optimalizáló tud a FT indexről • FileStream integrált • Nyitottá vált, látható minden működési részlet és adat SELECT * FROM candidates WHERE CONTAINS(resume,’”SQL Server”’) --FT indexAND ZipCode = ‘2049’ --SQL index

17. Megbízható függőségek • A függőségek {név} és id alapján is rögzítődnek • A késleltetett névfeloldás miatt • SQL Server 2005 csak id-t használt • Adatbázisok és szerverek közötti hivatkozásokat is követi! • sys.sql_expression_dependencies • Általános ki-kitől függ alaptábla • sys.dm_sql_referenced_entities • Kirehivatkozik? • sys.dm_sql_referencing_entities • Ki hivatkozik rá?

18. {Full Text kereső, Függőségek} demó

19. Profiler Deprecation Event-ök • Jelzik, ha olyan szolgáltatást használunk, amit már nem fognak támogatni • DeprecationAnnouncement • Kerüljük, mert már nem támogatják a jövőben • declare @int; set @ = 9 • ALTER DATABASE AdventureWorksSET TORN_PAGE_DETECTION ON • DeprecationFinalSupport • Már a következő verzió se támogatja • sp_addserver • SELECT … FROM Sales.SalesOrderDetailCOMPUTE SUM(UnitPrice) BY SalesOrderID

20. Apróságok • CONVERT hexa támogatás • Sor konstruktor (VALUES) • Értékadó operátorok: +=, -=, *=, /=, … • Változó inicializálás létrehozáskor DECLARE @b binary(4) = CAST(1234567890 as binary(4)) SELECT CONVERT(varchar(30), @b, 1) -- 0x075BCD15 INSERT INTO ContactVALUES ('John Doe', '425-333-5321'), ('Jane Doe', '206-123-4567'), ('John Smith', '650-434-7869'); UPDATE Raktárkészlet SET Mennyiség+=s.Mennyiség FROM Raktárkészlet AS rINNER JOIN Eladások AS rON r.id = e.id DECLARE@v int = 5; DECLARE @v1 varchar(10) = 'xxx';

21. A délutáni előadásokból • Fejlesztőknek • XML újdonságok • Entity Framework • Filestream • Spatial típusok • HierarchyID • Integration Services • Analysis Services • Reporting Services • Üzemeltetőknek • Szabály alapú felügyelet • Audit • Titkosítás • Tömörítés • Függőségek • Monitorozás • Optimalizálás • ResourceGovernor

23. {Kezdés 13:25-kor }