augusztus 2013 havi bejegyzések

A replikáció és az index rebuild

A replikáció egyszerű módja az adatbázisunk vagy annak egy részének a többszörözésére. A replikációról kell tudni, hogy a tranzakciós naplót (.ldf fájl) használja a replikálásra.

Nagy vonalakban a működése:

  • Sorrendben felolvassa a naplóból sql parancsok formájában a változásokat
  • Sorrendben eltárolja a distribution adatbázisban a felolvasott parancsokat
  • Sorrendben, batch-ben lefuttatja a parancsokat a replikán

Ha vannak nagy tábláink, és hát miért ne lennének, akkor azokat karban is kell tartani. Egyik ilyen karbantartási művelet az index rebuild. Előbb-utóbb elkerülhetetlen a használata (lesz olyan eset, amikor a reorganize már semmit sem ér). Tehát használjuk az index rebuild-et. Ha ezt még megfejeljük azzal, hogy az adatbázisunk FULL recovery modellben van, akkor elő is állt minden, hogy a replikációnk lehalhasson.

Tehát ami kell:

  • Replikáció
  • Index rebuild egy kellően nagy táblán (a lényeg, hogy tetemes mennyiségű logot generáljon)
  • FULL recovery modell

A rebuild legvégén történik valami – amit nem fejtettem meg, mert nem volt kedvem 50 millió sornyi tranzakciós logot átnézni -, ami miatt lehal a replikáció. Viszont a megoldás sokkal egyszerűbb, mint a probléma. Szerencsére az index rebuild a bulk műveletek közé tartozik, ezért, ha az adatbázist átállítjuk bulk-logged modellbe a rebuild idejére, akkor ez a probléma elkerülhető. Hátránya annyi, hogy amíg bulk-logged modellben vagyunk, addig nem tudunk időpontra visszaállni, mert ilyenkor lazábban naplóz az SQL Server. Ebben az időszakban célszerű sűrűbben készíteni tranzakciós log mentést, hogy kevésbé fájjon, ha esetleg baj lesz.

Reklámok

Extended property, használjuk egészséggel

Aki még nem használt extended property-t, azoknak nagy segítség lehet. Egyes objektumokhoz  készíthetünk vele leírásokat, megjegyzéseket. Pl.: egy táblához elmentjük, hogy mire való, ami fejlesztőknek lehet segítség, de akár adatbázisokhoz is, ami az üzemeltetőket segíti. Ha ügyesen használjuk ezeket a funkciókat, akkor egyfajta nyilvántartást vagy használati útmutatást is tudunk vele készíteni, ráadásul nem veszik el, mert ott van az adatbázisban rögzítve.

Nézzünk egy példát adatbázis extended property használatára. Az egyszerűség kedvéért a tempdb-hez csináljunk ilyet. Ezt tehetjük grafikusan vagy T-SQL-ből is:

Grafikusan:

01_extended_property

05_extended_property

Illetve T-SQL-ből:

USE [master]
GO
EXEC [tempdb].sys.sp_addextendedproperty @name=N'application', @value=N'Nem tartozik hozzá application'
GO
EXEC [tempdb].sys.sp_addextendedproperty @name=N'using', @value=N'Az adatbázist az SQL Server használja. Ide készít pl átmeneti táblákat'
GO

Fent elkönyveltük, hogy mire használatos az adatbázis és hogy csatlakozik e hozzá valamilyen alkalmazás. A lehetőségeknek csak a fantázia szab határt 🙂

Most nyerjük is ki az adatokat. Ezt többféleképpen megtehetjük, nekem az erre kitalált nézet tetszik a legjobban.

SELECT * FROM tempdb.sys.extended_properties WHERE class = 0

Az eredmény pedig itt látható:

06_extended_property

Lehetőség van egyben kilistázni egy szerver összes adatbázisának tulajdonságát is, itt egy példa:

