Ahoj všichni! Nedávno jsem narazil na problém, kdy zpracování dokumentu trvalo dlouho.

Vstupní data: konfigurace „Vedení výrobního podniku, vydání 1.3 (1.3.52.1)“, dokument „Příchozí platební příkaz“. Stížnost: držení v pracovní databázi trvá 20-30 sekund, což je zajímavé, v kopii databáze je stejný dokument držen 2-4 sekundy. Přečtěte si o vyšetřováních a důvodech tohoto chování níže.

Takže s pomocí měření výkonu Myslím, že každý ví, jak to použít, viník byl nalezen:

V tomto případě byla na záznamníku zaznamenána prázdná sada záznamů, jinými slovy pohyby byly před provedením vymazány. Za zmínku stojí, že tento postup byl volán 26x, tzn. pro každý registr, do kterého mohl náš dokument zapisovat.

Podle měření výkonu trvala tato operace 13 sekund, když spočítáte průměr, dostanete 0,5 sekundy na registr, věčnost!

Jak všichni víme, nemůžeme optimalizovat nahrávání, ale tady je zjevně něco špatně.
Pro další analýzu otevřete SQL Server Profiler A . Pro analýzu jsem použil třídy událostí:

• Showplan Statistický profil
• Showplan XML Statistics Profile
• RPC dokončeno
• SQL:BatchCompleted.

V nastavení trasování je filtrovat podle SPID:

SPID je ID procesu databázového serveru. V případě 1C se v podstatě jedná o spojení mezi serverem 1C a DBMS, můžete si jej prohlédnout v administrační konzoli serverů 1C ve sloupci „Připojení k DBMS“.

Zobrazí se, pokud tento moment připojení k databázi je zachyceno relací: buď probíhá volání DBMS, nebo je otevřena transakce, nebo je držen objekt „Temporary Table Manager“, ve kterém byla vytvořena alespoň jedna dočasná tabulka.

Napíšeme zpracování pro držení SPID, bude obsahovat jednu proceduru:

Je důležité, aby držený objekt připojení, v našem případě dočasný správce tabulek, byl definován jako proměnná pro zpracování. Otevřeme zpracování, spustíme proceduru a dokud bude otevřeno, SPID bude opraveno. Otevřete konzolu pro správu serveru 1C:

Takže, SPID bylo přijato, zadáme jeho hodnotu do filtru a získáme trasování z aktuální pracovní databáze pro naši relaci. Při analýze trasování byla nalezena operace, jejíž dokončení trvalo 11 sekund:

Co mě také zaujalo, byl počet přečtení - 1872578 , ale hned jsem tomu nepřikládal žádnou důležitost a začal jsem zjišťovat, co se tady dělá a s kterým stolem.

exec sp_executesql <= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))’,N’@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)’,0x8A2F00155DBF491211E87F56DD1A416E,’4018-05-31 23:59:59′,0x00000000000000000000000000000000,’4018-05-31 23:59:59′,0x9A95A0369F30F8DB11E46684B4F0A05F,’4018-05-31 23:59:59"

Jak můžete vidět z SQL dotazu, tabulka je zpracována "AccRg1465" Jedná se o tabulku v samonosném účetním registru. Textová reprezentace plánu provádění dotazu:

Jak můžete vidět z prováděcího plánu SQL dotazu, nic špatného se neděje, tabulka „ AccRg1465", clusterové indexové vyhledávání se používá všude.

Také jsem neviděl nic špatného na grafickém plánu, i když se mi zdál příliš nabubřelý - došlo ke sloučení a dvě vnořené smyčky bez zjevného důvodu. Odkud pochází tento počet přečtení a obrovská doba provádění?

Jak bylo uvedeno výše, problém nebyl reprodukován v nové kopii databáze; kopie byla odebrána z pracovní databáze poté, co se v ní problém objevil, takže bylo rozhodnuto analyzovat její chování v SQL Server Profiler na stejném dokumentu.
Zde jsou výsledky:

Text dotazu v SQL:

EXEC sp_executesql N" SELECT TOP 1 0x01 FROM dbo._AccRg1465 T1 WHERE (T1._RecorderTRef = 0x0000022D AND T1._RecorderRRef = @P1) AND (((T1._Period<= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))" , N"@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)“, 0x8A2F00155DBF491211E87F56DD1A416E, "4018-05-31 23:59:59" ,00, "4018-05-31 23:59:59" 0x9A63A08E116F, 0x9A63A08ED46F4 01 8-05-31 23:59:59"

Grafické znázornění plánu dotazů:

Texty požadavků jsou stejné, prováděcí plány jsou radikálně odlišné. Co by se mohlo stát? Mýlil jsem se o statistikách v SQL, ale mezi pracovní a kopií databáze jsou stejné a statistiky jsou uloženy v databázi pro každou tabulku:

Pojďme dále analyzovat: pokud jsou statistiky stejné, ale plány dotazů se liší, znamená to, že optimalizátor nepřistupuje ke statistikám pro vytvoření plánu dotazů, ale má plán uložený v mezipaměti, který používá. Vymažeme mezipaměť procedur v naší databázi, k tomu použijeme příkaz

DBCC FLUSHPROCINDB(< database_id >)

Kde< database_id >je identifikátor databáze. Chcete-li zjistit ID databáze, musíte spustit skript

vyberte jméno, id_databáze ze sys . databází

vrátí nám seznam databází a jejich identifikátorů.

Znovu dostaneme stopu:

Textová reprezentace plánu dotazů:

Grafické znázornění plánu dotazů:

Jak vidíte, optimalizátor znovu získal plán dotazů a starý mezipaměti nebyl použit, doba provádění se vrátila do normálu, stejně jako počet čtení. Není jasné, co to způsobilo, možná velký počet výměn nebo uzavření předchozích období, těžko říct. Byla nakonfigurována pravidelná údržba databáze. Toto je poprvé, co jsem narazil na podvod s plánem provádění dotazů v mezipaměti.

Děkuji za pozornost!

Pomohl vám tento článek?

6 odpovědí

Existuje několik způsobů, jak získat plán provádění, který bude záviset na vašich okolnostech. Obvykle můžete k získání plánu použít SQL Server Management Studio, pokud však z nějakého důvodu nemůžete spustit dotaz v SQL Server Management Studio, může být užitečné získat plán prostřednictvím SQL Server Profiler nebo kontrolou plánu. mezipaměti.

Metoda 1 – pomocí SQL Server Management Studio

SQL Server má několik úhledných funkcí, které usnadňují shromažďování plánu provádění, jen se ujistěte, že je zaškrtnutá položka nabídky „Zahrnout skutečný plán provádění“ (nachází se v nabídce Dotaz) a poběží váš jako normálně.

Pokud se pokoušíte získat plán provádění příkazů v uložené proceduře, provedli byste uloženou proceduru takto:

Exec p_Příklad 42

Po dokončení dotazu se v podokně výsledků zobrazí další karta Plán provádění. Pokud jste provedli mnoho schválení, může se na této kartě zobrazit mnoho plánů.

Zde můžete zkontrolovat plán provádění v SQL Server Management Studio nebo kliknout pravým tlačítkem na plán a vybrat "Uložit plán provedení jako..." pro uložení plánu do souboru XML.

