Dlaczego ustawienie ARITHABORT ON dramatycznie przyspieszyło zapytanie?

Trochę przestarzałe, ale dla każdego, kto kończy tutaj podobny problem …

Miałem ten sam problem. U mnie okazało się, że jest to węszenie parametrów, którego na początku nie rozumiałem na tyle, żeby się tym przejmować. Dodałem „ustawianie liczby włączonej”, które rozwiązało problem, ale potem wróciło. Potem przeczytałem:

http://www.sommarskog.se/query-plan-mysteries.html

Zostało wyczyszczone -wszystko- up. Ponieważ używałem Linq do SQL i miałem ograniczone możliwości rozwiązania problemu, skończyło się na tym, że użyłem przewodnika po planie zapytań (patrz koniec linku), aby wymusić plan zapytania, który chciałem.

Komentarze

• Ponad sześć lat później link podany w tej odpowiedzi jest nadal ” wymaganą lekturą ” … i nadal również aktualna, ostatnia wersja to grudzień ' 17.

Aplikacje .NET łączą się z opcją domyślnie wyłączoną, ale jest ona domyślnie włączona w Management Studio. W rezultacie serwer faktycznie buforuje 2 oddzielne plany wykonania dla większości / wszystkich procedur. Wpływa to na sposób wykonywania przez serwer obliczeń numerycznych i jako takie można uzyskać bardzo różne wyniki w zależności od procedury. To naprawdę tylko jeden z dwóch typowych sposobów, w jakie proc może dostać okropny plan wykonania, drugi to węszenie parametrów.

Spójrz na https://web.archive.org/web/20150315031719/http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx , aby uzyskać więcej dyskusji na ten temat.

Komentarze

• Zgadzam się z połową ta odpowiedź. Jestem jednak bardzo sceptyczny co do twierdzeń dotyczących obliczeń liczbowych!
• @Martin: Myślę, że nie było jasne. Właśnie mówiłem, że ARITHABORT ON powoduje, że SQL Server wyświetli błąd przy każdym błędzie div / 0 lub przepełnieniu arytmetycznym. Kiedy jest wyłączony, działa dalej iz jakiegoś powodu może powodować różnego rodzaju okropne problemy.
• @Ben – Tak, przepraszam, nie ' nie chcę specjalnie atakować twoja odpowiedź Właśnie wskazałem, że byłoby bardzo łatwo zmienić opcję SET, aby uzyskać lepszy plan i błędnie zdiagnozować to jako samą opcję, która jest winna. ' Nie jestem przekonany, że facet w Twoim linku nie ' tego nie zrobił.
• @Martin – To nie jest problem, nie ' nie sądziłem, że mnie atakujesz. Inna dyskusja, którą połączyłem, może być trochę niejasna. Próbowałem tylko przedstawić dowody potwierdzające.
• @Martin Z perspektywy czasu uważam, że masz rację.

Twierdziłbym, że prawie na pewno było to węszenie parametrów.

Często mówi się, że SET OPTIONS może wpływać w ten sposób na wydajność, ale nie widziałem jeszcze jednego wiarygodnego źródła dla tego twierdzenia, z wyjątkiem przypadku, gdy jesteś przy użyciu indeksowanych widoków / utrwalonych kolumn obliczonych.

W tym przypadku (dla SQL2005 + i , chyba że baza danych jest w trybie zgodności SQL2000 ). Jeśli masz zarówno ARITHABORT, jak i ANSI_WARNINGS OFF, zauważysz, że indeks nie jest używany więc może mieć skan zamiast żądanego wyszukiwania (i trochę narzutu, ponieważ utrwalony wynik obliczenia nie może być użyty). ADO.NET wydaje się domyślnie mieć ANSI_WARNINGS ON z szybkiego testu, który właśnie przeprowadziłem.

Oświadczenie w Odpowiedź Bena , że „sposób, w jaki serwer wykonuje obliczenia numeryczne” może dodać minuty do wyniku, który w innym przypadku zająłby mniej niż sekundę, po prostu nie wydaje mi się wiarygodny. Wydaje mi się, że po zbadaniu problemu z wydajnością Profiler jest używany do identyfikacji nieprawidłowego zapytania. To jest wklejane do studia zarządzania i uruchamiane i natychmiast zwraca wyniki. Jedyną widoczną różnicą między połączeniami jest opcja ARITH_ABORT.

