Archive for April, 2008

SQL Server 2005 Blocked process report

Thursday, April 24th, 2008

Ez 2005-ös cucc, de hasznos a túl sokáig blokkolt lekérdezések azonosításához.

Az SQL 2008-as sorozatot folytatom hamarosan, csak most sok energiát leköt a munkám illetve a májusi konfra készülés.

Solutions for Common T-SQL Problems

Wednesday, April 16th, 2008

Egész jó dolgok vannak benne, és gondolom majd bővítik még. A számokat tartalmazó tábla használatát például sokan nem ismerik, pedig hasznos trükkökre ad lehetőséget.

Tetszik ahogy előállítják, legtöbben valami ciklussal próbálkoznának, de itt nem:

SELECT TOP 2147483647 IDENTITY(INT,0,1) AS N
INTO Numbers
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d, sysobjects e

Entity Attribute Value szerkezet konvertálása sparse column alapú tervvé

Tuesday, April 15th, 2008

Korábban már volt róla szó, hogy változó számú jellemző tárolására többféle design létezik. Ezek közöl a fenti kettő közötti konverzióról szól ez a bejegyzés.

SQL Server 2008 újdonságok 36. – CHEKSUM a tempdbre is

Tuesday, April 15th, 2008

A CHEKSUM egy 2005-ben bevezetett új diszkhiba felismerő szolgáltatás, amely a torn page detection-t váltja fel hosszabb távon (az már deprecated feature).
Ez ment is szinte minden adatbázisra – és kell is használni -, de a tempdbre nem. 2008-tól most már erre is megy. Ez azért kritikus, mert ha a tempdb-t tároló diszk kezd el bolondozni, a hibás adatokat simán lehet, hogy belapátoljuk a felhasználói adatbáziosokba is.

Bekapcsolás:

ALTER DATASE tempdb SET PAGE_VERIFY CHECKSUM

Háromszög join – mi a szösz?

Friday, April 11th, 2008

Ezt az elnevezést még nem hallottam a nagyobbságon, kisebbségen alapuló joinokra (nem, most nem azokról beszélek :), de érdekes. Jó fejben tartani, hogy ez lassú, még ha elég triviális is, hogy az.

Interesting observation with table > 1TB

Thursday, April 10th, 2008

Amikor a 4 Giga ram se elég. Érdekes írás.

Vége a terminátor kliens /console kapcsolójának

Thursday, April 10th, 2008

Van helyette /admin. Itt leírják, miért?

LINQ Framework Design Guidelines

Thursday, April 10th, 2008

Az olyanoknak, mint én, akik csak ugatják a témát hasznos ez.

SQL Server 2008 újdonságok 35. – GROUPING SETS

Thursday, April 10th, 2008

A GROUP BY egyféle szempont, akárhány oszlop vagy kifejezés alapján csoportosít, aztán aggregáló függvényekkel trancsírozzuk a többi oszlop adatait. Időnként azonban többféle szempont szerint is kellene csoportosítani. Pl. eladások év alapján, eladások év és termék alapján, eladások csak úgy, összesen, stb. Ezt meg lehet tenni több lekérdezés UNION ALL-jával, amelyek eleje majdnem ugyanaz, csak a group by-ok mások.
Valami hasonlót csinált már az SQL aszem 7-ben bevezetett CUBE és ROLLUP záradék a group by után. Ezekbe be volt építve, hogy ne csak a megjelölt oszlopok szerint csoportosítsanak és aggregáljanak, hanem egyre több csoportosító oszlopot elhagyva egyre durvább, egyre nagyobb átfogással számoljanak, lsd. az első rész eladásos példája.

No, az új grouping set záradék a group by után arra szolgál, hogy mi, explicit megadhassunk több csoportosító feltételt is, így többféle dimenzió mentén aggregálhassunk. Azaz, ha akarunk eljuthatunk a cube illetve rollup-ig is, de kihagyhatunk bizonyos szempontokat is. Ez nem más tehát, mint egy átmenet a sima egyes group by és a mindenféle kombinációban aggregáló cube között.

Egy példa mindent megmagyaráz:

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

(A táblázat a webes kiadásban csak görgetéssel látszik teljes egészében, az rssben szerintem teljes szélességében látszani fog.)

CalendarYear CalendarQuarter SalesTerritoryCountry          SalesAmount
------------ --------------- ------------------------------ ---------------------
NULL         NULL            NULL                           80450596.9823
NULL         NULL            Australia                      1594335.3767
NULL         NULL            Canada                         14377925.5965
NULL         NULL            France                         4607537.935
NULL         NULL            Germany                        1983988.0373
NULL         NULL            United Kingdom                 4279008.8266
NULL         NULL            United States                  53607801.2102
2001         3               NULL                           3193633.9687
2001         3               Canada                         637982.8445
2001         3               United States                  2555651.1242
2001         4               NULL                           4871801.3366
2001         4               Canada                         875376.6118
2001         4               United States                  3996424.7248
2002         1               NULL                           4069186.0383
2002         1               Canada                         775755.4784
2002         1               United States                  3293430.5599
2002         2               NULL                           4153820.4239
2002         2               Canada                         790691.8802
2002         2               United States                  3363128.5437
2002         3               NULL                           8880239.4384
2002         3               Canada                         1860334.8668
2002         3               France                         523999.2337
2002         3               United Kingdom                 471783.2311
2002         3               United States                  6024122.1068
2002         4               NULL                           7041183.7534
2002         4               Canada                         1396216.9767
2002         4               France                         333123.9447
2002         4               United Kingdom                 369974.5296
2002         4               United States                  4941868.3024
2003         1               NULL                           5266343.505
2003         1               Canada                         1008618.4806
2003         1               France                         238772.5213
2003         1               United Kingdom                 273906.5922
2003         1               United States                  3745045.9109
2003         2               NULL                           6733903.8214
2003         2               Canada                         1349998.8179
2003         2               France                         332124.6758
2003         2               United Kingdom                 290827.6064
2003         2               United States                  4760952.7213
2003         3               NULL                           10926196.0872
2003         3               Australia                      450884.4054
2003         3               Canada                         1744784.0586
2003         3               France                         957497.1842
2003         3               Germany                        560152.5008
2003         3               United Kingdom                 862226.626
2003         3               United States                  6350651.3122
2003         4               NULL                           9276226.0116
2003         4               Australia                      396546.5586
2003         4               Canada                         1547904.0683
2003         4               France                         845409.6541
2003         4               Germany                        538714.1841
2003         4               United Kingdom                 733185.0082
2003         4               United States                  5214466.5383
2004         1               NULL                           7102685.111
2004         1               Australia                      340479.429
2004         1               Canada                         1024145.7867
2004         1               France                         597367.5743
2004         1               Germany                        393901.2514
2004         1               United Kingdom                 542463.7975
2004         1               United States                  4204327.2721
2004         2               NULL                           8935377.4868
2004         2               Australia                      406424.9837
2004         2               Canada                         1366115.726
2004         2               France                         779243.1469
2004         2               Germany                        491220.101
2004         2               United Kingdom                 734641.4356
2004         2               United States                  5157732.0936

Ennyi az egész, elég egyszerű.

Egyébként nem csak egyszerűbb a szintax, hanem gyorsabb is a group by groping settel, mert egy menetben csinálja meg az összes aggregálást, és fel tudja használni a finomabb felbontású aggregált eredményeket a durvábban. Pl.


--Alap
--Cost: 1.92, IO: 1613
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),
())

--Darabonkéti
--Cost: 5.87, IO: 6821
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 CalendarYear, CalendarQuarter, SalesTerritoryCountry

UNION ALL

SELECT D.CalendarYear, D.CalendarQuarter, NULL, 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 CalendarYear, CalendarQuarter

UNION ALL

SELECT NULL, NULL, 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 SalesTerritoryCountry

UNION ALL

SELECT NULL, NULL, NULL, 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

Jelentős IO és költség különbség van, ez plusz öröm, a tömör formátum mellett.

