Dlaczego SET ARITHABORT ON dramatycznie przyspieszy zapytanie?

Zapytanie jest pojedynczym wyborem zawierającym wiele poziomów grupowania i operacji agregowania. Po włączeniu ustawienia ARITHABORT ON zajmuje mniej niż sekundę, w przeciwnym razie zajmuje kilka minut. Widzieliśmy takie zachowanie na SQL Server 2000 i 2008.

Trochę przestarzałe, ale dla każdego, kto skończy tutaj z podobnym problemem ...

Miałem ten sam problem. Dla mnie okazało się, że jest to wąchanie parametrów, na które początkowo nie rozumiałem wystarczająco, żeby się tym przejmować. Dodałem „set arithabort on”, który naprawił problem, ale potem wrócił. Potem czytam:

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

Wszystko się wyjaśniło. Ponieważ korzystałem z Linq do SQL i miałem ograniczone opcje, aby rozwiązać problem, skończyłem z użyciem przewodnika planu zapytań (patrz koniec łącza), aby wymusić pożądany plan zapytań.

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, w jaki serwer wykonuje obliczenia numeryczne i dlatego można uzyskać bardzo różne wyniki w zależności od procedury. Jest to tak naprawdę tylko jeden z 2 typowych sposobów, w jaki proc może uzyskać straszny plan wykonania, drugi to wąchanie 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 na trochę więcej dyskusji na ten temat.

Twierdziłbym, że prawie na pewno był to wąchanie parametrów.

Często mówi się, że SET OPTIONSmoże to wpłynąć na wydajność w ten sposób, ale nie widziałem jeszcze jednego wiarygodnego źródła tego roszczenia, z wyjątkiem przypadku, w którym używasz indeksowanych widoków / utrwalonych kolumn obliczeniowych.

W takim przypadku (dla SQL2005 + i chyba, że ​​baza danych jest w trybie zgodności z SQL2000 ). Jeśli masz oba, ARITHABORTa ANSI_WARNINGS OFFnastępnie zauważysz, że indeks nie jest używany, więc może mieć skan zamiast pożądanego wyszukiwania (i trochę narzutu, ponieważ nie można użyć utrwalonego wyniku obliczeń). ADO.NET wydaje się domyślnie mieć ANSI_WARNINGS ONz szybkiego testu, który właśnie zrobiłem.

Twierdzenie w odpowiedzi 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. Myślę, że to, co się zwykle zdarza, polega na tym, że po zbadaniu problemu z wydajnością, Profiler jest używany do identyfikacji nieprawidłowego zapytania. To jest wklejane do studia zarządzania i natychmiast uruchamia i zwraca wyniki. Jedyną widoczną różnicą między połączeniami jest ARITH_ABORTopcja.

Szybki test w oknie studia zarządzania pokazuje, że po SET ARITHABORT OFFwłączeniu i uruchomieniu zapytania problem wydajności powtarza się, więc najwyraźniej sprawa jest zamknięta. Rzeczywiście wydaje się, że jest to metoda rozwiązywania problemów zastosowana w łączu Gregga Starka .

Jednak ignoruje to fakt, że z tym zestawem opcji możesz uzyskać dokładnie ten sam zły plan z pamięci podręcznej .

Ponowne użycie tego planu może się zdarzyć, nawet jeśli jesteś zalogowany jako inny użytkownik niż korzysta z połączenia aplikacji.

Przetestowałem to, wykonując najpierw zapytanie testowe z aplikacji sieci web, a następnie ze studia zarządzania za pomocą SET ARITHABORT OFFi widziałem, że liczba użytkowników rośnie od 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 współużytkowanie planów pf rzeczywiście miało miejsce, wszystkie klucze pamięci podręcznej planu muszą być takie same. Oprócz arithabortinnych przykładów wykonujący się użytkownicy potrzebują tego samego domyślnego schematu (jeśli zapytanie opiera się na niejawnym rozpoznawaniu nazw), a połączenia wymagają tego samego languagezestawu.

Pełniejsza lista kluczy pamięci podręcznej planu tutaj

Wiem, że spóźniłem się na tę imprezę, ale dla przyszłych gości Martin ma rację. Natrafiliśmy na ten sam problem - SP działało bardzo wolno dla klientów .NET, podczas gdy błyskawicznie płonął dla SSMS. Badając i rozwiązując problem, przeprowadziliśmy systematyczne testy, o które pyta Kenny Evitt w swoim komentarzu do pytania Martina.

