Archiv pro rubriku: T-SQL

Memory Grant a proč záleží na velikosti použitého datového typu

Sql server používá paměť především pro 3 věci:

Buffer pool – nakešované datové stránky
Plan cache – nakešované plány vvykonávání dotazu
Query workspace memory – paměť potřebná pro běh dotazu

Dotaz může pro svůj běh dostat až 25% přidělené paměti SQL serveru. Pokud tedy někomu chcete shodit server a máte práva jen na select, tak stačí souběžně spustit několik dotazů, které si řeknou o pořádnou porci paměti. Případně stačí nechat uživatelům spouštět přes den účetní reporty
Takovéto dotazy ubírají paměť buffer poolu, tedy SQL server bude muset tahat data z pomalého disku, ubírají paměť plan cache, tedy server zahodí plány a bude žhavit CPU, aby je spočítal znova a dotazy potřebující k běhu paměť se budou stavět do fronty na její přidělení.
Pojďme se podívat na to, jak takové dotazy můžou vypadat.

Nejdříve si vytvoříme tabulku, která bude obsahovat různě dlouhé pole typu VARCHAR. Naplníme ji řádky, ale varcharové pole necháme s hodnotou NULL.

--vytvořím tabulku
CREATE TABLE dbo.a(
	id INT IDENTITY(1,1) NOT NULL
		CONSTRAINT pk_a  PRIMARY KEY CLUSTERED,
	text100 VARCHAR(100) NULL,
	text500 VARCHAR(500) NULL,
	text5000 VARCHAR(5000) NULL,
	textmax VARCHAR(MAX) NULL
)

--naplním tabulku tak, že v ní bude 100 tisíc záznamů a všechny hodnoty NULL
INSERT INTO dbo.a(text100)
SELECT TOP (100000) NULL
FROM  sys.all_objects ao
CROSS JOIN sys.all_objects ao2
GO

Teď budu spouštět různé dotazy nad tabulkou a sledovat jak SQL server odhaduje náklady na dotaz (Estimated Subtree Cost) a především kolik dotazu přidělí paměti (Memory Grant). Tyto údaje vyčtu v exekučním plánu v properties selectu.

Dosažené hodnoty píšu do komentáře pod selectem.

--Ctrl+M (exekuční plán)
SELECT * 
FROM  dbo.a
--Estimated Subtree Cost 0,231801
--Memory Grant 0

SELECT textmax
FROM  dbo.a
--Estimated Subtree Cost 0,231801
--Memory Grant 0

Pro SELECT * i SELECT testmax to vypadá bez problémů. Dokud nemáme JOIN, ani ORDER BY, tak dotaz nepotřebuje paměť. Přidejme tedy ORDER BY.

SELECT text100
FROM  dbo.a
ORDER BY text100
--Estimated Subtree Cost 4,78899
--Memory Grant 14 MB

SELECT text500
FROM  dbo.a
ORDER BY text100
--Estimated Subtree Cost 5,68254
--Memory Grant 44 MB

SELECT text5000
FROM  dbo.a
ORDER BY text100
--Estimated Subtree Cost 197,926
--Memory Grant 313 MB

SELECT textmax
FROM  dbo.a
ORDER BY text100
--Estimated Subtree Cost 578,555
--Memory Grant 495 MB

SELECT * 
FROM  dbo.a
ORDER BY text100
--Estimated Subtree Cost 588,143
--Memory Grant 824 MB

