Optymalizacja zapytań w SQL Server 2005, statystyki bazy danych

Optymalizacja zapytań w SQL Server 2005, statystyki bazy danych SQL Server 2005, TWORZENIE STATYSTYK, STATYSTYK AKTUALIZACJI, USTAW NOCOUNT ON, plany wykonania zapytań, liczba logicznych odczytów, wskazówki optymalizatora, MAXDOP, OPTYMALIZACJA DLA, podręczniki plany wykonania (prowadnice planu), sp_create_plan_guide

Jeśli wszystkie inne sposoby optymalizacji wydajności są już wyczerpane, ostatnia rezerwa jest dostępna dla programistów i administratorów SQL Server - optymalizując wykonanie poszczególnych zapytań. Jeśli na przykład w Twoim zadaniu jest absolutnie konieczne przyspieszenie tworzenia jednego konkretnego raportu, możesz przeanalizować zapytanie używane podczas tworzenia tego raportu i spróbować zmienić jego plan, jeśli nie jest optymalny.

Stosunek do optymalizacji zapytań wśród wielu specjalistów jest niejednoznaczny. Z jednej strony działanie modułu oprogramowania Query Optimizer, który generuje plany wykonania zapytań, budzi wiele uczciwych skarg zarówno w SQL Server 2000, jak i SQL Server 2005. Query Optimizer często nie wybiera najbardziej optymalnych planów wykonania zapytań iw niektórych sytuacjach traci z podobnych modułów Oracle i Informix. Z drugiej strony ręczna optymalizacja zapytań jest niezwykle pracochłonnym procesem. Możesz poświęcić dużo czasu na taką optymalizację, a na koniec dowiedzieć się, że nic nie możesz zoptymalizować: plan zaproponowany przez Query Optimizer okazał się początkowo najbardziej optymalny (dzieje się tak w większości przypadków). Ponadto może się zdarzyć, że plan kwerend utworzony ręcznie po pewnym czasie (po dodaniu nowych informacji do bazy danych) nie jest optymalny i spowolni działanie zapytań.

Pamiętaj również, że aby wybrać najlepsze plany zapytań Optymalizatora kwerend, potrzebujesz poprawnych informacji o statystykach. Ponieważ, zgodnie z doświadczeniem autora, daleko od wszystkich administratorów wie, co to jest, powiemy Ci więcej o statystykach.

Statystyka jest specjalną informacją serwisową o rozkładzie danych w kolumnach tabel. Wyobraź sobie na przykład, że spełniana jest prośba, która powinna zwrócić wszystkich Iwanowów mieszkających w Petersburgu. Załóżmy, że 90% wpisów w tej tabeli ma taką samą wartość w kolumnie Miasto jak „St. Petersburg”. Oczywiście, z punktu widzenia wykonywania zapytań, najpierw korzystne jest wybranie wszystkich Ivanovów w tabeli (oczywiście nie będzie ich 90%), a następnie sprawdzenie wartości kolumny Miasto dla każdego wybranego rekordu. Aby jednak dowiedzieć się, jak dystrybuowane są wartości w kolumnie, należy najpierw uruchomić zapytanie. Dlatego SQL Server niezależnie inicjuje wykonywanie takich zapytań, a następnie przechowuje informacje o dystrybucji danych (zwanych statystykami) w tabelach usług bazy danych.

Dla baz danych SQL Server 2005 domyślnie ustawione są parametry AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS. Jednocześnie statystyki dla kolumn bazy danych zostaną utworzone i zaktualizowane automatycznie. W przypadku największych i najważniejszych baz danych może się zdarzyć, że operacje tworzenia i aktualizowania statystyk mogą zakłócać bieżącą pracę użytkowników. Dlatego w przypadku takich baz danych czasami te parametry są wyłączone, a operacje tworzenia i aktualizowania statystyk są wykonywane ręcznie w nocy. W tym celu użyj poleceń UTWÓRZ STATYSTYKĘ i AKTUALIZUJ STATYSTYKI.

Porozmawiajmy teraz o optymalizacji zapytań.

Pierwszą rzeczą do zrobienia jest znalezienie zapytań, które są przede wszystkim przedmiotem optymalizacji. Najłatwiej to zrobić za pomocą profilera, ustawiając filtr tak, aby uruchamiał zapytanie (filtr Duration w oknie Edit Filter , które można otworzyć za pomocą przycisku Column Filters w zakładce Events Selection okna właściwości sesji śledzenia). Na przykład liczba kandydatów do optymalizacji może uzyskać żądania, których czas wykonania był dłuższy niż 5 sekund. Ponadto można użyć informacji o zapytaniach dostarczonych przez Doradcę dostrajania bazy danych.