Korzystając z wariantu zapytania Martina, szukałem SP w pamięci podręcznej procedur i znalazłem dwa z nich. Patrząc na plany, tak naprawdę było tak, że jeden miał ARITHABORT WŁĄCZONY, a drugi ARITHABORT WYŁĄCZONY. Wersja ARITHABORT OFF miała wyszukiwanie indeksu, podczas gdy wersja ARITHABORT ON używała skanowania indeksu dla tego samego wyniku. Biorąc pod uwagę parametry, wyszukiwanie indeksu wymagałoby sprawdzenia wyników na dziesiątkach milionów rekordów.

Wyczyściłem dwie procedury z pamięci podręcznej i kazałem klientowi .NET ponownie uruchomić SP, używając tych samych parametrów (które obejmowały szeroki zakres dat dla klienta z dużą aktywnością). SP natychmiast powrócił. Plan buforowany używał tego samego skanu indeksu, który był wcześniej opisywany w planie ARITHABORT ON - ale tym razem plan dotyczył ARITHABORT OFF. Uruchomiliśmy SP z tymi samymi parametrami w SSMS i ponownie otrzymaliśmy wyniki natychmiast. Teraz widzieliśmy, że drugi skan 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. Odkryliśmy, że wynikowy buforowany plan zawierał wyszukiwanie indeksu, ponieważ ten sam wynik był wcześniej obsługiwany przez skanowanie (co było również wyszukiwaniem w pierwotnym planie z wyłączonym ARITHABORT). Ponownie z SSMS uruchomiliśmy SP, tym razem z tym samym szerokim zakresem dat, i zobaczyliśmy taką samą straszną wydajność, jaką mieliśmy w pierwotnym żądaniu .NET.

Krótko mówiąc, rozbieżność nie miała nic wspólnego z rzeczywistą wartością ARITHABORT - z włączeniem lub wyłączeniem, z dowolnego klienta, możemy uzyskać akceptowalną lub straszną wydajność: liczyły się tylko wartości parametrów użyte przy kompilacji i buforowaniu 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 wąchanie parametrów i wynikowy plan nie był optymalny dla wszystkich zakresów parametrów.

Właśnie miałem ten problem. Jak powiedzieli ludzie, główną przyczyną jest wiele planów zapytań, z których jeden nie jest optymalny. Chciałem tylko sprawdzić, czy ARITHABORT może sam powodować problem (ponieważ zapytanie, z którym miałem problemy, nie miało parametrów, co wyklucza wąchanie parametrów z równania).

To przypomina mi dokładnie ten sam problem, z którym się spotkałem na serwerze SQL Server 2008 dni. W naszym przypadku nagle zauważyliśmy, że jedno zadanie sql nagle zwolniło (zwykle kilka sekund, a teraz 9+ minut), zadanie musi uzyskać dostęp do połączonego serwera, dodaliśmy ustawienie ARITHABORT na etapie zadania i wydawało się, że to problem został rozwiązany na kilka dni, a następnie wrócił.

Później otworzyliśmy bilet ze wsparciem MS i początkowo nie mogli się tego dowiedzieć, a bilet został eskalowany do bardzo starszego zespołu PFE, a dwa PFE wsparcia próbowały rozwiązać ten problem.

Ostatnim powodem jest to, że poświadczenie użytkownika (w celu uruchomienia kroku zadania) nie może uzyskać dostępu do statystyk bazowych tabel (po stronie serwera połączonego), dlatego plan wykonania nie jest zoptymalizowany.

Szczegółowo, użytkownik nie ma uprawnień do DBCC SHOW_STATISTICS (chociaż użytkownik może wybrać z tabeli). Zgodnie z MSDN ta reguła uprawnień jest zmieniana po dodatku SP1 dla SQL 2012

Uprawnienia do SQL Server i bazy danych SQL

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 bazy danych db_owner lub stałej roli bazy danych db_ddladmin.

Dodatek SP1 dla programu SQL Server 2012 modyfikuje ograniczenia uprawnień i umożliwia użytkownikom z uprawnieniami SELECT korzystanie z tego polecenia. Pamiętaj, że istnieją następujące wymagania, aby uprawnienia SELECT wystarczały do ​​uruchomienia polecenia:

Aby zweryfikować ten problem, wystarczy uruchomić profiler na podłączonej instancji po stronie serwera i włączyć niektóre zdarzenia w sekcji „Błędy i ostrzeżenia”, jak pokazano poniżej.

Mam nadzieję, że to doświadczenie może jakoś pomóc społeczności.