Vyhledání procedur obsahujících nějaké slovo

Dneska tu mám rychlovku. Jak najít objekty (procedury, funkce, triggery), které obsahují nějaké slovo (zpravidla nějaký název tabulky). Já na to používám tohle:

SELECT DISTINCT OBJECT_NAME(id)
FROM  sys.syscomments s
WHERE s.text LIKE '%tabul%'

A jak to spustit nad více databázemi najednou? Třeba pomocí registrovaných serverů. Tam si vytvoříme Server Group (1Produkce), pod ní přidáme servery i s konkrétní databází (AssemblyTest, skoleni). A pak nad server group pravá myš a New Query. V management studiu se otevře okno, jehož obsah se pak spustí proti všem serverům/databázím ze skupiny.
vyhledani

Zakládání ID u tabulek bez IDENTITY

Máme historickou tabulku, která má umělé id, ale nemá na něm IDENTITY. Nechceme procházet očistcem a převádět ji na tabulku s IDENTITY, ale chceme zakládat ID tak, aby jsme se vyhnuli chybě „Violation of PRIMARY KEY constraint“ při souběhu ze dvou stran.

Ta historická tabulka může vypadat třeba takto:

CREATE TABLE dbo.tabulka(
	id_tabulka INT CONSTRAINT pk_tabulka PRIMARY KEY CLUSTERED,
	data VARCHAR(4000)
)

A špatný přistup, jak zakládat záznamy třeba takto:

BEGIN TRAN

DECLARE @id INT = (SELECT ISNULL(MAX(t.id_tabulka),0)+1 FROM dbo.tabulka t)

--jen pro zvýraznění problému
WAITFOR DELAY '0:0:10'

INSERT INTO dbo.tabulka
(
    id_tabulka,
    data
)
VALUES
(   @id,
    'data'
)
COMMIT

Toto když spustíme ve dvou oknech, tak nám to ve druhém upadne na „Violation of PRIMARY KEY constraint“, protože se pokusíme založit podruhé stejný primární klíč.

Možnosti řešení jsou dvě. Pokud to máme v transakci, tak můžeme použít UPDLOCK. Ten nám uvalí U zámek na poslední záznam v tabulce a druhá transakce ho tak nebude moct přečíst a zůstane čekat do doby, než první transakce doběhne do konce.

BEGIN TRAN

DECLARE @id INT = (SELECT ISNULL(MAX(t.id_tabulka),0)+1 FROM dbo.tabulka t WITH (UPDLOCK))

--jen pro zvýraznění problému
WAITFOR DELAY '0:0:10'

INSERT INTO dbo.tabulka
(
    id_tabulka,
    data
)
VALUES
(   @id,
    'data'
)
COMMIT

Druhou možností je, že v případě chyby zkusíme to maximální id vypočíst znova. Z opatrnosti tomu dáme jen 5 pokusů a pak toho případně necháme a chybu vypropagujem.

DECLARE @counter INT = 0;
DECLARE @max_counter INT = 5;
DECLARE @id INT = (SELECT ISNULL(MAX(t.id_tabulka),0)+1 FROM dbo.tabulka t WITH (UPDLOCK))
WHILE @counter < @max_counter
BEGIN
  BEGIN TRY
    SET @id = (SELECT ISNULL(MAX(t.id_tabulka),0)+1 FROM dbo.tabulka t)

    --jen pro zvýraznění problému
    WAITFOR DELAY '0:0:10'

    INSERT INTO dbo.tabulka
    (
      id_tabulka,
      data
    )
    VALUES
    (	
      @id,
      'data'
    )

    SET @counter = @max_counter; --vypadnout z cyklu
  END TRY
  BEGIN CATCH
    SET @counter += 1;
    IF ERROR_NUMBER() <> 2627 --Violation of PRIMARY KEY constraint
      OR @counter = @max_counter
        THROW;
  END CATCH;
END;