Metoda 2 – pomocí možností SHOWPLAN

Tato metoda je velmi podobná metodě 1 (to je ve skutečnosti to, co SQL Server Management Studio provádí interně), nicméně jsem ji zahrnul pro úplnost nebo pokud nemáte SQL Server Management Studio k dispozici.

Před provedením dotazu spusťte jeden následující operátoři. Výpis musí být jediným výpisem v balíku, tzn. Nemůžete současně provést jiný příkaz:

SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFIL ON SET STATISTICS XML ON -- Doporučená možnost použití

Toto jsou parametry připojení, takže toto musíte spustit pouze jednou pro každé připojení. Od této chvíle budou všechna spuštěná prohlášení doplněna další sadu výsledků obsahující váš prováděcí plán v požadovaném formátu – stačí spustit dotaz jako obvykle, abyste plán viděli.

Jakmile budete hotovi, můžete tuto možnost zakázat následujícím příkazem:

SOUBOR<> VYP

Porovnání formátů prováděcích plánů

Pokud máte silné preference, doporučuji použít možnost STATISTICS XML. Tato možnost je ekvivalentní možnosti "Zahrnout skutečný plán provádění" v SQL Server Management Studio a poskytuje nejvíce informací v nejužitečnějším formátu.

• SHOWPLAN_TEXT – zobrazí základní textový odhadovaný plán provádění bez provedení dotazu
• SHOWPLAN_ALL – zobrazí odhadovaný textový plán provádění s odhadem nákladů bez provedení dotazu
• SHOWPLAN_XML – Zobrazuje odhadovaný plán provádění na základě XML s odhady nákladů bez provedení dotazu. To je ekvivalentní možnosti "Zobrazit příklad provedení plánu..." v SQL Server Management Studio.
• STATISTICKÝ PROFIL - Provede dotaz a zobrazí skutečný plán provádění na základě textu.
• STATISTICS XML - Provede dotaz a zobrazí skutečný plán provádění založený na XML. Toto je ekvivalentní možnost "Zahrnout skutečný plán provádění" v SQL Server Management Studio.

Metoda 3 - pomocí SQL Server Profiler

Pokud nemůžete dotaz spustit přímo (nebo váš dotaz neběží pomalu, když jej spouštíte přímo – nezapomeňte, že chceme, aby plán dotazů běžel špatně), můžete plán zachytit pomocí SQL Server Profiler. Cílem je spustit dotaz, zatímco běží trasování, které zachycuje jednu z událostí „Showplan“.

Vezměte prosím na vědomí, že v závislosti na zatížení vás můžeš použijte tuto metodu v produkčním prostředí, ale měli byste být samozřejmě opatrní. Mechanismy profilování SQL Serveru jsou navrženy tak, aby minimalizovaly dopad na databázi, ale to neznamená, že nebude mít vliv na výkon. Můžete mít také potíže s filtrováním a určením správného plánu ve vašem trasování, pokud je vaše databáze velmi využívána. Samozřejmě byste se měli poradit se svým DBA, abyste se ujistili, že jsou spokojeni s tím, že to děláte pro vaši vzácnou databázi!

• Otevřete SQL Server Profiler a vytvořte nové trasování připojující se k požadované databázi, ze které chcete trasování zaznamenat.
• Na kartě Výběr událostí zaškrtněte políčko Zobrazit všechny události, zaškrtněte řádek Výkon -> Showplan XML a spusťte trasování.
• Zatímco je trasování spuštěno, udělejte vše, co potřebujete, aby se pomalý dotaz spustil.
• Počkejte, dokud nebude požadavek dokončen a trasování se zastaví.
• Chcete-li uložit trasování, klikněte pravým tlačítkem myši na plán xml v profilu serveru SQL a vyberte možnost „Extrahovat data události...“ pro uložení plánu do souboru XML.

Plán, který obdržíte, je ekvivalentem možnosti "Zahrnout skutečný plán provedení" v SQL Server Management Studio.

Metoda 4 - Kontrola mezipaměti dotazů

Pokud nemůžete spustit dotaz přímo a také nemůžete zachytit trasování profileru, měli byste být stále schopni získat odhadovaný plán kontrolou plánu mezipaměti dotazu SQL.

Zkontrolujeme mezipaměť plánu dotazem na SQL Server DMV. Níže je uveden základní dotaz, který zobrazí seznam všech plánů dotazů uložených v mezipaměti (jako xml) spolu s jejich textem SQL. Ve většině databází budete také muset přidat další podmínky filtru, abyste filtrovali výsledky podle plánů, které vás zajímají.

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Spusťte tento dotaz a kliknutím na plán XML otevřete plán v novém okně - klikněte pravým tlačítkem a vyberte "Uložit plán provádění jako..." pro uložení plánu do souboru ve formátu XML.

Poznámky:

Protože existuje tolik faktorů (od schématu tabulky a indexu po uložená data a statistiky tabulek), musíte Vždy pokuste se získat plán provádění z databáze, která vás zajímá (obvykle z té, která má problém s výkonem).

Pro zašifrované uložené procedury nemůžete potvrdit plán provádění.

„skutečné“ a „odhadované“ plány provádění

Skutečný plán provádění je ten, kde SQL Server skutečně provádí dotaz, zatímco odhadovaný plán provádění SQL Server pracuje na tom, co by mohl udělat bez provedení dotazu. Ačkoli je to logicky ekvivalentní, skutečný plán provádění je mnohem užitečnější, protože obsahuje další data a statistiky o tom, co se vlastně stalo, když byl dotaz proveden. To je důležité při diagnostice problémů, když je vyhodnocování SQL serveru zakázáno (například když jsou statistiky zastaralé).

Jak interpretovat plán provádění dotazu?

Toto je téma, které si zaslouží knihu zdarma.

Kromě komplexní odpovědi, která již byla někdy zveřejněna, je užitečné mít programový přístup k prováděcímu plánu a extrahovat informace. Ukázkový kód pro to je níže.

DECLARE @TraceID INT EXEC StartCapture @@SPID, @TraceID OUTPUT EXEC sp_help "sys.objects" /*<-- Call your stored proc of interest here.*/ EXEC StopCapture @TraceID

Můj oblíbený nástroj pro získávání a hloubkovou analýzu plánů provádění dotazů je SQL Sentry Plan Explorer. Je mnohem pohodlnější, uživatelsky přívětivější a kompletnější pro detailní analýzu a vizualizaci realizačních plánů než SSMS.

Zde je příklad obrazovky, abyste pochopili, jaké funkce nástroj nabízí:

Toto je pouze jeden z pohledů dostupných v nástroji. Všimněte si sady záložek ve spodní části okna aplikace, které vám umožňují získat různé typy zobrazení plánu provádění a užitečné doplňkové informace.

Navíc jsem si v jeho bezplatné verzi nevšiml žádných omezení, která by vám bránila v každodenním používání nebo vás nakonec nutila koupit verzi Pro. Pokud tedy chcete zůstat u bezplatné verze, nic není zakázáno.

