Archiv autora: Jakub Rychlý

SELECT do proměnné

Bolestivě jsem zjistil, že je propastný rozdíl mezi 

SELECT @prom = 5 FROM sys.objects WHERE 1 = 0

a

SET @prom = ( SELECT 5 FROM sys.objects WHERE 1 = 0)

První dotaz proměnnou neovlivní. Druhý nastaví dle očekávání hodnotu NULL.

Kompletní příklad.

DECLARE @prom INT = 1
SELECT @prom = 5 FROM sys.objects WHERE 1 = 0
SELECT @prom --vypise 1
SET @prom = ( SELECT 5 FROM sys.objects WHERE 1 = 0)
SELECT @prom --vypise NULL

Úrovně izolace

Problémy konzistence databáze

Ztracený update
Dva procesy přepisují stejná data na novou hodnotu. Hodnota z prvního procesu je ztracena a přepsána hodnotou z druhého procesu.

Špinavé čtení
Proces čte data, která upravuje jiná transakce, která ještě nebyla potvrzená COMMITem. Pokud druhý proces provede ROLLBACK, pak první proces přečetl chybná data.

Neopakovatelné čtení
Čtení je neopakovatelné, pokud proces v rámci transakce čte dvakrát stejná data a pokaždé přečte jinou hodnotu. To se stane pokud hodnotu mezitím změnil jiný proces.

Pro příklad předpokládejme, že máme tabulku se stavem zboží a v ní jeden kus zboží s id_zbozi = 5. Dvě souběžné transakce zkontrolují jestli je zboží dostatek a pokud ano, tak zboží vydají a sníží jeho stav na skladě.

čas Transakce 1 Transakce 2
1 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @prodano_kusu INT = 1
DECLARE @id_zbozi INT = 5

BEGIN TRAN
--zboží je dostatek, pokračujeme dál
IF (
	(
		SELECT pocet 
		FROM stav_skladu 
		WHERE id_zbozi = @id_zbozi
	) >= @prodano_kusu)
BEGIN
2 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @prodano_kusu INT = 1
DECLARE @id_zbozi INT = 5

BEGIN TRAN
--zboží je dostatek, pokračujeme dál
IF (
	(
		SELECT pocet 
		FROM stav_skladu 
		WHERE id_zbozi = @id_zbozi
	) >= @prodano_kusu)
BEGIN
3 
	--odeslani zbozi
	SELECT 'zbozi vydano'
	
	--update stavu skladu
	UPDATE stav_skladu 
	SET pocet = pocet - @prodano_kusu 
	WHERE id_zbozi = @id_zbozi

	--snížili jsme počet na 0
END
COMMIT
4 
	--odeslani zbozi
	SELECT 'zbozi vydano'
	
	--update stavu skladu
	--zde dojde k neopakovatelnému čtení
	--počet se změnil od vyhodnoceni podmínky
	UPDATE stav_skladu 
	SET pocet = pocet - @prodano_kusu 
	WHERE id_zbozi = @id_zbozi

	--snížili jsme počet na -1
END
COMMIT

Fantom
Fantom se objeví, pokud v rámci jedné transakce proběhne dvakrát stejný dotaz a pokaždé vrátí jiný počet záznamů.

Dvojité čtení
Jedná se o opakovaně přečtený záznam, který během čtení změnil svou polohu v indexu. Záznam je například přečten na začátku indexu a než čtení doběhne do konce indexu, tak jiná transakce tento záznam modifikuje a přesune jej tím na konec indexu. První čtoucí transakce pak záznam přečte ještě jednou až se dostane ke konci indexu.

Úrovně izolace (Isolation Level)

SQL server 2008 podporuje 5 úrovní izolace.

Read Uncommitted
Mohou se objevit všechny chyby, kromě ztracených updatů. Může se objevit problém, že se server rozhodne číst datové stránky postupně. Pak pokud je před koncem čtení vložen řádek do již přečtené stránky, tak se nedostane do výsledků. Pokud dojde k rozpadu stránky, může se stát, že je některý řádek přečten dvakrát.

Read Committed
Default isolation level. Existují dvě varianty, optimistická a pesimistická, v závislosti na nastavení READ_COMMITTED_SNAPSHOT (Default OFF). Zajišťuje, že aplikace nemůže přečíst nepotvrzená data. Při čtení se používají sdílené zámky, které jsou uvolňovány, jak nejdříve je to možné. Sdílený zámek umožňuje ostatním transakcím číst, úpravy ale musí počkat. READ_COMMITTED_SNAPSHOT také zaručuje, že aplikace nikdy nepřečte nepotvrzená data. Pokud je modifikovaný řádek, pak server vytvoří verzi řádku s předchozími potvrzenými hodnotami. Modifikovaná data jsou uzamčená, ale ostatní procesy mohou číst předchozí potvrzené hodnoty.

