Číst v angličtině

Identifikace problematických plánů dotazů

  • 15 min

Při řešení potíží s výkonem dotazů se typickým přístupem dbA nejprve identifikuje problematický dotaz, obvykle ten, který využívá nejvíce systémových prostředků, a pak načítá plán provádění. Existují dva hlavní scénáře. Jedním ze scénářů je, že dotaz konzistentně funguje špatně. Příčinou můžou být různé problémy, jako jsou omezení hardwarových prostředků (i když to obvykle nemá vliv na jeden dotaz spuštěný izolovaně), neoptimální strukturu dotazů, nastavení kompatibility databáze, chybějící indexy nebo špatné volby plánu optimalizátorem dotazů. Druhým scénářem je, že dotaz funguje dobře v některých spuštěních, ale špatně v jiných. Příčinou této nekonzistence můžou být faktory, jako je nerovnoměrná distribuce dat v parametrizovaném dotazu, který má efektivní plán některých spuštění a špatný plán pro jiné. Mezi další běžné faktory patří blokování, kdy dotaz čeká na dokončení jiného dotazu, aby získal přístup k tabulce nebo ke kolizím hardwaru.

Pojďme se podrobněji podívat na každý z těchto scénářů.

Omezení hardwaru

Hardwarová omezení se obvykle neprojevují během provádění jednoho dotazu, ale v produkčním zatížení se projeví, když jsou vlákna procesoru a paměť omezené. Kolize procesoru je možné zjistit sledováním čítače sledování výkonu% procesoru, který měří využití procesoru serveru. V SQL Serveru můžou typy čekání SOS_SCHEDULER_YIELD a CXPACKET indikovat tlak procesoru. Nízký výkon systému úložiště může zpomalit i optimalizované spouštění jednotlivých dotazů. Výkon úložiště je nejlépe sledován na úrovni operačního systému pomocí čítačů Disk Seconds/Read monitorování výkonu a Disk Seconds/Write, které měří doby dokončení vstupně-výstupních operací. SQL Server protokoluje nízký výkon úložiště, pokud vstupně-výstupní operace trvá déle než 15 sekund. Vysoké PAGEIOLATCH_SH čekání na SQL Server může značit problémy s výkonem úložiště. Výkon hardwaru se obvykle vyhodnocuje v rané fázi procesu řešení potíží kvůli snadnému posouzení.

Většinaproblémůchm dotazům vychází z neoptimálních vzorců dotazů. Například chybějící indexy můžou vést k zatížení procesoru, úložiště a paměti načtením více dat, než je potřeba. Před řešením problémů s hardwarem doporučujeme řešit a ladit neoptimální dotazy. Dále se podíváme na ladění dotazů.

Neoptimální konstrukce dotazů

Relační databáze fungují nejlépe při provádění operací založených na sadě, které manipulují s daty (INSERT, UPDATE, DELETEa SELECT) v sadách a vytvářejí jednu hodnotu nebo sadu výsledků. Alternativou je zpracování založené na řádcích, použití kurzorů nebo smyčky while, které zvyšují náklady lineárně s počtem ovlivněných řádků – problematické škálování s rostoucími objemy dat.

Detekce neoptimálního použití operací založených na řádcích s kurzory nebo smyčkami WHILE je důležitá, ale existují i další anti-vzory SQL Serveru, které je potřeba rozpoznat. Funkce s hodnotami tabulek (TVF), zejména multi-statement TVF, způsobily problematické vzory plánu provádění před SQL Serverem 2017. Vývojáři často používají více příkazů TVF ke spouštění více dotazů v rámci jedné funkce a agregaci výsledků do jedné tabulky. Používání tvfů ale může vést k sankcím za výkon.

SQL Server má dva typy TVF: vložený a více příkazů. Vložené tvfy se zpracovávají jako zobrazení, zatímco vícefaktorové TVF se během zpracování dotazů zachází jako s tabulkami. Vzhledem k tomu, že TVF jsou dynamické a chybí statistiky, SQL Server používá k odhadování nákladů na plán dotazů pevný počet řádků. To může být v pořádku pro malé počty řádků, ale neefektivní pro tisíce nebo miliony řádků.