Kromě metod popsaných v předchozích odpovědích můžete také použít bezplatný prohlížeč prováděcích plánů a nástroj pro optimalizaci dotazů ApexSQL Plan (na který jsem nedávno narazil).

Plán ApexSQL můžete nainstalovat a integrovat do SQL Server Management Studio, takže plány provádění lze přímo prohlížet z SSMS.

Zobrazit předpokládané plány provádění v plánu ApexSQL

• Klepněte na tlačítko Nová žádost v SSMS a vložte text dotazu do textového pole dotazu. Klikněte pravým tlačítkem myši a z kontextové nabídky vyberte "Zobrazit vzorový plán provádění".

1. Diagram prováděcího plánu zobrazí záložku Plánování provedení v sekci výsledků. Poté klikněte pravým tlačítkem myši na plán provádění a z kontextové nabídky vyberte možnost „Otevřít v plánu ApexSQL“.

1. Odhadovaný plán provádění se otevře v plánu ApexSQL a lze jej analyzovat za účelem optimalizace dotazů.

Zobrazení skutečných plánů provádění v plánu ApexSQL

Chcete-li zobrazit skutečný plán provádění dotazů, přejděte k druhému kroku uvedenému dříve, ale nyní, jakmile se zobrazí odhadovaný plán, klikněte na tlačítko „Skutečný“ na hlavním pásu karet v plánu ApexSQL.

Po kliknutí na tlačítko Skutečný se zobrazí skutečný plán realizace s podrobným náhledem parametrů nákladů spolu s dalšími údaji plánu realizace.

Další informace o zobrazení plánů provádění naleznete na tomto odkazu.

Plány dotazů lze získat z relace rozšířených událostí prostřednictvím události query_post_execution_showplan. Zde je příklad relace XEvent:

/* Vygenerováno pomocí šablony "Sledování podrobností dotazu". */ VYTVOŘIT RELACI UDÁLOSTI NA SERVERU PŘIDAT UDÁLOST sqlserver.query_post_execution_showplan(AKCE(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sql_text,sql.server.sql ,sqlserver.tsql_stack)), / * Podle potřeby odstraňte kteroukoli z následujících událostí (nebo přidejte další události). */ PŘIDAT UDÁLOST sqlserver.error_reported(AKCE(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver,query_plan_hash.sesql.text_serversqsql.text_sql. sql_frame,sqlserver.tsql_stack ) WHERE (.(.,(4)) AND .(.,(0)))), PŘIDAT UDÁLOST sqlserver.module_end(SET collect_statement=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver . plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4))) AND 0. sqlserver.rpc_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sql_text,sqlserver.session_sql,sqlserver.session_sql,sqlserver.session. qlserver.tsql_stack) WHERE (. ( .,(4)) AND .(.,(0))), PŘIDAT UDÁLOST sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_server_handle query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0))), sqcompleted_server. AKCE(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver,sql_server_text,sql_server_text,sql_server_text.sql_server.text ) KDE (.(.,( 4)) a. (., (0)))), Přidejte událost sqlserver.sql_statement_completed (Action (Packet0.event_sequence, sqlserver.client_app_name, sqlserver.database_id, sqlserver.plan_handle_handle_handle_handle, sqhash, sqhash, sqhash, sqhash, sqhash. .session_id , sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0))) PŘIDAT CÍLOVÝ balíček0.ring_buffer S (MAX_MEMORY=4096 KB, EVENT_PRODEJ_ZADÁNÍ_EVENTS_REV. L ATENCY =30 SECONDS,MAX_EVENT_SIZE=0 kB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) JÍT

Jakmile je relace vytvořena (v SSMS), přejděte do Prohlížeče objektů a přejděte na Spravovat | Rozšířené akce | Relace. Klikněte pravým tlačítkem na relaci "GetExecutionPlan" a spusťte ji. Klikněte na něj pravým tlačítkem a vyberte „Sledovat živá data“.

Poté otevřete nové okno dotazu a spusťte jeden nebo více dotazů. Zde je jeden pro AdventureWorks:

POUŽÍVEJTE AdventureWorks; GO SELECT p.Name AS ProductName, NonDiscountSales = (Množství objednávky * Jednotková cena), Slevy = ((Množství objednávky * JednotkováCena) * JednotkováCenaSleva) Z Production.Product AS p VNITŘNÍ PŘIPOJENÍ Prodej.ProdejDetailOrder AS sod NA p.ProductID = drn.ProductID ORDER Název produktu DESC; JÍT

Po minutě nebo dvou uvidíte na kartě „GetExecutionPlan: Live Data“ nějaké výsledky. Vyberte jednu z událostí query_post_execution_showplan v mřížce a potom klikněte na kartu Query Plan pod mřížkou. Mělo by to vypadat nějak takto:

UPRAVIT: Kód XEvent a snímek obrazovky byly vygenerovány z SQL/SSMS 2012 w/SP2. Pokud používáte SQL 2008/R2, můžete nastavit skript pro jeho spuštění. Tato verze však nemá GUI, takže budete muset extrahovat soubor XML showplan, uložit jej jako soubor *.sqlplan a otevřít jej v SSMS. Je to těžkopádné. XEvents v SQL 2005 nebo dřívějších neexistovaly. Pokud tedy nepoužíváte SQL 2012 nebo novější, velmi doporučuji jednu z dalších odpovědí zde zveřejněných.

podíl

Optimalizace dotazů v SQL Server 2005, statistika databáze SQL Server 2005, VYTVOŘENÍ STATISTIKY, AKTUALIZACE STATISTIKY, SET NOCOUNT ON, plány provádění dotazů, počet logických čtení, tipy optimalizátoru, MAXDOP, OPTIMIZE FOR, plány provádění výukových programů (průvodce plánem), sp_create_plan_guide

Pokud již byly vyčerpány všechny ostatní metody optimalizace výkonu, pak mají vývojáři a správci SQL Serveru k dispozici poslední rezervu – optimalizaci provádění jednotlivých dotazů. Pokud například váš úkol bezpodmínečně vyžaduje urychlení vytvoření jedné konkrétní sestavy, můžete analyzovat dotaz, který se používá k vytvoření této sestavy, a pokusit se změnit jeho plán, pokud není optimální.

Mnoho specialistů má k optimalizaci dotazů nejednoznačný postoj. Na jedné straně provoz softwarového modulu Query Optimizer, který generuje plány provádění dotazů, způsobuje mnoho spravedlivých kritik v SQL Server 2000 i SQL Server 2005. Query Optimizer často nevybírá nejoptimálnější plány provádění dotazů a v některých situacích ztrácí na podobné moduly od Oracle a Informix. Na druhou stranu je ruční optimalizace dotazů proces extrémně náročný na práci. Takovou optimalizací můžete strávit spoustu času a nakonec zjistíte, že nebylo optimalizováno nic: plán původně navržený Query Optimizerem se ukázal jako nejoptimálnější (to se stává ve většině případů). Kromě toho se může stát, že vámi ručně vytvořený plán provádění dotazů se po nějaké době (po přidání nových informací do databáze) ukáže jako neoptimální a sníží výkon při provádění dotazů.