Új: SQL Server Policy-Based Management blog

Wednesday, April 9th, 2008

This blog is dedicated to Policy-Based Management introduced in SQL Server 2008.

SQL Server 2008 újdonságok 34. – Megbízható függőségek

Wednesday, April 9th, 2008

Hát igen, függőségek. Ez az a téma, amivel ki lehet kergetni a világból egy SQL Server DBA-t. Mindenki tudja, hogy az SQL Server 2005-ben függőségek kezelése szánalmas, egyáltalán nem megbízható.

Egy egyszerű példával világítom meg.

create view dbo.VA
as
  select * from Production.Product
go

create view dbo.VB
as
  select * from VA
go

VB függ VA-tól, tiszta ügy. A gond az, hogy megalterezve VA-t eltűnik az az infó, hogy VB függ VA-tól.

alter view dbo.VA
as
  select * from Production.Product

Ez eszement dolog, de így van (nem tudom ellenőrizni, mert nincs a közelemben SQL 2005, ha valaki tudja validálni, kérem tegye meg).

2008-ban már normálisan mennek a függőségek. Ennek az az oka, hogy nem csak id szerint tárolják őket, hanem név szerint is. A régebbi verziók csak id-t használtak, így nem tudták letárolni a késleltetett névfeloldás miatt időlegesen hiányzó függőségeket (mondjuk ez nem menti fel őket az előbbi példa esetében).

2008-ban a függőségeket a sys.sql_expression_dependencies rendszertábla tárolja, és két függvény segít lekérdezni belőlük.

drop view dbo.VA
drop view dbo.VB
drop proc dbo.PA
drop proc dbo.PB
go

create view dbo.VA
as
  select * from Production.Product
go

select OBJECT_NAME(referencing_id), referenced_entity_name, referenced_id
from sys.sql_expression_dependencies
where referencing_id = OBJECT_ID('dbo.VA')
referencing_object             referenced_entity_name         referenced_id
------------------------------ ------------------------------ -------------
VA                             Product                        1461580245

Szépen van név és id is, hisz létezett a Product tábla. Eddig ok.

create view dbo.VB
as
  select * from VA
go

select OBJECT_NAME(referencing_id) referencing_object, referenced_entity_name, referenced_id
from sys.sql_expression_dependencies
where referencing_id in (OBJECT_ID('dbo.VA'), OBJECT_ID('dbo.VB'))
referencing_object             referenced_entity_name         referenced_id
------------------------------ ------------------------------ -------------
VA                             Product                        1461580245
VB                             VA                             992722589

Ez is rendben van. Most jön a régi problémás pont:

alter view dbo.VA
as
  select * from Production.Product
go

select OBJECT_NAME(referencing_id) referencing_object, referenced_entity_name, referenced_id
from sys.sql_expression_dependencies
where referencing_id in (OBJECT_ID('dbo.VA'), OBJECT_ID('dbo.VB'))

És a kimenet jó!

------------------------------ ------------------------------ -------------
VA                             Product                        1461580245
VB                             VA                             992722589

Jöhet a késeltetett névfeloldás tesztje.

create proc dbo.PA
as
  exec dbo.PB
go

select OBJECT_NAME(referencing_id) referencing_object, referenced_entity_name, referenced_id
from sys.sql_expression_dependencies
where referencing_id in (OBJECT_ID('PA'))
The module 'PA' depends on the missing object 'dbo.PB'. The module will still be created; however, it cannot run successfully until the object exists.
referencing_object             referenced_entity_name         referenced_id
------------------------------ ------------------------------ -------------
PA                             PB                             NULL

Látható, hogy a referenced_id NULL, mert még nem létezik PB, de név alapján felvették a függőséget. Kiváló! Mikor lesz kitöltve az id?

create proc dbo.PB
as
go

select OBJECT_NAME(referencing_id) referencing_object, referenced_entity_name, referenced_id
from sys.sql_expression_dependencies
where referencing_id in (OBJECT_ID('PA'))
referencing_object             referenced_entity_name         referenced_id
------------------------------ ------------------------------ -------------
PA                             PB                             1040722760

A reference_id most már jó, mert létezik a hivatkozott objektum.

Nézzük a nézeteket. Kire hivatkozik egy ojjektum? sys.dm_sql_referenced_entities nézet:

select OBJECT_NAME(referenced_id) referenced_object, * from sys.dm_sql_referenced_entities('dbo.PA', 'OBJECT')
referenced_object              referencing_minor_id referenced_server_name         referenced_database_name       referenced_schema_name         referenced_entity_name         referenced_minor_name          referenced_id referenced_minor_id referenced_class referenced_class_desc          is_caller_dependent is_ambiguous
------------------------------ -------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------- ------------------- ---------------- ------------------------------ ------------------- ------------
PB                             0                    NULL                           NULL                           dbo                            PB                             NULL                           1040722760    0                   1                OBJECT_OR_COLUMN               0                   0

Látható, hogy PA PB-re hivatkozik.

Ki hivatkozik PB-re? sys.dm_sql_referencing_entities függvény:

select * from sys.dm_sql_referencing_entities('dbo.PB', 'OBJECT')
referencing_schema_name        referencing_entity_name        referencing_id referencing_class referencing_class_desc         is_caller_dependent
------------------------------ ------------------------------ -------------- ----------------- ------------------------------ -------------------
dbo                            PA                             1024722703     1                 OBJECT_OR_COLUMN               0

Pontos lista, milyen függőségeket tárol a szerver.

Néha nem egyértelmű egy függőség. Az alábbi példában:

CREATE PROCEDURE dbo.p1 AS 
SELECT column_a, Sales.GetOrder() FROM Sales.MySales; 

A Sales.GetOrder() lehet egy Sales nevű oszlop a MySales táblában, ami egy UDT, és van neki egy GetOrder metódusa, de lehet egy UDF, ami a Sales sémában van, és GetOrder a neve. (Az SQL Server 2005 korábbi beta verziójában még a C++-os :: volt a típust és metódust elválasztó karakter, ha ezt meghagyták volna, most nem lenne ez a félreérthetőség.)

A függőségeket jegyzik adatbázisok (3 tagú nevekkel hivatkozva más objektumokra) sőt szerverek között is (4 tagú nevek, linked szerverek), nem csak egy adatbázison belül. Emellett kétféle függőség van, schema-bound objektumok és nem schema-bound objektumok esetén. A NEM séma kötött objektumokról NINCS oszlopszintű függőségi infó a sys.sql_expression_dependencies táblában, de a sys.dm_sql_referenced_entities ezekről is összeszedi az infókat, ezért ha teljeskörű függőségi infóra van szükség, inkább a függvény használatát javaslom.

És látá Isten, hogy ez jó. (Remélem, majd elválik. :)

SQL Server 2008 újdonságok 33. – Sparse columns (foghíjas oszlopok?)

Tuesday, April 8th, 2008

