Proč by SET ARITHABORT ON dramaticky urychlilo dotaz?

Trochu datováno, ale pro kohokoli, kdo zde skončí s podobný problém …

Měl jsem stejný problém. Ukázalo se mi, že to bylo čichání parametrů, kterému jsem nejdřív nerozuměl dost na to, abych se o něj staral. Přidal jsem „set arithabort on“, který problém vyřešil, ale pak se to vrátilo. p> http://www.sommarskog.se/query-plan-mysteries.html

Vymazalo to – všechno – protože jsem používal Linq SQL a měl omezené možnosti k vyřešení problému, skončil jsem pomocí průvodce plánem dotazů (viz konec odkazu) k vynucení plánu dotazů, který jsem chtěl.

Komentáře

• O více než šest let později je odkaz uvedený v této odpovědi stále “ vyžadován pro čtení “ … a stále aktuální také, poslední revize je prosinec ‚ 17.

Aplikace .NET se připojují s možností deaktivovanou ve výchozím nastavení, ale je ve výchozím nastavení povolena v Management Studio. Výsledkem je, že server ukládá do mezipaměti 2 samostatné plány provádění pro většinu / všechny procedury. To ovlivňuje způsob, jakým server provádí číselné výpočty, a jako takový můžete v závislosti na postupu získat divoce odlišné výsledky. Toto je opravdu jen jeden ze 2 běžných způsobů, jak se proc může dostat ke strašlivému plánu provádění, přičemž druhým je čichání parametrů.

Podívejte se na https://web.archive.org/web/20150315031719/http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx pro další diskusi.

Komentáře

• Souhlasím s polovinou tato odpověď. Jsem však velmi skeptický ohledně tvrzení o numerickém výpočtu!
• @Martin: Myslím, že jsem byl nejasný. Říkal jsem jen, že ARITHABORT ON způsobí, že SQL Server bude chybovat při jakékoli chybě div / 0 nebo aritmetickém přetečení. Když je vypnuto, stále pokračuje a z jakéhokoli důvodu může způsobit nejrůznější hrozné problémy.
• @Ben – Ano, promiň, ‚ nechci zvlášť útočit vaše odpověď, právě jsem poukazoval na to, že by bylo velmi snadné změnit možnost SET získat lepší plán a chybně ji diagnostikovat jako možnost, která je na vině. ‚ Nejsem přesvědčen, že člověk ve vašem odkazu to ‚ neudělal.
• @Martin – není to problém, nemyslel jsem si ‚, že na mě útočíš. Další diskuse, kterou jsem propojil, by mohla být trochu nejasná. Jen jsem se snažil podat podpůrné důkazy.
• @Martin Při zpětném pohledu věřím, že máte pravdu.

Tvrdím, že se téměř jistě jednalo o čichání parametrů.

Často se uvádí, že SET OPTIONS může ovlivnit výkon tímto způsobem, ale zatím jsem neviděl jediný autoritativní zdroj pro toto tvrzení, s výjimkou případu, kdy jste pomocí indexovaných zobrazení / trvalých vypočítaných sloupců.

V tomto případě (pro SQL2005 + a , pokud vaše databáze není v režimu kompatibility s SQL2000 ). Pokud máte ARITHABORT a ANSI_WARNINGS OFF index, který nepoužíváte takže může mít skenování spíše než požadované hledání (a některé režijní náklady, protože nelze použít trvalý výsledek výpočtu). Zdá se, že ADO.NET má ve výchozím nastavení ANSI_WARNINGS ON z rychlého testu, který jsem právě udělal.

Nárok v Benova odpověď , že „způsob, jakým server provádí číselné výpočty“, může přidat výsledek k minutám, které by jinak trvaly méně než sekundu, mi prostě nepřipadají věrohodné. Myslím, že se obvykle stává, že při vyšetřování problému s výkonem se Profiler používá k identifikaci nevhodného dotazu. Toto je vloženo do manažerského studia a okamžitě spouští a vrací výsledky. Jediným zjevným rozdílem mezi připojeními je možnost ARITH_ABORT.

Rychlý test v okně manažerského studia ukazuje, že při zapnutí SET ARITHABORT OFF a spuštění dotazu se problém s výkonem opakuje, takže je zjevně uzavřen případ. Ve skutečnosti se zdá, že jde o metodiku řešení problémů použitou v odkazu Gregg Stark .

To však ignoruje skutečnost, že s touto sadou možností můžete nakonec získáte úplně stejný špatný plán z mezipaměti .

K opětovnému použití tohoto plánu může dojít, i když jste přihlášeni jako jiný uživatel, než používá připojení aplikace.

Testoval jsem to spuštěním testovacího dotazu nejprve z webové aplikace, poté ze studia pro správu pomocí SET ARITHABORT OFF a viděl jsem, že z níže uvedeného dotazu stoupají usecounts.

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) 

Aby k tomuto sdílení mohlo skutečně dojít, musí být všechny klíče mezipaměti plánu stejné. Stejně jako samotné arithabort jsou některé další příklady toho, že provádějící uživatelé potřebují stejné výchozí schéma (pokud se dotaz spoléhá na implicitní rozlišení názvu) a připojení vyžadují stejné language set.

Zde najdete podrobnější seznam klíčů mezipaměti plánu

Vím, že na tuto párty přijdu pozdě, ale pro budoucí návštěvníky má Martin přesně pravdu. Narazili jsme na stejný problém – SP běžel velmi pomalu pro klienty .NET, zatímco pro SSMS to bylo rychlé. Při zkoumání a řešení problému jsme provedli systematické testování, na které se Kenny Evitt ptá ve svém komentáři k Martinově otázce.

