Archiv pro měsíc: Červen 2021

MocPeknyDeadlock

Kde najít info k proběhlému deadlocku

Kde najít info k proběhlému deadlocku? Od SQL 2012 v by default zapnuté extended event session system_health. Možná je to už od 2008, ale tam to má jiný formát a neznám nikoho, kdo by to tam chtěl hledat :) V sytem_health se loguje více věcí než deadlocky, má omezenou velikost a funguje tak, že nově logované události přepisují ty nejstarší. Na vytíženém serveru tam nemusí být historie ani za celý den.

Lepší je si založit vlastní session jen na deadlocky. Třeba takto:

CREATE EVENT SESSION [deadlocks] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'deadlocks.xel',max_file_size=(20),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

A teď fotoseríál jak z Bravíčka, jak se k těm informacím doklikat.

V Object Exploreru je to Management/Extended Events/Sessions/system_health a poklikat event_file
Deadlock_system_health

Já pak dávám Grouping, abych ten zmatek trochu zpřehlednil
Deadlock_system_health2

Podle názvu události
Deadlock_system_health3

Pak už je k nalezení deadlock, pokud je nějaký zalogovaný. Z deadlock grafu je vidět akorát o kterou jde databázi a o které tabulky se tam soupeřilo.
Deadlock_system_health4

Všechny informace jsou pak v XML, které dostanem na panelu Details po poklikání na XML.
Deadlock_system_health5

V XML jsou pak stacky volání a konkrétní dotazy, které se deadlocku zúčastnily a taky zdroje o které tam šlo. Zdroje tam jsou uvedené v podobě waitresource. Pokud je potřeba zjistit o který konkrétní záznam v tabulce šlo, tak tady Kendra Little popisuje jak ten waitresource rozkódovat.

Jak psát dynamické SQL

V minulém článku jsem popsal jak nepsat dynamické SQL. Dneska ukážu jak to udělat správně. Rovnou začnu přepsanou procedurou z minulého článku.

CREATE OR ALTER PROCEDURE p_CiselnikDejNazevDleKodu
    @Schema SYSNAME = 'dbo',
    @Tabulka SYSNAME,
    @Kod NVARCHAR(50),
	@Debug BIT = 0
AS
BEGIN
    DECLARE @prikaz NVARCHAR(MAX)
    SET @prikaz = N'
SELECT TOP (1) nazev 
FROM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Tabulka) + N' 
WHERE kod = @Kod';

	IF @Debug = 1
		PRINT(@prikaz);

    EXEC sys.sp_executesql
		@stmt = @prikaz,
		@param = N'@Kod NVARCHAR(50)',
		@Kod = @Kod
END
GO

Na vstupu procedury mi chybělo schéma, které jsem tady doplnil s výchozí hodnotou dbo. Schéma a název tabulky musím do příkazu zřetězit. Obojí ošetřuji funkcí QUTENAME, která názvy obalí hranatými závorkami a zdvojí případný výskyt pravé hranaté závorky a tím zabrání SQL injekci. Funkce QUTENAME by se měla používat na všechny systémové názvy, to je databáze, tabulky, sloupce, atd.. Co kdyby nějaký filuta pojmenoval tabulku [;DROP TABLE Students--], nebo tak nějak.
Abych ošetřil i parametr @Kod, tak místo zřetězení jeho hodnoty ho zadávám jako parametr @Kod rovnou do řetězce příkazu. Volání EXEC(@prikaz) jsem nahradil voláním EXEC sys.sp_executesql. Tomu předávám samotný příkaz, pak seznam parametrů a jejich datových typů, které jsou v příkazu použity a nakonec parametrům předám hodnotu. Příkaz PRINT jsem podmínil parametrem @Debug, aby se neprintovalo vždy a zbytečně.

Ještě kouknem, jak to vypadá v plan cache.

DBCC FREEPROCCACHE
 
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'P'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'E'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'TypObjektu',  @Kod = N'CH'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'TypObjektu',  @Kod = N'D'
 
SELECT
    p.usecounts, 
    p.cacheobjtype, 
    p.objtype, 
    p.size_in_bytes,
    t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
WHERE p.cacheobjtype = 'Compiled Plan'
AND t.text NOT LIKE '%dm_exec_cached_plans%';
GO

JakPsatDynamickeSQL

V plan cache mám tentokrát dva prepared plány a každý z toho dvakrát použitý. Plán se tedy pro každou tabulku sestaví jednou a pak se používá opakovaně i pro různé hodnoty parametru @Kod. Minule se mi pro každou hodnotu kódu sestavoval nový adhoc plan.