Ha egy oszlopot megjelölönk a SPARSE jelzővel, akkor a NULL értéket tartalmazó mezők egyáltalán nem fognak helyet elfoglalni a táblákban. Ez akkor hasznos, ha olyan az adatmodellünk, hogy nagyon sok oszlop van egy táblában, de ezek küzöl sok NULL értékű.
A mintapélda erre az esetre a termékeket és azok jellemzőit tároló adatbázis. Minden terméknek van pár jellemzője, ami vagy azonos, vagy nem a többiekével. Példál minden terméknek van ára, némelyiknek van színe, másnak mérete, stb. Ha a termékeket egy táblában akarjuk tárolni, akkor a közös adatok közönséges oszlopok lennének, a csak bizonyos termékekre vonatkozó adatok pedig sparse oszlopokban. Egy ilyen terv miatt akár több ezer oszlopa is lehet a táblának, ami miatt a sparse oszlopok száma felmegy 30000-re (csak az RTM-ben, most még nem).
Számomra furcsa ez a példa, mert egy új termék miatt sokszor sémát is kell módosítani, új oszlopot felvenni, ami nekem rossz tervet jelent, de sokan ezt favorizáják, ha nem sűrűn változnak az oszlopok, mint például Sharepointban, ahol a dokumentumok jellemzőit fogják majd várhatóan így tárolni (a következő verzióban).
A teljesség kedvéért elmondom, hogy nekem szimpatikusabb a másik két módszer erre a problémára. Az egyikben egy szótártábla van, név, jellemző oszlopokkal. A termékek tábla és a szótártábla között egy kapcsolótábla teremt több-több kapcsolatot. Így bármely termékhez felvehetünk tetszőleges adatokat, illetve egy már létező jellemzőt bármely termékhez hozzárendelhetjük. Entity-attribute-value (EAV) vagy open schema néven fut ez a világban.
A módszer hátránya, hogy a “jellemző” oszlop valamilyen polimorf izé kell legyen, valamilyen string vagy variant, hisz a jellemzők más és más típusúak. Azaz ez a megoldás nem strongly typed, nem erősen típusos. A sparse column-ös az igen.
A lekérdezés is macerás kicsit, hisz 3 táblából kell kijoinolni az adatokat, ami lassabb is, míg a sparse esetén egy sort kell egy táblából lekérdezni, valamint az eredmények sorokban jönnek, nem a természetesebb oszlopos megjelenítésben mint a sparse-nál. Mondjuk egy pivot operátor ezen segíthet.
Harmadik lehetséges design xml oszlop használata, abba aztán minden termékhez annyi adatot rakunk be, olyan típussal, ahogy csak akarjuk. Ekkor az adatok be-ki tolása kicsit körülményesebb, illetve az xml sémamódosítás nem leányálom a szerverben.
Mindenki másra esküszik. Celko pl. a sparse columns híve.
No, de beszéljünk most már konkrétan a sparse oszlopokról.
Előnyök:

  • A NULL egyáltalán nem foglal el helyet
  • Kívülről nem látszik az oszlopról, hogy belül spórolósan tárol
  • A Filtered Indexekkel szuperül együtt tud működni, hisz egy sparse oszlop IS NOT NULL-os filtered index nagyon hatékonyan csak a tényleges adatokat fogja indexelni.
  • A tábla sparse oszlopaira lehet készíteni egy ún. column set-et, amin keresztül xmlben ki lehet nyerni a nem null oszlopok adatait egyben, sőt, ezen keresztül módosítani is őket. Mindjárt lesz rá példa.

De persze mindennek meg van az ára:

  • A NULL ugyan nem foglal semmi helyet, de ha van ott adat, akkor plusz 4 byte kell hozzá. Egy bit esetén azért ez jelentős veszteség. :)
  • A text, ntext, image, timestamp, bármely saját típus, geometry, geography, varbinray (max) FILESTREAM-mel vagy a számított oszlopok nem lehet sparse-ként definiálva
  • Nem lehet default értékük (minek is persze)
  • A laptömörítés és a merge replikáció nem kompatibilis vele (a tranzakciós igen).

A plusz 4 byte miatt nagyon meg kell gondolni, érdemes-e használni ezt a szolgáltatást egy oszlopra. Van itt egy remek kis táblázat, ami azt taglalja, legalább hány százaléka legyen egy oszlop értékeinek null, hogy megérje bevetni a sparse-t. Nyilván ez típusonként változó, bitnél ha 2%-nál több sorban van nem null, már nem éri meg. Látható a táblázatból, hogy tipikus típusoknál kb. az adatok fele null kell legyen, különben több a veszteség, mint a nyereség.

Lássunk egy termékeket tároló táblás példát:

create table Termek
(
  id int not null primary key identity,
  nev nvarchar(30) not null,
  ar decimal null,
  szin nvarchar(30) sparse,
  szag nvarchar(30) sparse,
  suly decimal sparse,
  maxhofok int sparse,
  eltarthatosag date sparse,
  egyebAdatok xml column_set for all_sparse_columns
)

Id, név, ár minden terméknek van, de a többi saláta nem értelmezhető minden termékre, azok sparse oszlopokba kerültek. Az összes sparse oszlopot összefog egy xml column set. Ezen keresztül látható és módosítható is az összes sparse adat egyszerre.

Szúrjunk be pár sort, majd kérdezzük le őket explicit oszlopnevekkel!

insert Termek (nev, ar, szin, szag, suly, maxhofok, eltarthatosag)
values
(N'Amar margarin', 250, NULL, N'büdös', 250, NULL, '20080104'),
(N'Lítiumbázisú csapágyzsír', 140, 'sárga', N'fincsi', 100, 240, NULL),
(N'Kalciumbázisú csapágyzsír', 140, 'piros', N'kellemes', 90, 110, NULL)

select id, nev, ar, szin, szag, suly, maxhofok, eltarthatosag from Termek
id          nev                            ar                                      szin                           szag                           suly                                    maxhofok    eltarthatosag
----------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- ----------- -----------------------
1           Amar margarin                  250                                     NULL                           büdös                          250                                     NULL        2008-01-04 00:00:00.000
2           Lítiumbázisú csapágyzsír       140                                     sárga                          fincsi                         100                                     240         NULL
3           Kalciumbázisú csapágyzsír      140                                     piros                          kellemes                       90                                      110         NULL

Kívülről semmi különbség a sima és a sparse oszlopok között. Van azonban egy column setünk, ami picit felborítja a szokásos viselkedést. Nézzünk csak egy select *-ot!

select * from Termek
id          nev                            ar                                      egyebAdatok
----------- ------------------------------ --------------------------------------- ----------------------------------------------------------------------------------------------------
1           Amar margarin                  250                                     <szag>büdös</szag><suly>250</suly><eltarthatosag>2008-01-04</eltarthatosag>
2           Lítiumbázisú csapágyzsír       140                                     <szin>sárga</szin><szag>fincsi</szag><suly>100</suly><maxhofok>240</maxhofok>
3           Kalciumbázisú csapágyzsír      140                                     <szin>vörös</szin><szag>penetráns</szag><suly>90</suly><maxhofok>110</maxhofok>

Hoppá. A sparse oszlopok nem látszanak a select *-ban, csak az összesítő xml column set oszlopunk, amin keresztül xmlként jönnek át az adatok. Amelyik oszlop NULL, annak megfelelő elem hiányzik az xml tartalomból. Ez felfogható egyfajta számított oszlopként, hisz nincs letárolva pluszként, a sparse oszlopokból rakják össze dinamikusan.
Számított létére azonban okos, mert rajta keresztül lehet update-elni és insert-álni:

update Termek
set egyebAdatok = N'<szin>vörös</szin><szag>penetráns</szag><suly>90</suly>'
where id = 3

select id, nev, ar, szin, szag, suly, maxhofok, eltarthatosag, egyebAdatok from Termek
id          nev                            ar                                      szin                           szag                           suly                                    maxhofok    eltarthatosag           egyebAdatok
----------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- ----------- ----------------------- ----------------------------------------------------------------------------------------------------
1           Amar margarin                  250                                     NULL                           büdös                          250                                     NULL        2008-01-04 00:00:00.000 <szag>büdös</szag><suly>250</suly><eltarthatosag>2008-01-04</eltarthatosag>
2           Lítiumbázisú csapágyzsír       140                                     sárga                          fincsi                         100                                     240         NULL                    <szin>sárga</szin><szag>fincsi</szag><suly>100</suly><maxhofok>240</maxhofok>
3           Kalciumbázisú csapágyzsír      140                                     vörös                          penetráns                      90                                      NULL        NULL                    <szin>vörös</szin><szag>penetráns</szag><suly>90</suly>

Látható, hogy az adatok sorrendje tetszőleges az xml bemenetben, és a hiányzó adatok NULL-ra lesznek kitöltve.

Egyébként a select * csak akkor nem tartalmazza a sparse oszlopokat, ha van xml column set is definiálva a táblán. Ha nincs, a sparse oszlopok úgy látszanak, mint a sima oszlopok.