DECLARE @sql NVARCHAR(max) = N''
SELECT @sql += N'SELECT ''' +  QUOTENAME(name) + ''' as db, name COLLATE Latin1_General_CI_AS, value FROM ' + QUOTENAME(name) + '.sys.extended_properties WHERE class = 0 union all ' FROM sys.databases
SET @sql = LEFT(@sql, LEN(@sql) - LEN(' union all '))
EXEC sp_executesql @sql

Itt felhívnám a figyelmet a query-ben a “name COLLATE Latin1_General_CI_AS”. Aki nem ismeri, annak mondom, hogy ez az eltérő adatbázisok collation konfliktusát igyekszik megszüntetni. Mindenkinek célszerű a saját szerveréhez beállítani a collate-et a lekérdezésben.

Aki többet szeretne tudni a témáról annak ajánlom a books online ide vonatkozó fejezetét.

A NOLOCK, avagy élet az aknamezőn

Sokat töprengtem rajta, hogy mi legyen a címe a mai postnak, végül ezt találtam a legtalálóbbnak.

A nolock parancsot minden fejlesztő ismeri, szereti, használja vagy használta. Általánosan elterjedt tévhit, hogy egyetlen hátránya a piszkos olvasás, cserébe pedig hihetetlenül gyors válaszidőket kapunk. Aki eddig ezt hitte, azt ki kell ábrándítsam. Ennél komolyabb hátulütője is van. Mégpedig egy hibás eredményhalmaz lehetősége. Mielőtt kifejteném összegezzük a nolockot:

  1. Nagyon gyors válaszidőket kapunk: nem veszi figyelembe a zárolásokat, nincs várakozás
  2. Piszkos olvasást eredményezhet: nem veszi figyelembe a zárolásokat, ezért a nyitott tranzakciókba is beleolvas
  3. Hibás eredményhalmazt kaphatunk: Ha page split (lapszétválasztás) közben érkezünk adatot olvasni, akkor vagy kihagyunk valamit az olvasásból vagy duplán olvassuk azt be (Ezt egy jelenségnek nevezném).

Az 1. és 2. pontról átlépem, mert tele van az internet jobbnál jobb cikkekkel. Helyette inkább a 3. pont komolyságáról írnék és mutatok egy jól használható demót.

Az adatok a merevlemezen tárolódnak, mégpedig egy vagy több adatbázisfájlban (általában .mdf és .ndf kiterjesztések). Ezek a fájlok 8 KB-os úgynevezett page-ekből állnak. Ezekre a page-ekre ír az SQL szerver “mindent”. Az előzőekből következik, hogy a page-eken találhatóak pl: a táblák / indexek sorai is. Amikor az SQL Server “teleírt” egy page-et, akkor kezd egy következőt. Viszont, ha olyan adat érkezik, aminek egy teleírt page-en a helye, akkor a tele page-et ketté osztja. A page-en lévő adatok felét hagyja a helyén a másik felét egy új, üres page-re helyezi. Így lesz két félig üres page-ünk. Az érkező adat pedig a két fél page egyikére fog kerülni (az most mindegy, hogy hova). Ez a page split (buuummmm). Ha a nolock hinttel ellátott (vagy read uncommitted izolációs szinten futtatott) select elkap egy page split-et, akkor előfordulhat, hogy 1-1 ilyen fél page kimarad, vagy kétszer kerül beolvasásra.

Az egész attól egy aknamező, hogy nem tudod, hogy hol és mikor szaladsz bele. Bárhol, bármikor előjöhet és egy szempillantás alatt véget is ér. De arra pont elég, hogy egyszer rossz eredményhalmaz kerüljön a felhasználó kezébe (és problémázzon). Simán okozhatja, hogy a felhasználó egyik pillanatban kétszer annyi pénzt lát a számláján, mint amennyi valójában van. De akár azt is, hogy a felhasználó azt látja, hogy egy adott számla kétszer került lekönyvelésre, holott ez nem igaz, csak kétszer került megjelenítésre.

Demózzuk le. A példa nem lesz életszerű, de nagyon jól elkapja a page split-et és pontosan látszik, hogy hol a gond. A három scriptet nyissuk meg 3 külön management studió ablakban. Futtassuk le az elsőt. Ha lefutott, akkor a 3. scriptet, hogy lássuk a jó eredményeket, így lesz mihez viszonyítani. Ez után, futtassuk a 2. scriptet, majd indulás után rögtön a 3. script abalakban található lekérdezések egyikét, többször egymás után, amíg bele nem botlunk a rossz eredményhalmazba.

– Script 1: Kell egy tábla, amin előidézzük a page split-eket.

use tempdb
go

set xact_abort on;

if object_id('tempdb..users', 'U') is not null
    drop table dbo.users
go

create table dbo.users (
    id uniqueidentifier not null primary key clustered default newsequentialid(),
    name nvarchar(20) not null,
    amount int not null default 1,
    user_id int not null identity(1,1)
)
go

begin tran
    ;with cte (user_id) as (
        select 1 as user_id
        union all
        select user_id + 1 from cte
        where user_id < 10000
)

insert into dbo.users (name)
select top 40000 names.name
from cte
cross join (values ('József'), ('Mária'), ('Botond'), ('Ágnes')) names (name)
option (maxrecursion 10000)
commit
go

declare @i int = 0

while (@i < 4)
begin
    set @i += 1;
    begin tran
        insert into dbo.users (name) select name from users
    commit
end

– Script 2: A bomba. Ez 5-15 másodperc között fut le. Ez ideig kell nyomkodni majd a 3-as scriptet.

use tempdb
go
update users set id = newid()
option (maxdop 1)

– Script 3: Itt van sok query, hogy lássuk a hibák sokféleségét. Célszerű először a 2-es query futtatása előtt lefuttatni, hogy lássuk mik a jó eredmények.

use tempdb
go

-- Query 1
declare
    @from uniqueidentifier = '00000000-0000-0000-0000-000000000000'
   ,@to uniqueidentifier =   'FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF'

select count(*) as db from dbo.users with (nolock)
where id between @from and @to
option (maxdop 1)

-- Query 2
select name, sum(amount) sum_amount, count(*) as cnt
from dbo.users (nolock)
group by name
option (maxdop 1)

-- Query 3
select
    case when name is null then 'Summary' else name end as name,
    count(*) as cnt
from dbo.users (nolock)
where user_id between 1 and 100000000
group by name
with rollup
order by name
option (maxdop 1)

-- Query 4
select count(id) as db from dbo.users (nolock)
where user_id between 0 and 11111999
option (maxdop 1)

-- Query 5
select * from dbo.users (nolock) option (maxdop 1)

-- Query 6
;with cte as (
    select top 1000000
        row_number() over(partition by user_id order by name) uid, *
    from dbo.users (nolock)
)

select * from cte where uid > 1 option (maxdop 1)

-- Query 7
select t.name, si.rowcnt
from sys.tables t
inner join sys.sysindexes si
    on t.object_id = si.id
where si.indid < 2 and t.name = 'users'

Remélem a demó nálatok is olyan jól szemlélteti a problémát, mint nálam.

A kérdés már csak az, hogy hogyan védekezhetünk ellene? Egyetlen védelem, ha elhagyjuk a nolock hintet, illetve nem használjuk a read uncommitted szintet. Minden mással csak a bekövetkezés esélye csökkenthető.