První řešení mi přijde čistší, ale zase tam druhá transakce musí čekat na dokončení první.

Jak na UPSERT

Často potřebujeme podle určitého klíče zaktualizovat hodnotu. A pokud klíč neexistuje, tak ho s hodnotou vložit do tabulky. Tedy UPDATE nebo INSERT…UPSERT. A často nás na první dobrou napadne úplně špatné řešení.

CREATE TABLE dbo.tabulka(
	id INT IDENTITY(1,1) NOT NULL CONSTRAINT pk_tabulka PRIMARY KEY CLUSTERED, 
	dalsiKlic VARCHAR(50) NOT NULL CONSTRAINT uq_tabulka_dalsiKlic UNIQUE, 
	data VARCHAR(50) NULL
)
GO

CREATE OR ALTER PROCEDURE dbo.p_velky_spatny_UPSERT
	@dalsiKlic VARCHAR(50)
	,@data VARCHAR(50)
AS
BEGIN
	IF EXISTS(
		SELECT * 
		FROM  dbo.tabulka t
		WHERE t.dalsiKlic = @dalsiKlic
	)
		UPDATE dbo.tabulka
		SET data = @data
		WHERE dalsiKlic = @dalsiKlic
	ELSE
		INSERT INTO dbo.tabulka
		(
			dalsiKlic,
			data
		)
		VALUES
		(   @dalsiKlic,
			@data
			)
END
GO

Tedy najdi záznam a pokud jsi našel, tak ho najdi znova a zaktualizuj. No a pokud jsi ho nenašel, tak ho založ. Problém je, že pokud záznam existuje, tak ho hledám dvakrát a plýtvám tak zdroji. Druhý problém je, že ta operace není atomická a v READ COMMITED isolation levelu se může stát, že dvě transakce najednou ověří, že záznam s daným klíčem neexistuje a obě se ho pak pokusí založit. V lepším případě pak skončíme s chybou Violation of UNIQUE KEY constraint…, v horším založíme duplicitu (pokud nám chybí constraint). Zabalit to do transakce nic neřeší.

Zabalit to do transakce a zvýšit isolation level na serializable řešením je:

CREATE OR ALTER PROCEDURE dbo.p_velky_spatny_serializable_UPSERT
	@dalsiKlic VARCHAR(50)
	,@data VARCHAR(50)
AS
BEGIN
	SET TRAN ISOLATION LEVEL SERIALIZABLE
	BEGIN TRAN
	IF EXISTS(
		SELECT * 
		FROM  dbo.tabulka t
		WHERE t.dalsiKlic = @dalsiKlic
	)
		UPDATE dbo.tabulka
		SET data = @data
		WHERE dalsiKlic = @dalsiKlic
	ELSE
		INSERT INTO dbo.tabulka
		(
			dalsiKlic,
			data
		)
		VALUES
		(   	@dalsiKlic,
			@data
		)
	COMMIT
END
GO

Ale tím vyměníme problém se zakládáním duplicit, za problém s deadlocky. U neexistující položky provede select v existu zamčení RangeS-S zámkem, čímž si pojistí, aby daná hodnota nebyla vložena v jiné transakci. Problém je, že RangeS-S je kompatiblní s RangeS-S. Tedy, pokud se dvě transakce sejdou a obě provedou EXISTS podmínku, tak si obě můžou hodit na stejný rozsah záznamů RangeS-S zámek. Následně při insertu se pokusí použít X zámek, čímž dojde k deadlocku.

Jak je to správně?
Správně to je v transakci, v serializable a bez zbytečného selectu.

CREATE OR ALTER PROCEDURE dbo.p_dobry_UPSERT
	@dalsiKlic VARCHAR(50)
	,@data VARCHAR(50)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
	UPDATE dbo.tabulka WITH (SERIALIZABLE)
	SET data = @data
	WHERE dalsiKlic = @dalsiKlic

	IF @@ROWCOUNT = 0
		INSERT INTO dbo.tabulka
		(
			dalsiKlic,
			data
		)
		VALUES
		(   	@dalsiKlic,
		    	@data
		)
	COMMIT