Dalším anti-vzorem je použití skalárních funkcí, které mají podobný odhad a problémy s prováděním. Společnost Microsoft výrazně zlepšila výkon inteligentního zpracování dotazů na úrovni kompatibility 140 a 150.

Možnosti sargability

Termín SARGable v relačních databázích odkazuje na predikát (WHERE klauzuli) formátovaný tak, aby k urychlení provádění dotazů používal index. Predikáty ve správném formátu se nazývají "Argumenty hledání" nebo skupiny SARG. Použití sarg na SQL Serveru znamená, že optimalizátor vyhodnocuje použití neclusterovaného indexu ve sloupci odkazovaném v SARG pro operaci SEEK místo prohledávání celého indexu nebo tabulky, aby načetl hodnotu.

Přítomnost sarg nezaručuje použití indexu pro #B0 SEEK #A1 . Algoritmy nákladů optimalizátoru by stále mohly určit, že index je příliš nákladný, zejména pokud SARG odkazuje na velké procento řádků v tabulce. Absence SARG znamená, že optimalizátor nevyhodnotí funkce SEEK u neclusterovaného indexu.

Příklady výrazů, které nejsou SARGable, zahrnují výrazy s LIKE klauzulí používající zástupný znak na začátku řetězce, například WHERE lastName LIKE '%SMITH%'. Jiné predikáty, které nejsou SARGable, se vyskytují při použití funkcí ve sloupci, například WHERE CONVERT(CHAR(10), CreateDate,121) = '2020-03-22'. Tyto dotazy se obvykle identifikují prozkoumáním plánů provádění pro prohledávání indexů nebo tabulek, kde by se mělo jinak objevit hledání.

#B0 #A1 #A2 snímek obrazovky s plánem dotazování a provádění pomocí funkce, která není sargable #A3 #A4 #C5

Ve sloupci #B0 City #C1 je index, který se používá v klauzuli #D2 dotazu a zatímco se používá v tomto plánu provádění výše, můžete vidět, že se index kontroluje, což znamená, že se čte celý index. Funkce #D0 v predikátu způsobí, že tento výraz není SARGable. Optimalizátor nevyhodnotí použití indexového hledání v indexu ve sloupci #B0 City #C1.

Tento dotaz může být napsán tak, aby používal predikát, který je SARGable. Optimalizátor pak vyhodnotí #C1 #B0 HLEDÁNÍ v indexu ve sloupci #B2 City #C3. Operátor hledání indexu by v tomto případě načetl menší sadu řádků.

#B0 #A1 #A2 snímek obrazovky s plánem dotazu a spuštění s predikátem SARGable. #A3 #A4 #C5

Změna #D0 funkce na #D1 způsobí hledání indexu.

Poznámka

Klíčové LIKE slovo v tomto příkladu nemá na levé straně zástupný znak, takže hledá města, která začínají M. Pokud by byl "oboustranný" nebo začal se zástupným znakem ('%M%' nebo '%M'), bylo by to non-SARGable. Operace hledání se odhaduje na vrácení 1 267 řádků nebo přibližně 15% odhadu dotazu s predikátem, který není SARGable.

Některé další anti-vzory vývoje databází považují databázi za službu místo úložiště dat. Použití databáze k převodu dat na JSON, manipulaci s řetězci nebo provádění složitých výpočtů může vést k nadměrnému využití procesoru a vyšší latenci. Dotazy, které se pokusí načíst všechny záznamy a pak provádět výpočty v databázi, můžou vést k nadměrnému využití vstupně-výstupních operací a procesoru. V ideálním případě byste měli databázi použít pro operace přístupu k datům a optimalizované databázové konstrukce, jako je agregace.

Chybějící indexy

