Archiv pro štítek: index

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

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

Statistiky v MSSQL serveru

Podrobnosti o existující statistice je možné zjistit v

  • SSMS – Object Explorer – statistics – properties – Details.
  • systémových pohledech sys.stats a sys.stats_columns
  • DBCC SHOW_STATISTICS(<table_name>, <stats_name>) [WITH HISTOGRAM]

Co statistika obsahuje?

All Density – 1 / (počet jedinečných hodnot)

Statistika má maximálně 200 řádků.

Řádek statistiky obsahuje:

  • RANGE_HI_KEY – Horní mez intervalu kroku histogramu.
  • RANGE_ROWS – počet řádků v intervalu, mimo horní mez
  • EQ_ROWS – počet řádků odpovídající horní mezi intervalu
  • DISTINCT_RANGE_ROWS – počet jedinečných hodnot v intervalu, mimo horní mez
  • AVG_RANGE_ROWS – průměrná hodnota počtu jedinečných hodnot, mimo horní mez. Tedy RANGE_ROWS / DISTINCT_RANGE_ROWS

Pokud v exekučním plánu vidíme velmi rozdílnou hodnotu v údaji Estimated Number of Rows a Actual Number of Rows, pak máme problém se statistikami.

Pokud je na úrovni SQL serveru zapnuto Auto Create Statistics a Auto Update Statistics (a tyto hodnoty by měly být zapnuty), pak pokud server při vytváření exekučního plánu zjistí chybějící statistiky respektive zastaralé statistiky, tak dojde k vytvoření potřebné statistiky respektive k občerstvení zastaralé statistiky. To může zdržovat při vykonávání dotazu. Odchytit update statistik při vykonání dotazu je možné Profilerem – událost Performance/Auto Stat.

Při rebuildu indexu dojde k aktualizaci statistik s ním spojených. V tomto případě se provede fullscan, tedy statistiky se vypočtou ze všech řádků a dostaneme tak nejpřesnější možné statistiky. Po tomto není vhodné aktualizovat statistiky s implicitním nastavením, výsledek by byl horší než při použití fullscan.

Server automaticky (pokud je zapnuto Auto Create Statistics) vyrábí statistiky, které potřebuje pro vytvoření plánu vykonávání dotazu. Statistiky vyrábí jen pro jednotlivé sloupce. Automaticky není schopen vyrobit statistiky pro kombinací sloupců. V určitých případech může být vhodné takovéto statistiky vytvořit manuálně, nebo za použití Database Tuning Advisor.

Filtrované statistiky

S vytvořením filtrovaného indexu se vytvoří i filtrovaná statistika.
Filtrované statistiky je možné vytvářet i manuálně.
Výhodou je možnost obejít limit 200 řádků statistiky tak, že vytvoříme dvě statistiky v různých intervalech hodnot. Například jednu pro 90% historických dat a jednu podrobnější pro 10% aktuálních a často používaných dat.
Nevýhodou je, že pokud používáme filtrované statistiky nad sloupcem a pokud server zjistí, že chybí nefiltrovaná statistika, pak nefiltrovanou statistiku automaticky vytvoří. S tím souvisí režie s udržováním dvojích statistik. Doporučuje se ponechat zapnuté automatické tvoření statistik a u nefiltrované statistiky vypnout automatický přepočet (nastavit NORECOMPUTE) a nechat ji zestárnout, server pak bude používat aktuální filtrované statistiky.

Automatický update statistik

Řídí se nastavením serveru:
Auto Update Statistics – zapíná automatickou aktualizaci
Auto Update Statistics Asynchronously – zapíná asynchronní aktualizaci statistik, implicitně vypnuto