END
GO

Pokud záznam existuje, tak se provede update. Pokud neexistuje, tak po updatu zůstane na unikátním indexu RangeS-U zámek, který zabezpečí, že si jiný proces neuvalí stejný zámek a zároveň, že jiný proces nevloží hodnotu klíče, o které jsme se právě přesvědčili, že neexistuje. Tedy žádné deadlocky a žádné Violation of UNIQUE KEY constraint.

UPDATE WITH (UPDLOCK)

Ve zděděném kódu občas potkávám UPDATE tabulka WITH (UPDLOCK) SET… a nikdo mi k tomu nechtěl říct jestli to je k něčemu dobré. Teorie praví, že pro vyhledání updatovaného záznamu se použije U zámek a updatovaný záznam pak dostane X zámek, který se drží do konce transakce. Hint WITH (UPDLOCK) by měl zaručit držení U zámku do konce transakce. Pojďme si to zkusit v praxi.

Zkoušet to budeme na 3 tabulkách. Za prvé na haldě bez primárního klíče. Za druhé na haldě s neklastrovaným primárním klíčem. A za třetí na tabulce s klastrovaným primáním klíčem, kterou ještě okořeníme druhým indexovaným klíčem.

CREATE TABLE dbo.halda(
	id INT NOT NULL, 
	data VARCHAR(500) NULL
)

CREATE TABLE dbo.haldaPK(
	id INT NOT NULL CONSTRAINT pk_haldaPK PRIMARY KEY NONCLUSTERED, 
	data VARCHAR(500) NULL
)

CREATE TABLE dbo.tabulka(
	id INT NOT NULL CONSTRAINT pk_tabulka PRIMARY KEY CLUSTERED, 
	dalsiKlic VARCHAR(50) NOT NULL CONSTRAINT uq_tabulka_dalsiKlic UNIQUE, 
	data VARCHAR(50) NULL
)
GO

Naplníme nějakými testovacími daty

INSERT INTO dbo.halda(id, data)
VALUES(1,'a'),(2,'b'),(3,'c')

INSERT INTO dbo.haldaPK(id, data)
VALUES(1,'a'),(2,'b'),(3,'c')

INSERT INTO dbo.tabulka(id,dalsiKlic,data)
VALUES(1,'a','dataA'),(2,'b','dataB'),(3,'c','dataC')
GO

A jdeme zkoušet updaty. Nejdříve na haldě.

BEGIN TRAN
UPDATE dbo.halda WITH (UPDLOCK) SET data = 'x' WHERE id = 3
EXEC sp_lock @spid1 = @@SPID
ROLLBACK
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     32     0           0      DB                                    S        GRANT
61     32     1845581613  0      RID  1:40441:2                        X        GRANT
61     32     1845581613  0      PAG  1:40441                          IX       GRANT
61     32     1845581613  0      TAB                                   IX       GRANT
61     1      1467152272  0      TAB                                   IS       GRANT
61     32767  -571204656  0      TAB                                   Sch-S    GRANT
*/

Hmm, tak na první pokus žádný U zámek.

Zkusíme haldu s primárním klíčem.

BEGIN TRAN
UPDATE dbo.haldaPK WITH (UPDLOCK) SET data = 'x' WHERE id = 3
EXEC sp_lock @spid1 = @@SPID
ROLLBACK
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     32     0           0      DB                                    S        GRANT
61     32     1861581670  2      KEY  (98ec012aa510)                   U        GRANT
61     32     1861581670  0      RID  1:8089:2                         X        GRANT
61     32     1861581670  0      TAB                                   IX       GRANT
61     32     1861581670  0      PAG  1:8089                           IX       GRANT
61     32     1861581670  2      PAG  1:16177                          IU       GRANT
61     1      1467152272  0      TAB                                   IS       GRANT
61     32767  -571204656  0      TAB                                   Sch-S    GRANT
*/