Všimněte si také, že Optimalizátor dotazů vyžaduje správné statistické informace k výběru nejlepších plánů dotazů. Vzhledem k tomu, že podle zkušeností autora ne všichni správci vědí, co to je, řekneme vám o statistikách více.

Statistika- jedná se o speciální servisní informace o distribuci dat ve sloupcích tabulky. Představme si například, že se provádí dotaz, který by měl vrátit všechny Ivanovy žijící ve městě Petrohrad. Předpokládejme, že 90 % záznamů v této tabulce má ve sloupci stejnou hodnotu Město - "Petrohrad". Samozřejmě z hlediska provádění dotazu je nejprve výhodnější vybrat v tabulce všechny Ivanovy (nebudou jich samozřejmě 90%) a poté zkontrolovat hodnotu sloupce Město pro každý vybraný záznam. Chcete-li však zjistit, jak jsou distribuovány hodnoty ve sloupci, musíte nejprve spustit dotaz. SQL Server proto nezávisle iniciuje provádění takových dotazů a poté ukládá informace o distribuci dat (která se nazývá statistika) v tabulkách databázových služeb.

Pro databáze SQL Server 2005 jsou výchozí nastavení AUTO_CREATE_STATISTICS A AUTO_UPDATE_STATISTICS. V tomto případě budou statistiky pro sloupce databáze vytvořeny a aktualizovány automaticky. U největších a nejdůležitějších databází se může stát, že operace vytváření a aktualizace statistik mohou narušovat současnou uživatelskou zkušenost. Proto jsou u takových databází někdy tyto parametry zakázány a operace vytváření a aktualizace statistik se provádějí ručně v noci. K tomu slouží příkazy VYTVOŘTE STATISTIKY A AKTUALIZOVAT STATISTIKY.

Nyní pojďme mluvit o optimalizaci dotazů.

První věc, kterou musíte udělat, je najít ty dotazy, které primárně podléhají optimalizaci. Nejjednodušší způsob, jak to udělat, je pomocí profilovače nastavením filtru na dobu trvání požadavku (filtr Doba trvání v okně UpravitFiltr(Upravit filtr), který lze otevřít pomocí tlačítka SloupecFiltry na kartě UdálostiVýběr okno vlastností trasovací relace). Kandidáti na optimalizaci mohou například zahrnovat dotazy, jejichž doba provedení byla delší než 5 sekund. Můžete také použít informace o dotazu poskytnuté Poradcem pro ladění databáze.

Dále musíte zkontrolovat, zda je parametr nastaven pro vaše připojení, uložené procedury a funkce NEPOČET. Můžete jej nainstalovat pomocí příkazu SET NOCOUNT ON. Při nastavování tohoto parametru se nejprve zakáže návrat ze serveru a zobrazení informace o počtu řádků ve výsledcích dotazu (tj. "N ovlivněných řádků" na kartě Zprávy(C zprávy) okna pro práci s kódem při provádění požadavku v Management Studio). Za druhé, přenos speciální zprávy serveru je zakázán DONE_IN_PROC, která se standardně vrací pro každý krok uložené procedury. Při volání většiny uložených procedur potřebujete pouze výsledek jejich provedení a nikoho nezajímá počet zpracovaných řádků pro jednotlivé fáze. Proto nastavení parametru NEPOČET protože uložené procedury mohou výrazně zlepšit jejich výkon. Rychlost provádění běžných dotazů se také zvyšuje, ale v menší míře (až o 10 %).

Poté můžete začít pracovat s plány provádění dotazů.

Nejjednodušší způsob, jak zobrazit plán provádění dotazů, je z SQL Server Management Studio. Chcete-li získat informace o očekávaném plánu provádění dotazu, můžete použít nabídku Dotaz(Dotaz) vyberte příkaz ZobrazitOdhadovanýProvedeníPlán(Zobrazte očekávaný plán provedení). Pokud chcete znát skutečný plán provádění dotazu, můžete před jeho provedením nastavit parametr ve stejné nabídce ZahrnoutAktuálníProvedeníPlán(Zahrňte skutečný plán provedení). V tomto případě se po provedení dotazu v okně výsledků v SQL Server Management Studio objeví další karta ProvedeníPlán(Plán provádění), který zobrazí skutečný plán provádění dotazu. Když najedete myší na některou z fází, můžete o ní získat další informace (obr. 11.15).

Rýže. 11.15. Plán provádění dotazů v SQL Server Management Studio

V plánu provádění dotazů, jak můžete vidět na obrázku, může být mnoho prvků. Porozumět jim, stejně jako navrhnout jiný plán provádění, je poměrně obtížný úkol. Je třeba říci, že každý z možných prvků je ve své situaci optimální. Fáze optimalizace dotazu proto obvykle vypadají takto:

q Nejprve v okně Management Studio spusťte příkaz NASTAVTE STATISTIKY IO ZAPNUTO. V důsledku toho se po každém provedení požadavku zobrazí další informace. V něm nás zajímá hodnota pouze jednoho parametru - Logické čtení. Tento parametr znamená počet logických čtení při provádění dotazů, tedy kolik operací čtení muselo být provedeno při provádění daného dotazu bez zohlednění vlivu mezipaměti (počet čtení z mezipaměti i disku). Toto je nejdůležitější parametr. Počet fyzických čtení (čtení pouze z disku) není příliš reprezentativní údaj, protože závisí na tom, zda byly k těmto tabulkám dříve přístupy nebo ne. Časové statistiky jsou také proměnlivé a závisí na dalších operacích, které server v té době provádí. Ale počet logických čtení je nejobjektivnějším ukazatelem, který je nejméně ovlivněn dalšími faktory;

q pak zkuste změnit plán provádění dotazu a zjistěte celkový počet logických čtení pro každý plán. Obvykle se plán provádění dotazu mění pomocí tipů optimalizátoru. Explicitně sdělují optimalizátoru, který plán provádění má použít.

SQL Server 2005 má mnoho tipů pro optimalizaci. Informace o nich si můžete přečíst v Books Online (v seznamu na záložce Index(Index) musí být vybráno DotazRady [SQLserver](Nápovědy k dotazu), PřipojitRady(Připojit tipy) nebo StůlRady [SQLserver](Nápovědy k tabulce)). Nejčastěji používané rady jsou:

q NOLOCK, VIDLICE NA VESLO, PAGLOCK, TABLOCK, HOLDLOCK, READCOMMITTEDLOCK, UPDLOCK, XLOCK- tyto rady se používají ke správě zámků (viz část 11.5.7);

q RYCHLE počet řádků - bude vybrán plán provádění dotazu, ve kterém se co nejrychleji zobrazí zadaný počet řádků (první od začátku sady záznamů). Pokud uživatel potřebuje přesně první záznamy (například nejnovější objednávky), pak lze pomocí této nápovědy je co nejrychleji načíst do okna aplikace;

q VYNUCENÝ ROZKAZ- spojování tabulek při provádění dotazu bude provedeno přesně v pořadí, v jakém jsou tyto tabulky uvedeny v dotazu;

q MAXDOP(z Maximum Degree of Parallelism - maximální stupeň paralelizace požadavku) - pomocí této nápovědy je indikován maximální počet procesorů, které lze použít k provedení požadavku. Obvykle se tato nápověda používá ve dvou situacích:

· když kvůli přepínání mezi procesory ( kontextpřepínání) je výrazně snížena rychlost provádění dotazu. Toto chování bylo typické pro SQL Server 2000 na víceprocesorových systémech;

· když chcete, aby nějaký náročný požadavek měl minimální dopad na aktuální uživatelskou zkušenost;

q OPTIMALIZOVAT PRO- tato nápověda vám umožňuje určit, že požadavek je optimalizován pro konkrétní hodnotu parametru, který je mu předán (například pro hodnotu filtru pro KDE);

q POUŽÍVEJTE PLÁN- to je nejsilnější příležitost. Pomocí takové nápovědy můžete explicitně definovat plán provádění dotazu předáním plánu jako řetězcové hodnoty ve formátu XML. Náznak POUŽÍVEJTE PLÁN se objevil pouze v SQL Server 2005 (v předchozích verzích bylo možné explicitně definovat plány provádění dotazů, ale to bylo provedeno jinými prostředky). Plán ve formátu XML lze napsat ručně, nebo jej lze vygenerovat automaticky (např. kliknutím pravým tlačítkem myši na grafickou obrazovku s plánem realizace znázorněným na obr. 11.15 a výběrem příkazu v kontextové nabídce UložitProvedeníPlánTak jako(Uložit plán provádění jako)).

SQL Server 2005 zavádí důležitou novou funkci, která vám umožňuje ručně změnit plán provádění dotazu, aniž byste museli zasahovat do textu dotazu. Často se stává, že kód požadavku nelze změnit: je pevně zapojen do kódu kompilované aplikace. Pro boj s tímto problémem zavedl SQL Server 2005 uloženou proceduru sp_create_plan_guide. Umožňuje vytvářet tzv Příručky prováděcího plánu (plánprůvodci), který bude automaticky použit na odpovídající dotazy.

Pokud analyzujete dotazy odeslané do databáze aplikací, má smysl nejprve věnovat pozornost následujícím bodům:

q jak často se operace objevuje v plánech provádění dotazů StůlSkenovat(Úplný sken tabulky). Může se dobře ukázat, že přístup k tabulce pomocí indexů bude efektivnější;

q zda jsou v kódu použity kurzory. Kurzory jsou velmi jednoduché z hlediska syntaxe programu, ale extrémně neefektivní z hlediska výkonu. Velmi často se můžete vyhnout používání kurzorů pomocí jiných syntaktických konstrukcí a získat velký nárůst rychlosti;

q zda kód používá dočasné tabulky nebo datový typ Stůl. Vytváření dočasných tabulek a práce s nimi vyžaduje spoustu zdrojů, takže byste se jim měli pokud možno vyhnout;

q Kromě vytváření dočasných tabulek vyžaduje změna jejich struktury také značnou spotřebu systémových prostředků. Proto by příkazy pro změnu struktury dočasných tabulek měly okamžitě upoutat vaši pozornost. Obvykle je možné okamžitě vytvořit dočasnou tabulku se všemi potřebnými sloupci;

q někdy dotazy vrátí více dat, než aplikace skutečně potřebuje (nadbytečné sloupce nebo řádky). To samozřejmě nezlepší produktivitu;

q pokud aplikace odesílá příkazy serveru VYKONAT, pak má smysl přemýšlet o jejich nahrazení voláním uložené procedury sp_executesql. Oproti běžnému příkazu má výkonnostní výhody VYKONAT;

q Zlepšení výkonu lze někdy dosáhnout odstraněním potřeby znovu kompilovat uložené procedury a vytvářet nové plány provádění dotazů. Je třeba věnovat pozornost použití parametrů, snažit se nesměšovat příkazy DML a DDL v kódu uložené procedury a ujistit se, že parametry připojení NASTAVIT ANSI_DEFAULTS, NASTAVIT ANSI_NULLS, NASTAVIT ANSI_PADDING, NASTAVTE ANSI_WARNINGS A SET CONCAT_NULL_YIELDS_NULL se mezi požadavky nezměnily (jakákoli změna takových parametrů ruší staré prováděcí plány). Problém může obvykle nastat, když jsou tyto parametry nastaveny na úrovni jednotlivých požadavků nebo v kódu uložené procedury.

Uvědomte si, že v každém případě je ruční vytváření plánů provádění dotazů a používání rad až poslední možností a je třeba se mu pokud možno vyhnout.

plán provádění SQL dotazu, nebo plán dotazů, je posloupnost kroků nebo instrukcí DBMS potřebných k provedení dotazu SQL. V každém kroku operace, která zahájila tento krok provádění dotazu SQL, načte řádky dat, které mohou tvořit konečný výsledek nebo mohou být použity pro další zpracování. Pokyny plánu provádění dotazů SQL jsou reprezentovány jako sekvence operací, které jsou PROVÁDĚNY příkazy DBMS FOR SQL SELECT, INSERT, smazat a Aktualizace. Obsah plánu dotazů je obvykle reprezentován ve stromové struktuře a zahrnuje následující informace:

• pořadí připojení zdrojů dat (tabulky, pohledy atd.);
• přístupová metoda pro každý zdroj dat;
• metody pro připojení zdrojů dat;
• operace omezující výběr, třídění a agregaci dat;
• náklady a závažnost každé operace;
• možné využití dělení a paralelismu. Informace poskytované plánem provádění dotazů SQL umožňují vývojáři zjistit, jaké přístupy a metody optimalizátor volí k provádění operací SQL.

Interpretace plánu SQL Query Execution Plan

Vizualizace plánu provádění SQL dotazu závisí na nástrojích a vývojových nástrojích, které mohou být buď součástí DBMS, jejichž dotaz je zajímavý pro analýzu, nebo mohou být samostatnými komerčními nebo volně distribuovanými softwarovými produkty, které přímo nesouvisejí s konkrétním DBMS. výrobce. Použití jednoho nebo druhého nástroje pro vizualizaci plánu dotazů obvykle významně neovlivní vnímání toho, co prezentovaný plán dotazů popisuje. Určujícím faktorem v procesu analýzy, kterou cestou se optimalizátor vydá při provádění konkrétního dotazu, je schopnost správně interpretovat informace uvedené v plánu dotazů.

Jak již bylo zmíněno, plán dotazů SQL má stromovou strukturu, která popisuje nejen posloupnost provádění operací SQL, ale také vztahy mezi těmito operacemi. Každý uzel ve stromu plánu dotazů je operace, jako je řazení nebo metoda přístupu k tabulce. Mezi uzly existuje vztah rodič-dítě. Vztahy mezi rodiči a dětmi se řídí následujícími pravidly:

• rodič může mít jedno nebo více dětí;
• dítě má pouze jednoho rodiče;
• operace, která nemá žádnou nadřazenou operaci, je vrcholem stromu;
• V závislosti na metodě vizualizace plánu dotazů SQL je potomek umístěn s určitým odsazením vzhledem k nadřazenému prvku. Potomci jednoho rodiče se nacházejí ve stejné vzdálenosti od svého rodiče.