alter table Termek
drop column egyebAdatok
select * from Termek
id          nev                            ar                                      szin                           szag                           suly                                    maxhofok    eltarthatosag
----------- ------------------------------ --------------------------------------- ------------------------------ ------------------------------ --------------------------------------- ----------- -----------------------
1           Amar margarin                  250                                     NULL                           büdös                          250                                     NULL        2008-01-04 00:00:00.000
2           Lítiumbázisú csapágyzsír       140                                     sárga                          fincsi                         100                                     240         NULL
3           Kalciumbázisú csapágyzsír      140                                     vörös                          penetráns                      90                                      NULL        NULL

A 30000-es oszlopszámot teszteltem:

declare @i int = 0
while (@i < 10000) begin declare @sql nvarchar(max) = N'alter table Termek add ujoszlop' + CAST(@i as nvarchar(20)) + ' nvarchar(10) sparse' print @sql exec(@sql) set @i += 1 end [/source] De egyelőre még 1024 a limit, mint régen, majd az RTM-ig még megváltoztatják.

SQL Server 2008 újdonságok 32. – Star join és Optimized Bitmap Filtering

Monday, April 7th, 2008

Advanced téma jön, de nagyon fincsi.
Star join az, amikor egy táblához sok másik táblát kapcsoluk hozzá. Tipikusan adattárházakban van ilyen, ahogy egy hatalmas ténytáblához sok kisebb, de még azok is akár millió soros dimenzió tábla tartozik.
Az AdventureWorksDW példaadatbázis egy ilyen tárházi minta (DataWarehouse).
Ezek az adattárházi táblák elég speciálisan vannak tervezve, nem a hagyományos normalizált módon.
Például a FactInternetSales táblában a dátumok nem datetime-ként, hanem intként vannak tárolva. 20010701, ez egy egész szám, amit dátumként értelmezünk. Azért van ez a bizarr felállás, mert integereket nagyon gyorsan lehet összehasonlítani, ami a join szempontjából fontos.
No, amikor ezeket a b. nagy táblákat joinoljuk, akkor általában szóba se jöhet a loop join a világ legjobb indexei mellett se, mert százmilliószor nekifutni egy táblának még index mellett is nagyon költséges. Ilyen nagy tábláknál merge vagy hash join jöhet szóba. A merge csak a kulcsok azonos rendezettsége, azaz általában akkor működik, ha mindkét oszlopon clustered index van. Ez ritkán jön össze mindkét oldalnál, ezért elég ritkán látni merge joint nagy táblák esetén. Kis tábláknál egyébként még arra is hajlandó a szerver, hogy az kisebb táblát lerendezi úgy, mint a nagyot, aztán merge-öl. De ezt csak pár százezer soros táblákig vállalja be, és csak akkor, ha jó sok ram van a gépben.
Azaz, a legtöbb esetben az adattárházakban hash joinokat fogunk látni. A merge és a hash join is azért jobb a ciklusosnál nagy táblák esetén, mert mindkettő csak egyszer járja be a táblákat. Igaz, hogy akkor az egészet, de legalább nem esik neki sok milliószor, mint a loop join.
A hash join azt csinálja, hogy a kisebb táblát a joinolandó oszlop mentén lehasheli. Azaz általában az adott dimenziótáblát. Azaz épít egy hashtable-t a kulcs alapján. Aki valamely programozási frameworkből ismeri a hashtable-t, az tudja, hogy ez egy olyan memóriastrukrúra, ami piszok gyors keresést tesz lehetővé a hash key alapján. A kiinduló értékek hash-ei között lesz ütközés, azaz a 123 és a 3455 is lehet, hogy ugyanarra a hash értékre, mondjuk 12-re képeződik le. Az ütköző értékeket ún. vödrökbe rakják, amiben lineáris listában tárolódnak az eredeti kulcsok, azaz egy vödrön belül a keresés már nem gyors, csak lineáris, és nem a hasheket, hanem az eredeti típusokat kell komparálni.
No, amikor a build input kész az egész (de a kisebb) táblára, akkor elkezdenek végigmenni a nagyobbik táblán, és megnézik, benne van-e a join kulcsa a build inputban. Ehhez ugye lehashelik ennek is a kulcsát, majd a vödrök között felezős kereséssel gyorsan megtalálják azokat a sorokat, amelyekhez tartozó hash kulcs ugyanennyi. Ez eddig gyors. Azonban a vödrön belül lehet számtalan tétel, amelyekkel már ténylegesen össze kell hasonlítani a kulcsokat, és ez már nem túl gyors (főleg, ha nem int a kulcs, hanem pl. varchar, mindenféle collation szarakodással).
No, ez a sima hash join, ez elég gyors még indexeletlen táblákra is, de mindkét táblán egyszer végigmegy. Eszetlen nagy tábláknál (100m sorok) azonban még a bináris keresés, amivel a hashek között keresnek is lassúvá válik, főleg, hogy mondjuk 30 tábla esetén mindegyik hashtáblájában keresni kell. Azaz nem válik lassúvá, csak annyi sorra kell végrehajtani, hogy a végén mégis csak sok lesz a költsége.
Ezt akarja valamelyest csökkenteni a bitmap filter, azaz eleve kidobni azokat a sorokat a probe inputból, a ténytáblából, amelyekről valamilyen mágikus módon tudjuk, hogy biztos nincs hozzájuk passzoló sor a többi táblában. Az SQL Server 2008 ún. Bloom filtert használ (gugliék is használják ezt több termékükben is). Ez egy nagyon érdekes adatstruktúra, amellyel halmazokat lehet nagyon hatékonyan kezelni. (Akit érdekelnek a részletek, érdemes megnézni ezt a C# implementációt. Ez csak két hash függvénnyel dolgozik, és ha több mint két hash eredmény kell, akkor ezekből származtatja a többit is, így nem kell annyit hash-elnie.) Érdekessége a Bloom filternek, hogy lehet, hogy egy elemre tévesen azt mondja, hogy benne van a halmazban, pedig nincs, de sose mondja azt, hogy nincs benne egy elem a halmazban, ami pedig a valóságban beleraktak. Mivel a szerver ezt a garantáltan nem egyező sorok kiszűrésére használja nem probléma valamennyi false pozítív (azt hiszi, hogy benne van, de nincs), mert után hagyományos hash join-nal úgyis megnézik a potenciális sorokra, hogy tényleg egyeznek-e a join feltételnek megfelelően.
A Bloom filter úgy működik, hogy ha be akarnak rakni egy elemet a halmazba, akkor azt n féle hash függvénnyel is lehashelik, és a hash értéknek megfelelő bitet egyre állítják egy bitarrayben. Azaz pl. n=10 hash függvény alkalmazása esetén 10 bitpozíció lesz 1-re állítva. Más elemeket hozzáadva persze egyre több olyan bit lesz, ami már eleve 1 volt a korábban behelyezett elemek miatt.
Hogy egy elem eleme-e a halmaznak, azt úgy nézik meg, hogy lehashelik a példabeli 10 hash függvénnyel, és megnézik, hogy ezok a bitek 1-re vannak-e állítva? Ha igen, akkor az elem VALÓSZÍNŰLEG eleme a halmaznak. De nem biztos. Nyilván minél kisebb a tömb és minél több elemet tárolunk benne, annál többször hazudik, jól kell belőni a méretét és a hashek számát is. Aki szereti a valszámot, nézze meg a wikis cikket.
No, az SQL Server Star Join esetén, azaz, amikor egy nagy ténytáblához sok kisebb dimenziótáblát joinolunk hozzá, miközben minden egyes dimenziótáblához felépíti a build inputot, azaz lehasheli a kulcsaikat, közben közben építi a Bloom filtereket is, ezt hívja Bitmap Filternek. Minden egyes dimenziótáblához készül tehát egy bitmap filter. Ezek után nekilát a ténytáblát soronként felolvasni. Normál esetben le kellene hashelni az összes oszlop értéket a sorból, amely valamelyik join feltételben szerepel, és rápróbálni a megfelelő dimenziótábla hashtáblájára. Ez lenne a sima hash join. Ehelyett azt csinálja, hogy megcsinálja a bitmap filterhez tartozó n féle hashképzést, és rápróbálja azokat az első, általa legszelektívebbek gondolt dimenzió tábla bitmap filterére. Ha az azt mondja nincs benne az elem a halmazban, akkor ez garantáltan azt jelenti, hogy ez a sor kiesik a join miatt, így se ezen dimenziótábla hash table-jében, se a többiében nem érdemes megnézni, benne van-e az elem. Ha az első bitmap filter nem ejtette ki a sort, akkor jön a következő. Ha mindegyiken átment a dolog, akkor még mindig megvan, ha nagyon kicsi is a valószínűsége, hogy fals pozitív a sor, azaz valójában nincs is párja a többi táblában, ezért ilyenkor még meg kell csinálni a hash joint erre a sorra a hagyományos módon, összepárosítva az összes táblával.
Jól látszik, hogy ha egy olyan bitmap filtereket csinál a szerver, amelyek nem elég szelektívek, azaz nem ejtenek ki elég sort, akkor nyereség helyett veszteség lesz a sok felesleges hashelés miatt. Ezért van troubleshooting cikk a témáról. :)
Persze, okos a szerver, ha menet közben észreveszi, hogy nem a legszelektívebb filter van elöl, átrendezi a listáját. Ez végül is minimális statisztikázással nyomonkövethető a részéről.

