Průvodce ověřováním a optimalizací po migraci

platí pro:SQL Server

Krok po migraci SQL Serveru je zásadní pro přizpůsobení přesnosti a úplnosti dat a odhalení problémů s výkonem úlohy.

Běžné scénáře výkonu

Následuje několik běžných scénářů výkonu, ke kterým dochází po migraci na platformu SQL Server a jak je vyřešit. Patří sem scénáře specifické pro migraci SQL Serveru na SQL Server (starší verze na novější verze) a cizí platformu (například Oracle, DB2, MySQL a Sybase) na migraci SQL Serveru.

Regrese dotazů kvůli změně verze estimátoru kardinality (CE)

Platí pro: Migrace SQL Serveru na SQL Server

Při migraci ze starší verze SQL Serveru na SQL Server 2014 (12.x) nebo novějších verzí a upgrade úrovně kompatibility databáze na nejnovější dostupnou verzi může být úloha vystavena riziku regrese výkonu.

Důvodem je to, že počínaje SQL Serverem 2014 (12.x) jsou všechny změny Optimalizátoru dotazů svázané s nejnovější úrovní kompatibility databáze, takže plány se v okamžiku upgradu nezmění správně, ale když uživatel změní COMPATIBILITY_LEVEL možnost databáze na nejnovější. Tato funkce v kombinaci s úložištěm dotazů poskytuje skvělou úroveň kontroly nad výkonem dotazů v procesu upgradu.

Další informace o změnách optimalizátoru dotazů zavedených v SQL Serveru 2014 (12.x) najdete v tématu Optimalizace plánů dotazů pomocí nástroje pro posouzení kardinality SQL Serveru 2014.

Další informace o CE naleznete v tématu Odhad kardinality (SQL Server).

Postup řešení

Změňte úroveň kompatibility databáze na zdrojová verze a postupujte podle doporučeného pracovního postupu upgradu, jak je znázorněno na následujícím obrázku:

Další informace o tomto článku naleznete v tématu Zachování stability výkonu během upgradu na novější SQL Server.

Citlivost na zašifrování parametrů

Platí pro: Migrace z cizí platformy (například Oracle, DB2, MySQL a Sybase) na SQL Server.

Poznámka:

Pokud tento problém existoval na zdrojovém SQL Serveru, migrace na novější verzi SQL Serveru as-is tento scénář nevyřeší.

SQL Server kompiluje plány dotazů na uložené procedury pomocí zašifrování vstupních parametrů při první kompilaci, generování parametrizovaného a opakovaně použitelného plánu optimalizovaného pro danou distribuci vstupních dat. I když nejsou uložené procedury, většina příkazů generující triviální plány je parametrizována. Po prvním uložení plánu do mezipaměti se jakékoli budoucí spuštění mapuje na dříve uložený plán v mezipaměti.

Potenciální problém nastane, když první kompilace nepoužívá nejběžnější sady parametrů pro běžnou úlohu. U různých parametrů se stejný plán provádění stane neefektivním. Další informace o tomto článku najdete v tématu Citlivost parametrů.

Postup řešení

1. Použijte nápovědu RECOMPILE . Plán se vypočítá pokaždé, když se přizpůsobí každé hodnotě parametru.

2. Přepište uloženou proceduru tak, aby používala možnost (OPTIMIZE FOR(<input parameter> = <value>)). Rozhodněte se, kterou hodnotu použít, která vyhovuje většině relevantních úloh, a vytvořte a udržujte jeden plán, který bude efektivní pro parametrizovanou hodnotu.

3. Přepište uloženou proceduru pomocí místní proměnné uvnitř procedury. Optimalizátor teď používá vektor hustoty pro odhady, což vede ke stejnému plánu bez ohledu na hodnotu parametru.

4. Přepište uloženou proceduru tak, aby používala možnost (OPTIMIZE FOR UNKNOWN). Stejný účinek jako použití techniky místní proměnné.

5. Přepište dotaz tak, aby používal nápovědu DISABLE_PARAMETER_SNIFFING. Stejný účinek jako použití techniky místních proměnných tím, že zcela zakáže zašifrování parametru, pokud OPTION(RECOMPILE)WITH RECOMPILE se nepoužívá nebo OPTIMIZE FOR <value> není použit.

Návod

Pomocí funkce Analýza plánu sady Management Studio můžete rychle zjistit, jestli se jedná o problém. Další informace najdete v tématu Nové v nástroji SSMS: Jednodušší řešení potíží s výkonem dotazů.

Chybějící indexy

Platí pro: Zahraniční platforma (například Oracle, DB2, MySQL a Sybase) a SQL Server na migraci SQL Serveru.

Nesprávné nebo chybějící indexy způsobují nadbytečné vstupně-výstupní operace, které vedou k nedostatku paměti a procesoru. Důvodem může být změna profilu úlohy, například použití různých predikátů a zneplatnění stávajícího návrhu indexu. Mezi důkazy o špatné strategii indexování nebo změnách profilu úloh patří:

• Vyhledejte duplicitní, redundantní, zřídka používané a zcela nepoužité indexy.
• Zvláštní péče o nepoužívané indexy s aktualizacemi

Postup řešení

1. Pro všechny chybějící odkazy na index použijte plán grafického spouštění.

2. Návrhy indexování generované poradcem pro ladění databázového stroje

3. Použijte sys.dm_db_missing_index_details.

4. Pomocí existujících skriptů, které můžou používat existující zobrazení dynamické správy, můžete získat přehled o všech chybějících, duplicitních, redundantních, zřídka používaných a zcela nepoužívaných indexech, ale také v případě, že se jakýkoli odkaz na index naznačuje nebo pevně zakóduje do existujících procedur a funkcí ve vaší databázi.

