Zamykání a blokování v SQL Serveru

SQL Server miluje zámky.

SQL Server standardně používá „pesimistické“ zamykání – vaše dotazy jsou přirozeně defenzivní. To může vést k problémům s blokováním, které vám brání ve škálování SQL Serveru.

Bezplatná výuková videokurz – než začnete řešit problémy s blokováním a deadlocky, začněte s knihou How to Think Like the Engine. Jedná se o rychlou 90minutovou lekci, která vysvětluje, jak fungují vnitřnosti clusterových a neclusterových indexů, což je zásadní pro pochopení toho, jak indexování ovlivňuje blokování. Je zdarma – protože věříme, že každý odborník na SQL Server by měl začít své školení právě zde. Je to opravdu tak důležité – dobrá věc.

Nástroje pro diagnostiku soupeření o zámek

„Mám právě teď čekání na zámek?“

Nástroje pro diagnostiku soupeření o zámek? Pomocí nástroje sp_BlitzFirst® můžete diagnostikovat, zda čekání na zámky aktuálně ovlivňuje váš SQL Server.

„Které tabulky mají nejhorší problémy s blokováním?“ Spusťte sp_BlitzIndex® a vyhledejte „Aggressive Index Disorder“, abyste zjistili, které tabulky mají vysoké množství čekání na zablokování (pro tabulky a indexy, které jsou aktuálně v paměti).

„Jak se vyznat v hlášení o zablokovaných procesech?“ Po zachycení hlášení o blokovaných procesech SQL Serveru (skripty níže) je k dispozici bezplatný nástroj Blocked Process Report Viewer od Michaela J. Swarta, který vám pomůže interpretovat výsledky.

„Co když moje aplikace nezachytí chyby deadlocku?“. Amit Banerjee z týmu CSS společnosti Microsoft vám pomůže zjistit podrobnosti dotazem na rozšířené události relace System Health serveru SQL Server (SQL Server 2008+).

Čítače výkonu související s bloky

Tyto čítače výkonu mohou být velmi užitečné pro konfiguraci výstrahy agenta SQL Serveru založené na čítačích perf, takže vás SQL Server může upozornit, když blokování překročí vámi nastavené prahové hodnoty.

