Archiv pro rubriku: T-SQL

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

Parametr sniffing – experimenty

Jako parametr sniffing se označuje chování SQL serveru, kdy při prvním spuštění procedury server očichá hodnoty vstupních parametrů a na jejich základě postaví exekuční plán, který pak používá při každém dalším spuštění procedury. Ve většině případů je to v pořádku. Ušetří se čas na sestavování exekučního plánu. Pokud však máme proceduru, která na základě různých vstupních parametrů vrací tak rozdílné sady výsledků, že by zasloužily i rozdílné exekuční plány, pak máme problém. Prozkoumal jsem pár možností jak přesvědčit server sestavit odpovídající exekuční plán.

Nejdříve si vytvoříme podkladovou tabulku s daty.

CREATE TABLE tabule(
	id INT IDENTITY(1,1) CONSTRAINT pk_tabule PRIMARY KEY,
	data NVARCHAR(max) NULL,
	a INT
)


--vygeneruji 200 000 záznamů, 
--sloupec [a] naplním náhodným číslem s maximální hodnotou 10 000
DECLARE @counter INT
SET @counter = 0
WHILE @counter < 200000
BEGIN
	PRINT @counter
	INSERT INTO tabule (a) SELECT CAST(RAND() * 10000 AS INT)
	SET @counter+=1
END

--naplním tabulku daty, aby bylo bolestivé číst celý clusterovaný index
UPDATE tabule SET data = N'
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'

--vytvořím index na sloupci [a]
CREATE INDEX ix_tabule_a ON tabule(a)

Na jednoduchých dotazech si ověřím, který přistup je nejoptimálnější.

SELECT * FROM tabule WHERE 1 < a AND a < 10
--Table 'tabule'. Scan count 1, logical reads 452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

V prvním případě jsem se snažil načíst pouze malou část tabulky. Server optimálně použil index seek nad indexem ix_tabule_a pro vyhledání záznamů vyhovujících podmínce a následně key lookup pro načtení zbylých dat tabulky. Server k tomu potřeboval 452 logických čtení.
selectSNIFF1

SELECT * FROM tabule WHERE 1 < a AND a < 10000
--Table 'tabule'. Scan count 1, logical reads 99845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ve druhém případě chceme vrátit velké množství dat. Server optimálně zvolil index scan nad indexem primárního klíče ze kterého načetl všechna data. Server k tomu potřeboval 99845 logických čtení.
selectSNIFF2

SELECT * FROM tabule WITH (INDEX (ix_tabule_a)) WHERE 1 < a AND a < 10000
--Table 'tabule'. Scan count 1, logical reads 612740, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ve třetím pokusu se serveru snažím vnutit index seek nad indexem ix_tabule_a pro načtení všech dat. Z počtu logických čtení (612740) opravdu vidíme, že tento přístup je několikanásobně horší než přístup předchozí.
selectSNIFF3

Konečně vytvoříme několik procedur, kde každá bude používat jiný přístup k problému.

IF  OBJECT_ID('p_sniff1') IS NOT NULL
DROP PROCEDURE [dbo].[p_sniff1]
GO
--klasicky parametr sniffing
CREATE PROCEDURE [dbo].[p_sniff1]
	@a INT,
	@b INT
AS
BEGIN
	SELECT * FROM tabule WHERE a > @a  AND a < @b
END

GO


IF  OBJECT_ID('p_sniff2') IS NOT NULL
DROP PROCEDURE [dbo].[p_sniff2]
GO

--vnitrni promenne
CREATE PROCEDURE [dbo].[p_sniff2]
	@a VARCHAR(30),
	@b VARCHAR(30)
AS
BEGIN
	DECLARE @_a VARCHAR(30)
	DECLARE @_b VARCHAR(30)
	SET @_a = @a
	SET @_b = @b
	SELECT * FROM tabule WHERE a > @_a  AND a < @_b
END

GO

IF  OBJECT_ID('p_sniff3') IS NOT NULL
DROP PROCEDURE [dbo].[p_sniff3]
GO

--skladany dotaz
CREATE PROCEDURE [dbo].[p_sniff3]
	@a INT,
	@b INT