Repeatable Read
Zajišťuje větší bezpečnost. Dotaz provedený v transakci dvakrát, dostane pokaždé stejný výsledek. Sdílené zámky jsou drženy po celou dobu transakce a ne jen po dobu nezbytně nutnou ke čtení. Tím je zaručeno, že ostatní procesy nemohou čtená data po dobu transakce změnit. Samozřejmě snižuje možnost souběhu a degraduje výkon.

Snapshot
Druhá optimistická úroveň izolace. Stejně jako READ_COMMITTED_SNAPSHOT umožňuje číst starší verzi potvrzených dat.

Serializable
Odstraňuje výskyt fantomů. Jedná se o nejbezpečnější režim, protože odstraňuje všechny problémy jmenované na začátku. Server musí zamykat nejen čtená data, ale i data, která by mohl jiný proces vložit do čteného rozsahu řádků. Používají se key-range zámky nad indexem sloupce. Pokud čtený sloupec nemá index, zamyká se celá tabulka. Zámek se drží po celou dobu transakce.

Isolation Level Špinavé čtení Neopakovatelné čtení Fantom Dvojité čtení
Read Uncommitted Ano Ano Ano Ano
Read Committed Ne Ano Ano Ano
Read Committed snapshot Ne Ano Ano
Repeatable Read Ne Ne Ano Ne
Snapshot Ne Ne Ne
Serializble Ne Ne Ne Ne

Srovnání Read Committed Snapshot Isolation (RCSI) a Snapshot Isolation (SI)
RCSI se zapne pouze na úrovni databáze. SI se musí zapnout na úrovni databáze (ALLOW_SNAPSHOT_ISOLATION) a pak v každém připojení použít SET TRANSACTION ISLOTION LEVEL SNAPSHOT.
RCSI může v rámci jedné transakce stejným dotazem přečíst jednou starší verzi zamčených dat a podruhé (po COMMIT ve druhé transakci) přečíst novou verzi dat. Vyskytuje se zde tedy anomálie neopakovatelného čtení. U SI přečteme v transakci pokaždé stejnou hodnotu, přestože ji mohla mezitím druhá transakce změnit a potvrdit.
U SI nemůže dojít ke ztraceným updatům. Pokud by k tomu mělo dojít, SQL server vyhodí chybu se zprávou 3960, na kterou by měl programátor reagovat znovuspuštěním transakce.

Change Tracking – sledování změn pro synchronizaci databází

Pokud potřebujeme synchronizovat změny v databázi do jiných databází, pak potřebujeme mechanizmus, kterým zjístíme, které záznamy se od poslední synchronizace změnily. Na to právě v SQL serveru slouží Change Tracking. Change Tracking je také možné využít pro aktualizací keší.

Change Tracking se zapíná na úrovni databáze. Zde bude změny uchovávat dva dny, starší změny pak zahodí. Interval CHANGE_RETENTION je tedy nutné nastavit delší než jak často budeme databáze synchronizovat.

ALTER DATABASE mojedb
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
GO

Připravíme si tabulky, na kterých si sledování změn otestujeme.

CREATE TABLE tab1(
	id INT IDENTITY(1,1), 
	data1 NVARCHAR(50), 
	data2 NVARCHAR(50), 
	CONSTRAINT pk_tab1 PRIMARY KEY (id)
)
GO

CREATE TABLE tab2(
	id INT IDENTITY(1,1), 
	data1 NVARCHAR(50), 
	data2 NVARCHAR(50), 
	CONSTRAINT pk_tab2 PRIMARY KEY (id)
)
GO

Zapneme sledování změn na úrovni tabulek.

ALTER TABLE tab1
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
GO

ALTER TABLE tab2
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
GO

Představíme si, že jsem provedli synchronizaci všech dat.
Zjístíme aktuální verzi změn. S tímto číslem se budeme při další synchronizaci doptávat na to, co se změnilo.

SELECT CHANGE_TRACKING_CURRENT_VERSION();

--vysledek 2

Nasimulujeme nějaké změny na tabulkách.