• Nejprve je uveden OBJEKT, pak COUNTER
• SQL Server: Obecné statistiky – blokované procesy
• SQL Server: Zámky – Doba čekání na zámek (ms)
• SQL Server:
  

  Přečtěte si články o zamykání, úrovních izolace a deadlocích

  Přečtěte si úvod do dvoufázového zamykání a deadlocků od Jeremiaha.

  Přečtěte si, jak můžete testovat změny v kódu, abyste zjistili, jaké zámky jsou potřeba, od Kendry.

  Jeremiah vás naučí, jak najít mrtvé bloky a jak jim předcházet.

  Zjistěte, jak implementovat optimistické zamykání v SQL Serveru od Kendry.

  Přečtěte si více o úrovních izolace v SQL Serveru – podívejte se na naši stránku s obsáhlými odkazy a referencemi.

  Skripty pro použití hlášení o blokovaných procesech

  „Hlášení o blokovaných procesech“ je v SQL Serveru již dlouho – a stále je to užitečný nástroj. Ve výchozím nastavení v SQL Serveru se detektor zablokování probouzí každých 5 sekund a kontroluje, zda vaše dotazy nejsou ve „smrtelném objetí“. Můžete zapnout volbu, která požádá SQL Server, aby pomocí tohoto prostředku zkontroloval, zda nedochází k dlouhodobému blokování, a vydal hlášení.

  UPOZORNĚNÍ: Hlášení o zablokovaném procesu byste měli nakonfigurovat pouze pro hodnoty pět sekund nebo vyšší. Společnost Microsoft varuje, že pokud tuto hodnotu nastavíte od 1 do 4, můžete způsobit, že detektor zablokování poběží nepřetržitě a zničí váš výkon.

  Chcete-li použít hlášení o zablokovaném procesu, musíte nakonfigurovat sledování, které vyhledá událost „Hlášení o zablokovaném procesu“ v části „Chyby a varování“. Doporučujeme používat trasování na straně serveru – a samozřejmě ho spouštět pouze tehdy, když ho potřebujete.

  Chcete vidět ukázku? Podívejte se na jednu zde.

  Skripty v této části jsou ukázkami běžných způsobů provádění těchto úloh – musíte je otestovat a zkontrolovat odpovídajícím způsobem pro své prostředí a spustit na vlastní nebezpečí.

  Krok 1: Zapněte hlášení o zablokovaném procesu. Tím se vyhledají všechna blokování trvající 20 sekund nebo déle.

  Transakce.SQL

  1 2 3 4 5 6 7 . 8 9 10 11 12 13 14 15

  –Ujistěte se, že nemáte žádné čekající změny SELECT * FROM sys.configurations WHERE value <> value_in_use; GO exec sp_configure ‚show advanced options‘, 1; GO RECONFIGURE GO exec sp_configure ‚blocked process threshold (s)‘, 20; GO RECONFIGURE GO

  Krok 2: Nastavte sledování pro zachycení zprávy o zablokovaném procesu. Spusťte jej jako trasování na straně serveru.

  • Mělo by obsahovat jednu událost: Chyby a varování -> Zpráva o zablokovaném procesu.
  • Potřebuje pouze dva sloupce: Text, spid

  Tento příklad sledování na straně serveru poběží pět minut a automaticky se zastaví. Je třeba změnit cestu na platný adresář, do kterého má SQL Server právo zapisovat.

  Transakce-SQL

  1 2 3 4 5 6 7 8 . 9 . 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

  — Vytvořil: SQL Server 2012 Profiler — Vytvoření fronty declare @rc int declare @TraceID int declare @maxfilesize bigint declare @DateTime datetime ———Zde přidána funkce: set @DateTime = DATEADD(mi,5,getdate()); /* Spustit na pět minut */ set @maxfilesize = 5 — Nahraďte text InsertFileNameHere, vhodným — názvem souboru s předponou cesty, např.g., c:\MyFolder\MyTrace. K názvu souboru bude automaticky připojena přípona .trc –. Pokud zapisujete ze — vzdáleného serveru na místní jednotku, použijte cestu UNC a ujistěte se, že server má — přístup k zápisu do síťového sdílení ———–Zadejte zde název mého souboru: exec @rc = sp_trace_create @TraceID output, 0, N’S:\Traces\BlockedProcessReportDemo‘, @maxfilesize, @Datetime if (@rc != 0) goto error — Soubor a tabulku na straně klienta nelze skriptovat — Nastavení událostí deklarujte bit @on nastavte @on = 1 exec sp_trace_setevent @TraceID, 137, 1, @on exec sp_trace_setevent @TraceID, 137, 12, @on — Nastavení filtrů declare @intfilter int declare @bigintfilter bigint — Nastavení stavu trasování na start exec sp_trace_setstatus @TraceID, 1 — zobrazit id stopy pro budoucí reference select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go

  Krok 3: Podívejte se na běžící stopy a zjistěte id stopy:

  Transact-SQL

  1 2	SELECT * from sys.traces; GO

  Zastavení stopy, v případě potřeby

  Transact-SQL

  1 2 3 4 5

  –Připojte správné traceid z výše uvedeného dotazu EXEC sp_trace_setstatus @traceid =2, @status = 0; — zastavte sledování (za předpokladu, že je to ID stopy 2) GO EXEC sp_trace_setstatus @traceid =2, @status = 2; — ukončete sledování (za předpokladu, že je to ID stopy 2) GO

  Krok 4: Načtení dat zprávy o zablokovaném procesu.

  V této ukázce použijeme bezplatný prohlížeč zpráv o blokovaných procesech od Michaela J. Swarta.

  Transact-SQL

  1 2 3

  exec dbo.sp_blocked_process_report_viewer @Source=’S:\Traces\BlockedProcessReportDemo.trc‘; GO

  Krok 5: Úklid!

  Nezapomeňte na tento krok.

  Transact-SQL

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

  –Ujistěte se, že vaše stopa je pryč SELECT * from sys.traces; GO –Vypněte hlášení o zablokovaných procesech, pokud ho nepoužíváte. -Ujistěte se, že nemáte žádné čekající změny SELECT * FROM sys.configurations WHERE value <> value_in_use; GO exec sp_configure ‚blocked process threshold (s)‘, 0; GO RECONFIGURE GO exec sp_configure ‚blocked process threshold (s)‘; GO