A bitmap filtert csak párhuzamos tervek esetén használja az SQL Server. Meg lehet nézni, hogy mutat ez a végrehajtási tervben, némi magyarázattal. Berakom ide a is a képet, szokjuk:

Bitmap Filter akcióban
A végrehajtási terven látható, hogy a FactInternetSales tábla tartalmát előszűri a fenti két bitmap filterrel.
Az SQL 2005 is tudta már ezt, csak az statikusan, a terv generálása közben döntötte el, hogy használni fog filtert, míg az SQL 2008 a közbenső join-ok végrehajtása közben is dinamikusan tud dönteni róla, ej, elég volt a hagyományos hash joinból, itt bizony bitmap filtereket kell építeni.
Jó, mi?

@@DBTS és min_active_rowversion()

Friday, April 4th, 2008

Régi cucc, de nekem ez valahogy kimaradt.

Returns the value of the current timestamp data type for the current database. This timestamp is guaranteed to be unique in the database.

SQL Server 2005 SP2-től van ez is:
min_active_rowversion()

Akik szinkronizálnak adatbázisok között házi módszerekkel, nagyon nézzék meg az utóbbit.

SQL Server 2008 újdonságok 31. – Adatlapok tömörített tárolása II.

Friday, April 4th, 2008

Nézzünk konkrét példákat, mekkora nyereséget érhetünk el a tömörítéssel (elmélet ez előző részben).

Nézzük meg egy tábla méretét, majd lássuk, mi lesz belőle row és page compression után (legalábbis mit jósolnak)?

exec sp_spaceused 'Production.TransactionHistoryArchive'
exec sp_estimate_data_compression_savings 'Production', 'TransactionHistoryArchive', NULL, NULL, 'row'
exec sp_estimate_data_compression_savings 'Production', 'TransactionHistoryArchive', NULL, NULL, 'page'
name                           rows        reserved           data               index_size         unused
------------------------------ ----------- ------------------ ------------------ ------------------ ------------------
TransactionHistoryArchive      89253       8224 KB            4992 KB            2776 KB            456 KB


object_name                    schema_name                    index_id    partition_number size_with_current_compression_ size_with_requested_compressio sample_size_with_current_compr sample_size_with_requested_com
------------------------------ ------------------------------ ----------- ---------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
TransactionHistoryArchive      Production                     1           1                5136                           3240                           5008                           3160
TransactionHistoryArchive      Production                     2           1                1144                           968                            1024                           872
TransactionHistoryArchive      Production                     3           1                1488                           1240                           1728                           1448

object_name                    schema_name                    index_id    partition_number size_with_current_compression_ size_with_requested_compressio sample_size_with_current_compr sample_size_with_requested_com
------------------------------ ------------------------------ ----------- ---------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
TransactionHistoryArchive      Production                     1           1                5136                           1680                           5008                           1640
TransactionHistoryArchive      Production                     2           1                1144                           816                            1024                           736
TransactionHistoryArchive      Production                     3           1                1488                           1144                           1728                           1336

Mit látunk? Az alapban 5.1 megás adatból és rajta levő 2.7 megányi indexből row compressionnel 3.2 és 2.2 mega lesz. Az adat kb. a felére megy össze, ami nem rossz, hisz row compressionről van szó, ami nagyon gyors. Az indexet nem tudta úgy összenyomni, valószínűleg az indexekben levő int adatok jelentős része 2 byte hosszan tárolható csak el, így csak felére nyomható össze.

Lap szintű tömörítésnél az 5.1 megás adatok csak 1.7 megát foglalnak el, azaz 3x tömörítést kapunk. Az indexek mérete 2.7-ről 1.95 megára esik vissza, nem sokkal kisebbre, mint csak sor tömörítéssel. Szóval indexnél esetünkben nem sokat ért egyik módszer sem, az adatok jellege miatt. Én lehet, hogy indexnél sor, adatnál pedig lap szintű tömörítést használnék.

Az adatok tényleges tömörítése lap szinten:

alter table Production.TransactionHistoryArchive 
rebuild with (data_compression = page);

Nézzük meg, mit nyerünk? Tudni kell, attól, hogy bekapcsoljuk valamely tömörítést, még dönthet úgy a szerver, hogy bizonyos lapokat nem tömörít, mert esetleg nagyobb lenne az eredmény, mint a forrás (a zip, rar, stb. programok is így tesznek). Nézzük meg a példánkban mi a helyzet?

select 
index_id,
index_level,
index_type_desc,
page_count,
compressed_page_count,
(select top 1 name from sys.indexes si where si.object_id = s.object_id and si.index_id = s.index_id) IndexName
from 
sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'), object_id('Production.TransactionHistoryArchive'), NULL, NULL, 'DETAILED') s
order by s.index_id, s.index_level desc
index_id    index_level index_type_desc                                    page_count           compressed_page_count IndexName
----------- ----------- -------------------------------------------------- -------------------- --------------------- --------------------------------------------------
1           1           CLUSTERED INDEX                                    1                    0                     PK_TransactionHistoryArchive_TransactionID
1           0           CLUSTERED INDEX                                    203                  203                   PK_TransactionHistoryArchive_TransactionID
2           1           NONCLUSTERED INDEX                                 1                    0                     IX_TransactionHistoryArchive_ProductID
2           0           NONCLUSTERED INDEX                                 125                  0                     IX_TransactionHistoryArchive_ProductID
3           1           NONCLUSTERED INDEX                                 1                    0                     IX_TransactionHistoryArchive_ReferenceOrderID_Refe
3           0           NONCLUSTERED INDEX                                 168                  0                     IX_TransactionHistoryArchive_ReferenceOrderID_Refe

Látható, hogy a clustered index 1. szintje, az index gyökér lapja nem page tömörített (korábban írtam ,miért), de a 203 levélszintű lap, azaz az adatlapok mind tömörítettek.
Látható az is, hogy az indexek egyáltalán nincsenek tömörítve, az alter table clu index esetén csak arra vonatkozik, az nc indexekre nem. Ha azokat is tömöríteni akarjuk, alter index kell:

alter index IX_TransactionHistoryArchive_ProductID 
on Production.TransactionHistoryArchive
rebuild with (data_compression = page);

Ennek hatására a korábbi lekérdezés kimenete:

2           1           NONCLUSTERED INDEX                                 1                    0                     IX_TransactionHistoryArchive_ProductID
2           0           NONCLUSTERED INDEX                                 90                   89                    IX_TransactionHistoryArchive_ProductID

Szinte minden indexlap page compressed lett, csak 1 maradt row (a gyökéren kívül).

