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.

Najdi nahraď a regulární výrazy v SSMS

Často potřebuji přeformátovat kus T-SQL kódu, odstranit prázdné řádky, obalit něco do uvozovek, čárky nahradit koncem řádku a podobně. K tomu se hodí regulární výrazy podpořené v najdi a nahraď.

Kvantifikátory
určují kolikrát se smí opakovat předchozí element
* : 0 nebo vícekrát
+ : 1 nebo vícekrát
{n} : Přesně n krát
{m, n} : Mezi m a n krát. Pokud není zadáno n, pak nejméně m krát.

Zástupné znaky
\t : tab
\s : bílé znaky, taby, mezery
\d : číslice
\w : alfanumerické znaky [a-zA-Z0-9]
\r\n : odřádkování CR LF
. : jakýkoliv znak

Ukotvení
^ : Začátek řetězce
$ : Konec řetězce

Skupiny
Do kulatých závorek můžeme uzavřít část vyhledávaného řetězce. Na odpovídajicí hodnotu se pak můžeme odkázat při nahrazení jako na $1 pro první závorky, $2 pro druhé závorky, atd.

Příklady
Odstranění prázdných řádků

Uvozovka na konec řádku

Nahrazení tabulátorů za uvozovka čárka uvozovka

Seznam řetězců oddělený čárkou

Odstranění zakomentovaných řádků

Odřádkování

Přidání ISNULL

Odstranění komentářů

Klávesové zkratky pro SSMS

Ctrl + E nebo F5 pro spuštění dotazu
Ctrl + R – schová/zobrazí okno s výsledky dotazu
Ctrl + K, Ctrl + C: zakomentuje označené řádky
Ctrl + K, Ctrl + U: odkomentuje označené řádky
Ctrl + U: hodí kurzor do výběru databází, tam stačí začít psát název databáze, plus třeba dovybrat šipkama
Alt + F1 volá proceduru sp_help a jako parametr ji předá označený text
Alt + F1 při označeném názvu tabulky vypíše sloupece tabulky, zda má IDENTITY, indexy, constrainty, cizí klíče. Sloupce se dají snadno vykopírovat a použít do rozepsaného selectu nebo insertu.
Alt + F1 při označeném názvu procedury vypíše parametry procedury
Ctrl + G pro skok na konkrétní řádek textu
Shift + Alt + šipka nahoru/dolů roztáhne kurzor přes více řádků a umožní psát/mazat na více řádcích najednou

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

PowerShell – první kroky

Jednotlivé příkazy v PowerShellu se nazývají cmdlet [command-let].

Vypadají nějak takhle:
Get-Date vypíše aktuální datum a čas (vrátí objekt System.DateTime s aktuálním datem a časem)
Get-Location vypíše aktuální cestu; vše je objekt, takže vlastně vrátí objekt s aktuální cestou

Cmdlet se skládá ze slovesa a podstatného jména. Exustuje sada povolených sloves. Dostat ji můžeme cmdletem Get-Verb. Podstatné jméno je vždy v jednotném čísle.

Bezpečnost
Soubory s PowerShell skriptem mají koncovku .ps1. Implicitně se otevírá v notepadu. Skript v aktuální složce nelze spustit pouhým zadáním názvu skriptu. Je potřeba použít konvenci ./skript.ps1.

Bezpečnostní politiky

Unrestricted – povoluje spouštění všech skriptů
RemoteSigned – vyžaduje digitální podpis u skriptů, které nepocházejí z lokálního počítače
AllSigned – všechny skripty musí být podepsané
Restricted – implicitní politika; je zakázáno spouštět jakékoliv skripty; lze spouštět pouze příkazy zadané ručně do konzole

Get-ExecutionPolicy zobrazí aktuální bezpečnostní politiku
Set-ExecutionPolicy RemoteSigned změní politiku, umožní spuštět skripty pocházející z aktuálního počítače

Get-Help about_signing nápověda k politikám

Nápověda
Get-Help log vyhledá témata nápovědy s výskytem „log“
Get-Help Get-Date vypíše nápovědu pro cmdlet Get-Date
Get-Help Get-Date -Full vypíše plnou nápovědu pro cmdlet
Get-Help Get-Date -Examples vypíše pouze příklady z nápovědy
Help Get-Date -Full vypíše plnou nápovědu, ale výstup bude stránkovat

Objekty
PowerShell pracuje s objekty, které mají vlastnosti a metody.

Get-Date vrátí objekt DateTime
$a = Get-Date do proměnné $a se uloží objekt DateTime
$a.Month vrátí vlastnost Month objektu DateTime
$a.AddMonth(2) vrátí objekt DateTime zvětšený o dva měsíce oproti objektu $a

Roura
Objekty jsou z jednoho příkazu předávány do dalšího pomocí roury |.
Get-Date | Get-Member Get-Date vytvoří objekt DateTime a pošle ho do cmdeltu Get-Member, který ho zanalyzuje a vypíše typ objektu, jeho vlastnosti a metody

Práce s objekty
Get-Process | ForEach-Object{Write-Host $_.name} pro každý proces zapíše jeho název na konzoli
Get-Process | Where-Object{$_.CPU -gt 100} vrátí procesy, kde CPU > 100
Get-Process | Select-Object Name, CPU vrátí objekt, který bude mít pouze vlastnosti Name a CPU, už to nebude objekt procesu a nepůjde na něj volat metody procesu

Pár cmdletů do začátku
Get-Command vrátí příkazy, které je možné volat
Get-Command *-Object Vrátí příkazy, které je možné volat nad objektem
Get-Module vrátí moduly, které jsou nebo mohou být naimportovány
Get-Alias -Name dir vrátí definici aliasu dir
Get-Alias -Definition Get-ChildItem vrátí aliasy cmdletu Get-ChildItem

Format- formátuje výstup do konzole, již nevrací objekty
Get-Process | Format-List Name, CPU vypíše požadované vlastnosti, každou na nový řádek
Get-Process | Format-Table Name, CPU vypíše požadované vlastnosti do tabulky

Poznámky k T-SQL

píše Jakub Rychlý