Synchronní aktualizace – Pokud se při sestavování plánu zjistí zastaralá statistika, pak se okamžitě provede její aktualizace. Výhodou je plán na základě vždy aktuálních statistik. Nevýhodou je, že aktualizace statistik zdržuje sestavení plánu. Toto nastavení se doporučuje.
Asynchronní aktualizace – Plán se sestaví na základě dostupných statistik. Pokud se zjistí zastaralá statistika, pak je asynchronně odpálena její aktualizace. Výhodou je, že sestavení plánu není zdržováno výpočtem statistik. Nevýhodou je, že plán může být postaven na základě neaktuálních statistik.

Statistiky jsou zastaralé když

  • tabule má více než 500 řádků a je modifikováno více než 20% řádků + další 500 řádků
  • tabule má méně nebo rovno 500 řádků a je modifikováno 500 řádků
  • pokud se počet řádků tabule změní z 0 na nenulový počet
  • u temporarních tabulí po každých změněných 6 záznamech

Filtrované statistky se vyhodnocují bohužel podle počtu modifikací všech řádků a ne jen těch řádků zahrnutých do filtru. Z toho vyplývá nutnost manuální aktualizace filtrovaných statistik.

Zda jsou statistiky zastaralé se vyhodnocuje při sestavování plánu vykonání dotazu.

Chybějící statistiky