AS
BEGIN
	DECLARE @prikaz NVARCHAR(max)
	SET @prikaz = 'SELECT * FROM tabule WHERE a > '+CAST(@a AS VARCHAR(30))+' AND a < '+CAST(@b AS VARCHAR(30))
	PRINT @prikaz
	EXEC(@prikaz)
END

GO

IF  OBJECT_ID('p_sniff4') IS NOT NULL
DROP PROCEDURE [dbo].[p_sniff4]
GO

--WITH RECOMPILE
CREATE PROCEDURE [dbo].[p_sniff4]
	@a INT,
	@b INT
WITH RECOMPILE
AS
BEGIN
	SELECT * FROM tabule WHERE a > @a  AND a < @b
END

GO

Pak procedury spustíme s následujícími parametry.

EXEC p_sniff1 @a = 1, @b = 10000--index scan nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 99845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff1 @a = 1, @b = 10--index scan nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 99845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff2 @a = 1, @b = 10000--index seek nad ix_tabule_a + key lookup nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 612740, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff2 @a = 1, @b = 10--index seek nad ix_tabule_a + key lookup nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 462, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff3 @a = 1, @b = 10000--index scan nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 99845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff3 @a = 1, @b = 10--index seek nad ix_tabule_a + key lookup nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 461, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff4 @a = 1, @b = 10000--index scan nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 99845, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

EXEC p_sniff4 @a = 1, @b = 10--index seek nad ix_tabule_a + key lookup nad pk_tabule
--Table 'tabule'. Scan count 1, logical reads 461, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Procedura p_sniff1 používá klasický parametr sniffing. Při prvním spuštění byl sestaven exekuční plán využívající index scan nad pk_tabule, což je správně. Při druhém spuštění se tohoto plánu stále drží a to je problém, protože při výběru pár hodnot by byl výhodnější index seek nad ix_tabule_a.

Na internetu je možné najít, že pro řešení problému s parametr sniffingem se dá použít metoda s deklarací vnitřních proměnných v proceduře. Toto zkouším v proceduře p_sniff2. Už ale první spuštění pro celý rozsah sloupce a skončilo s exekučním plánem využívajícím index seek nad indexem ix_tabule_a, což je blbost. Server přečetl index a navíc ještě celou tabuli. Pokud se podíváme na exekuční plán a na tooltip nad ikonou index seek, pak se dozvíme, že Estimated Number of Rows je 18 000 v obou případech a Actual Number of Rows je 199 960 v prvním případě (@a = 1, @b = 10000) a 147 ve druhém případě (@a = 1, @b = 10). Příčinou je, že server nemůže kvůli použití vnitřních proměnných vhodně využít statistiky a učiní tak odhad na základě průměrného počtu jedinečných řádků v celé tabulce. Tento odhad je pro oba případy 18 000 řádků. Na základě tohoto odhadu vybral server index seek. V prvním případě byl tento odhad velmi vzdálen 199 9600 vráceným řádkům a využití index seek bylo naprosto nevhodné. Ve druhém případě i navzdory tomu, že se odhad velmi lišil od hodnoty 147 vybraných řádků bylo použití index seek vhodné. Použití vnitřních proměnných považuji za absolutně nevhodné, protože server nemůže optimálně vyžít statistiky. Více o příčinách v článku Odhady počtu řádků a proměnné. Více o statistikách v článku Statistiky v MSSQL serveru.

Procedura p_sniff3 používá skládaný dotaz. Exekuční plán dostaneme správný pro obě volání. Ve skutečnosti server cachuje plán pro každou kombinaci vstupních parametrů. Je potřeba zvážit, zda je vhodné takto plnit cache.

Procedura p_sniff4 používá hint WITH RECOMPILE, čímž donutí server poskládat správný exekuční plán při každém spuštění procedury. Ztratíme výhodu nacachovaného exekučního plánu, ale zato vždy dostaneme plán správný. Pokud potřebujeme pro různé vstupní parametry procedury různé exekuční plány, přijde mi toto řešení jako ideální varianta.

Pokrývací index

Nutno dodat, že všechny problémy by se zde daly vyřešit pokrývacím indexem, který by byl schopen vrátit všechny požadované sloupce a kde jedinou správnou variantou je index seek nad tímto indexem. Pak odpadá problém, že pro různé vstupy je potřeba různý exekuční plán.