Jak nepsat dynamické SQL

Mějme nějaké číselníkové tabulky s daty:

CREATE TABLE dbo.Obor(
	IdObor INT IDENTITY NOT NULL 
		CONSTRAINT pk_Obor PRIMARY KEY CLUSTERED,
	Kod NVARCHAR(50) NOT NULL,
	Nazev NVARCHAR(250) NOT NULL
)
GO

INSERT INTO dbo.Obor(Kod, Nazev)
VALUES
(   N'P', N'Plyn'),
(   N'E', N'Elektřina')
GO

CREATE TABLE dbo.TypObjektu(
	IdTypObjektu INT IDENTITY NOT NULL 
		CONSTRAINT pk_TypObjektu PRIMARY KEY CLUSTERED,
	Kod NVARCHAR(50) NOT NULL,
	Nazev NVARCHAR(250) NOT NULL
)
GO

INSERT INTO dbo.TypObjektu(Kod, Nazev)
VALUES
(   N'CH', N'Chata'),
(   N'D', N'Dům')
GO

A mějme výborný nápad na proceduru, která vrátí název ke kódu číselníku a libovolné tabulce.

CREATE OR ALTER PROCEDURE p_CiselnikDejNazevDleKodu
	@Tabulka SYSNAME,
	@Kod NVARCHAR(50)
AS
BEGIN
	DECLARE @prikaz NVARCHAR(MAX)
	SET @prikaz = N'
SELECT TOP (1) nazev 
FROM ' + @Tabulka + N' 
WHERE kod = ''' + @Kod + N'''';
	PRINT(@prikaz);
	EXEC(@prikaz);
END
GO

Vypadá to, že to funguje parádně.

EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'P'
/*
Vrátí:
nazev
-----
Plyn
*/

Ale pokud vyliji plan cache, parkát to spustím a kouknu co je v keši:

DBCC FREEPROCCACHE

EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'P'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'E'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'TypObjektu',  @Kod = N'CH'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'TypObjektu',  @Kod = N'D'

SELECT 
	p.usecounts, 
	p.cacheobjtype, 
	p.objtype, 
	p.size_in_bytes,
	t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
WHERE p.cacheobjtype = 'Compiled Plan'
AND t.text NOT LIKE '%dm_exec_cached_plans%';
GO

JakNepsatDynamickeSQL

Teď už to nevypadá tak dobře. Každý select spálil CPU na výpočet plánu, každý text selectu a jeho plán je v plan cache a zabíra tam zbytečně místo. Nemusí jít jen o číselníky. Dneska jsem viděl tisíce položek v keši, které se lišily jen v id, které bylo zřetězené do dotazu.
Samotný dotaz jede rychle, ale pálí se zbytečně CPU na výpočet plánu a zabírá se paměť. Tím jde dolů výkon celého serveru.

A co když je některý parametr procedury vstupem od uživatele?

EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'nevim',  @Kod = N'P'
/*
SELECT TOP (1) nazev 
FROM nevim 
WHERE kod = 'P'
Msg 208, Level 16, State 1, Line 60
Invalid object name 'nevim'.
*/

Pokud se neošetřené výjimky vracejí až k uživateli, tak díky printu dostane celý text, co je v komentáři výše. Prostě včetně struktury toho pochybného SELECT TOP (1)…

No a pokud bude uživatel důvtipnější, tak si vrátí strukturu celé DB.

EXEC p_CiselnikDejNazevDleKodu 
    @Tabulka = N'Obor',  
    @Kod = N''' UNION SELECT name FROM sys.tables--'
/*
nazev
------------
a
Obor
tabule
tabule2
TypObjektu
*/

Prostě Little Bobby Tables.

Pokračování článku o tom, jak by to mělo vypadat.

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.
Memory Grant
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ů
regex 01 odstraneni prazdnych radku

Uvozovka na konec řádku
regex 02 strich na konec radku

Nahrazení tabulátorů za uvozovka čárka uvozovka
regex 03 taby za strich carka strich

Seznam řetězců oddělený čárkou
regex 04 seznam retezcu oddeleny carkou

Odstranění zakomentovaných řádků
regex 05 odstraneni zakomentovanych radku

Odřádkování
regex 06 odradkovani

Přidání ISNULL
regex 07 pridani ISNULL

Odstranění komentářů
regex 08 odstraneni komentu