SQL Server 2005/8 Wskazówki dotyczące optymalizacji zapytań

Zastanawiam się nad edukacją zespołu w zakresie pisania lepszych zapytań SQL Server i zastanawiałem się, jakie najlepsze wskazówki dla ludzi mogą poprawić wydajność.

Na przykład kiedyś miałem DBA, który nalegał, aby count (*) działał gorzej niż count (1) (nie mam pojęcia, czy miała rację, czy nadal jest poprawna w stosunku do najnowszych optymalizatorów zapytań).

Jakie proste rzeczy powinienem powiedzieć zespołowi, aby starał się zawsze używać lub unikać? Idealnie szukam rzeczy, które (a) mogłyby mieć sensowną różnicę i (b) są proste, 1-2 linie do stwierdzenia.

Strojenie zapytania 101

Nie ma magicznej srebrnej kuli, by zapytać o strojenie, chociaż mogę dać ci kilka wskazówek i wskazówek. Pierwszą rzeczą do zrobienia jest zrozumienie, co faktycznie dzieje się za kulisami. Zdobądź dobrą książkę o wnętrzach, taką jak trzecia książka Guru.

Słabo wydajne zapytania występują w dwóch podstawowych odmianach: zapytania transakcyjne, które trwają zbyt długo, oraz mielenie zadań wsadowych (lub raportów), które trwają zbyt długo. Dobrym znakiem zapytania, w którym coś jest nie tak, jest pojedynczy element w planie zapytań, który zajmuje 99% czasu.

Zapytania transakcyjne

W większości przypadków słabo wykonane zapytanie transakcyjne jest jedną z kilku rzeczy:

• Brakujący indeks. Można to zobaczyć w planie zapytań - skany dużych tabel w złączach, które powinny być bardzo selektywne (tzn. Zwracać kilka wierszy).

• Zapytanie nie może korzystać z indeksu. Jeśli masz warunki LUB w klauzuli where, przyłącza się do obliczonej wartości lub innego elementu w zapytaniu, który nie jest możliwy do sarg, może być konieczne ponowne zapisanie zapytania. W skrócie, sargs to predykaty zapytań, które mogą wykorzystywać indeksy do eliminowania wierszy. Logiczne AND, równość i nierówność (>,> =, <, <= i! =) Są wszystkie możliwe do sargowania. OR tradycyjnie nie jest w stanie sargować. Jednak często można tłumaczyć OR na predykaty sargowe, odwracając sens z OR na konstrukcje typu NOT (foo i not bar).

• Nieefektywne predykaty. Na przykład, jeśli masz where inodwołanie do zagnieżdżonego podzapytania, sprawdź, czy można je ponownie zapisać jako where existspołączenie. Może to skutkować wydajniejszymi planami zapytań. Oto inne standardowe zapisy, które możesz wypróbować. Ponownie, przewodniki Guru i inne na ten temat są dobrym punktem wyjścia.

Zapytania wsadowe

Zapytania wsadowe są bardziej skomplikowane i mają różne problemy ze strojeniem. Niektóre wskazówki to:

• Indeksowanie Może to mieć dużą różnicę z tego samego powodu, co w przypadku zapytań transakcyjnych. Często dobrym znakiem braku indeksu jest długa, mieląca operacja (99% planu zapytań), która nie wydaje się niszczyć maszyny.

• Tymczasowe stoły. Lepszym rozwiązaniem może być podzielenie zapytania na kilka zapytań wypełniających tabele tymczasowe. Większe zapytania dają optymalizatorowi więcej miejsca na zepsucie, chociaż nie jest to już taki problem. Twórz tabele temp, select intoponieważ ta operacja jest minimalnie rejestrowana (znacznie mniej aktywności dziennika), co zmniejsza obciążenie We / Wy.
  
  Należy zauważyć, że tabele tymczasowe w tempdb mają tę samą strukturę danych, z której korzysta optymalizator do przechowywania wyników łączenia pośredniego, więc nie ma to negatywnego wpływu na wydajność. Możesz także utworzyć indeks (w tym indeksy klastrowe i indeksy) w tabeli tymczasowej, co może poprawić wydajność odczytu zapytań z tych samych powodów, dla których poprawiają one zapytania w tabelach statycznych.
  
  Nie przesadzaj jednak z tabelami tymczasowymi, ponieważ mogą utrudnić prześledzenie zapytania. W przypadku mniejszych tabel w ramach procedury składowanej sprawdź, czy zmienne tabeli pomagają. Są to struktury danych w pamięci, dzięki czemu mogą wygrać wydajność.