Návod

Mezi příklady takových existujících skriptů patří vytvoření indexu a informace o indexu.

Nemožnost používat predikáty k filtrování dat

Platí pro: Zahraniční platforma (například Oracle, DB2, MySQL a Sybase) a SQL Server na migraci SQL Serveru.

Poznámka:

Pokud tento problém existoval na zdrojovém SQL Serveru, migrace na novější verzi SQL Serveru as-is tento scénář nevyřeší.

Optimalizátor dotazů SQL Serveru může obsahovat pouze informace, které jsou známé v době kompilace. Pokud úloha spoléhá na predikáty, které je možné znát pouze v době provádění, zvyšuje se potenciál pro špatnou volbu plánu. V případě lepšího plánu kvality musí být predikáty SARGable.

Poznámka:

Termín SARGable v relačních databázích odkazuje na predikát Search ARGschopný predikát, který může použít index ke zrychlení provádění dotazu. Další informace najdete v průvodci návrhem a architekturou indexu SQL SQL a SQL.

Některé příklady predikátů, které se nedají sargable použít:

• Implicitní převody dat, jako je varchar na nvarchar nebo int na varchar. V plánech skutečného spuštění vyhledejte upozornění modulu runtime CONVERT_IMPLICIT . Převod z jednoho typu na jiný může také způsobit ztrátu přesnosti.

• Složité nedeterminované výrazy, například WHERE UnitPrice + 1 < 3.975, ale ne WHERE UnitPrice < 320 * 200 * 32.

• Výrazy používající funkce, jako WHERE ABS(ProductID) = 771 jsou nebo WHERE UPPER(LastName) = 'Smith'

• Řetězce s úvodním zástupným znakem, například WHERE LastName LIKE '%Smith', ale ne WHERE LastName LIKE 'Smith%'.

Postup řešení

1. Vždy deklarujte proměnné/parametry jako zamýšlené cílové datové typy.

   To může zahrnovat porovnání libovolného uživatelem definovaného konstruktoru kódu, který je uložený v databázi (například uložené procedury, uživatelem definované funkce nebo zobrazení) se systémovými tabulkami, které obsahují informace o datových typech používaných v podkladových tabulkách (například sys.columns).

2. Pokud nelze procházet veškerý kód k předchozímu bodu, změňte datový typ tabulky tak, aby odpovídal jakékoli deklaraci proměnné nebo parametru.

3. Zdůvodnění užitečnosti následujících konstruktorů:

   • Funkce používané jako predikáty;
   • Vyhledávání pomocí zástupných znaků;
   • Komplexní výrazy založené na sloupcových datech – vyhodnoťte nutnost vytvořit trvalé počítané sloupce, které je možné indexovat;

Poznámka:

Všechny tyto kroky je možné provádět programově.

Použití funkcí s hodnotami tabulky (více příkazů vs. vložené)

Platí pro: Zahraniční platforma (například Oracle, DB2, MySQL a Sybase) a SQL Server na migraci SQL Serveru.

Poznámka:

Pokud tento problém existoval na zdrojovém SQL Serveru, migrace na novější verzi SQL Serveru as-is tento scénář nevyřeší.

Funkce s hodnotami tabulky vrací datový typ tabulky, který může být alternativou k zobrazením. Zobrazení jsou sice omezená na jeden SELECT příkaz, ale uživatelem definované funkce můžou obsahovat další příkazy, které umožňují více logiky, než je v zobrazeních možné.

Vzhledem k tomu, že výstupní tabulka funkce s hodnotami tabulky s více příkazy (MSTVF) není vytvořena v době kompilace, sql Server Query Optimizer spoléhá na heuristické a ne skutečné statistiky k určení odhadů řádků.

I když se indexy přidají do základních tabulek, nepomůže to.

Pro MSTVFs sql Server používá pevný odhad 1 pro počet řádků, které má vrátit MSTVF (počínaje SQL Serverem 2014 (12.x), který pevný odhad představuje 100 řádků).

Postup řešení

1. Pokud je MSTVF pouze jeden příkaz, převeďte na vloženou funkci s hodnotou tabulky.

   CREATE FUNCTION dbo.tfnGetRecentAddress (@ID INT)
   RETURNS
       @tblAddress TABLE ([Address] VARCHAR (60) NOT NULL)
   AS
   BEGIN
       INSERT INTO @tblAddress ([Address])
       SELECT TOP 1 [AddressLine1]
       FROM [Person].[Address]
       WHERE AddressID = @ID
       ORDER BY [ModifiedDate] DESC;
       RETURN;
   END
   

   Zobrazí se další příklad vloženého formátu.

   CREATE FUNCTION dbo.tfnGetRecentAddress_inline
   (@ID INT)
   RETURNS TABLE
   AS
   RETURN
       (SELECT TOP 1 [AddressLine1] AS [Address]
        FROM [Person].[Address]
        WHERE AddressID = @ID
        ORDER BY [ModifiedDate] DESC)
   

2. Pokud je složitější, zvažte použití průběžných výsledků uložených v tabulkách Memory-Optimized nebo dočasných tabulkách.

Související obsah

• osvědčené postupy pro monitorování úloh pomocí úložiště dotazů
• Ukázková databáze pro In-Memory OLTP
• Uživatelem definované funkce
• Proměnné tabulky a odhady řádků – část 1
• Proměnné tabulky a odhady řádků – část 2
• Ukládání do mezipaměti a opětovné použití plánu provádění