Szybki test w oknie studia zarządzania pokazuje, że gdy SET ARITHABORT OFF jest włączone i uruchamiane jest zapytanie, problem z wydajnością powtarza się, więc wielkość liter jest najwyraźniej zamknięta. Rzeczywiście wydaje się, że jest to metodologia rozwiązywania problemów używana w linku Gregg Stark .

Jednak ignoruje to fakt, że z tym zestawem opcji można w końcu dostajesz dokładnie ten sam zły plan z pamięci podręcznej .

Ponowne wykorzystanie tego planu może nastąpić, nawet jeśli jesteś zalogowany jako inny użytkownik niż używa połączenie aplikacji.

Przetestowałem to, wykonując zapytanie testowe najpierw z aplikacji internetowej, a następnie ze studia zarządzania za pomocą SET ARITHABORT OFF i mogłem zobaczyć wzrost liczby użytkowników z poniższego zapytania.

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 to udostępnianie planów pf faktycznie wystąpiło, wszystkie klucze pamięci podręcznej planu muszą być takie same. Oprócz samego arithabort, kilka innych przykładów wskazuje, że użytkownicy wykonujący potrzebują tego samego domyślnego schematu (jeśli zapytanie opiera się na niejawnym rozpoznawaniu nazw), a połączenia wymagają tego samego language set.

Tutaj znajduje się pełniejsza lista kluczy pamięci podręcznej planu

Wiem, że spóźniłem się na to przyjęcie, ale dla przyszłych gości Martin ma dokładnie rację. Napotkaliśmy ten sam problem – SP działał bardzo wolno dla klientów .NET, podczas gdy dla SSMS było to bardzo szybkie. Badając i rozwiązując problem, przeprowadziliśmy systematyczne testy, o które pyta Kenny Evitt w swoim komentarzu do pytania Martina.

Używając wariantu Martina, szukałem SP w pamięci podręcznej procedur i znalazłem dwa z nich. Patrząc na plany, faktycznie było tak, że jeden miał ARITHABORT ON, a drugi ARITHABORT OFF. Wersja ARITHABORT OFF miała przeszukiwanie indeksu podczas gdy wersja ARITHABORT ON używała skanowania indeksu dla to samo wyjście. Biorąc pod uwagę parametry, przeszukiwanie indeksu wymagałoby przeszukania dziesiątek milionów rekordów danych wyjściowych.

Wyczyściłem dwie procedury z pamięci podręcznej i kazałem klientowi .NET ponownie uruchomić SP, używając te same parametry (z szerokim zakresem dat dla klienta o dużej aktywności). SP wrócił natychmiast. Plan zapisany w pamięci podręcznej wykorzystywał ten sam skan indeksu, który był wcześniej zawarty w planie ARITHABORT ON – ale tym razem plan był na ARITHABORT OFF. Uruchomiliśmy SP z tymi samymi parametrami w SSMS i ponownie otrzymaliśmy natychmiastowe wyniki. Teraz widzieliśmy, że drugi plan został zapisany w pamięci podręcznej, dla ARITHABORT ON, ze skanowaniem indeksu.

Następnie wyczyściliśmy pamięć podręczną, uruchomiliśmy SP w SSMS z wąskim zakresem dat i otrzymaliśmy natychmiastowy wynik. Okazało się, że wynikowy plan zapisany w pamięci podręcznej zawierał przeszukiwanie indeksu, ponieważ ten sam wynik był wcześniej obsługiwany przez skanowanie (co było również wyszukiwaniem w oryginalnym planie z wyłączoną funkcją ARITHABORT). Ponownie z SSMS uruchomiliśmy SP, tym razem z tym samym szerokim zakresem dat, i zobaczyliśmy tę samą straszną wydajność, jaką mieliśmy w pierwotnym żądaniu .NET.

Krótko mówiąc, rozbieżność nie miała nic wspólnego z rzeczywista wartość ARITHABORT – z włączeniem lub wyłączeniem, od dowolnego klienta, mogliśmy uzyskać akceptowalną lub straszną wydajność: jedyne, co się liczyło, to wartości parametrów używane podczas kompilowania i buforowania planu.