INSERT INTO tab1(data1, data2) VALUES('a','a')
INSERT INTO tab1(data1, data2) VALUES('b','b')
INSERT INTO tab2(data1, data2) VALUES('a','a')
INSERT INTO tab2(data1, data2) VALUES('b','b')
INSERT INTO tab1(data1, data2) VALUES('c','c')
UPDATE tab1 SET data1 = 'd' WHERE data1 = 'c'
GO

Zjístíme změněné záznamy pro novou synchronizaci. Číslo poslední známé verze (2) známe z minula.

SELECT *
FROM CHANGETABLE(CHANGES tab1, 2) AS CT

SELECT *
FROM CHANGETABLE(CHANGES tab2, 2) AS CT

/*
SYS_CHANGE_VERSION   SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS                                                                                                                                                                                                                                                 SYS_CHANGE_CONTEXT                                                                                                                                                                                                                                                 id
-------------------- --------------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -----------
3                    3                           I                    NULL                                                                                                                                                                                                                                                               NULL                                                                                                                                                                                                                                                               1
4                    4                           I                    NULL                                                                                                                                                                                                                                                               NULL                                                                                                                                                                                                                                                               2
8                    7                           I                    NULL                                                                                                                                                                                                                                                               NULL                                                                                                                                                                                                                                                               3

(3 row(s) affected)

SYS_CHANGE_VERSION   SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS                                                                                                                                                                                                                                                 SYS_CHANGE_CONTEXT                                                                                                                                                                                                                                                 id
-------------------- --------------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -----------
5                    5                           I                    NULL                                                                                                                                                                                                                                                               NULL                                                                                                                                                                                                                                                               1
6                    6                           I                    NULL                                                                                                                                                                                                                                                               NULL                                                                                                                                                                                                                                                               2

(2 row(s) affected)
*/

Ve výsledku není vidět poslední update, který pro synchronizaci není podstatný. Přeneseme prostě vše co přibylo.
Pokud bychom byli ve verzi 7, update by vidět byl.

SELECT *
FROM CHANGETABLE(CHANGES tab1, 7) AS CT

/*
SYS_CHANGE_VERSION   SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS                                                                                                                                                                                                                                                 SYS_CHANGE_CONTEXT                                                                                                                                                                                                                                                 id
-------------------- --------------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -----------
8                    NULL                        U                    0x0000000002000000                                                                                                                                                                                                                                                 NULL                                                                                                                                                                                                                                                               3

(1 row(s) affected)

*/

Také je možné vyhodnotit, který sloupec byl změněn, abychom zbytečně nesynchronizovali nezměněná data.

DECLARE @data1ColumnId int = COLUMNPROPERTY(OBJECT_ID('tab1'), 'data1', 'ColumnId')
DECLARE @data2ColumnId int = COLUMNPROPERTY(OBJECT_ID('tab1'), 'data2', 'ColumnId')

SELECT
    CHANGE_TRACKING_IS_COLUMN_IN_MASK(@data1ColumnId, CT.SYS_CHANGE_COLUMNS) AS data1Zmenen, 
	CHANGE_TRACKING_IS_COLUMN_IN_MASK(@data2ColumnId, CT.SYS_CHANGE_COLUMNS) AS data2Zmenen
FROM CHANGETABLE(CHANGES tab1, 7) AS CT

/*
data1Zmenen data2Zmenen
----------- -----------
1           0

(1 row(s) affected)
*/

Fill factor

Fill factor udává procento zaplnění datových stránek při vytvoření, reorganizaci, nebo rebuildu indexů.

Hodnoty 0 a 100 jsou shodné a znamenají, že stránka bude plná. Hodnota 70 znamená, že stránka bude zaplněna na 70%, a že v ní tedy bude ještě 30% volného místa.

Fill factor se nastavuje na jinou hodnotu než 100, pokud chceme zamezit častému dělení stránek, nebo rychle rostoucí fragmentaci indexu. Dělení stránek je nákladná operace. Pokud ve stránce necháme volné místo, pak vložení nových dat nemusí hned znamenat dělení stránky. Fill factor tedy může snižovat náklady při vkládání záznamů.

Na druhou stranu je potřeba si uvědomit, že pokud nastavíme fill factor na hodnu 50, pak zdvojnásobíme velikost indexu a tím dvakrát zpomalíme čtení, protože bude nutné přečíst dvakrát tolik dat. Data budou zabírat dvakrát tolik jak v paměti, tak na disku.

Volné místo dle fill factoru se ve stránkách vytváří při vytvoření, reorganizaci, nebo rebuildu indexu. S vkládanými záznamy se již neudržuje.