Tady už se něco událo. Vidíme X zámek na záznamu na haldě a U zámek na záznamu indexu primárního klíče.

Teď zkusme update tabulky s klastrovaným primárním klíčem, kde budeme vyhledávat právě podle toho primárního klíče.

BEGIN TRAN
UPDATE dbo.tabulka WITH (UPDLOCK) SET data = 'x' WHERE id = 3
EXEC sp_lock @spid1 = @@SPID
ROLLBACK
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     32     0           0      DB                                    S        GRANT
61     32     1909581841  1      KEY  (98ec012aa510)                   X        GRANT
61     32     1909581841  0      TAB                                   IX       GRANT
61     32     1909581841  1      PAG  1:24265                          IX       GRANT
61     1      1467152272  0      TAB                                   IS       GRANT
61     32767  -571204656  0      TAB                                   Sch-S    GRANT
*/

A tentokrát zase žádný U zámek.

Naposledy zkusíme hledat v klastrované tabulce podle jiného klíče než primárního.

BEGIN TRAN
UPDATE dbo.tabulka WITH (UPDLOCK) SET data = 'x' WHERE dalsiKlic = 'c'
EXEC sp_lock @spid1 = @@SPID
ROLLBACK
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     32     0           0      DB                                    S        GRANT
61     32     1909581841  1      KEY  (98ec012aa510)                   X        GRANT
61     32     1909581841  0      TAB                                   IX       GRANT
61     32     1909581841  2      KEY  (f037bcc414ff)                   U        GRANT
61     32     1909581841  1      PAG  1:24265                          IX       GRANT
61     1      1467152272  0      TAB                                   IS       GRANT
61     32767  -571204656  0      TAB                                   Sch-S    GRANT
61     32     1909581841  2      PAG  1:32353                          IU       GRANT
*/

Zafungovalo to. Updatovaný záznam v klastrovaném indexu je zamčený X zámkem a záznam v unikátním indexu je zamčený U zámkem.

Závěr
Závěrem zatím budiž to, že při použití WITH (UPDLOCK) se U zámek drží do konce transakce na záznamu indexu, podle kterého se vyhledávalo, pokud je tento rozdílný od podkladové struktury, která drží data (halda, klastrovaný index).

Využití
Hodně jsem se snažil najít nějaký pěkný příklad, k čemu je to dobré. Ale našel jsem jen jeden dost pochybný. Pokud mám nějaký kód, který v jedné transakci dvakrát updatuje stejný řádek a navíc je tento kód vykonáván paralelně ve dvou vláknech nad stejným záznamem. V takovém případě může dojít k deadlocku, kterému se dá zabránit právě tím UPDATE WITH (UPDLOCK).

Příkladem budiž:

BEGIN TRAN
UPDATE dbo.tabulka SET data = 'x' WHERE dalsiKlic = 'c'

--nejaka dalsi prace
WAITFOR DELAY '0:0:10'


UPDATE dbo.tabulka SET data = 'x' WHERE dalsiKlic = 'c'
COMMIT

Tohle když spustím ve dvou oknech SSMS, tak mi to v jednom okně upadne na deadlock.

Mechanismus je takový, že se provede první update v prvním okně a na záznamu v klastrovaném indexu je pak držen X zámek. První update ve druhém okně pak provede jen vyhledání záznamu k updatu v unikátním indexu, který si označí U zámkem. Následně se pokusí označit U zámkem i záznam v klastrovaném indexu, což nebude kompatibilní s již uvaleným X zámkem, takže zůstane čekat na jeho uvolnění. Transakce v prvním okně mezitím dojde k druhému updatu. Pokusí se vyhledat záznam pro update v unikátním klíči a uvalit na něj U zámek, což nebude kompatibilní s již uvaleným U zámkem z druhé transakce. Dostaneme se tedy do situace, kdy první transakce čeká na druhou a druhá na první. Deadlock. SQL server jednu vybere a sestřelí.