Następnie należy sprawdzić, czy parametr NOCOUNT jest ustawiony dla połączeń, procedur przechowywanych i funkcji. Możesz go zainstalować za pomocą polecenia SET NOCOUNT ON. Gdy ten parametr jest ustawiony, po pierwsze, zwrot z serwera i dane wyjściowe dotyczące liczby wierszy w wynikach zapytania są wyłączone (tj. Wiersz „N dotyczy wierszy”) na karcie Wiadomości okna kodu podczas wykonywania żądanie w Management Studio). Po drugie, transmisja specjalnej wiadomości serwera DONE_IN_PROC jest wyłączona, która domyślnie jest zwracana dla każdego etapu procedury składowanej. Podczas wywoływania większości procedur przechowywanych potrzebny jest tylko wynik ich wykonania, a liczba przetworzonych wierszy dla każdego etapu nie interesuje nikogo. Dlatego ustawienie parametru NOCOUNT dla procedur przechowywanych może znacznie poprawić ich wydajność. Szybkość wykonywania zwykłych zapytań jest również zwiększona, ale w mniejszym stopniu (do 10%).

Następnie możesz rozpocząć pracę z planami wykonania zapytania.

Plan wykonania zapytania jest najłatwiejszy do wyświetlenia z SQL Server Management Studio. Aby uzyskać informacje o oczekiwanym planie wykonania dla zapytania, można wybrać polecenie Wyświetl plan wykonania szacowanego z menu Zapytanie . Jeśli chcesz poznać rzeczywisty plan wykonania dla zapytania, możesz ustawić parametr Uwzględnij plan aktualnej realizacji w tym samym menu przed jego wykonaniem. W tym przypadku po wykonaniu zapytania w oknie wyników w SQL Server Management Studio pojawia się jeszcze jedna karta: Plan wykonania ( Plan wykonania ), który pokaże prawdziwy plan wykonania zapytania. Jeśli najedziesz kursorem myszy na którykolwiek z etapów, możesz uzyskać dodatkowe informacje na jego temat (rys. 11.15).

Rys. 11.15. Zapytanie o wykonanie planu w SQL Server Management Studio

Jeśli chodzi o wykonywanie zapytań, jak widać na rysunku, może być wiele elementów. Zrozumienie ich i zaproponowanie innego planu wdrożenia jest dość skomplikowanym zadaniem. Muszę powiedzieć, że każdy z możliwych elementów jest optymalny w swojej sytuacji. Dlatego zazwyczaj kroki optymalizacji zapytań wyglądają tak:

q Najpierw w oknie Management Studio wykonaj komendę SET STATISTICS IO ON. W rezultacie po wykonaniu każdego żądania zostaną wyświetlone dodatkowe informacje. W nim interesuje nas wartość tylko jednego parametru - Odczyty logiczne. Ten parametr wskazuje liczbę odczytów logicznych podczas wykonywania zapytań, czyli ile odczytów trzeba było wykonać podczas wykonywania tego zapytania bez uwzględnienia wpływu pamięci podręcznej (liczby odczytów z pamięci podręcznej i dysku). Jest to najważniejszy parametr. Liczba odczytów fizycznych (odczyt tylko z dysku) nie jest bardzo reprezentatywną informacją, ponieważ zależy od tego, czy były jakieś odniesienia do tych tabel przed, czy nie. Statystyki czasu są również wartością zmienną i zależą od innych operacji wykonywanych przez serwer w tym czasie. Jednak liczba odczytów logicznych jest najbardziej obiektywnym wskaźnikiem, na który najmniej wpływają dodatkowe czynniki;

q następnie spróbuj zmienić plan wykonania zapytania i sprawdź całkowitą liczbę logicznych odczytów dla każdego planu. Zwykle plan wykonania zapytania jest zmieniany za pomocą wskazówek optymalizatora. Wyraźnie informują optymalizatora, którego planu wykonania użyć.

Optymalizator podpowiedzi w SQL Server 2005 zapewnia wiele. Możesz przeczytać o nich informacje w Books Online (na liście w zakładce Index musisz wybrać Query Hints [ SQL Server] ( Query Hints [SQL Server]), Join Hints ( Join Hints ) lub Table Hints [ SQL Server] (Wskazówki dotyczące tabeli) [SQL Server]))). Najczęściej używane są następujące wskazówki:

q NOLOCK, ROWLOCK, PAGLOCK, TABLOCK, HOLDLOCK, READCOMMITTEDLOCK, UPDLOCK, XLOCK - te wskazówki służą do sterowania zamkami (patrz sekcja 11.5.7 ) ;

q SZYBKA liczba wierszy - zostanie wybrany plan wykonania zapytania, w którym określona liczba wierszy zostanie wyświetlona tak szybko, jak to możliwe (pierwszy od początku zestawu rekordów). Jeśli użytkownik potrzebuje dokładnie pierwszych rekordów (na przykład ostatnich zamówień), aby jak najszybciej wprowadzić je do okna aplikacji, możesz użyć tej podpowiedzi;

