Identifikace problematických plánů dotazů

  • 15 min

Cestou, kterou většina dbA provede při řešení potíží s výkonem dotazů, je nejprve identifikovat problematický dotaz (obvykle dotaz využívající nejvyšší množství systémových prostředků) a pak načíst plán provádění tohoto dotazu. Existují dva scénáře. Jedním z nich je to, že dotaz konzistentně funguje špatně. Konzistentní nízký výkon může být způsoben několika různými problémy, včetně omezení hardwarových prostředků (i když tato situace obvykle nebude mít vliv na jeden dotaz spuštěný izolovaně), neoptimální strukturu dotazů, nastavení kompatibility databáze, chybějící indexy nebo špatnou volbu plánu optimalizátorem dotazů. Druhým scénářem je, že dotaz funguje dobře pro některá spuštění, ale ne pro jiné. Tento problém může být způsoben několika dalšími faktory, nejběžnější je nerovnoměrná distribuce dat v parametrizovaném dotazu, který má efektivní plán pro některé spuštění a špatný pro jiné spuštění. Další běžné faktory v nekonzistentním výkonu dotazů blokují, kdy dotaz čeká na dokončení jiného dotazu, aby získal přístup k tabulce nebo hardwarové kolizi.

Pojďme se podrobněji podívat na každý z těchto potenciálních problémů.

Omezení hardwaru

Hardwarová omezení se obvykle neprojeví při provádění jednoho dotazu, ale bude zřejmé, kdy se použije produkční zatížení, a existuje omezený počet vláken procesoru a omezené množství paměti, které se má mezi dotazy sdílet. Pokud máte kolize procesoru, obvykle se zjistí sledováním čítače sledování výkonu %Čas procesoru, který měří využití procesoru serveru. Při hlubším pohledu na SQL Server se může zobrazit typy čekání SOS_SCHEDULER_YIELD a CXPACKET , když je server pod tlakem procesoru. V některých případech ale s nízkým výkonem systému úložiště může být pomalé i jedno spuštění dotazu, které je jinak optimalizováno. Výkon systému úložiště je nejlépe sledován na úrovni operačního systému pomocí čítačů monitorování výkonu 'Disk Seconds/Read' a 'Disk Seconds/Write', které měří, jak dlouho trvá dokončení vstupně-výstupní operace. SQL Server zapíše do protokolu chyb, pokud zjistí nízký výkon úložiště (pokud dokončení vstupně-výstupních operací trvá déle než 15 sekund). Pokud se podíváte na statistiku čekání a zobrazí se vysoké procento čekání PAGEIOLATCH_SH na SQL Serveru, může dojít k problému s výkonem systému úložiště. Výkon hardwaru se obvykle zkoumá na vysoké úrovni v rané fázi procesu řešení potíží s výkonem, protože je poměrně snadné ho vyhodnotit.

Většina problémů s výkonem databáze může být přiřazována neoptimálním vzorcům dotazů, ale v mnoha případech bude spouštění neefektivních dotazů tlačit na hardware. Chybějící indexy můžou například vést k zatížení procesoru, úložiště a paměti načtením více dat, než je potřeba ke zpracování dotazu. Před řešením problémů s hardwarem doporučujeme řešit neoptimální dotazy a ladit je. 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ě. Operace založené na sadách provádějí manipulaci s daty (INSERT, UPDATE, DELETEa SELECT) v sadách, kde se práce provádí na sadě hodnot a vytváří buď jednu hodnotu, nebo sadu výsledků. Alternativou k operacím založeným na nastavení je provádět práci založenou na řádcích pomocí kurzoru nebo smyčky while. Tento typ zpracování se označuje jako zpracování založené na řádcích a jeho náklady se lineárně zvyšují s počtem ovlivněných řádků. Toto lineární měřítko je problematické, protože objemy dat pro aplikaci rostou.