Je možné odhalit profilerem zachycením události Errors and Warnings/Missing Column Statistics
Příčinou může být vypnutá volba serveru Auto Create Statistics.
Druhou příčinou může být používání tabulkových proměnných pro které server statistiky nevyrábí (odhadovaný počet řádků je vždy 1 respektive 0 při nesplnitelné podmínce). Mělo by být pravidlem, že tabulkové proměnné se používají jen pro tabulky do 100 řádků. Pro větší tabulky je lépe využívat temporární tabuli (s ‚#‘ na začátku názvu).

Nemožnost použít statistiky

Při použití proměnných v podmínkách výběru nemůže server vyžít statistiky, protože nezná hodnotu proměnné v době sestavování plánu. Pro odhadovaný počet řádků tak server použije průměrnou hodnotu z celé tabulky, která se může diametrálně lišit od hodnoty odpovídající hodnotě proměnné, můžeme tak dostat chybný plán vykonání.

Příliš hrubé statistiky

Pro 4 000 000 záznamů dostaneme 200 řádků statistiky po 20 000 záznamech. Z toho může být 90% historických dat, pro které se statistiky nemění. Řešením je udělat dvě statistiky. Jednu pro historická data, tu není potřeba často udržovat. A jednu pro aktuální živá data. Statistika pro 10% živých dat tak bude jemněji dělena a bude muset být manuálně častěji udržována aktuální.

Nedostatečné automatické aktualizace

20% modifikovaných řádků je málo pro odpálení automatické aktualizace u tabulí s velkým počtem řádků. Nejhorší je to u primárních klíčů, které přibývají na konci řady, tedy mimo statistku a server je tak nemůže vyhodnotit. Zde je nutná častější manuální aktualizace statistiky.

Statistiky a paměť

Statistiky jsou používány pro odhad velikosti potřebné paměti pro některé druhy joinu. Pokud je odhad podhodnocen, pak je místo chybějící paměti využívána tempdb a dochází k cca 10 násobnému zpomalení. Tento problém je možné zachytit profilerem jako události Errors and Warnings/Sort Warnings a Errors and Warnings/Hash Warnings

Zdroje

SQL Server Statistics

Indexed view

Mějme špatně navrženou tabulku

CREATE TABLE testpohledu (id INT IDENTITY(1,1),data NVARCHAR(50))
GO

--naplníme daty
DECLARE @counter INT
SET @counter = 0
WHILE @counter < 100000
BEGIN
	INSERT INTO testpohledu(data)
	SELECT CHAR(ROUND(RAND()*25,2)+65)+cast(round(RAND()*10000,0) as varchar(30))
	SET @counter += 1
END

A pak mějme proceduru, které prohledává testpohledu.data a hledá číslo, nehledíc na první znak.

SELECT * FROM testpohledu WHERE SUBSTRING(data,2,5) = '9601'

Jenže tohle hledání je pomalé. I kdybychom na sloupci data měli index, tak by nám byl k ničemu, protože index je seřazený od prvního znaku. Co teď? Zákazník si stěžuje na pomalost, přidat vypočtený a oindexovaný sloupec nemůžeme, protože kdoví kde všude programátoři narvali * a co by se tím všechno rozbilo…

Zbývá indexovaný pohled

CREATE VIEW v_testpohledu WITH SCHEMABINDING
AS
SELECT id,data,CAST(SUBSTRING(data,2,5) AS VARCHAR(30)) subst FROM dbo.testpohledu
GO

CREATE UNIQUE CLUSTERED INDEX ix_v_testpohledu ON v_testpohledu(subst,data,id)
GO

Podle teorií by tohle samo o sobě mělo stačit, ale nestačí…
EDIT: Takže podle teorií tohle funguje u verze 2008 jen v edici Enterprise .

SELECT * FROM testpohledu WHERE SUBSTRING(data,2,5) = '9601'
--logical reads 348

SELECT id, data FROM v_testpohledu WHERE subst = '9601'
--logical reads 348

SELECT id, data  FROM v_testpohledu WITH (NOEXPAND) WHERE subst = '9601'
--logical reads 3

Pomohlo až přepsání procky pro pro použití pohledu a hint NOEXPAND, který přinutil server použít index seek na indexu ix_v_testpohledu a nečíst zdrojovou tabulku.

Deadlock, zámky a indexy

Nasimulujume deadlock.
Nejdříve si vytvoříme tabulku s clusterovaným indexem.

CREATE TABLE tabule(
  id INT CONSTRAINT pk_tabule PRIMARY KEY IDENTITY(1,1), 
  a INT
)

Dále v jednom okně management studia spustíme pár insertů pro naplnění tabulky.

BEGIN TRAN
INSERT INTO tabule(a) VALUES(3)
INSERT INTO tabule(a) VALUES(2)
INSERT INTO tabule(a) VALUES(1)

Transakci nepotvrdíme a proto záznamy zůstanou exkluzivně zamčené.

Ve druhém okně uděláme další insert.

BEGIN TRAN
INSERT INTO tabule(a) VALUES(4)

Zase transakci nepotvrdíme a záznam bude tudíž exkluzivně zamčený.

V prvním okně se třemi inserty ve stále běžící transakci se pokusíme smazat záznamy vyhovující podmínce.

DELETE FROM tabule WHERE a = 3

Protože na sloupci „a“ nemáme index, server se pokusí přečíst všechny záznamy (clustered index scan) a na každém záznamu se pokusí udělat UPDLOCK. Příkaz nedoběhne, protože bude čekat na uvolnění exkluzivního zámku na jednom vloženém záznamu z druhého okna.

Pokud se i ve druhém okně pokusíme mazat, pak dojde k deadlocku, protože v této situaci už ani jeden proces nemůže zámky uvolnit. Čekají na sebe navzájem = deadlock.

BEGIN TRAN
DELETE FROM tabule WHERE a = 4

Tyto problémy je možné vyřešit použitím vhodného indexu. Pokud vytvoříme index nad sloupcem „a“, pak při mazání záznamů s podmínkou proti sloupci „a“ server použije procházení indexu (index seek). Podaří se nám tedy smazat nezamčené záznamy, zamčené záznamy samozřejmě smazat nelze (vložené záznamy budou zamčené exkluzivně i na indexu a při mazání se nepovede vytvořit UPDLOCK zámek).

V obou transakcích provedeme ROLLBACK a vytvoříme index. Pak se pokusíme vkládat a mazat záznamy dle předchozího scénáře. Obojí se povede.

CREATE INDEX ix_tabule_a ON tabule(a)

Vhodným indexem tedy můžeme řešit problémy se zamykáním a deadlocky.