CREATE INDEX ix_tabule_covering ON tabule(a) INCLUDE(data)

EXEC p_sniff1 @a = 1, @b = 10000
--Table 'tabule'. Scan count 1, logical reads 66828, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
EXEC p_sniff2 @a = 1, @b = 10000
--Table 'tabule'. Scan count 1, logical reads 66828, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
EXEC p_sniff3 @a = 1, @b = 10000
--Table 'tabule'. Scan count 1, logical reads 66828, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
EXEC p_sniff4 @a = 1, @b = 10000
--Table 'tabule'. Scan count 1, logical reads 66828, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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.

Jak ovlivnit zámky na úrovni definice tabulky nebo indexu

LOCK_ESCALATION

SQL server využívá eskalaci zámků na vyšší úroveň aby ulevil režii se zámky. Například, pokud server drží příliš mnoho zámků na řádcích, pak dojde k eskalaci zámků na stránku a zámky na řádcích jsou uvolněny.
Eskalace zámků může způsobit blokování zdrojů, protože takto může být zamčeno více řádků než je nutné.

Na úrovni tabulky můžeme ovlivnit, jestli server bude eskalaci zámků používat.

--vypne eskalaci zámků pro většinu případů
ALTER TABLE tabule SET (LOCK_ESCALATION = DISABLE)

--eskalace na úroveň partition (pokud existuje) nebo tabulky
ALTER TABLE tabule SET (LOCK_ESCALATION = AUTO)

--eskalace na úroveň tabulky
ALTER TABLE tabule SET (LOCK_ESCALATION = TABLE)

ALLOW_PAGE_LOCKS, ALLOW_ROW_LOCKS

Na úrovni indexu (a to i clusterovaného) můžeme ovlivnit, zda server může používat zámky stránek a zámky řádků.

--povolí zámky stránek
ALTER INDEX [pk_tabule] ON [dbo].[tabule] SET ( ALLOW_PAGE_LOCKS  = ON )

--povolí zámky řádků
ALTER INDEX [pk_tabule] ON [dbo].[tabule] SET ( ALLOW_ROW_LOCKS  = ON )

--zakáže zámky stránek
ALTER INDEX [pk_tabule] ON [dbo].[tabule] SET ( ALLOW_PAGE_LOCKS  = OFF )

--zakáže zámky řádků
ALTER INDEX [pk_tabule] ON [dbo].[tabule] SET ( ALLOW_ROW_LOCKS  = OFF )

Rychlokurz XML v T-SQL

Funkce nad xml

  • query() – dotaz nad XML, výsledkem je opět XML
  • value() – dotaz nad XML, vrátí hodnotu definovaného datového typu
  • exists() – test, zda dotaz vrátí neprázdnou hodnotu
  • modify() – modifikace XML
  • nodes() – dotaz nad XML, výsledkem je tabulka s jedním sloupcem typu XML

Do XQuery dotazu můžeme dostat SQL proměnnou díky funkci sql:variable(„@nazevPromenne“) nebo hodnotu z tabulky díky funkci sql:column(„tabulka.sloupec“) .

DECLARE @xml XML
SET @xml = '
<zamestnanci xmlns="nejaky-namespace">
	<oddeleni typ="vyvoj">
		<zamestnanec id="1">
			<jmeno>Jarmil</jmeno>
			<prijmeni>Novák</prijmeni>
			<plat premie="ne" xmlns="dalsi-namespace">20000</plat>
		</zamestnanec>
		<zamestnanec id="2">
			<jmeno>Vlastimil</jmeno>
			<prijmeni>Hujer</prijmeni>
			<plat premie="ano" xmlns="dalsi-namespace">40000</plat>
		</zamestnanec>
	</oddeleni>
	<oddeleni typ="implementace">
		<zamestnanec id="3">
			<jmeno>Petr</jmeno>
			<prijmeni>Zeman</prijmeni>
			<plat premie="ne" xmlns="dalsi-namespace">50000</plat>
		</zamestnanec>
	</oddeleni>
</zamestnanci>
'

Vypořádání se s namespace