Použití varianty Martinův dotaz, hledal jsem SP v mezipaměti procedur a našel dva z nich. Při pohledu na plány to bylo ve skutečnosti to, že jeden měl ARITHABORT ON a jeden měl ARITHABORT OFF. Verze ARITHABORT OFF měla indexové hledání zatímco verze ARITHABORT ON používala indexové skenování stejný výstup. Vzhledem k použitým parametrům by hledání indexu vyžadovalo pro výstup vyhledávání desítek milionů záznamů.

Vymazal jsem tyto dva postupy z mezipaměti a nechal jsem .NET klienta znovu spustit SP pomocí stejné parametry (které obsahovaly široké časové období pro zákazníka se spoustou aktivity). SP se okamžitě vrátil. Plán uložený v mezipaměti používal stejný indexový sken, který byl dříve uveden v plánu ARITHABORT ON – ale tentokrát byl plán pro ARITHABORT OFF. Spustili jsme SP se stejnými parametry v SSMS a okamžitě jsme dostali výsledky. Nyní jsme viděli, že byl uložen do mezipaměti druhý plán, a to pro ARITHABORT ON, s indexovým skenováním.

Poté jsme vymazali mezipaměť, spustili SP v SSMS s úzkým rozsahem dat a dostali okamžitý výsledek. Zjistili jsme, že výsledný plán v mezipaměti měl hledání indexu, protože stejný výstup byl dříve zpracován skenováním (což bylo také hledání v původním plánu s vypnutým ARITHABORT). Znovu od SSMS jsme spustili SP, tentokrát se stejným širokým časovým rozsahem a viděli jsme stejný hrozný výkon, jaký jsme měli v původním požadavku .NET.

Stručně řečeno, rozdíl neměl nic společného s skutečná hodnota ARITHABORT – s jeho zapnutím nebo vypnutím od kteréhokoli klienta bychom mohli získat přijatelný nebo hrozný výkon: Důležité byly pouze hodnoty parametrů použitých při kompilaci a ukládání do mezipaměti plánu.

Zatímco MSDN naznačuje, že samotné ARITHABORT OFF může mít negativní dopad na optimalizaci dotazů, naše testování potvrzuje, že Martin má pravdu – příčinou bylo čichání parametrů a výsledný plán nebyl optimální pro všechny rozsahy parametrů.

Komentáře

• Zajímalo by mě, co ta fráze Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues. znamená. Ať už hovoří jen o neschopnosti používat indexy na vypočítaných sloupcích a pohledech (pokud je ANSI_WARNINGS také vypnuto), nebo pokud to skutečně má nějaký jiný účinek.
• Nejsem si jistý ‚. Zajímalo by mě, jestli je to ‚ prostě případ, že někdo na MSDN narazil na podobnou situaci, nastavil ARTIHABORT na ON, viděl zlepšení výkonu a přešel ke stejným závěrům, jaké mají ostatní. Pokud jde o indexovaná zobrazení a vypočítané sloupce, není mi ‚ jasné. V jednom okamžiku uvádí, že možnosti SET musí mít konkrétní hodnoty, pokud operace INSERT, UPDATE nebo DELETE upraví v nich uložené datové hodnoty. Jinde uvádějí, že optimalizátor bude ignorovat indexy pro “ jakýkoli dotaz „, který odkazuje na uvedené indexované zobrazení nebo vypočítaný sloupec. Jsou oba pravdivé, nebo je to opravdu “ jakýkoli dotaz upravující data „?

Právě jsem měl tento problém. Jak zde lidé řekli, hlavní příčinou je několik plánů dotazů, z nichž jeden je neoptimální. Chtěl jsem jen ověřit, že ARITHABORT může skutečně způsobit problém sám o sobě (protože dotaz, s nímž jsem měl problémy, neměl žádné parametry, což vyňalo čichání parametrů z rovnice).

To mi připomíná přesně stejný problém, jaký jsem zažil na serveru SQL Server 2008. V našem případě jsme najednou zjistili, že jedna úloha sql se náhle zpomalila (obvykle na několik sekund a nyní 9 a více minut), úloha potřebuje přístup k propojenému serveru, v kroku úlohy jsme přidali zapnutou ARITHABORT a problém byl vyřešen na několik dní a poté se vrátil.

Později jsme otevřeli lístek s podporou MS a zpočátku to nemohli zjistit ani oni, a lístek byl eskalován velmi vysokému týmu PFE a dvěma podpora PFE se pokusil vyřešit tento problém.

Posledním důvodem je, že pověření uživatele (ke spuštění kroku úlohy) nemá přístup ke statistikám podkladových tabulek (na straně propojeného serveru), a proto plán optimalizace není optimalizován.

Uživatel podrobně nemá oprávnění k DBCC SHOW_STATISTICS (i když uživatel může VYBRAT z stůl). Podle MSDN se toto pravidlo oprávnění změní po aktualizaci sql 2012 SP1

Oprávnění pro SQL Server a SQL Database

Chcete-li zobrazit statistický objekt, musí uživatel vlastnit tabulku nebo uživatel musí být členem pevné role serveru sysadmin, pevné role databáze db_owner nebo pevné databázové role db_ddladmin.

SQL Server 2012 SP1 upravuje omezení oprávnění a umožňuje uživatelům s oprávněním SELECT používat tento příkaz. Všimněte si, že pro spuštění příkazu jsou dostatečné následující podmínky oprávnění SELECT:

Chcete-li tento problém ověřit, stačí spustit profiler na instanci propojeného serveru a zapnout některé události v části „Chyby a Varování „, jak je uvedeno níže.

Doufám, že tato zkušenost může komunitě nějak pomoci.