Podívejme se blíže na informace, které poskytuje plán provádění SQL dotazů. Uvedené příklady byly provedeny v prostředí Oracle DBMS. Oracle SQL Developer byl použit jako nástroj pro provádění dotazů a vizualizaci plánu dotazů SQL. Fragment plánu dotazů SQL je znázorněn na Obr. 10.11.

I Id I Operation

• 0RDER_ITEMS

PR0DUCT_INF0RMATI0N_PK INFORMACE O PRODUKTU

SELECT STATEMENT Třídit POŘADÍ PODLE VNOŘENÝCH SMYČEK VNOŘENÉ SMYČKY PŘÍSTUP K TABULCE PLNÝ INDEX UNIKÁTNÍ PŘÍSTUP K TABULCE SKENOVÁNÍ PODLE INDEXU ROWID

Rýže. 10.11. Fragment plánu provádění SQL dotazu v prostředí Oracle DBMS

Pomocí relačních pravidel operací plánu dotazů můžeme definovat jejich následující formální popis.

Operace 0 je kořen stromu plánu dotazů. Kořen má jednoho potomka: operace 1.

Operace 1 - operace má jedno dítě: operace 2.

Operace 2 - operace má dvě děti: operaci 3 a operaci 6.

Operace 3 - operace má dvě děti: operaci 4 a operaci 5.

Operace 4 - operace nemá děti.

Operace 5 - operace nemá děti.

Operace 6 - operace nemá děti.

Interakce rodič-dítě mezi operacemi plánu dotazů je znázorněna na Obr. 10.12.

Operace prováděné v plánu dotazů lze rozdělit do tří typů: samostatné, nevázané operace spojení a operace spojeného spojení (obrázek 10.13).

Autonomní		Operace nesouvisející		Související operace
operace		sdružení		sdružení

Rýže. 10.12.

Rýže. 10.13.

Autonomní operace - Jedná se o operace, které mají nejvýše jednu podřízenou operaci.

Následující pravidla, podle kterých se provádějí autonomní operace, lze formulovat následovně.

• 2. Každá podřízená operace se provede pouze jednou.
• 3. Každá podřízená operace vrátí svůj výsledek nadřazené operaci.

Na Obr. Obrázek 10.14 ukazuje plán pro následující dotaz:

SELECT o.order_id ,o.order_status FROM orders o ORDER BY o.order_status

Tento dotaz obsahuje pouze samostatné operace.

S přihlédnutím k pravidlům pro sledování autonomních operací bude pořadí jejich provádění následující.

• 1. V souladu s pravidlem následujících autonomních operací č. 1 bude nejdříve provedena operace s ID = 2. Všechny řádky tabulky příkazů jsou načítány postupně.
• 2. Dále se provede operace s ID = 1. Řádky vrácené operací s ID = 2 jsou seřazeny podle podmínek třídicí klauzule ORDER BY.
• 3. Provede se operace s ID = 0. Vrátí se výsledná datová sada.

Unbound Union Operations

Unbound Union Operations jsou operace, které mají více než jednu nezávisle prováděnou podřízenou operaci. Příklad: HASH JOIN, MERGE JOIN, INTERSECTION, MINUS, UNION ALL.

Následující pravidla, podle kterých fungují operace nevázaného spojení, lze formulovat následovně.

• 1. Podřízená operace se provede před nadřazenou operací.
• 2. Podřízené operace se provádějí postupně, počínaje nejmenší hodnotou ID operace ve vzestupném pořadí těchto hodnot.
• 3. Před zahájením každé následující podřízené operace musí být aktuální operace úplně dokončena.
• 4. Každá podřízená operace se provede pouze jednou, bez ohledu na další podřízené operace.
• 5. Každá podřízená operace vrátí svůj výsledek nadřazené operaci.

Na Obr. Obrázek 10.15 ukazuje plán pro následující dotaz:

VYBERTE o.order_id z objednávek o UNION ALL

SELECT oi.order_id z order_items oi

Tento dotaz obsahuje operaci nevázaného spojení UNION vše. Zbývající dvě operace jsou autonomní.

Rýže. 10.15. Nevázané operace spojení, plán dotazů

	1 VYBERTE PROHLÁŠENÍ I

S ohledem na pravidla pro sledování nesouvisejících operací spojení bude pořadí jejich provádění následující.

• 1. V souladu s pravidly 1 a 2 následujících nesouvisejících operací spojení bude nejprve provedena operace s ID = 2. Všechny řádky tabulky objednávek se čtou postupně.
• 2. V souladu s pravidlem 5 vrátí operace s ID = 2 řádky nadřazené operace s ID = 1 přečtené v kroku 1.
• 3. Operace s ID = 3 se začne provádět až po ukončení operace s ID = 2.
• 4. Po dokončení operace s ID = 2 začne operace s ID = 3. Všechny řádky tabulky order_items se načtou postupně.
• 5. V souladu s pravidlem 5 vrátí operace s ID = 3 řádky nadřazené operace s ID = 1 přečtené v kroku 4.
• 6. Operace s ID = 1 generuje výslednou sadu dat na základě dat přijatých ze všech jejích dceřiných operací (s ID = 2 a ID = 3).
• 7. Provede se operace s ID = 0. Vrátí se výsledná datová sada.

Lze tedy poznamenat, že operace nezávislého spojení provádí své podřízené operace postupně.

Propojené operace spojení

Propojené operace spojení - Jedná se o operace, které mají více než jednu podřízenou operaci, přičemž jedna z operací řídí provádění ostatních. Příklad: vnořené smyčky, aktualizace.

Následující pravidla, podle kterých fungují operace zřetězeného spojení, lze formulovat následovně.

• 1. Podřízená operace se provede před nadřazenou operací.
• 2. Podřízená operace s nejnižším číslem operace (ID) řídí provádění zbývajících podřízených operací.
• 3. Podřízené operace, které mají společnou nadřazenou operaci, se provádějí počínaje nejnižší hodnotou ID operace ve vzestupném pořadí těchto hodnot. Zbývající podřízené operace NEJSOU prováděny postupně.
• 4. Pouze první podřízená operace se provede jednou. Všechny ostatní podřízené operace jsou provedeny několikrát nebo vůbec.

Na Obr. Obrázek 10.16 ukazuje plán pro následující dotaz:

FROM order_items oi, orders o

WHERE o.order_id= oi.order_id

AND oi.product_id>100

A o.customer_id mezi 100 a 1000

Tento dotaz obsahuje související operaci spojení, NESTED LOOPS.

I Id I Operation

		VYBERTE PROHLÁŠENÍ |

Rýže. 10.16. Propojené operace spojení, plán dotazů

S ohledem na pravidla pro následující operace zřetězeného spojení bude pořadí jejich provádění následující.