• Indeksy klastrowe i obejmujące. Mogą one poprawić wydajność zapytania, ponieważ wymuszają lokalizację odniesienia na dysku na podstawie kolumny grupującej. Indeks klastrowy może znacząco wpłynąć na wydajność zadania wsadowego.

• Nieefektywne predykaty. Mogą one powodować problemy z sargs i innymi błędami optymalizacji w podobny sposób, jak w przypadku zapytań transakcyjnych.

• Skanowanie stołu to twój przyjaciel. W przeciwieństwie do powszechnego przekonania, skany tabel nie są z natury złe. Zasadniczo są one oznaką czegoś złego w zapytaniu transakcyjnym, ale często są najbardziej wydajnym sposobem wykonania dużej operacji wsadowej. Jeśli robisz coś z więcej niż kilkoma procentami wierszy w tabeli, skanowanie tabeli jest często najbardziej efektywnym sposobem na pokrycie tabeli.

• Łączy się zagnieżdżone pętle. Zobacz, co robi optymalizator po obu stronach złączenia. Mogą być one nieefektywne, jeśli jesteś (na przykład skanując tabelę dwie duże tabele po obu stronach sprzężonej pętli zagnieżdżonej. Rozważ użycie indeksów klastrowych lub order bypróbę zmiany operacji na połączenie scalające lub podpowiedź w celu promowania łączenia mieszającego, jeśli jedna strona jest wystarczająco mały, aby to zrobić.

Zamykający

Blokowanie może również powodować problemy z wydajnością. Jeśli twój system źle działa pod obciążeniem, spójrz na liczniki profilera i perfmon związane z blokadami i sprawdź, czy istnieje jakakolwiek znacząca rywalizacja. sp_who2ma kolumnę „BlkBy” w zestawie wyników, która pokaże, czy zapytanie jest zablokowane i co go blokuje. Również profile ze zdarzeniami „zakleszczenia” (w przypadku zakleszczenia zapytań) i zdarzeniami związanymi z blokowaniem mogą być przydatne do rozwiązywania problemów z blokowaniem.

Najlepsza wskazówka: użyj SQL Server 2008 i uruchom Monitor aktywności podczas testów. Zwróć uwagę na zapytania, które zajmują najwięcej czasu / mają najwięcej operacji we / wy itp. Kliknij prawym przyciskiem myszy te zapytania, aby wyświetlić zapytanie i / lub plan wykonania.

Następnie: naucz się rozumieć plany wykonania.

Dalej: użyj kreatora dostrajania bazy danych.

Te kroki pomogą Ci wygenerować własne „najlepsze wskazówki”.

Doskonały darmowy ebook od RedGate dotyczący pracy z planami wykonania programu SQL Server i ich zrozumienia

http://www.red-gate.com/specials/Grant.htm?utm_content=Grant080623

Bezwstydna wtyczka, odnoszę się do materiałów dostrajania wydajności na moim blogu w obszarze Wydajność programu SQL Server .

Gdy będziesz miał okazję przetrawić niektóre z tych materiałów, możesz zamieścić tutaj post lub skontaktować się ze mną bezpośrednio w sprawie konkretnych pytań.

Po pierwsze, indeksowanie. Wiele osób nie zdaje sobie sprawy, że klucze obce nie otrzymują automatycznie indeksów. Ponieważ są używane w złączeniach, prawie zawsze powinny mieć indeks.

Dokładnie sprawdź wszystkie kursory, aby sprawdzić, czy można je zastąpić kodem opartym na zestawie. W ten sposób zmieniłem kod, który działał przez kilka godzin lub sekund.

Unikaj podkwerend. Jeśli masz je w kodzie, zamień je na połączenia lub połączenia na tabele pochodne.

Upewnij się, że klauzula where jest podatna na obciążenia.

Naucz się czytać plany wykonania.

Upewnij się, że w biurze jest kilka dobrych książek na temat dostrajania wydajności.

Zmienne tabel są lepsze niż tabele tymczasowe w niektórych przypadkach, a tabele tymczasowe działają lepiej w innych. Jeśli musisz ich użyć, wypróbuj oba i zobacz, który z nich działa lepiej w danym przypadku.