Fill factor nemá cenu nastavovat pro indexy nad sloupci, které jsou stále rostoucí řadou (identity, časové razítka), protože do takového indexu se záznamy vkládají na konec a volné místo uprostřed indexu se tak nevyužije.

Clustered index – více sloupců

Pro vazební tabulky, které tvoří vazbu M:N je vhodný clusterovaný index nad více sloupci. Tedy sloupci, které tvoří cizí klíče. Tento vzor je podobný vzoru pro cizí klíč.

Mějme tabulku zamestnanec, která má clusterovaný index podle vzoru identity sloupec.

CREATE TABLE zamestnanec(
	id_zamestnanec INT IDENTITY(1,1),
	jmeno NVARCHAR(100),
	prijmeni NVARCHAR(100),
	CONSTRAINT pk_zamestnanec PRIMARY KEY CLUSTERED( id_zamestnanec )
)
GO

Dále mějme tabulku pracovni_role, která má také clusterovaný index podle vzoru identity sloupec.

CREATE TABLE pracovni_role(
	id_pracovni_role INT IDENTITY(1,1),
	nazev_role NVARCHAR(100),
	CONSTRAINT pk_pracovni_role PRIMARY KEY CLUSTERED( id_pracovni_role )
)
GO

Vztah mezi zaměstnancem a jeho rolí pak bude reprezentován tabulkou zamestnanec_pracovni_role, která bude podle vzoru více sloupců.

CREATE TABLE zamestnanec_pracovni_role(
	id_zamestnanec_pracovni_role INT IDENTITY(1,1),
	id_zamestnanec INT,
	id_pracovni_role INT,
	CONSTRAINT pk_zamestnanec_pracovni_role PRIMARY KEY NONCLUSTERED 
	(
		id_zamestnanec_pracovni_role
	),
	CONSTRAINT clus_zamestnanec_pracovni_role UNIQUE CLUSTERED(
		id_zamestnanec,
		id_pracovni_role
	)
)
GO

ALTER TABLE zamestnanec_pracovni_role
ADD CONSTRAINT fk_zamestnanec_pracovni_role_id_zamestnanec 
	FOREIGN KEY(id_zamestnanec)
	REFERENCES zamestnanec(id_zamestnanec)
GO

ALTER TABLE zamestnanec_pracovni_role	
ADD CONSTRAINT fk_zamestnanec_pracovni_role_id_pracovni_role 
	FOREIGN KEY(id_pracovni_role)
	REFERENCES pracovni_role(id_pracovni_role)
GO

CREATE INDEX i_fk_zamestnanec_pracovni_role_id_pracovni_role 
	ON zamestnanec_pracovni_role(id_pracovni_role)
GO

Dohledání kandidátů pro sparse column

Dohledejme si sloupce, které jsou nullable a vyhovují podmínkám pro řídké (sparse) sloupce.

SELECT s.name [schema], o.name [table], c.name [column], c.is_nullable, c.is_sparse, t.name dataType, c.max_length, 0 nullRows, 0 notNullRows, CAST(0 AS NUMERIC(18,2)) ratio
INTO #sparseCandidate
FROM sys.objects o 
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.columns c ON c.object_id = o.object_id
JOIN sys.types t ON c.system_type_id = t.user_type_id
WHERE o.type  = 'U' --user table
AND c.is_nullable = 1
AND t.name NOT IN ('geography', 'geometry', 'image', 'ntext', 'text', 'timestamp')
AND c.is_sparse = 0
AND c.is_nullable = 1
AND c.is_rowguidcol = 0
AND c.is_identity = 0
AND c.is_computed = 0
AND c.is_filestream = 0
AND c.default_object_id = 0
AND c.rule_object_id = 0
AND NOT EXISTS(--nemuze byt v unikatnim klici ani v clusterovanem indexu
	SELECT 1
	FROM sys.indexes i
	JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
	WHERE o.object_id = i.object_id AND c.object_id = ic.object_id AND c.column_id = ic.column_id
	AND (
		i.is_primary_key = 1
		OR i.type = 1 --clustered
	)
)

Dohledáme zaplnění sloupců hodnotami.

DECLARE @schema CHAR(100)
DECLARE @table CHAR(100)
DECLARE @column CHAR(100)
DECLARE @command NVARCHAR(MAX)