Tady už to bylo zajímavější. Je z toho vidět, že čím větší datové typy se používají, tím víc paměti si dotaz vezme. Stejně tak, čím víc sloupců dotaz vrací, tím hůř. Datové typy by tedy měly být co možná nejmenší a select by měl vracet jen potřebné sloupce. Připomínám, že všechny sloupce v tabulce mají hodnotu NULL. SQL server vlastně nic neseřazoval, ani žádná data nevracel. Podle velikosti datových typů si odhadl kolik paměti bude potřebovat pro setřízení a tuto paměť zabral, i když ji měl nakonec k ničemu. Poslední dotaz se blížil 1GB paměti a to šlo jen o 100 tisíc řádků. Nad většími daty není problém zabrat gigabajty paměti a vypláchnout tak buffer pool. Nejde ani tak o to, že by tyto dotazy byly pomalejší, spíše se tím zpomalují ostatní dotazy, které budou muset tahat data z disku. Vlastně se tak snižuje výkon celého SQL serveru.

Ještě si ukážeme rozdíl mezi SELECT TOP 100 a SELECT TOP víc než 100

SELECT TOP 101 * 
FROM  dbo.a
ORDER BY text100
--Estimated Subtree Cost 563,9
--Memory Grant 824 MB

SELECT TOP 100 * 
FROM  dbo.a
ORDER BY text100
--Estimated Subtree Cost 563,9
--Memory Grant 4 MB

DROP TABLE dbo.a

Pokud je TOP vetší než 100, tak si server pro setřízení alokuje stejnou paměť jako by tam žádný TOP nebyl. Pokud je TOP 100 a méně, tak se použije jiný typ setřízení a ušetří se násobky paměti. U mě 824 MB : 4 MB. To je 206 krát méně. U větších dat bude rozdíl ještě větší. Pokud používáme TOP, tak nejlépe do hodnoty 100.

Retry logika na ošetření deadlocku

Nejjednodušším řešením deadlocku je spustit znova transakci, která se stala jeho obětí. Transakce, která se stala obětí deadlocku je neplatná. Nedá se nad ní udělat commit, ani rollback po savepoint. Jedinnou možností je rollback celé transakce. Z toho plyne, že retry logiku můžu nasadit jen v místě, kde mám kontrolu nad zahájením transakce. Celou transakci pak zabalím do cyklu WHILE a v něm se ji v případě deadlocku pokusím několikrát zopakovat. Pokud se mi to nepodaří ani na několikátý pokus, pak chybu vypropaguju ven.