Hasonlítsuk össze a teljes tábla kiolvasásának IO költségét a page tömörítéssel és simán:

set statistics io on
select * from Production.TransactionHistoryArchive

alter table Production.TransactionHistoryArchive 
rebuild with (data_compression = none);

select * from Production.TransactionHistoryArchive

set statistics io off
(89253 row(s) affected)
Table 'TransactionHistoryArchive'. Scan count 1, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(89253 row(s) affected)
Table 'TransactionHistoryArchive'. Scan count 1, logical reads 622, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Harmadára esik vissza az IO, a háromszoros tömörítés miatt.

A módosítások költségére gyakorolt káros hatást HF-ként feladom a kedves olvasóknak. :)

Összegezve, a tömörítés egy igen kellemes szolgáltatás, amely hatalmas táblák esetén jelentősen csökkentheti az IO költséget, így ha az a szűk keresztmetszet, akkor nem csak sprórol a vinyóval, de még gyorsít is.

Hangsúlyozom viszont, hogy OLTP jellegű tábláknál sokat ronthat, pont ezért szándékosan nincs olyan sp a szerverben, ami minden táblára és indexre bekapcsolná a tömörítést, mert sok ember majom módjára zipelne. :) Sajnos persze már van fenn az interneten ilyesmi sp, nem is linkelem be.

És végül egy kellemetlen hír: a fícsör csak az enterprise verzióban lesz benne. Valahogy motiválni kellett az eladásokat, ez eléggé erős érv a űbergalaktikus verzió mellett.

Vista restore – szeretem

Thursday, April 3rd, 2008

Tegnap szétesett a laptopban a vinyó. Szerencsére volt mentésem, jó, kb. egy hónapos, de volt. Complete backup volt, Vistából indítva egy külső vinyóra, közben nyugodtan dolgoztam, nem zavarta.

Ma a szervizes emberke kihozta az új vinyót, még írta a papírokat már futott is a restore. Nem kérdezett feleslegeseket, egyszerűen csak tette azt, amire terveztetett. Most már a visszaállított oprendszerről írok. Elégedett vagyok, na.

Ami viszont fura, hogy újra kellett aktiválni a Vistát, mert azt mondja már 3 dolog megváltozott a vasban. Az tény, hogy a tápot is kicserélték hétfőn, de azzal együtt is ez csak 2 változtatás (meg persze nem veszi figyelembe a tápot :). Külső hddk, DVD író gondolom nem ér. De akkor mi az anyja kínjáért akart aktiválni?

SQL Server 2008 újdonságok 30. – Adatlapok tömörített tárolása I.

Thursday, April 3rd, 2008

A 2008-ban az egyik leghangsúlyosabban fejlesztett scenario az adattárházak építése és kezelése.
Gondoljunk csak az insert minimál loggingolósra fejlesztésére, a szó lesz majd a star joinok bitmap filter alapú optimalizálásáról (egy későbbi bejegyzésben), a particionált adatok párhuzamos feldolgozásáról, az indexelt nézetek, amelyek táblái állhatnak particionált lábakon, a merge utasítás, ami gyors adatátlapátolást tesz lehetővé, Change Data Capture, stb. Erősen Data Warehouse szagúak a fícsörök, nemde?
No, ezen a vonalon domborít a tömörítés is. Az SQL 2005 SP2 már bevezette a vardecimal tömörített tárolását, amely nem fix hosszúságon tárolja ezeket a számokat, csak olyan hosszan, amennyi az adott példány tényleges tárolásához kell. Például a 2.23 kevesebb helyet kér, mint a 2234234.23 vagy a 3.345353535345. Változó hosszúsággal ábrázolják tehát az egyébként fix hosszúságú decimal adatot, ezzel helyet spórolnak meg. Gondolom nem kell mondanom, miért pont ezt a típust rakták be az SP2-be, azért, mert a pénzmennyiségeket ebben szoktuk tárolni (nem kettes, hanem 10-es számrendszer alapú, ezért véges tizedes törteket pontosan tud ábrázolni, szembem mondjuk a real-lel, ami 2-es számrendszer alapú).
Az SQL Server 2008 nyilván ezen a vonalon ment tovább, és már nem csak a decimalt, hanem a többi fix hosszúságú számot is tudja változó hosszal, azaz tömörítve tárolni.
Mielőtt azoban megbeszélnénk az összes tömörítése módszert, nézzük, miért is jó ez nekünk? A compression elsődleges célja a nagy táblák adatainak tömörítése, egyszerűen a HDD-k jobb kihasználása érdekében. Azonban mivel így kevesebb IO művelettel járnak a lekérdezések, a végén még lehet – de nem biztos – hogy gyorsabbak lesznek a lekérdezések. Az adatmódosítások nyilván lassulnak. Ezért elsősorban közel csak olvasható adatokra érdemes használni. Nincs dráma a módosításnál, de pár 10%-kal lassabb lehet. A backup gyorsul, hisz kevesebb adatot kell kimásolni. A backup is tud tömöríteni 2008-ban, a kettő egymástól független, és használható együtt.
Mivel tömörebbek az adatlapok, jobban kihasználható a gép memóriája cache céljára, azaz egy 2x tömörítés hasonló hatású, mintha dupla annyi ramunk lenne cache céljára. Azért ez sem elhanyagolandó adat.
Lehet tömöríteni adatot, indexet és akár egy tábla vagy index bizonyos partícióit is. Ez utóbbi nagyon jó, mert így az archív adatokat lehet tömörítve tárolni régebbi partíciókban, míg az éppen töltött adatokat tömörítés nélkül, hogy ne lassuljanak a DML műveletek.
Hogyan tömörít az SQL Server 2008? Azért azt látni kell, hogy nem lehet egy zipet vagy egy rart berakni a szerverbe, mert bár az valószínűleg nagyobb tömörítési arányt érne el, de sokkal lassabb lenne tőle a szerver. Olyan tömörítés kellett, ami elég sokat tömörít, de nem túl nagy költséggel. Biztos sokféle módszert implementáltak és teszteltek, érdekes feladat lehetett.

Egyféle technikáról bár beszéltem, a fix adatok változó hosszúságú enkódolásáról. Ez működik a számokra és a char, nchar típusra. Kívülről persze ez nem látszik, az int továbbra is 4 bytenak látszik, annak ellenére, hogy belül lehet, hogy csak 1.5 byte. Row compression néven érhető el ez a tömörítés.