• 1. Podle pravidel 1 a 2 následujících operací zřetězeného spojení musí být nejprve provedena operace s ID = 2. Operace s 1D = 2 a 1D = 3 jsou však autonomní a podle pravidla 1 následujících autonomních operací nejprve bude provedena operace s ID = 2. ID = 3. Rozsah indexu ORDCUSTOMERIX se zobrazí na základě podmínky: o. ID zákazníka mezi 100 a 1000.
• 2. Operace s ID=3 vrátí do rodičovské operace (s Ш=2) seznam Rowldových identifikátorů řádků získaných v kroku 1.
• 3. Operace s ID = 2 načte řádky v tabulce objednávek, ve kterých hodnota Rowld odpovídá seznamu hodnot Rowld získanému v kroku 2.
• 4. Operace s ID = 2 vrátí přečtené řádky nadřazené operace (s ID = 1).
• 5. Pro každý řádek vrácený operací s ID = 2 se provede druhá podřízená operace (s ID = 4) operace. vnořené smyčky. To znamená, že pro každý řádek vrácený operací s ID = 2 se provede úplné sekvenční prohledávání tabulky order_items, aby se našla shoda v atributu spojení.
• 6. Krok 5 se opakuje tolikrát, kolikrát je počet řádků vrácených operací s ID = 2.
• 7. Operace s ID = 1 vrátí výsledky nadřazené operace (s ID = 0).
• 8. Provede se operace s ID = 0. Vrátí se výsledná datová sada.

V závislosti na složitosti analyzovaného dotazu může mít jeho realizační plán poměrně složitou strukturu, která se na první pohled zdá obtížně interpretovatelná. Metodická implementace výše popsaných pravidel a dekompozice operací vám umožní efektivně analyzovat plán provádění SQL dotazu jakékoli složitosti. Podívejme se na příklad dotazu, který generuje seznam zákazníků, počet zboží, které zakoupili, a jeho celkové náklady:

SELECT s. cust_first_name customer_name,

COUNT(DISTINCT oi.product_id) jako produkt_množství,

SUM(oi.množství* oi.jednotková_cena) jako celkové_náklady OD oe.objednávek o VNITŘNÍ JOIN zákazníci c ON

o.customer_id=c.customer_id

INNER JOIN oe.order_items oi ON o.order_id= oi.order_id GROUP BY c. cust_first_name

Posloupnost operací tohoto dotazovacího plánu je znázorněna na Obr. 10.17.

VYBRAT PROHLÁŠENÍ I

SEŘADIT SKUPINU PODLE YG

	PŘÍSTUP K TABULCE PLNÝ
	INDEX RANGE SCAN
PŘÍSTUP K TABULCE PODLE INDEXU ROWIDd
PŘÍSTUP K TABULCE PLNÝ

Rýže. 10.17. Plán dotazu, sled operací

Popišme si možný přístup k interpretaci prováděcího plánu 80b dotazu uvedeného na Obr. 10.17. Tento přístup zahrnuje dvě hlavní fáze: rozložení operací do bloků a určení pořadí operací.

V první fázi je nutné rozložit prováděné operace do bloků. K tomu najdeme všechny svazové operace, tzn. operace, které mají více než jednu podřízenou operaci (na obr. 10.17 se jedná o operace 2, 3 a 4), a rozdělit tyto podřízené operace do bloků. V důsledku toho pomocí příkladu na Obr. 10.17 dostáváme tři odborové operace a sedm bloků operací.

Ve druhé fázi je určeno pořadí provádění bloků operací. Chcete-li to provést, musíte použít pravidla pro následující operace popsané výše. Proveďme řadu úvah týkajících se provedení každé operace ve vztahu k jejímu identifikačnímu číslu (III).

Operace Ш = 0 je autonomní a je rodičem operace сШ = 1.

Operace Yu = 1 je také autonomní; je rodičem operace W = 2 a provede se před operací Y = 0.

Operace GO = 2 je nesouvisející operace sjednocení a je rodičovskou operací pro operace Yu = 3, Yu = 8. Operace GO = 2 se provede před operací GO = 1.

Operace GO = 3 je propojená operace spojení, je to nadřazená operace pro operace GO = 4, GO = 7. Operace GO = 3 se provede před operací GO = 2.

Operace GO = 4 je sdružená operace, je to nadřazená operace pro operace GO = 5, GO = 6. Operace GO = 4 se provede před operací GO = 3.

Operace GO = 5 je autonomní operace, která se provádí před operací GO = 4.

Operace GO = 6 je autonomní operace, která se provádí před operací GO = 5.

Operace GO = 7 je autonomní operace, prováděná po provedení bloku operací „C“.

Operace GO = 8 je autonomní operace, prováděná po bloku operací „E“.

Na základě výše uvedených úvah a následujících pravidel formulujeme pořadí prováděných operací:

• 1. Nejprve se provede autonomní operace GO = 5, viz pravidla pro sekvenci souvisejících operací spojení. Celá tabulka se čte postupně.
• 2. Výsledek operace GO = 5 - přečtené řádky tabulky - se přenese do operace GO = 4.
• 3. Provede se operace GO = 4: pro každý řádek vrácený operací GO = 5 se provede operace GO = 6. To znamená, že rozsah indexu se naskenuje proti atributu spojení. Získání seznamu identifikátorů řádků Yaou1s1.
• 4. Výsledek operace GO = 4 se přenese do operace GO = 3. To znamená, že se přenese seznam identifikátorů řádků Kosh1s1.
• 5. Provede se operace GO = 3: pro každou hodnotu 11оу1с1 vrácenou jako výsledek operace bloku operací „C“ se provede operace GO = 7, tzn. řádky tabulky se čtou z daného seznamu identifikátorů řádků ITMI, získaných po provedení operace Ш = 4.
• 6. Provede se autonomní operace GO = 8 - sekvenční čtení celé tabulky.
• 7. Provede se nesouvisející operace spojení GO = 2: spojení se provede hašováním výsledků operačních bloků „E“ a „E“.
• 8. Výsledek operace GO = 2 se přenese na operaci GO = 1.
• 9. Provede se nesouvisející operace sloučení GO = 1: data získaná jako výsledek operace GO = 2 se agregují a seřadí.
• 10. Provede se operace GO = 0. Vrátí se výsledná datová sada.

Následující pravidla formulovaná pro hlavní typy operací platí pro většinu plánů pro provádění dotazu BSGO. Existují však konstrukce používané v dotazech BSGO, které naznačují porušení pořadí operací popsaného v následujících pravidlech. Takové situace mohou nastat v důsledku použití například poddotazů nebo anti-join predikátů. V každém případě proces interpretace plánu provádění dotazu BSGO neznamená pouze použití řady pravidel, která zajistí nejpřesnější analýzu toho, co optimalizátor udělá při provádění dotazu BSGO. Další požadavek BSGO je vždy individuální případ; a to, jak bude spuštěn v DBMS, závisí na mnoha faktorech, včetně verze DBMS, verze a typu operačního systému, na kterém je instance DBMS nasazena, použitého hardwaru, kvalifikace autora dotazu 80b, atd.

1 msdevcon.ru #msdevcon

3 Olontsev Sergey SQL Server MCM, MVP Kaspersky Lab

4 Strukturovaný dotazovací jazyk

5 Příklad dotazu vyberte os.křestní jméno, os.příjmení, emp.jobtitle, emp.nationalidnumber z HumanResources.Employee jako emp vnitřní spojení Osoba.Person jako os na pers.businessentityid = emp.businessentityid kde os.firstname = N"John" a emp.hiredate >= " "