Nejběžnější problémy s výkonem správců databází vycházejí z nedostatku užitečných indexů, což způsobuje, že modul čte více stránek, než je nutné vrátit výsledky dotazu. Indexy spotřebovávají prostředky (ovlivňující výkon zápisu a spotřebovávají místo), ale jejich výkon často převáží nad dodatečnými náklady na prostředky. Plány spouštění s těmito problémy můžou identifikovat operátor dotazu Clustered Index Scan nebo kombinaci hledání neclusterovaného indexu a vyhledávání klíčů označující chybějící sloupce v existujícím indexu.

Databázový stroj pomáhá hlásit chybějící indexy v plánech provádění. Názvy a podrobnosti doporučených indexů jsou k dispozici prostřednictvím zobrazení sys.dm_db_missing_index_detailsdynamické správy . Ostatní zobrazení dynamické správy se podobají sys.dm_db_index_usage_stats a sys.dm_db_index_operational_stats zvýrazňují využití existujících indexů.

Vyřazení nepoužívaného indexu může být rozumné. Chybějící zobrazení dynamické správy indexu a upozornění plánu by měly být výchozími body pro ladění dotazů. Je důležité porozumět klíčovým dotazům a vytvářet indexy, které je podporují. Vytváření všech chybějících indexů bez vyhodnocení v kontextu se nedoporučuje.

Chybějící a zastaralé statistiky

Pochopení důležitosti statistiky sloupců a indexů pro optimalizátor dotazů je zásadní. Je také důležité rozpoznat podmínky, které můžou vést k zastaralým statistikům a k tomu, jak se tento problém může projevit na SQL Serveru. Nabídky Azure SQL mají výchozí nastavení automatického aktualizace statistik nastavených na ZAPNUTO. Před SQL Serverem 2016 bylo výchozím chováním statistiky automatického aktualizace neaktualizovat statistiky, dokud počet úprav sloupců v indexu odpovídal přibližně 20% počtu řádků v tabulce. Toto chování může vést k významným úpravám dat, které mění výkon dotazů bez aktualizace statistik, což vede k neoptimálním plánům na základě zastaralých statistik.

Před SQL Serverem 2016 je možné příznak trasování 2371 použít ke změně požadovaného počtu úprav dynamické hodnoty, aby se zvýšilo procento úprav řádků potřebných k aktivaci aktualizace statistiky. Novější verze SQL Serveru, Azure SQL Database a Azure SQL Managed Instance podporují toto chování ve výchozím nastavení. Funkce sys.dm_db_stats_properties dynamické správy zobrazuje čas poslední aktualizace statistiky a počet úprav od poslední aktualizace, což vám umožní rychle identifikovat statistiky, které můžou vyžadovat ruční aktualizace.

Špatné volby optimalizátoru

I když optimalizátor dotazů dělá dobrou úlohu optimalizace většiny dotazů, existuje několik hraničních případů, kdy optimalizátor založený na nákladech může provádět důležitá rozhodnutí, která nejsou plně srozumitelná. Existuje mnoho způsobů, jak to vyřešit, včetně použití tipů pro dotazy, příznaků trasování, vynucení plánu provádění a dalších úprav, aby bylo možné dosáhnout stabilního a optimálního plánu dotazů. Microsoft má tým podpory, který může pomoct s řešením těchto scénářů.

V následujícím příkladu z databáze #B0 AdventureWorks2017 #C1 se používá nápověda dotazu k tomu, aby optimalizátor databáze vždy používal název města Seattle. Tento tip nezaručuje nejlepší plán provádění pro všechny hodnoty města, ale je předvídatelný. Hodnota Seattle pro #D0 se použije pouze při optimalizaci. Během provádění se použije skutečná zadaná hodnota (‘Ascheim’) .

SQL 
DECLARE @city_name nvarchar(30) = 'Ascheim',
        @postal_code nvarchar(15) = 86171;

SELECT * 
FROM Person.Address
WHERE City = @city_name 
      AND PostalCode = @postal_code