Předejít tomu jde přidáním WITH (UPDLOCK) na první update.

BEGIN TRAN
UPDATE dbo.tabulka WITH (UPDLOCK) SET data = 'x' WHERE dalsiKlic = 'c'

--nejaka dalsi prace
WAITFOR DELAY '0:0:10'


UPDATE dbo.tabulka SET data = 'x' WHERE dalsiKlic = 'c'
COMMIT

V takovém případě zůstane po prvním updatu v prvním okně držen X zámek na záznamu v klastrovaném indexu a U zámek na záznamu v unikátním indexu. Transakci v druhém okně se už nepovede přidat U zámek na záznam v unikátním indexu a zůstane tedy čekat. Zároveň už nebude nijak omezovat transakci z prvního okna v úspěšném dokončení.

Jak vyskriptovat data z tabulky

Pomocí wizardu

Pro jednorázové akce je nejjednodušší si to vyklikat ve wizardu. V Object Exploreru nad databází pravá myš a Tasks/Generate Scripts… Next, abychom se dostali na záložku Choose Objects, kde vybereme konkrétní tabulky, pro které potřebuje vyskriptovat data.
generate scripts1

Na další záložce vybereme, kde chceme skript uložit, třeba Open in new query window. A pak klikneme na tlačítko Advanced, kde vybereme jestli chceme skriptovat data, strukturu, nebo obojí. Já volím Data only. By default tam je Schema only a data bychom tak nedostali.

generate scripts2

Dvakrát Next a Finish a v novém okně máme insert skripty.

SET IDENTITY_INSERT [dbo].[Kategorie] ON 
GO
INSERT [dbo].[Kategorie] ([IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (1, N'domacnost', N'domácnost', CAST(N'2021-09-30T08:53:06.287' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Kategorie] ([IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (2, N'podnikatel', N'podnikatel', CAST(N'2021-09-30T08:53:06.287' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[Kategorie] OFF
GO
SET IDENTITY_INSERT [dbo].[KategorieTyp] ON 
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (1, 1, N'dum', N'dům', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (2, 1, N'byt', N'byt', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (3, 1, N'chata', N'chata', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (4, 2, N'garaz', N'garáž', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (5, 2, N'provozovna', N'provozovna', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (6, 2, N'kancelar', N'kacelář', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[KategorieTyp] OFF
GO

sp_generate_inserts

Pokud potřebujeme pravidelně přenášet data z databáze do databáze, tak se nám hodí nějaká procedura, která nám insert skript vytvoří. K dostání je třeba tady:https://github.com/lambacck/generate_inserts.

Pak v management studiu přepneme Results to text (Ctrl+T) a spustíme:

EXEC dbo.sp_generate_inserts @table_name = 'Kategorie'

Výsledkem pak je skript:

--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com
 
SET NOCOUNT ON
 
SET IDENTITY_INSERT [dbo].[Kategorie] ON
GO
 
 
PRINT 'Inserting values into [Kategorie]'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO [Kategorie] ([IdKategorie],[Kod],[Nazev],[PlatnostOd],[PlatnostDo])VALUES(1,'domacnost','domácnost','Sep 30 2021  8:53:06:287AM','Dec 31 2099 12:00:00:000AM')
INSERT INTO [Kategorie] ([IdKategorie],[Kod],[Nazev],[PlatnostOd],[PlatnostDo])VALUES(2,'podnikatel','podnikatel','Sep 30 2021  8:53:06:287AM','Dec 31 2099 12:00:00:000AM')

PRINT 'Done'
 
 
SET IDENTITY_INSERT [dbo].[Kategorie] OFF
GO
SET NOCOUNT OFF
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