6 Logický strom dotazů Projekt os.křestní jméno, os.příjmení, zam.pracovní název, zam.národníidčíslo D A T A Filtr Připojit os.jméno = N"Jan" a zaměstnanec >= " " os.businessentityid = id.podnikatele Osoba.Osoba jako os Získat data Získat data HumanResources.Employee jako zam

7 Plán dotazů Ukazuje, jak se dotaz T-SQL provádí na fyzické úrovni.

8 Několika způsoby

9 DEMO Jednoduchý plán Výběr všech dat z tabulky, jak získat plán dotazů

11 Metody operátora Init() Metoda Init() způsobí, že se fyzický operátor inicializuje a připraví všechny potřebné datové struktury. Fyzický operátor může přijímat mnoho volání Init(), ačkoli obvykle přijímá pouze jedno. GetNext() Metoda GetNext() způsobí, že fyzický operátor získá první nebo následující řádek dat. Fyzický operátor může přijímat mnoho volání GetNext() nebo žádné. Metoda GetNext() vrací jeden řádek dat a počet jejích volání je indikován hodnotou ActualRows ve výstupu příkazu Showplan. Close() Když je zavolána metoda Close(), fyzický operátor provede určité vyčištění a zavře se. Fyzický operátor obdrží pouze jedno volání Close().

12 Interakce mezi operátory Operátor 1 Operátor 2 Operátor 3

13 Interakce mezi operátory 1. Vyžádejte si operátora řádku 1 operátora 2 operátora 3

14 Interakce mezi operátory 1. Řádek požadavku 2. Řádek požadavku Operátor 1 Operátor 2 Operátor 3

15 Interakce mezi operátory 1. Řádek požadavku 2. Operátor řádku požadavku 1 Operátor 2 Operátor 3 3. Odeslat řádek

16 Interakce mezi operátory 1. Řádek požadavku 2. Operátor řádku požadavku 1 Operátor 2 Operátor 3 4. Odeslat řádek 3. Odeslat řádek

17 Interakce mezi operátory 1. Řádek požadavku 2. Operátor řádku požadavku 1 Operátor 2 Operátor 3 4. Odeslat řádek 3. Odeslat řádek

18 DEMO Operátor TOP Aneb proč je lepší nazývat operátora iterátorem

19 Tabulky neexistují!

20 HoBT Page 1 Strana 2 Strana 3 Strana 4 Řada 1 řada 3 řada 5 řada 7 řada 2 řada 4 řada 6 řada 8

21 HoBT Stránka Stránka Stránka Stránka Stránka Stránka Stránka

22 DEMO Operátoři přístupu k datům Scan, Seek, Lookup

23 Kdo má v databázi pouze jednu tabulku?

24 vnořených smyček, spojení hash a spojení sloučení

25 Join Operators Vnořené smyčky vnitřní spojení, levé vnější spojení, levé poloviční spojení, levé protilehlé spojení Sloučení Spojit vnitřní spojení, levé vnější spojení, levé poloviční spojení, levé protilehlé spojení, pravé vnější spojení, pravé poloviční spojení, pravé protipolové spojení , union Hash Spojte všechny typy logických operací

26 DEMO Spojení, řazení a první operátor Vnořené smyčky, Merge Join, Hash Join, Sort, První operátor

27 Varování

28 DEMO Chyby a varování v plánech dotazů

29 Vím, že nic nevím. Sokrates

30 DEMO Malá ukázka něčeho nejasného

31 Diagnostika plánů dotazů -- TOP 10 dotazů, které spotřebovávají nejvíce CPU a jejich plány vyberte top(10) podřetězec (t.text, qs.statement_start_offset / 2, případ, kdy qs.statement_end_offset = -1 pak len(t.text) else (qs.statement_end_offset - qs.statement_start_offset) / 2 end), qs.execution_count, cast(qs.total_worker_time / as decimal(18, 2)) as total_worker_time_ms, cast (qs.total_worker_time * 1. / qs. (18, 2)) jako avg_worker_time_ms, cast(p.query_plan jako xml) jako query_plan ze sys.dm_exec_query_stats jako qs cross použít sys.dm_exec_sql_text(qs.sql_handle) jako t cross použít sys.dm_offset.plan_text_text , qs.statement_end_offset) jako p pořadí podle qs.total_worker_time desc; jít

32 Techniky čtení velkých plánů dotazů Zkuste je rozdělit do logických bloků a postupně je analyzovat. V SSMS, když je plán zobrazen graficky, se v pravém dolním rohu objeví tlačítko pro snazší navigaci v plánu dotazů. Můžete použít XQuery\XPath.

33 DEMO Velký plán dotazů

35 DEMO SQL Sentry Plan Explorer

36 Shrňme První operátor Úroveň optimalizace Doba kompilace Velikost v mezipaměti Parametry, Hodnoty kompilace Důvod pro předčasné ukončení Náklady na iterátory Nejprve se podívejte na operátory s nejvyššími náklady. Mějte na paměti, že se jedná pouze o odhadované hodnoty (i ve skutečných plánech realizace).

37 Shrňme si Bookmark\Key Lookup Pokud je jich málo, pak s největší pravděpodobností není problém. Pokud je jich hodně, vytvoření krycího indexu pomůže se jich zbavit. Varování Musíte zkontrolovat, proč k tomu dochází, a v případě potřeby podniknout kroky.

38 Shrňme si Spojení mezi operátory (datové toky) Čím silnější spojení, tím více dat mezi těmito operátory předá. Zvláště stojí za pozornost, pokud se v určité fázi datový tok prudce zvýší. Pořadí spojování tabulek Čím menší jsou datové toky, tím snazší je je spojovat. Nejprve je tedy potřeba spojit ty tabulky, jejichž výsledný datový tok bude menší.

39 Souhrnné kontroly Kontroly neznamenají, že došlo k problému. Je možné, že v tabulce není dostatek indexu pro efektivnější vyhledávání. Na druhou stranu, pokud potřebujete vybrat celou nebo velkou část tabulky, bude skenování efektivnější. Hledání neznamená, že je vše v pořádku. Problémem může být velký počet vyhledávání v indexech bez klastrů. Cokoli, co o plánu nevíte, může být potenciálně problém.

40 otázek

41 Kontakty Olontsev Sergey Kaspersky Lab

42 2013 Microsoft Corporation. Všechna práva vyhrazena. Microsoft, Windows, Windows Vista a další názvy produktů jsou nebo mohou být registrované ochranné známky a/nebo ochranné známky v U.S. a/nebo jiné země. Zde uvedené informace slouží pouze pro informační účely a představují aktuální pohled společnosti Microsoft Corporation k datu této prezentace. Protože společnost Microsoft musí reagovat na měnící se podmínky na trhu, nemělo by to být vykládáno jako závazek ze strany společnosti Microsoft a společnost Microsoft nemůže zaručit přesnost žádných informací poskytnutých po datu této prezentace. SPOLEČNOST MICROSOFT NEPOSKYTUJE ŽÁDNÉ ZÁRUKY, VÝSLOVNÉ, PŘEDPOKLÁDANÉ NEBO ZÁKONNÉ, NA INFORMACE V TÉTO PREZENTACI.