OPTION (OPTIMIZE FOR (@city_name = 'Seattle');

Jak je vidět v příkladu, dotaz používá nápovědu (klauzuli #D0) k tomu, aby optimalizátoru řekl, aby k sestavení plánu provádění použil konkrétní hodnotu proměnné.

Šifrování parametrů

SQL Server ukládá plány provádění dotazů do mezipaměti pro budoucí použití. Vzhledem k tomu, že proces načítání plánu provádění je založený na hodnotě hash dotazu, musí být text dotazu stejný pro každé spuštění dotazu, který se má použít v mezipaměti. Aby bylo možné podporovat více hodnot ve stejném dotazu, mnoho vývojářů používá parametry předávané prostřednictvím uložených procedur, jak je vidět v následujícím příkladu:

SQL 
CREATE PROC GetAccountID (@Param INT)
AS

<other statements in procedure>

SELECT accountid FROM CustomerSales WHERE sales > @Param;

<other statements in procedure>

RETURN;

-- Call the procedure:

EXEC GetAccountID 42;

Dotazy lze také explicitně parametrizovat pomocí postupu #B0 . Explicitní parametrizace jednotlivých dotazů se ale provádí prostřednictvím aplikace s určitým formulářem (v závislosti na rozhraní API) příkazu PREPARE a EXECUTE. Když databázový stroj spustí tento dotaz poprvé, optimalizuje dotaz na základě počáteční hodnoty parametru, v tomto případě 42. Toto chování, označované jako zašifrování parametrů, umožňuje snížit celkovou úlohu kompilace dotazů na serveru. Pokud ale dochází ke nerovnoměrné distribuci dat, výkon dotazů se může značně lišit.

Například tabulka, která obsahovala 10 milionů záznamů, a 99% těchto záznamů má ID 1 a druhý 1% jsou jedinečná čísla, výkon je založený na tom, které ID se původně použilo k optimalizaci dotazu. Tento výrazně fluktuační výkon značí nerovnoměrnou distribuci dat a nejedná se o základní problém se zašifrováním parametrů. Toto chování je poměrně běžný problém s výkonem, o který byste měli vědět. Měli byste pochopit možnosti pro zmírnění problému. Existuje několikzpůsobůch

  • Použijte #D0 nápovědu v dotazu nebo možnost #D1 spuštění v uložených procedurách. Tato nápověda způsobí, že se dotaz nebo procedura znovu zkompilují při každém spuštění, což zvýší využití procesoru na serveru, ale vždy použije aktuální hodnotu parametru.
  • Můžete použít nápovědu k #D0 dotazu. Tento tip způsobí, že se optimalizátor rozhodne, že parametry nešifruje a porovná hodnotu s histogramem dat sloupce. Tato možnost vám nezístí nejlepší možný plán, ale umožní konzistentní plán provádění.
  • Přepište proceduru nebo dotazy přidáním logiky kolem hodnot parametrů pouze reKOMPIL pro známé problémové parametry. V následujícím příkladu platí, že pokud je parametr SalesPersonID NULL, provede se dotaz pomocí parametru OPTION (RECOMPILE).
SQL 
CREATE OR ALTER PROCEDURE GetSalesInfo (@SalesPersonID INT = NULL)
AS
DECLARE  @Recompile BIT = 0
         , @SQLString NVARCHAR(500)

SELECT @SQLString = N'SELECT SalesOrderId, OrderDate FROM Sales.SalesOrderHeader WHERE SalesPersonID = @SalesPersonID'

IF @SalesPersonID IS NULL
BEGIN
     SET @Recompile = 1
END

IF @Recompile = 1
BEGIN
    SET @SQLString = @SQLString + N' OPTION(RECOMPILE)'
END

EXEC sp_executesql @SQLString
    ,N'@SalesPersonID INT'
    ,@SalesPersonID = @SalesPersonID
GO

Tento příklad je dobrým řešením, ale vyžaduje poměrně velké vývojové úsilí a pevné pochopení distribuce dat. Vyžaduje údržbu při změnách dat.