Podczas gdy MSDN wskazuje, że sam ARITHABORT OFF może mieć negatywny wpływ na optymalizację zapytań, nasze testy potwierdzają, że Martin ma rację – przyczyną było podsłuchiwanie parametrów, a wynikowy plan nie był optymalny dla wszystkich zakresów parametrów.

Komentarze

• Zastanawiam się, co oznacza to wyrażenie Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.. Czy mówią tylko o niemożności użycia indeksów w obliczonych kolumnach i widokach (jeśli ANSI_WARNINGS jest również wyłączone), czy też rzeczywiście ma to jakiś inny efekt.
• Nie jestem ' nie jestem pewien. Zastanawiam się, czy ' to po prostu przypadek, że ktoś w MSDN znalazł się w podobnej sytuacji, ustawił ARTIHABORT na WŁ., Zauważył poprawę wydajności i doszedł do takich samych wniosków, jakie mają inni. Jeśli chodzi o widoki indeksowane i obliczone kolumny, ' m nie jest jasne. W pewnym momencie stwierdza, że opcje SET muszą mieć określone wartości, jeśli operacja INSERT, UPDATE lub DELETE modyfikuje wartości danych w nich przechowywane. W innym miejscu stwierdzają, że optymalizator zignoruje indeksy dla ” dowolnego zapytania „, które odwołuje się do wspomnianego widoku indeksowanego lub kolumny obliczeniowej. Czy oba są prawdziwe, czy naprawdę ” jakiekolwiek zapytanie modyfikujące dane „?

Właśnie miałem ten problem. Jak powiedziano tutaj, główną przyczyną jest wiele planów zapytań, z których jeden jest nieoptymalny. Chciałem tylko sprawdzić, czy ARITHABORT rzeczywiście może sam spowodować problem (ponieważ zapytanie, z którym miałem problemy, nie miało żadnych parametrów, co powoduje wykrywanie parametrów z równania).

Przypomina mi to dokładnie ten sam problem, którego doświadczyłem w dniach 2008 serwera sql. W naszym przypadku nagle okazało się, że jedno zadanie sql nagle zwolniło (zwykle kilka sekund, a teraz ponad 9 minut), zadanie wymaga dostępu do serwera połączonego, dodaliśmy ustawienie ARITHABORT włączone w kroku zadania i wydawało się, że problem został rozwiązany na kilka dni, a następnie wróciliśmy.

Później otworzyliśmy zgłoszenie z pomocą techniczną stwardnienia rozsianego i początkowo oni też nie mogli się dowiedzieć, a bilet został przekazany do bardzo starszego zespołu PFE, a dwa wsparcie PFE próbowało rozwiązać ten problem.

Ostatnim powodem jest to, że poświadczenia użytkownika (do wykonania kroku zadania) nie mogą uzyskać dostępu do statystyk tabel bazowych (po stronie serwera połączonego), a zatem plan wykonania nie jest zoptymalizowany.

W szczegółach, użytkownik nie ma uprawnień do DBCC SHOW_STATISTICS (chociaż może wybrać stół). Zgodnie z MSDN ta reguła uprawnień została zmieniona po dodatku SP1 dla programu SQL 2012

Uprawnienia dla SQL Server i SQL Database

Aby wyświetlić obiekt statystyki, użytkownik musi być właścicielem tabeli lub użytkownik musi być członkiem stałej roli serwera sysadmin, stałej roli db_owner w bazie danych lub stałej roli bazy danych db_ddladmin.

SQL Server 2012 SP1 modyfikuje ograniczenia uprawnień i umożliwia użytkownikom z uprawnieniem SELECT używanie tego polecenia. Zauważ, że istnieją następujące wymagania, aby uprawnienia SELECT były wystarczające do uruchomienia polecenia:

Aby zweryfikować ten problem, wystarczy uruchomić profiler na instancji serwera połączonego i włączyć niektóre zdarzenia w polu „Błędy and Warnings ”, jak pokazano poniżej.

Mam nadzieję, że to doświadczenie może w jakiś sposób pomóc społeczności.