Přečtení jedné konkrétní hodnoty, deklarace defaultního namespace

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace' 
)
SELECT @xml.value('(/zamestnanci/oddeleni[@typ="vyvoj"]/zamestnanec[@id="2"]/prijmeni)[1]','VARCHAR(50)') AS prijmeni
/*
prijmeni
--------------------------------------------------
Hujer
*/

Přečtení jedné konkrétní hodnoty, deklarace defaultního namespace

SELECT @xml.value('
declare default element namespace "nejaky-namespace";
(/zamestnanci/oddeleni[@typ="vyvoj"]/zamestnanec[@id="2"]/prijmeni)[1]','VARCHAR(50)') AS prijmeni
/*
prijmeni
--------------------------------------------------
Hujer
*/

Přečtení jedné konkrétní hodnoty, jakýkoliv namespace

SELECT @xml.value('(/*:zamestnanci/*:oddeleni[@typ="vyvoj"]/*:zamestnanec[@id="2"]/*:plat)[1]','VARCHAR(50)') AS plat
/*
plat
--------------------------------------------------
40000
*/

Přečtení jedné konkrétní hodnoty, deklarace namespace

SELECT @xml.value('
	declare namespace N="nejaky-namespace";
(/N:zamestnanci/N:oddeleni[@typ="vyvoj"]/N:zamestnanec[@id="2"]/N:prijmeni)[1]','VARCHAR(50)') AS prijmeni
/*
prijmeni
--------------------------------------------------
Hujer
*/

Přečtení jedné konkrétní hodnoty, deklarace dvou namespace

SELECT @xml.value('
	declare namespace N="nejaky-namespace";
	declare namespace P="dalsi-namespace";
(/N:zamestnanci/N:oddeleni[@typ="vyvoj"]/N:zamestnanec[@id="2"]/P:plat)[1]','VARCHAR(50)') AS plat
/*
plat
--------------------------------------------------
40000
*/

Čtění z XML

Vypsání všech hodnot uzlu na konkrétní úrovni, absolutní adresa

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat, --používám "dalsi-namespace"
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie --používám "dalsi-namespace"
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec') x(x) --dohledání uzlu, ke kterému se bude vztahovat funkce value
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Jarmil                                             Novák                                              20000       ne
Vlastimil                                          Hujer                                              40000       ano
Petr                                               Zeman                                              50000       ne
*/

vypsání všech hodnot uzlu na konkrétní úrovni, relativní adresa

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat, 
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie 
FROM @xml.nodes('//zamestnanec') x(x)--relativní adresa
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Jarmil                                             Novák                                              20000       ne
Vlastimil                                          Hujer                                              40000       ano
Petr                                               Zeman                                              50000       ne
*/

podmínka na hodnotu atributu

;WITH XMLNAMESPACES(
    'dalsi-namespace' AS P,
    DEFAULT 'nejaky-namespace'
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec[@id="1"]') x(x)
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Jarmil                                             Novák                                              20000       ne
*/

podmínka na hodnotu atributu ve větší úrovni zanoření

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec/P:plat[@premie="ne"]/..') x(x)
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Jarmil                                             Novák                                              20000       ne
Petr                                               Zeman                                              50000       ne
*/

podmínka ve where

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P 
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec') x(x)
WHERE x.value('P:plat[1]/@premie','VARCHAR(3)') = 'ano'
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Vlastimil                                          Hujer                                              40000       ano
*/

čtění na základě proměnné

DECLARE @promenna INT
SET @promenna = 50000

--čtění na základě proměnné, jednoduše ve where klauzuli
;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P 
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec') x(x)
WHERE x.value('P:plat[1]','INT') = @promenna
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Petr                                               Zeman                                              50000       ne
*/

čtění na základě proměnné, sql proměnná protažená do XQuery výrazu

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P 
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec/P:plat1/..') x(x)
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Petr                                               Zeman                                              50000       ne
*/

mazání

smazání uzlu

DECLARE @smaz XML
SET @smaz = @xml
SET @smaz.modify('delete /*:zamestnanci/*:oddeleni[@typ="vyvoj"]') --jakykoliv namespace
SELECT @smaz
/*
<zamestnanci xmlns="nejaky-namespace">
	<oddeleni typ="implementace">
		<zamestnanec id="3">
			<jmeno>Petr</jmeno>
			<prijmeni>Zeman</prijmeni>
			<plat xmlns="dalsi-namespace" premie="ne">50000</plat>
		</zamestnanec>
	</oddeleni>
</zamestnanci>
*/

smazání uzlu s deklarací dafaultniho namespace

SET @smaz = @xml
SET @smaz.modify('
	declare default element namespace "nejaky-namespace";
	delete /zamestnanci/oddeleni[@typ="vyvoj"]'
)
SELECT @smaz
/*
<zamestnanci xmlns="nejaky-namespace">
  <oddeleni typ="implementace">
    <zamestnanec id="3">
      <jmeno>Petr</jmeno>
      <prijmeni>Zeman</prijmeni>
      <plat xmlns="dalsi-namespace" premie="ne">50000</plat>
    </zamestnanec>
  </oddeleni>
</zamestnanci>
*/

smazání atributu

SET @smaz = @xml
SET @smaz.modify('
	declare default element namespace "nejaky-namespace";
	declare namespace P = "dalsi-namespace";
	delete /zamestnanci/oddeleni/zamestnanec/P:plat/@premie
	'
)
SELECT @smaz
/*
<zamestnanci xmlns="nejaky-namespace">
  <oddeleni typ="vyvoj">
    <zamestnanec id="1">
      <jmeno>Jarmil</jmeno>
      <prijmeni>Novák</prijmeni>
      <plat xmlns="dalsi-namespace">20000</plat>
    </zamestnanec>
    <zamestnanec id="2">
      <jmeno>Vlastimil</jmeno>
      <prijmeni>Hujer</prijmeni>
      <plat xmlns="dalsi-namespace">40000</plat>
    </zamestnanec>
  </oddeleni>
  <oddeleni typ="implementace">
    <zamestnanec id="3">
      <jmeno>Petr</jmeno>
      <prijmeni>Zeman</prijmeni>
      <plat xmlns="dalsi-namespace">50000</plat>
    </zamestnanec>
  </oddeleni>
</zamestnanci>
*/

modifikace

bez cyklu lze modifikovat jen jednu hodnotu

SET @smaz = @xml
SET @smaz.modify('
	declare default element namespace "nejaky-namespace";
	declare namespace P = "dalsi-namespace";
	replace value of (/zamestnanci/oddeleni/zamestnanec[@id=2]/P:plat/text())[1]
	with "10000"
	'
)
SELECT @smaz
/*
<zamestnanci xmlns="nejaky-namespace">
  <oddeleni typ="vyvoj">
    <zamestnanec id="1">
      <jmeno>Jarmil</jmeno>
      <prijmeni>Novák</prijmeni>
      <plat xmlns="dalsi-namespace" premie="ne">20000</plat>
    </zamestnanec>
    <zamestnanec id="2">
      <jmeno>Vlastimil</jmeno>
      <prijmeni>Hujer</prijmeni>
      <plat xmlns="dalsi-namespace" premie="ano">10000</plat>
    </zamestnanec>
  </oddeleni>
  <oddeleni typ="implementace">
    <zamestnanec id="3">
      <jmeno>Petr</jmeno>
      <prijmeni>Zeman</prijmeni>
      <plat xmlns="dalsi-namespace" premie="ne">50000</plat>
    </zamestnanec>
  </oddeleni>
</zamestnanci>
*/

Další zdroje
http://beyondrelational.com/modules/2/blogs/28/posts/10279/xquery-labs-a-collection-of-xquery-sample-scripts.aspx
http://www.w3schools.com/xpath/default.asp
http://www.w3schools.com/xquery/default.asp

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.

Zamknutí záznamu tak, aby nešel přečíst

Mějme situaci, kdy chceme uzamknout záznam tak, aby ho jiná transakce nemohla přečíst. Používáme ISOLATION LEVEL READ COMMITTED a kvůli propustnosti nechceme zamykat více záznamů než je nutné. Napadne nás použít hinty ROWLOCK a XLOCK, ale ejhle, ono to nefunguje.

Uvedu příklad. V jednom okně management studia vytvořím tabulku, naplním ji daty, započnu transakci, přečtu jeden záznam tabulky, který uzamknu, aby ho nemohl číst nikdo další. Dále spustím sp_lock a ověřím, že záznam je opravdu exkluzivně zamčený.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE tabulka(id INT CONSTRAINT PK_tabulka PRIMARY KEY IDENTITY(1,1), hodnota INT)
INSERT INTO tabulka VALUES(1)
INSERT INTO tabulka VALUES(2)
INSERT INTO tabulka VALUES(3)

BEGIN TRAN
SELECT *, %%LOCKRES%% AS Resource FROM tabulka WITH (ROWLOCK, XLOCK) WHERE id = 3
EXEC sp_lock

Potom ve druhém okně management studia přečtu z tabulky všechna data, očekávajíc, že to neprojde.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM tabulka

Ale ono to prošlo. Server totiž u ISOLATION LEVEL READ COMMITTED na zámky řádků moc nehledí. Všimne si, že záznam nebyl v jiné transakci změněn (je tedy COMMITTED) a klidně ho přečte. Pokud můžeme ovlivnit druhý SELECT, pak je řešením použít u něj hint HOLDLOCK. Pokud můžeme ovlivnit pouze první transakci, pak můžeme použít trik s UPDATE a vnutit tak serveru, že řádek se změnil a nemůže ho z jiné transakce číst.

UPDATE tabulka WITH (ROWLOCK) SET hodnota = hodnota + 0 WHERE id = 3

Pozor, SET hodnota = hodnota nestačí.

Další možností je, použít zamykání na úrovní stránky.

BEGIN TRAN
SELECT *, %%LOCKRES%% AS Resource FROM tabulka WITH (PAGLOCK, XLOCK) WHERE id = 3
EXEC sp_lock

Problém popsán taky v článku The madness of “exclusive” row locks.

Konverzní deadlock

Nasimulujeme si konverzní deadlock.

Nejdříve založíme tabulku, se kterou budeme následně pracovat.

CREATE TABLE tabule(a INT, b INT)
INSERT INTO tabule(a,b) VALUES(1,1)
INSERT INTO tabule(a,b) VALUES(2,2)
INSERT INTO tabule(a,b) VALUES(3,3)

Poté si v prvním okně management studia spustíme následující dotaz. (Řekněme, že hint HOLDLOCK používáme proto, že nechceme, aby nám jiný proces změnil čtená data pod rukama.)

BEGIN TRANSACTION
SELECT * FROM tabule WITH (HOLDLOCK)

A ten samý dotaz si spustíme i ve druhém okně management studia.

Oba dotazy doběhnou a vrátí data z tabulky. Protože jsme ale neukončili transakci a použili jsme HOLDLOCK budou oběma procesy sdíleně zamčené data tabulky.

Teď v obou oknech spustíme následující update.

UPDATE tabule SET b = 5 WHERE a= 1

V jednom okně pak dostaneme zprávu, že vznikl deadlock a proces byl vybrán jako oběť:

Msg 1205, Level 13, State 56, Line 1
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Ve druhém okně update projde.

Dostali jsme se do situace, kdy dva procesy měly sdíleně uzamknutý jeden zdroj. Následně oba procesy chtěly nad tímto zdrojem použít update lock. Žádný z procesů nemohl použít update zámek, protože zdroj byl sdíleně zamknutý druhým procesem. Neřešitelná situace, deadlock. Server tedy vybral jednu transakci jako oběť a zrušil ji, čímž uvolnil její sdílený zámek. Druhá transakce pak mohla udělat update.

Jak předejít konverznímu deadlocku

Pokud víme, že data čtená v transakci budeme v této transakci i modifikovat, pak můžeme konverznímu deadlocku předejít pomocí hintu UPDLOCK.

Pokud spustíme v prvním okně tento dotaz

BEGIN TRANSACTION
SELECT * FROM tabule WITH (UPDLOCK)

pak nám doběhne a vrátí data. Ve druhém okně nám ale stejný dotaz zůstane čekat, dokud neukončíme transakci v prvním okně. Druhý proces tedy bude už od začátku čekat a nebude mít možnost uvalit na data žádné zámky. K deadlocku tedy již nemůže dojít.

Query hints a zamykání

HOLDLOCK
Použité zámky jsou drženy až do konce transakce. Obdobné jako SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, akorát jen pro jednu tabulku.
UPDLOCK
Nutí server použít při čtení update lock namísto share lock. Můžeme použít pro eliminaci konverzních deadlocků.
TABLOCK
Nutí server použít share lock na celou tabulku i přesto, že by se jinak zamykala pouze stránka. To je užitečné, pokud víme, že by se zámky nakonec eskalovaly na celou tabulku. Pokud použijeme TABLOCK při mazání z haldy, umožníme SQL serveru uvolnit stránky hned, jakmile jsou smazány.
PAGLOCK
Nutí server použít sdílený zámek na stránku, tam kde by jinak použil sdílený zámek celé tabulky.
TABLOCKX
Exkluzivní zámek tabulky držený do konce transakce. (TABLOCK a XLOCK)
ROWLOCK
Použije se sdílený zámek řádků tam, kde by se jinak použil zámek stránky nebo tabulky.
READUNCOMMITTED, REPEATABLEREAD, SERIALIZABLE
Na tabulku se použije stejný mechanizmus zamykání, jako by byl zapnutý ISOLATION LEVEL se stejným názvem.
READCOMMITTED
Pokud je READ_COMMITTED_SNAPSHOT OFF, pak server používá sdílené zámky a uvolňuje je hned jak je to možné.

Pokud je READ_COMMITTED_SNAPSHOT ON, pak server nepoužívá zámky, ale verzování řádků.
READCOMMITTEDLOCK
Server používá sdílené zámky i pokud je READ_COMMITTED_SNAPSHOT ON.
NOLOCK
Umožnuje nepotvrzené, špinavé čtení. Zámky se nehlídají, je možné přečíst data, která jsou exkluzivně zamčená. Odpovídá READUNCOMMITED.
READPAST
Přeskakuje zamčené řádky. Aplikuje se pouze při READ COMMITTED isolation level a přeskakuje pouze zámky na úrovni řádků.
XLOCK
Použije exkluzivní zámky. Je možné kombinovat s PAGLOCK nebo TABLOCK.

Zámky

Zkratka Druh zámku Popis
S Shared Umožňuje ostatním procesům číst, ale neumožňuje měnit zamčené zdroje.
Tyto zámky jsou používány automaticky, když server čte data. Mohou být použíté na tabulku, stránku, klíč indexu, nebo řádek. Více procesů může použít sdílený zámek na stejný zdroj. Na zdroj zamčený sdíleným zámkem nelze použít exkluzivní zámek. Běžně jsou zámky uvolňovány jakmile jsou data přečtena. Toto můžeme ovlivnit hintem, nebo nastavením úrovně izolace.
X Exclusive Brání ostatním měnit i číst zamčená data. Sql server tento zámek používá při INSERT, UPDATE, DELETE operacích. Data jsou zamčená po celou dobu transakce, tedy než se provede COMMIT, nebo ROLLBACK. Na takto zamčená data nelze použít žádný jiný zámek. Ostatní procesy, tak nemají k datům přístup. Toto je možné změnit pomocí hintů.
U Update Brání ostatním získat update nebo exclusive zámek. Získání shared zámku a čtení je umožněno. SQL server používá tento zámek, když vyhledává data pro modifikaci. Použitím hintů můžeme tento zámek vynutit a předejít tak konvezním deadlockům.
IS Intent shared Označuje, že komponenta zdroje je zamčena shared zámkem. Může zamykat tabulku nebo stránku.
IU Intend update Označuje, že komponenta zdroje je zamčena update zámkem. Může zamykat tabulku nebo stránku.
IX Intent exclusive Označuje, že komponenta zdroje je zamčena exclusive zámkem. Může zamykat tabulku nebo stránku.
SIX Shared with intent exclusive Označuje, že zdroj zamčený shared zámkem obsahuje komponentu (stránku nebo řádek) zamčenou exclusive zámkem.
SIU Shared with intent update Označuje, že zdoj zamčený shared zámkem obsahuje komponentu (stránku nebo řádek) zamčenou update zámkem.
UIX Update with intent exclusive Označuje, že zdoj zamčený update zámkem obsahuje komponentu (stránku nebo řádek) zamčenou update zámkem.
Sch-S Schema stability Označuje, že tabulka je kompilována.
Sch-M Schema modification Označuje, že se mění struktura tabulky.
BU Bulk update Používá se při bulk operacích a zamyká celou tabulku.