Főleg szöveges adatok esetén azonban ez a módszer nem tudna nyereséget adni, maximum ostobán megszerkesztett hosszú char, nchar oszlopoknál, de van annyi eszünk, hogy változó hosszúságú adatokat Nvarchar és társaiban tárolunk. Más módszer kell a tömörítésre, ez pedig a redundancia csökkentésével működik. Az első módszer az adatok első részében levő redundanciát űzi el. Pl. az Áladár és az Álamizsna szavakban az Ála karaktereket csak egyszer írják le a lap fejlécében modjuk 1-es index-szel, és a mezőkben csak 1dár és 1mizsna lesz. Ez a column-prefix tömörítés. Ami izgalmas ebben, hogy a módszer adattípustól függetlenül működik, ha pont egy integer első 3 byte-ja azonos egy varchar szöveg első 3 karakterét reprezentáló byte-tal, akkor összevonják őket.
Update: kissé leegyszerűsítettem itt a képet, de a megértéshez ez elég. Aki pontosan szeretné látni hogy működik ez, itt nézzen szét.
A másik módszer szótár alapú, azaz ha az alma szó egy lapon 15 sorban is szerepel bármely oszlopban, akkor csak egyszer tárolják le, és a sorokba mutatókat raknak az adatszótár adott bejegyzésére. Valójában a kettő együtt működik, először a közös prefixeket emelik ki, majd megnézik a maradékot (előző példában dár, mizsna), és ha az is többször szerepel, akkor a szótárazós módszerrel tömörítik.
Persze ezek nem csak szöveges, hanem bináris adatokra is mennek, csak így könnyebb volt szemléltetni a folyamatot. Update: pontosabban és részletesebben itt.
Melyiket használjam? A row compressionnek jelentősen kisebb a költsége, ezért a gyakrabban lekérdezett vagy módosított adatokhoz ez megfelelőbb. Cserébe nem tud annyira tömöríteni.
A gyakran használt indexeket valószínűleg nem érdemes tömöríteni, csak azokat, amelyek nagyok, de ritkán használatosak.
Kis táblákra kár baszakodni bármelyik módszerrel is, csak izzítjuk vele feleslegesen a procit.
Index seek-eken nem sokat javít a tömörítés, mert egy-egy sor miatt 6-8 lapot is ki kell csomagolni, ami felesleges macera. Range seek-ekre vagy index scan-ekre már megéri.
A sys.dm_db_index_operational_stats nézet megmutatja, melyik index mennyire és milyen módon van kihasználva (ó, ha ez meg lett volna már az SQL 2000-ben!)?
A nagy adatokra, mint varchar(max) és társai NEM működik a tömörítés, hisz az előbb leírt módszerek nyilvánvalóan nem mennek nagy adatokra, ezeket inkább a hagyományos stream alapú tömörítésekkel lehet összepakolni. Mit lehet tenni, ha ezeket is tömöríteni akarjuk?
1. Az alkalmazás maga tömörít. A mai világban ez már nem nagy dolog.
2. Tömörítő CLR UDF-et írunk, azzal tömörítünk a tárolás előtt, mondjuk egy sp-ben.
3. Olyan UDT-t írunk, ami tömörítve tárol. A méretlimit feloldása miatt ez most már lehetséges.
4. FILESTREAM oszlopot használunk tömörített NTFS könyvtárban. Ez nem tömörít olyan agresszíven, de elég gyors.

Ok, rájöttünk, hogy lehet, hogy kell nekünk a tömörítés. Mielőtt bezipelnénk az univerzumot érdemes kicsit méricskélni, mit várhatunk el tőle, hisz az adatainktól nagyban függ, mekkor lesz a nyereség, ha egyáltalán lesz? Először is, a tábla induló méretét az sp_spaceused spvel lehet megkapni.
Eztán az sp_estimate_data_compression_savings tárolt eljárással ki lehet próbáltatni, hogy egy adott tábla vagy index egy adott partícióján a row vagy page compression mennyit hozna a konyhára. Az sp persze nem áll neki a 80 Exabájtos táblát betömöríteni, hanem mintavételezéssel csinál egy kis minta táblát a tempdbben, és azt csomagolgatja, majd ennek eredményét vetíti vissza az eredeti táblára.
Méricskéljünk kicsit! (Majd egy hamarosan következő részben, mert elpukkant a laptopban a vinyó, így nincs kéznél szerverem).

SQL Server 2008 újdonságok 29. – insert-select bulk optimalizálások

Wednesday, April 2nd, 2008

Ahogy már korábban is írtam, közismert, hogy a minimálisan logolt vagy más néven bulk műveletek nagyon gyorsak, mert a tranzakciós logba nem kerül bele minden egyes pl. beszúrt sor a egy bulk insertnél, hanem csak azok az lapok (extentek? mikor mit írnak) kerülnek megjelölésre, amelyek módosulnak. Azaz sokkal kevesebb adat kerül a logba, több tucatszor gyorsabb lehet a művelet. Nem csak a bulk insert minimálisan logolt, hanem a truncate table, select into, writetext, updatetext, bizonyos index műveletek.

Ez eddig is így volt. Nade, 2008-ban már bizonyos esetkben az insert-select is miminálisan logolt! Ez nagy szám ám, mert sokan úgy töltenek be adatokat, hogy először nyersen behúzzák azokat egy átmeneti táblába, aztán elemezgetik, javítgatják, tisztítgatják, majd áttöltik egy másik táblába. A nyers betöltés mehetett már eddig is gyorsan bulk inserttel (.NETből a cégnél most használtam nemrég az SqlBulkCopy osztályt, durván gyorsabb volt, mint a sima insert), de a két tábla közötti adatátvitel eddig logolt, ergo lassú volt. Eddig.

No, most már megy gyors módon is az insert-select, ha:

“No nonclustered index exists on the table.
If the clustered index is unique, the IGNORE_DUP_KEY option must be set to OFF.
A table lock is specified on the target table.
Inserting insert into an empty table that has a clustered index
Inserting into a heap (azaz olyan tábla, amin nincs clustered index) that has no indexes but that can be non-empty”

Szóval azért nem lehet ezt ész nélkül használni, de odafigyelve nagyon gyors lehet.
Egy egyszerű példa. Előkészületek:

drop table Celtabla
drop table Forrastabla

create table Celtabla
(
id int,
adat nvarchar(1000)
)
go
create table Forrastabla
(
id int,
adat nvarchar(1000)
)
go

set nocount on

declare @i int = 1
while (@i < 1001) begin insert into Forrastabla values (@i, replicate('a', 1000)) set @i += 1 end [/source] Aztán a teszt: [source='sql'] begin tran --hogy ne truncate-olódjon a log mielőtt megnéznénk insert into Celtabla with (tablock) select * from Forrastabla select top 100 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName, Context from fn_dblog(null, null) where allocunitname='dbo.Celtabla' order by [Current LSN] desc --rollback --commit checkpoint --hadd ürüljön a log (full recoverynél persze nem menne) [/source] [source='c'] operation context log record fixed length log record length AllocUnitId AllocUnitName Context ------------------------------ ------------------------------ ----------------------- ----------------- -------------------- ------------------------------ ------------------------------ LOP_SET_BITS LCX_GAM 54 72 72057594062045184 dbo.Celtabla LCX_GAM LOP_SET_BITS LCX_IAM 54 72 72057594062045184 dbo.Celtabla LCX_IAM LOP_MODIFY_ROW LCX_PFS 62 92 72057594062045184 dbo.Celtabla LCX_PFS LOP_MODIFY_ROW LCX_PFS 62 92 72057594062045184 dbo.Celtabla LCX_PFS LOP_MODIFY_ROW LCX_PFS 62 92 72057594062045184 dbo.Celtabla LCX_PFS ... [/source] A log record length oszlop az érdekes, bár a sor 2 kbyte-os, mégis csak 92 byte kerül a logba, mert mimimális logolással (bulk) megy a táblába az insert. Ha kivesszük a tablock hintet, akkor átmegy sima logolós insertbe, ezért 2108 byte-ot kell neki minden sorhoz logolni: [source='c'] ------------------------------ ------------------------------ ----------------------- ----------------- -------------------- ------------------------------ ------------------------------ LOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP LOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP LOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP LOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP LOP_FORMAT_PAGE LCX_HEAP 80 84 72057594061455360 dbo.Celtabla LCX_HEAP LOP_MODIFY_ROW LCX_PFS 62 80 72057594061455360 dbo.Celtabla LCX_PFS LOP_INSERT_ROWS LCX_HEAP 62 2108 72057594061455360 dbo.Celtabla LCX_HEAP ... [/source] Az egész dolog működése finomodik majd még a CTP6 (februári, most aktuális) után, akkor majd frissítem ez a bejegyzést. A restrikciók listája fog csökkenni.

SQL Server 2008 újdonságok 28. – Filtered indexek

Tuesday, April 1st, 2008