DECLARE crs CURSOR FOR SELECT [schema], [table], [COLUMN] FROM #sparseCandidate
OPEN crs
FETCH NEXT FROM crs INTO @schema, @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @command = '
		UPDATE #sparseCandidate 
		SET nullRows = (
			SELECT COUNT(*)
			FROM '+@schema+'.'+@table+'	WHERE '+@column+' IS NULL
		),
		notNullRows = (
			SELECT COUNT(*)
			FROM '+@schema+'.'+@table+'	WHERE '+@column+' IS NOT NULL
		)
		WHERE [schema] = '''+@schema+''' AND [table] = '''+@table+''' AND [column] = '''+@column+'''
	'
	PRINT @command
	EXEC (@command)
	FETCH NEXT FROM crs INTO @schema, @table, @column
END

CLOSE crs
DEALLOCATE crs

Dopočteme poměr.

UPDATE #sparseCandidate 
SET ratio = CAST( nullRows * 100 / (nullRows + notNullRows) AS NUMERIC(18,2)) 
WHERE nullRows <> 0 AND notNullRows <> 0

Řekněme, že nás zajímají sloupce s poměrem nad 70%. Tyto sloupce je vhodné nastavit jako sparse a případně doplnit index o filtr na NOT NULL.

SELECT * 
FROM #sparseCandidate
WHERE ratio > 70
ORDER BY ratio DESC

Clustered index – identity sloupec

Základním vzorem pro tvorbu clusterovaného indexu je identity sloupce. Clusterovaný index je postaven nad primárním klíčem, který je tvořen identity sloupcem.

Příkladem buď tabulka pro hlavičku faktury:

CREATE TABLE faktura(
	id_faktura INT IDENTITY(1,1),
	cislo_faktury CHAR(20),
	CONSTRAINT pk_faktura PRIMARY KEY CLUSTERED(id_faktura)
)

Clustered index – cizí klíč

Podívejme se na vzor pro clusterovaný index, který je možné s výhodou použít u tabulek, které mají vazbu 1:N (master/detail, hlavička/řádky). Vzor cizí klíč se zde použije pro závislou tabulku, tedy pro detaily, nebo řádky.

Mějme tabulku pro fakturu, která používá vzor identity sloupec.

CREATE TABLE faktura(
	id_faktura INT IDENTITY(1,1),
	cislo_faktury CHAR(20),
	CONSTRAINT pk_faktura PRIMARY KEY CLUSTERED(id_faktura)
)

A dále vytvořme tabulku pro řádky faktury, která využije vzor cizí klíč.

CREATE TABLE faktura_radek(
	id_faktura_radek INT IDENTITY(1,1),
	id_faktura INT NOT NULL,
	nazev_polozky NVARCHAR(500),
	castka DECIMAL(18,2),
	CONSTRAINT pk_faktura_radek PRIMARY KEY NONCLUSTERED(id_faktura_radek),
	CONSTRAINT clus_faktura_radek UNIQUE CLUSTERED(id_faktura, id_faktura_radek),
	CONSTRAINT fk_faktura_faktura_radek FOREIGN KEY (id_faktura) REFERENCES faktura(id_faktura)
)

U tohoto vzoru je na primární klič použit neclusterovaný index. Clustrovaný index je pak postavený nad kombinací sloupců pro cizí klíč a primární klíč. Umožňuje tak serveru rychle vyhledat položky faktury a všechny její sloupce při jediném průchodu clusterovaným indexem.

Non-clustered index – základní otázky

Při vytváření neclusterovaných indexů je potřeba si zodpovědět několik důležitých otázek.

Jaká je frekvence změn v kličových sloupcích neclusterovaného indexu?
Čím více změn, tím více je nutných přesunů položek v B-stromu indexu, což znamená režii navíc.

Které často prováděné dotazy má index zlepšit?

Jakou business logiku index řeší?
Index je důležitý i pokud je využit jednou měsíčně, ale řeší důležitou aplikační úlohu.

Jaké jsou náklady na údržbu vs. přínos indexu?
Je potřeba zvážit zvýšenou režii při udržování indexu, zabrané místo, čas pro defragmentaci indexu se skutečným přínosem indexu.

Clustered Index – základy

Clusterovaný index je B-strom, který tvoří samotnou databázovou tabulku. Data v B-stromu jsou uspořádána na základě klíčových sloupců. Správný návrh clusterovaného indexu je důležitý pro optimální přístup k datům.

Dobře definovaný clusterovaný index by měl být:

  • statický – data ve sloupcích indexů se nesmí s časem měnit
  • krátký – např. do 8B
  • unikátní
  • stále rostoucí řadou