Při zjišťování neoptimálního použití operací založených na řádcích s kurzory nebo smyčkami WHILE je důležité, existují i další anti-vzory SQL Serveru, které byste měli rozpoznat. Funkce s hodnotami tabulek (TVF), zejména funkce s více příkazy s hodnotami tabulky, způsobily problematické vzory plánu provádění před SQL Serverem 2017. Mnozí vývojáři rádi používají funkce s hodnotami tabulky s více příkazy, protože můžou spouštět více dotazů v rámci jedné funkce a agregovat výsledky do jedné tabulky. Každý, kdo píše kód T-SQL, ale musí vědět o možných sankcích za výkon při používání TVF.

SQL Server má dva typy funkcí s hodnotou tabulky, vložené a více příkazů. Pokud používáte vložený tvF, databázový stroj s ním zachází stejně jako se zobrazením. Funkce TVF s více příkazy se při zpracování dotazu zachází stejně jako s jinou tabulkou. Vzhledem k tomu, že tvfy jsou dynamické a jako takové, SQL Server na nich nemá statistiky, použil při odhadu nákladů na plán dotazu pevný počet řádků. Pevný počet může být v pořádku, pokud je počet řádků malý, ale pokud TVF vrátí tisíce nebo miliony řádků, může být plán provádění neefektivní.

Dalším anti-vzorem bylo použití skalárních funkcí, které mají podobné odhady a problémy s prováděním. Společnost Microsoft výrazně zlepšila výkon díky zavedení 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 (klauzule WHERE) v určitém formátu, který může použít index k urychlení provádění dotazu. Predikáty ve správném formátu se nazývají "Argumenty hledání" nebo skupiny SARG. Použití skupiny SARG na SQL Serveru znamená, že optimalizátor vyhodnotí použití neclusterovaného indexu ve sloupci odkazovaném v SARG pro operaci SEEK místo prohledávání celého indexu (nebo celé tabulky) k načtení hodnoty.

Přítomnost SARG nezaručuje použití indexu pro funkci SEEK. Algoritmy nákladů optimalizátoru můžou stále určovat, že index je příliš nákladný. To může být případ, kdy sarg odkazuje na velké procento řádků v tabulce. Absence SARG znamená, že optimalizátor ani nevyhodnotí funkce SEEK v neclusterovaného indexu.

Některé příklady výrazů, které nejsou SARG (někdy se říká, že nejsou sargable) jsou ty, které obsahují LIKE klauzuli se zástupným znakem na začátku řetězce, který se má shodovat, WHERE lastName LIKE ‘%SMITH%’například . Jiné predikáty, které nejsou SARG, se vyskytují při použití funkcí ve sloupci, WHERE CONVERT(CHAR(10), CreateDate,121) = ‘2020-03-22’například . Tyto dotazy s nesáritelnými výrazy jsou obvykle identifikovány prozkoumáním plánů provádění pro prohledávání indexů nebo tabulek, kde by se měly jinak provádět hledání.

Screenshot of query and execution plan using a non-SARGable function.

Ve sloupci Město je index, který se používá v WHERE klauzuli 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 LEFT v predikátu způsobí, že tento výraz není SARGable. Optimalizátor nevyhodnotí použití indexového hledání v indexu ve sloupci Město .

Tento dotaz může být napsán tak, aby používal predikát, který je SARGable. Optimalizátor by pak vyhodnotil hledání v indexu ve sloupci Město . Operátor hledání indexu by v tomto případě četl mnohem menší sadu řádků, jak je znázorněno níže.

Screenshot of a query and execution plan with a SARGable Predicate.

Změna LEFT funkce na LIKE výsledky hledání indexu

Poznámka:

Klíčové slovo LIKE v tomto případě nemá na levé straně zástupný znak, takže hledá města, která začínají M. Pokud by byla "oboustranná" nebo byla spuštěna se zástupným znakem (%M% nebo %M), bylo by to nespravovatelné. Operace hledání se odhaduje na vrácení 1267 řá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, které vidíme jako správci databáze, jsou způsobené nedostatkem užitečných indexů, které způsobují, že modul bude číst mnohem více stránek, než je nutné k vrácení výsledků dotazu. I když indexy nejsou bezplatné z hlediska prostředků (přidání dalších indexů do tabulky může ovlivnit výkon zápisu a spotřeba místa), zvýšení výkonu, které nabízejí, může vykompenzovat dodatečné náklady na prostředky mnohokrát. Plány provádění s těmito problémy s výkonem můžou často identifikovat operátor dotazu Clustered Index Scan nebo kombinaci nonclustered Index Search a Key Lookup (což indikuje chybějící sloupce v existujícím indexu).