Ez egy igen durva optimalizálási lehetőség azoknak (is), akik nagy táblákkal dolgoznak.
Normál esetben egy indexfa a teljes tábla adatainak egy részhalmazát, a kulcsként kijelölt oszlopok tartalmát tárolják rendezetten egy fában, így nagyon gyorsan lehet a kulcs(ok) alapján keresni. A hangsúly a teljes táblán van. Mi van, ha mondjuk több évnyi adat van egy táblában, de mi sokszor (de nem mindig) csak az utóbbi egy hónappal dolgozunk? Egyrészt lehet particionálni, ebbe most nem megyek bele, bár fontos téma.
Ha NC indexet rakunk a dátum oszlopra és kevés sort szűrünk le van esélyünk a hatékony szűrésre, de ha sok adatunk van jó nagy lesz az indexfa, feleslegesen, hisz mint állítottam, a lekérdezéseink tipikusan NEM érintik a régi adatokat, azaz az indexfa csak kis részét használjuk ki. Ráadásul sokszor úgy hozunk létre NC indexet, hogy jó sok oszlopot pakolunk bele, ezzel lefedve bizonyos lekérdezéseket, amelyeknek nagyon gyorsnak kell lenni. Azaz az index minden oszlopot tartalmaz, amire a lekérdezés hivatkozik, bármely részében, ekkor csak az indexfából is végrehajtható a lekérdezés, nem kell az adatlapokhoz nyúlni a szervernek. Ezt hívják cover indexnek és querynek. A cover indexek sokszor piszok nagyok, hisz lehet, hogy tucatnyi oszlopot is tartalmaznak. 2005-ben megjelent az included columns az indexben, ezzel a nem kulcs adatokat, azaz amelyekre nem szűrünk az index támogatásával csak a lefedettség miatt kell csak levélszinten rakja be a szerver az indexbe, közbenső szinten nem. Ennek örültünk, egyfajta mini clustered indexet hozhattunk létre az oszlopok egy részhalmazából. Piszok gyors lekérdezéseket lehetett így összehozni, de marha nagy lett az indexfa.

Jöhetnek a filtered indexek.

Filtered index esetén az index létrehozáskor meg lehet adni egy where feltételt, így az indexfa csak az általunk fontosnak vélt adatokat tartalmazza. Az előbbi példában mondjuk csak az utolsó hónap adatait. Vagy csak egy adott megye adatait. Vagy a nem NULL adatokat. Így az index nem tud megtámogatni minden lekérdezést, hisz nincs benne minden adat, viszont kisebb és gyorsabb lehet, mint egy nem szűrt index. Ráadásul a statisztikák is az adatok részhalmazára vonatkozik, így pontosabbak, mint a teljes táblásak.
Az index karbantartása is kisebb költségű adatmódosítások esetén, hisz lehet, hogy az adott sor nincs is az indexben, illetve kisebb az indexfa is, így a statisztika frissítése is kisebb költségű.

Lássunk egy példát a BOL-ból. Kiindulási táblánk az AdventureWorks BillOfMaterials táblája. Nem nagy, 4e soros, ezért nem lesznek látványosak a gyorsulások, de legalább kéznél van. Ebben az EndDate oszlop NULL-os, vannak is benne NULL értékek:

select 
sum(case when EndDate is not null then 1 else 0 end) NotNullos,
sum(case when EndDate is null then 1 else 0 end) Nullos
from Production.BillOfMaterials
NotNullos   Nullos
----------- -----------
199         2480

Sok gyakorlati lekérdezést láttam már, amely úgy néz ki, hogy a where feltételben van valamilyen szűrési feltétel AND valamilyen oszlop IS NOT NULL. No, ezeket jól meg tudja támogatni egy filtered index, ami csak a nem nullos adatokat indexeli.

Nézzük ezt a lekérdezést a BOL-ból:

select ProductAssemblyID, ComponentID, StartDate 
from Production.BillOfMaterials
where EndDate is not null;

Index nélkül. Cost: 20ms, IO: 22. Nem sok, dehát kicsi a tábla.

A BOL ezt a filtered indexet rakja rá:

create nonclustered index idx_FIBillOfMaterialsWithEndDate
on Production.BillOfMaterials (ComponentID, StartDate)
where EndDate IS NOT NULL;

Ez csak a not nullos EndDate értékű sorokat tartalmazza, azaz csak 199 sorból áll. Picike. De nem az EndDate szerint van rendezve, hanem ComponentID, StartDate szerint. Mit jelent ez az előbbi lekérdezésre nézve?
Felhasználja az indexet a szerver, Index Scan lesz (nem seek), Cost: 3.5ms, IO: 2. Nézzük csak. Miért használja egyáltalán az indexet, hisz nincs is EndDate szerint rendezve?
Ez egy olyan lekérdezés, amit az index lefed, mert minden oszlopa benne van az indexben. ProductAssemblyID, ComponentID, StartDate, EndDate, ezekre hivatkozik a select. ComponentID, StartDate triviálisan benne van az indexben, annak a kulcsa. Az EndDate nincs benne, de a szűrési feltétel pont azonos az indexével, így tudjuk, hogy az index pont azokat a sorokat tartalmazza, amelyek a feltételt kielégítik. És mi a helyzet a ProductAssemblyID-vel? Nos, mint tudjuk (tudjuk?) minden NC indexben benne vannak a CLU index oszlopai is, hisz az NC index levélszinten ilyenkor nem Row Locatorokat tartalmaz, hanem CLU kulcsokat, azaz az NC index használata után még a CLU indexen is végigmegy a szerver. Azaz az előbbi indexünk levélszinten a következőket tartalmazza: ComponentID, StartDate, ProductAssemblyID, ComponentID, StartDate, mert az utóbbi 3 alkotja a Clu indexet. Hogy mi, két oszlop is kétszer van az indexben? Igen, ezért nem szerencsés a példa. Csak zabálná a helyet. Szerencsére a szerver okosabb ennél (legalábbis a 2008, a DBCC PAGE-dzsel való vizsgálódásaim alapján), és csak egyszer tárolja le a többször szereplő oszlopokat. (Miután megírtam ezt a bejegyzést találtam egy linket, ami pont ezt a témát taglalja.)
No, a lekérdezést tehát lefedi az index, ezért nem kell az adatsorokhoz nyúlni, csak az indexből teljesíthető a lekérdezés. Azért Scan és nem Seek a művelet, mert a teljes index tartalmát bejárják, hisz nincs szűrés az index kulcs adataira. Így is hatékony a select, mert kicsi az indexfa. Hogy igazán elemében legyen az index, szűrjünk már egy kicsit a kulcsára is:

select ProductAssemblyID, ComponentID, StartDate 
from Production.BillOfMaterials
where ComponentID = 324 and EndDate is not null;

Ekkor használja az indexet, seekel. Cover query seek-kel, ez a leggyorsabb index művelet ami csak létezik a szerverben.

Nem akarom túlragozni a kérdést tovább, aki tudja, hogyan kell cover query-t készíteni NC index-szel, az most készíthet sokkal kisebb index fát is, filtered index segítségével, ha tudja, hogy a nagyon felgyorsítandó lekérdezés(ek) tudják használni a szűrt, kicsi indexfát.
Nem cover helyzetben is jó lehet ez, csak ekkor még elő kell szedni a sorokat az adatlapokról, amivel nő ugyan a költség, de még mindig gyors lehet.

Aki ismeri az SQL Server 2000-ben bevezetett indexelt nézeteket, az most azt mondja, mi ebben az új? Indexelt nézettel ugyanezt meg lehet csinálni. Nos, filtered indexben sokkal szűkebb a szűrési lehetőségek:

“The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.”

Emiatt gyorsabb lehet, mint az indexed view. Az indexelt view elképesztően fel tud gyorsítani pl. aggregáló műveleteket használó lekérdezéseket vagy előtárol join-okat, de finnyás a különböző set opciókra, emiatt viszont korlátozottabban lehet csak használni, ha nem stimmelnek lekérdezéskor, akkor nem használja az indexet, illetve az alaptáblák módosítása sem fog működni, hibát jelez. Emiatt jóval kevesebb helyen tudták bevetni az indexelt nézeteket, mint ahol be fogják a filtered indexet. Pont a nagyon behatárolt szűrések miatt nincsenek a set opciós megkötések mint a view-nál (a nullos kitételek is ezért vannak), ezért kényelmesebben lehet használni, noha butább, mint az indexelt nézet.

Pl. ha rendszeresen egyes kategóriákra kell szűrni egy nagy táblából, akkor azt jól meg lehet majd gyorsítani egy adott kategóriákat tároló filtered index-szel.

Szeressük a filtered indexet, mert az jó.