q FORCE ORDER - tabele zostaną połączone, gdy zapytanie zostanie wykonane w dokładnie takiej kolejności, w jakiej te tabele są wymienione w zapytaniu;

q MAXDOP (od Maksymalnego stopnia równoległości - maksymalny stopień równoległego zapytania) - dzięki tej wskazówce można określić maksymalną liczbę procesorów, które mogą być użyte do wykonania żądania. Zazwyczaj ta wskazówka jest używana w dwóch sytuacjach:

· Gdy z powodu przełączania między procesorami ( przełączanie kontekstu ), szybkość żądania jest znacznie zmniejszona. To zachowanie było typowe dla SQL Server 2000 w systemach wieloprocesorowych;

· Gdy chcesz, aby ciężkie zapytanie miało minimalny wpływ na bieżące doświadczenie użytkownika;

q OPTYMALIZUJ DLA - ta wskazówka pozwala określić, że zapytanie jest zoptymalizowane dla określonej wartości przekazywanego parametru (na przykład pod wartością filtru dla GDZIE);

q USE PLAN to najpotężniejsza funkcja. Dzięki tej wskazówce można jawnie określić plan wykonania zapytania, przesyłając plan jako wartość ciągu w formacie XML. Wskazówka USE PLAN pojawiła się tylko w SQL Server 2005 (w poprzednich wersjach możliwe było jawne zdefiniowanie planów wykonania zapytań, ale do tego użyto innych środków). Możesz napisać plan XML ręcznie lub wygenerować go automatycznie (na przykład, klikając prawym przyciskiem myszy ekran graficzny z planem wykonania pokazanym na rysunku 11.15 i wybierając polecenie Zapisz plan wykonania jako z menu kontekstowego).

W SQL Server 2005 pojawiła się nowa ważna funkcja, która pozwala ręcznie zmienić plan wykonania zapytania bez zakłócania tekstu zapytania. Często zdarza się, że kodu żądania nie można zmienić: jest on na stałe wpisany w kod skompilowanej aplikacji. Aby rozwiązać ten problem, procedura składowana sp_create_plan_guide pojawiła się w SQL Server 2005. Umożliwia tworzenie tzw. Przewodników po planach wdrożeniowych ( przewodników po planach ), które będą automatycznie stosowane do odpowiednich wniosków.

Jeśli przeanalizujesz żądania wysyłane przez aplikację do bazy danych, warto najpierw zwrócić uwagę na następujące punkty:

q Jak często w planach wykonywania kwerend występuje operacja skanowania tabeli (pełne skanowanie tabeli ). Może być tak, że dostęp do tabeli za pomocą indeksów będzie bardziej wydajny;

q czy kursory są używane w kodzie. Kursory są bardzo prostym narzędziem pod względem składni programu, ale niezwykle nieefektywnym pod względem wydajności. Bardzo często można uniknąć używania kursorów przy użyciu innych konstrukcji składniowych i uzyskać duży wzrost szybkości pracy;

q czy kod używa tabel tymczasowych lub tabeli typu danych. Tworzenie tymczasowych tabel i praca z nimi wymaga dużych nakładów zasobów, więc w miarę możliwości należy ich unikać;

q Oprócz tworzenia tabel tymczasowych, znaczne zużycie zasobów systemowych wymaga zmiany ich struktury. Dlatego polecenia zmieniające strukturę tabel tymczasowych powinny natychmiast przyciągnąć twoją uwagę. Zwykle możliwe jest natychmiastowe utworzenie tabeli tymczasowej ze wszystkimi niezbędnymi kolumnami;

q Czasami kwerendy zwracają więcej danych, niż aplikacja faktycznie potrzebuje (zbyt wiele kolumn lub wierszy). Oczywiście nie sprzyja to poprawie wyników;

q jeśli aplikacja wysyła polecenia EXECUTE do serwera, to warto rozważyć zastąpienie ich wywołaniem procedury sp_executesql. Ma przewagę wydajności nad zwykłym poleceniem EXECUTE;

q Poprawiona wydajność może być czasami osiągnięta poprzez wyeliminowanie konieczności ponownej kompilacji procedur przechowywanych i tworzenia nowych planów zapytań. Konieczne jest, aby zwrócić uwagę na wykorzystanie możliwości, staram się nie mieszać procedury dowodzenia kod przechowywane DML i DDL i upewnić się, że ustawienia połączenia SET ANSI_DEFAULTS, ustaw ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS i ustaw CONCAT_NULL_YIELDS_NULL nie uległ zmianie między żądaniami (żadnych zmian tych parametrów prowadzi fakt, że stare plany wdrożeniowe są uważane za nieważne). Zazwyczaj problem może wystąpić, gdy te parametry są ustawione na poziomie pojedynczego żądania lub w kodzie procedury składowanej.

Zauważ, że w każdym przypadku tworzenie planów ręcznego wykonywania zapytań i używanie podpowiedzi jest skrajnym środkiem, którego należy unikać, jeśli to możliwe.