DECLARE @pocetPokusu INT = 1;
WHILE (1=1)
BEGIN
	BEGIN TRY
		BEGIN TRAN

		RAISERROR('pokousim se vykonat transakci',1,1) WITH NOWAIT;

		EXECUTE('
		CREATE TYPE dbo.instantiDeadlock AS TABLE(
			Id INT NOT NULL
		)
		')

		EXEC('
		declare @t dbo.instantiDeadlock;')

		COMMIT;
		BREAK;
	END TRY
	BEGIN CATCH
		ROLLBACK;

		IF ERROR_NUMBER() = 1205 AND @pocetPokusu < =  3--jsem obeti deadlocku a jeste jsem si nedal 3 pokusy
		BEGIN
			RAISERROR('jsem obeti deadlocku',1,1) WITH NOWAIT;
			SET @pocetPokusu += 1;
		END
		ELSE
			THROW;
	END CATCH
END

IF EXISTS (SELECT 1 FROM sys.table_types tt WHERE tt.name = 'instantiDeadlock')
	DROP TYPE instantiDeadlock

pokousim se vykonat transakci
Msg 50000, Level 1, State 1
jsem obeti deadlocku
Msg 50000, Level 1, State 1
pokousim se vykonat transakci
Msg 50000, Level 1, State 1
jsem obeti deadlocku
Msg 50000, Level 1, State 1
pokousim se vykonat transakci
Msg 50000, Level 1, State 1
jsem obeti deadlocku
Msg 50000, Level 1, State 1
pokousim se vykonat transakci
Msg 50000, Level 1, State 1
Msg 1205, Level 13, State 55, Line 6
Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Skript pro plnění číselníků

Při vývoji je potřeba často zaverzovat skripty pro naplnění dat. Pokud chceme, aby byly opakovaně spustitelné, tak jsou zpravidla plné IF NOT EXISTS…INSERT INTO konstrukcí.
Já mám raději konstrukci se SELECT * FROM (VALUES()), která mi připadá přehlednější. Ukážu na příkladu.

Nejdříve si založíme dvě provázané číselníkové tabule.

IF OBJECT_ID('dbo.Kategorie') IS NULL
CREATE TABLE dbo.Kategorie(
	IdKategorie INT NOT NULL IDENTITY CONSTRAINT pk_Kategorie PRIMARY KEY CLUSTERED,
	Kod	NVARCHAR(50) NOT NULL CONSTRAINT uq_Kategorie_Kod UNIQUE,
	Nazev NVARCHAR(250) NOT NULL,
	PlatnostOd DATETIME NOT NULL,
	PlatnostDo DATETIME NOT NULL
)
GO

IF OBJECT_ID('dbo.KategorieTyp') IS NULL
CREATE TABLE dbo.KategorieTyp(
	IdKategorieTyp INT NOT NULL IDENTITY CONSTRAINT pk_KategorieTyp PRIMARY KEY CLUSTERED,
	IdKategorie INT NOT NULL CONSTRAINT fk_KategorieTyp_Kategorie FOREIGN KEY (IdKategorie) REFERENCES dbo.Kategorie(IdKategorie),
	Kod	NVARCHAR(50) NOT NULL CONSTRAINT uq_KategorieTyp_Kod UNIQUE,
	Nazev NVARCHAR(250) NOT NULL,
	PlatnostOd DATETIME NOT NULL,
	PlatnostDo DATETIME NOT NULL
)
GO

Plním to pak pomocí INSERT INTO SELECT, kde za FROM mám konstruktor VALUES, do kterého vypíšu všechna data, co chci založit. Tuhle za běhu vytvořenou tabulku si pojmenuju jako data a její sloupce jako Kod a Nazev. Na tuhle tabuli se pak odkážu za klauzulí SELECT, nebo ve WHERE podmínce.

INSERT INTO dbo.Kategorie
(
    Kod,
    Nazev,
    PlatnostOd,
    PlatnostDo
)
SELECT 
	 data.Kod
	,data.Nazev
	,GETDATE()
	,'20991231'
FROM (VALUES
	(N'domacnost'	,N'domácnost'	),
	(N'podnikatel'	,N'podnikatel'	)
)data(Kod			,Nazev)
WHERE NOT EXISTS(
	SELECT * 
	FROM  dbo.Kategorie k
	WHERE k.Kod = data.Kod
)
GO

INSERT INTO dbo.KategorieTyp
(
    IdKategorie,
    Kod,
    Nazev,
    PlatnostOd,
    PlatnostDo
)
SELECT 
	 k.IdKategorie
    ,data.Kod
    ,data.Nazev
	,GETDATE()
	,'20991231'
FROM (VALUES
	(N'domacnost'	,N'dum'			,N'dům'			),
	(N'domacnost'	,N'byt'			,N'byt'			),
	(N'domacnost'	,N'chata'		,N'chata'		),
	(N'podnikatel'	,N'garaz'		,N'garáž'		),
	(N'podnikatel'	,N'provozovna'	,N'provozovna'	),
	(N'podnikatel'	,N'kancelar'	,N'kacelář'		)
)data(KategorieKod  ,Kod			,Nazev)
JOIN dbo.Kategorie k ON data.KategorieKod = k.Kod
WHERE NOT EXISTS(
	SELECT * 
	FROM  dbo.KategorieTyp kt
	WHERE kt.Kod = data.Kod
)
GO

A tohle jsou konstrukce, které potkávám častěji a přijde mi to škoda:

IF NOT EXISTS(SELECT * FROM dbo.Kategorie k WHERE k.Kod = 'domacnost')
	INSERT INTO dbo.Kategorie
	(
	    Kod,
	    Nazev,
	    PlatnostOd,
	    PlatnostDo
	)
	VALUES
	(   N'domacnost',
	    N'domácnost',
	    GETDATE(),
	    '20991231'
	    )

IF NOT EXISTS(SELECT * FROM dbo.Kategorie k WHERE k.Kod = 'podnikatel')
	INSERT INTO dbo.Kategorie
	(
	    Kod,
	    Nazev,
	    PlatnostOd,
	    PlatnostDo
	)
	VALUES
	(   N'podnikatel',
	    N'podnikatel',
	    GETDATE(),
	    '20991231'
	    )
GO


IF NOT EXISTS(
	SELECT * FROM dbo.KategorieTyp kt WHERE kt.Kod = N'dum'
)
INSERT INTO dbo.KategorieTyp
(
    IdKategorie,
    Kod,
    Nazev,
    PlatnostOd,
    PlatnostDo
)
VALUES
(   (SELECT k.IdKategorie FROM dbo.Kategorie k WHERE k.Kod = 'domacnost'),
    N'dum',
    N'dům',
    GETDATE(),
    '20991231'
    )

IF NOT EXISTS(
	SELECT * FROM dbo.KategorieTyp kt WHERE kt.Kod = N'byt'
)
INSERT INTO dbo.KategorieTyp
(
    IdKategorie,
    Kod,
    Nazev,
    PlatnostOd,
    PlatnostDo
)
VALUES
(   (SELECT k.IdKategorie FROM dbo.Kategorie k WHERE k.Kod = 'domacnost'),
    N'byt',
    N'byt',
    GETDATE(),
    '20991231'
    )
GO

Jak vytáhnout z SQL nechutně dlouhý řádek textu skrz management studio

--text obsahující balast připomínající XML, 70 000 písmen 'a' a na konci je 'b', pro kontrolu, že se vypsalo vše
DECLARE @out NVARCHAR(MAX) = N'<?<a>?>'+CHAR(13)+CHAR(10)+REPLICATE(CAST(N'a' AS NVARCHAR(MAX)),70000) +  N'b'
--print zvládne jen 8000 bytů, to je 4000 znaků v NVARCHAR
PRINT @out
GO

--text obsahující balast připomínající XML, 70 000 písmen 'a' a na konci je 'b', pro kontrolu, že se vypsalo vše
DECLARE @out VARCHAR(MAX) = '<?<a>?>'+CHAR(13)+CHAR(10)+REPLICATE(CAST('a' AS NVARCHAR(MAX)),70000) +  'b'
--print zvládne jen 8000 bytů, to je 8000 znaků ve VARCHAR
PRINT @out
GO

--text obsahující balast připomínající XML, 70 000 písmen 'a' a na konci je 'b', pro kontrolu, že se vypsalo vše
DECLARE @out NVARCHAR(MAX) = N'<?<a>?>'+CHAR(13)+CHAR(10)+REPLICATE(CAST(N'a' AS NVARCHAR(MAX)),70000) +  N'b'
--select a Results to grid (Ctrl-D)
--Do SSMS 18.1 včetně se dá z buňky gridu vykopírovat 43680 znaků, přesto, že v nastavení gridu je 65 535
--Od SSMS 18.2 se dá vzít až 2 milony znaků
--Pravá myš/Query Option/Results/Grid
--Nebo Tools/Options/Query Results/SQL Server/Results to Grid
----Retain CR/LF on copy or save - pokud chceme z gridu vykopírovat i konce řádků (Od SSMS 16)
----Non XML data: pro SSMS 18.1 maximálně 65 535, od 18.2 maximálně 2 000 000
SELECT @out
GO

--text obsahující balast připomínající XML, 70 000 písmen 'a' a na konci je 'b', pro kontrolu, že se vypsalo vše
DECLARE @out NVARCHAR(MAX) = N'<?<a>?>'+CHAR(13)+CHAR(10)+REPLICATE(CAST(N'a' AS NVARCHAR(MAX)),70000) +  N'b'
--select a Results to text (Ctrl-T)
--Query Option/Resutls/Text/Maximum number of characters diplayed in each column - maximálně 8192 - tedy málo
SELECT @out
GO

--text obsahující balast připomínající XML, 70 000 písmen 'a' a na konci je 'b', pro kontrolu, že se vypsalo vše
DECLARE @out NVARCHAR(MAX) = N'<?<a>?>'+CHAR(13)+CHAR(10)+REPLICATE(CAST(N'a' AS NVARCHAR(MAX)),70000) +  N'b'
--Results to grid (Ctrl-D)
--funkční i pro starší ssms. Výsledkem je XML sloupec prokliknutelný z management studia
--poradí si s konci řádků
--výsledek je uvozený <? a zakončený ?>
--problém nastane jen pokud text obsahuje ?>
SELECT @out AS [processing-instruction(x)] FOR XML PATH('')
GO

--text obsahující balast připomínající XML, 70 000 písmen 'a' a na konci je 'b', pro kontrolu, že se vypsalo vše
DECLARE @out NVARCHAR(MAX) = N'<?<a>?>'+CHAR(13)+CHAR(10)+REPLICATE(CAST(N'a' AS NVARCHAR(MAX)),70000) +  N'b'
--Results to grid (Ctrl-D)
--funkční i pro starší ssms. Výsledkem je XML sloupec prokliknutelný z management studia
--poradí si s konci řádků
--výsledek je uvozený <export><![CDATA[ a zakončený ]]></export>
SELECT  1 as Tag,  
        0 as Parent,  
        @out as [export!1!!CDATA]
FOR XML EXPLICIT

Zřetězení sloupce řetězců do jednoho řetězce s oddělovačem


CREATE TABLE #zretezit(slovo NVARCHAR(30))
INSERT INTO #zretezit(slovo)
	VALUES
	 (N'pondělí')
	,(N'úterý')
	,(N'středa')

DECLARE @zretezeni0 NVARCHAR(MAX) = (
	SELECT ','+z.slovo
	FROM #zretezit z
	FOR XML PATH('')
)
PRINT @zretezeni0
/*
Výsledek, všechno hraje jak má:
,pondělí,úterý,středa
*/

--Horší je, jak tam někdo nacpe html tagy a odřádkování
INSERT INTO #zretezit(slovo)
	VALUES
	(N'<b>text</b>')
	,(N'Lorem'+CHAR(13)+CHAR(10)+N'iposum')

DECLARE @zretezeni1 NVARCHAR(MAX) = (
	SELECT ','+z.slovo
	FROM #zretezit z
	FOR XML PATH('')
)
PRINT @zretezeni1
/*
Vysledkem je:
,pondělí,&lt;b&gt;text&lt;/b&gt;,Lorem&#x0D;
iposum
*/

--Pak pomáhá to zkonvertovat na NVARCHAR
DECLARE @zretezeni2 NVARCHAR(MAX) = (
	SELECT (
		SELECT ','+z.slovo
		FROM #zretezit z
		FOR XML PATH(''), TYPE
	).value('(./text())[1]','NVARCHAR(MAX)')
)
PRINT @zretezeni2
/*
Výsledek:
,pondělí,<b>text</b>,Lorem
iposum
*/

--Ještě se zbavit první čárky a máme tu konstrukci jak víno:
DECLARE @zretezeni3 NVARCHAR(MAX) = (
	SELECT STUFF(
	(
		SELECT ','+z.slovo
		FROM #zretezit z
		FOR XML PATH(''), TYPE
	).value('(./text())[1]','NVARCHAR(MAX)')
	,1
	,1
	,''
	)
)
PRINT @zretezeni3
/*
Krása střídá nádheru:
pondělí,úterý,středa,<b>text</b>,Lorem
iposum
*/

--Asi by bylo dobré zmínit, že od SQL 2017 funguje tohle
DECLARE @zretezeni4 NVARCHAR(MAX) = (
	SELECT STRING_AGG(z.slovo,',')
	FROM #zretezit z
)
PRINT @zretezeni4

MERGE aneb INSERT, UPDATE, DELETE v jednom příkazu

CREATE TABLE faktura(
	id_faktura INT NOT NULL IDENTITY(1,1),
	cislo	NVARCHAR(50) NOT NULL,
	CONSTRAINT pk_faktura PRIMARY KEY CLUSTERED (id_faktura) 
)
GO

CREATE TABLE polozka(
	id_polozka INT NOT NULL IDENTITY(1,1),
	id_faktura INT NOT NULL,
	zbozi NVARCHAR(50) NOT NULL,
	mnozstvi INT NOT NULL,
	cena NUMERIC(10,2) NOT NULL,
	CONSTRAINT pk_polozka PRIMARY KEY CLUSTERED (id_polozka),
	CONSTRAINT fk_polozka_faktura FOREIGN KEY (id_faktura) 
		REFERENCES faktura(id_faktura)
)
GO

INSERT INTO faktura(cislo)
	VALUES('2021001'),('2021002')

INSERT INTO polozka(id_faktura, zbozi, mnozstvi, cena)
	VALUES	(1,'chleba',5,100),
			(1,'rohlik',5,10),
			(2,'cukr',1,10),
			(2,'olej',2,50)
GO

CREATE TYPE tt_polozka AS TABLE(
	zbozi NVARCHAR(50) NOT NULL,
	mnozstvi INT NOT NULL,
	cena NUMERIC(10,2) NOT NULL
)
GO

CREATE PROCEDURE p_aktualizuj_polozky_faktury
	@id_faktura INT,
	@polozky tt_polozka READONLY
AS
BEGIN
	MERGE INTO polozka p
	USING (
		SELECT f.id_faktura, p.zbozi, p.mnozstvi, p.cena
		FROM faktura f
		CROSS JOIN @polozky p
		WHERE f.id_faktura = @id_faktura
	)zdroj ON zdroj.id_faktura = p.id_faktura AND zdroj.zbozi = p.zbozi
	WHEN MATCHED THEN
		UPDATE SET
			p.mnozstvi = zdroj.mnozstvi,
			p.cena = zdroj.cena
	WHEN NOT MATCHED THEN 
		INSERT(id_faktura, zbozi, mnozstvi, cena)
		VALUES(zdroj.id_faktura, zdroj.zbozi, zdroj.mnozstvi, zdroj.cena)
	WHEN NOT MATCHED BY SOURCE AND p.id_faktura = @id_faktura THEN
		DELETE;
END
GO

DECLARE @polozky tt_polozka
INSERT INTO @polozky
VALUES('cukr',5,50),('pivo',10,200);

SELECT *
FROM faktura f
JOIN polozka p ON f.id_faktura = p.id_faktura

EXEC p_aktualizuj_polozky_faktury
	@id_faktura = 2,
	@polozky = @polozky

SELECT *
FROM faktura f
JOIN polozka p ON f.id_faktura = p.id_faktura
GO

id_faktura  cislo                                              id_polozka  id_faktura  zbozi                                              mnozstvi    cena
----------- -------------------------------------------------- ----------- ----------- -------------------------------------------------- ----------- ---------------------------------------
1           2021001                                            1           1           chleba                                             5           100.00
1           2021001                                            2           1           rohlik                                             5           10.00
2           2021002                                            3           2           cukr                                               1           10.00
2           2021002                                            4           2           olej                                               2           50.00

id_faktura  cislo                                              id_polozka  id_faktura  zbozi                                              mnozstvi    cena
----------- -------------------------------------------------- ----------- ----------- -------------------------------------------------- ----------- ---------------------------------------
1           2021001                                            1           1           chleba                                             5           100.00
1           2021001                                            2           1           rohlik                                             5           10.00
2           2021002                                            3           2           cukr                                               5           50.00
2           2021002                                            5           2           pivo                                               10          200.00

dm_exec pohledy a funkce

Handle

Plan_handle
Je hash odvozený z kompilovaného plánu celé dávky, dá se považovat za jednoznačný identifikátor plánu.

Sql_handle
MD5 hash textu celé dávky, tedy včetně komentářů. Je identifikátorem textu dávky. K 1 Sql_handle může existovat více plan_handle, například kvůli různým set options.

Query_hash
Identifikuje podobné statementy lišící se pouze konstantama

Query_plan_hash
Pro jeden query_hash můžeme dostat vice query_plan_hash, protože pro různé parametry můžeme kvůli rozložení dat dostat různé plány.

Pohledy

dm_exec_background_job_queue

asynchronní query procesor joby, asynchronní update statistik

dm_exec_background_job_queue_stats

agregované statistiky pro asynchronní query processor joby

dm_exec_cached_plans

všechny nakešované plány
zabraná paměť plánem, počet použití plánu
plan_handle

dm_exec_connections

informace o připojení
session_id
connection_time
last_read – datetime
most_recent_sql_handle

dm_exec_query_memory_grants

informace o dotazech, které potřebovaly další paměť

dm_exec_query_profiles

monitoruje postup ve vykonávání plánu běžícího dotazu

dm_exec_query_optimizer_info

informace o optimalizátoru

dm_exec_requests

spuštěné requesty

dm_exec_sessions

last_request_time, reads, writes, logical_reads, set options

dm_exec_procedure_stats

database_id
object_id
sql_handle
plan_handle
last_execution_time
execution_count
max, min, last, total time of physical reads, worker, logical reads, logical writes, elapsed

dm_exec_trigger_stats

obdobné jak pro procedury výše

dm_exec_query_stats

obdobné jak pro procedury výše
navíc:
statement_start_offset a statement_end_offset – urcuje statement v ramci davky (nebo v ramci procedury, triggeru)
query_hash
query_plan_hash
statement_sql_handle
statement_context_id
total, last, min, max rows, dop, grant_kb, used_grant_kb, ideal_grant_kb, reserved_threads, used_threads

dm_exec_query_resource_semaphores

má systém dost paměti?
dva řádky, jeden pro regular resource semaphore (id 0) a druhý pro small-query resource semaphore (id 1)

Funkce

dm_exec_cached_plan_dependent_objects(plan_handle)

řádek pro každý tsql plán, clr plán, cursor asociovaný s plánem

dm_exec_cursors(session_id)

informace o otevřených kurzorech
sql_handle, statement_start_offset, statement_end_offset

dm_exec_xml_handles(session_id|0)

informace o aktivních handlech z sp_xml_preparedocument

dm_exec_query_plan(plan_handle)

vrací XML s plánem
omezení na 128 úrovní v XML, pokud víc, tak vyhodí chybu

dm_exec_text_query_plan(plan_handle, {statement_start_offset|0|DEFAULT} , statement_end_offset|0|DEFAULT})

vrací řetězec s XML plánem dotazu
je možné zacílit statement
výstup není limitován velikostí

dm_exec_input_buffer(session_id, request_id)

statement dle session_id a request_id

dm_exec_describe_first_result_set(@tsql, @params, @include_browse_information)

informace o prvním vráceném result setu, název sloupce, datový typ, …

dm_exec_describe_first_result_set_for_object(@object_id, @include_browse_information )

obdobné, jen pro procedury a triggery zadané skrz @object_id

dm_exec_sql_text(sql_handle | plan_handle)

vrací text dotazu, procedury

dm_exec_plan_attributes(plan_handle)

vrací tabulku atributů(attribute, value, is_cache_key)
zajímavý atribut set_options, kde se dá vyčíst set option připojeni, které mohou ovlivnit výběr exekučního plánu, typicky arith_abort z .net vs. z ssms

SELECT do proměnné

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

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

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)
*/

Poznámky k T-SQL

píše Jakub Rychlý