Databázový stroj se pokusí tento problém vyřešit hlášením chybějících indexů v plánech provádění. Názvy a podrobnosti doporučených indexů jsou k dispozici prostřednictvím zobrazení dynamické správy s názvem sys.dm_db_missing_index_details. Existují také další zobrazení dynamické správy v SQL Serveru, například sys.dm_db_index_usage_stats a sys.dm_db_index_operational_stats, které zvýrazňují využití stávajících indexů.

Může být vhodné vyřadit index, který nepoužívá žádné dotazy v databázi. Chybějící zobrazení dynamické správy indexu a upozornění plánu by se měly používat jenom jako výchozí bod pro ladění dotazů. Je důležité pochopit, co jsou klíčové dotazy, a vytvářet indexy pro podporu těchto dotazů. Vytvoření všech chybějících indexů bez vyhodnocení indexů v kontextu ostatních se nedoporučuje.

Chybějící a zastaralé statistiky

Dozvěděli jste se o důležitosti statistik sloupců a indexů pro optimalizátor dotazů. Je také důležité pochopit podmínky, které můžou vést k zastaralým statistikům a jak se tento problém může projevit na SQL Serveru. Nabídky Azure SQL mají ve výchozím nastavení nastavenou statistiku automatické aktualizace na hodnotu ZAPNUTO. Před SQL Serverem 2016 bylo výchozím chováním statistik automatické aktualizace neaktualizovat statistiky, dokud se počet úprav sloupců v indexu rovnal přibližně 20 % počtu řádků v tabulce. Kvůli tomuto chování byste mohli mít úpravy dat, které byly dostatečně významné ke změně výkonu dotazů, ale neaktualizovat statistiky. Jakýkoli plán, který použil tabulku se změněnými daty, by byl založený na zastaralých statistikách a často by byl neoptimální.

Před SQL Serverem 2016 jste měli možnost použít příznak trasování 2371, který změnil požadovaný počet úprav na dynamickou hodnotu, aby se vaše tabulka zvětšila, takže procento úprav řádků, které bylo nutné k aktivaci aktualizace statistiky, bylo menší. Novější verze SQL Serveru, Azure SQL Database a Azure SQL Managed Instance podporují toto chování ve výchozím nastavení. K dispozici je také funkce dynamické správy s názvem sys.dm_db_stats_properties, která ukazuje čas poslední aktualizace statistiky a počet změn provedených od poslední aktualizace, což umožňuje rychle aktualizovat statistiky identity, které může být potřeba aktualizovat ručně.

Š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 AdventureWorks2017 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 bude předvídatelný. Hodnota "Seattle" pro @city_name bude použita pouze během optimalizace. Během provádění se použije skutečná zadaná hodnota (‘Ascheim’) .

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 OPTION (klauzuli) k tomu, aby optimalizátoru řekl, že k sestavení plánu provádění použije 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:

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 sp_executesql. Explicitní parametrizace jednotlivých dotazů se ale obvykle 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 tento dotaz spustí 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 s 10 miliony záznamů a 99 % těchto záznamů má ID 1 a druhý 1 % je jedinečných čísel, výkon bude 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 nápovědu RECOMPILE WITH RECOMPILE v dotazu nebo možnost 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 OPTIMIZE FOR UNKNOWN k 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, dotaz se spustí s parametrem OPTION (RECOMPILE).
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

Výše uvedený příklad je dobrým řešením, ale vyžaduje poměrně velké úsilí o vývoj a pevné pochopení distribuce dat. Může také vyžadovat údržbu při změnách dat.