SQL jest opisowym, deklaratywnym językiem. Oznacza to, że zapytania SQL są takie, jakby zadawały pytania, a SQL Server niezależnie decyduje o tym, jak na nie odpowiedzieć. Na tej podstawie lwia część optymalizacji jest przeprowadzana na poziomie SQL Server, a nie samych zapytań.
Biorąc pod uwagę przybliżony koszt każdej operacji logicznej opartej na dystrybucji danych, dostępnych indeksach i możliwościach sprzętowych, optymalizator zapytań (Optymalizator zapytań) tworzy drzewo operacji logicznych, które zapewniają najszybsze wykonanie zadania, tj. ogólny plan wykonania zapytania.
Optymalizacja zapytań polega więc głównie na tworzeniu odpowiednich indeksów, aby optymalizator zapytań mógł wybierać dane w możliwie najszybszy sposób. Tylko w wyjątkowych przypadkach zmiana struktury wniosku wpływa na wydajność. Jednocześnie większość żądań produkcyjnych napisanych na trzy różne sposoby zwróci identyczne (a nawet całkowicie zbieżne) plany wykonania.
Strojenie indeksu to umiejętność, która wymaga głębokiego zrozumienia kilku niezależnych czynników. W rzeczywistości dostrojenie zapytań jest skomplikowane przez fakt, że sam optymalizator zapytań jest trochę czarną skrzynką. A zanim będziesz mógł przewidzieć jego zachowanie, musisz zrozumieć powiązanie następujących czynników.
? Architektura stronicowania SQL Server, model schematu fizycznego i operatory planu zapytań logicznych.
? Dystrybucja danych, statystyki indeksów, wybór indeksu przez optymalizator zapytań i konserwacja indeksu.
? Struktura indeksu klastrowanego, współczynnik wypełnienia, fuzja stron i konserwacja indeksu.
? Zapytania, indeksy, plany wykonania zapytań i optymalizator zapytań.
? Użyj ponownie planu kwerend i zastosuj parametry.
Jako programista lub administrator bazy danych musisz zrozumieć, które indeksy są najlepsze wśród masy zapytań produkcyjnych, a nie tylko pojedyncze zapytanie.
Najlepszą rzeczą, jaką mogę polecić, jest przestudiowanie wszystkich czynników, a następnie przeprowadzenie własnych eksperymentów, aby zrozumieć wszystkie zawiłości korelacji między danymi, schematem, indeksami i zapytaniami. Dostosowywanie indeksów nie ma wpływu na logiczny wynik żadnego zapytania. Administratorzy baz danych nie powinni bać się eksperymentować z indeksami.
Indeksowanie
Indeks tematyczny książki pomaga czytelnikom szybko znaleźć odpowiedź na ich pytanie. Najlepszym indeksem tematu jest ten, który otwiera czytelnikowi najkrótszą ścieżkę od zadawania mu pytań do znajdowania odpowiedniego tekstu w książce. Indeksy baz danych odgrywają tę samą rolę.
Chociaż indeksy mogą być złożone, cel indeksowania jest prosty: zmniejszenie liczby czytelnych stron fizycznych.
Podstawy indeksowania
Istnieją dwa główne typy indeksów. Indeks, który fizycznie sortuje strony tekstowe, jak książka telefoniczna, nazywany jest klastrowanym, ponieważ same dane są uporządkowane w określonej kolejności. Ten typ indeksu tematu znajduje się fizycznie w tekście książki. Oczywiście tekst może być w tym przypadku posortowany tylko w jednej kolejności. Podobnie tabele SQL Server mogą mieć tylko jeden porządek sortowania fizycznego i nazywany jest indeksem tabel klastrowanych.
Indeks nieklastrowany jest podobny do indeksu tematu na końcu książki - wysyła czytelnika na określone strony. Każde pytanie można łatwo znaleźć w tekście, jeśli znajdziesz je najpierw w indeksie, a następnie kliknij łącze do określonej strony. SQL Server ma również indeksy nieklastrowane, które wykonują sortowanie przy użyciu kolumn innych niż indeks klastrowany.
Kolumny używane w indeksie są nazywane kluczem.
Ponieważ klucze podstawowe implementują unikalną metodę identyfikacji dowolnego wiersza, indeksy i klucze podstawowe są ze sobą powiązane - klucz podstawowy musi być indeksowany, ale jednocześnie może być klastrowany lub nieklastrowany.
Podczas planowania indeksów istnieje pewne napięcie w związku między obsługą żądań wyboru a żądaniami aktualizacji. Pomimo faktu, że indeksy zwiększają wydajność kwerend, musisz płacić dodatkowe koszty za aktualizację indeksów podczas wstawiania i aktualizowania wierszy. Jednak niektóre operacje indeksowania są potrzebne do operacji zapisu. Aktualizacje i usuwanie muszą wybierać wiersze przed wykonaniem rekordu fizycznego, a dobre indeksy mogą przyspieszyć ten wybór, zwiększając jednocześnie wydajność samego rekordu.
Dodatkowych indeksów tabel nie należy mylić z widokami indeksowanymi. Edycja informacji Enterprise Edition zawiera funkcję denormalizacji, która tworzy indeks klastrowany, który rozciąga się na wiele tabel. Nieprawidłowe użycie indeksowanych widoków może znacznie zmniejszyć wydajność operacyjnych baz danych. W rozdziale 53 omówimy więcej widoków indeksowanych.
Indeksy klastrowane
Indeks klastrowy to narzędzie do scalania zbalansowanego drzewa ze stronami danych tabel, obsługujące dane w tym samym porządku fizycznym, co indeks (Rys. 50.1). Węzły liści w zbalansowanym drzewie indeksu są w rzeczywistości danymi strony danych.
Rys. 50.1. Indeks klastrowany łączy węzły liści stron indeksu ze stronami danych, zapewniając taką samą kolejność danych jak w indeksie
Indeks klastrowany może wpływać na wydajność w jeden ze sposobów opisanych poniżej.
? Gdy operacja wyszukiwania znajdzie wiersz za pomocą indeksu klastrowego, potrzebne dane są w nim zawarte. Dzięki temu kolumna jest używana do identyfikacji wierszy, potencjalnego kandydata na klucz podstawowy i idealnego dla indeksu klastrowego.
? Klastrowane indeksy grupują wiersze o takich samych lub podobnych wartościach w najmniejszą możliwą liczbę stron, zmniejszając w ten sposób liczbę wierszy danych potrzebnych do pobrania zestawu wierszy. Dlatego indeksy klastrowe są idealne dla kolumn, które są najczęściej używane do wybierania zakresów wierszy (na przykład kolumna OrderDetail. OrderlD).
/ Każda tabela ma jakiś porządek fizyczny. Jeśli tabela nie ma
Zwróć uwagę na indeks klastrowany, który ma postać sterty, tj. w sposób nieuporządkowany. W tym przypadku wiersze, gdy nie można ich zidentyfikować za pomocą kolumny klucza indeksu klastrowego, są identyfikowane przy użyciu wewnętrznego identyfikatora wiersza (RowiD) sterty. Ten identyfikator nie jest używany w zapytaniach.
Indeksy nieklastrowane
Nonclustered to indeks w postaci zrównoważonego drzewa, zaczynający się od węzła głównego i rosnący przez węzły pośrednie do węzłów liści. Węzły arkuszy wskazują bezpośrednio na wiersze stron danych (patrz Rysunek 50.1). Tabela SQL Server 2005 może mieć do 249 ^ indeksów klastrowanych, ale w praktyce nie spotkałem tabel, które wymagają więcej niż dziesięciu dobrze zaprojektowanych indeksów.
Indeks nieklastrowany można utworzyć na kolumnie obliczeniowej. Aby włączyć tworzenie indeksu lub widoku indeksowanego na kolumnie obliczeniowej, ustaw parametr quoted_identif ier na on.
Tworzenie indeksów
W oknie Eksploratora obiektów programu Management Studio istniejące indeksy dla każdej tabeli są wyświetlane w obszarze Bazy danych ^ Tabele ^ Węzły Indeksy. Właściwości każdego nowego lub istniejącego indeksu można dostosować w oknie dialogowym Właściwości indeksu (rys. 50.2). To okno otwiera się dla istniejącego indeksu, klikając jego nazwę prawym przyciskiem myszy i wybierając Właściwości z menu kontekstowego. Nowe indeksy są tworzone przy użyciu menu kontekstowego węzła Indeksy określonej tabeli.
W Management Studio indeksy są wyświetlane jako węzły w panelu Object Explorer. Korzystając z elementu Nowe indeksy indeksów w menu kontekstowym, możesz utworzyć nowy indeks. Formularz otwierający zawiera cztery zakładki.
? Ogólne. Zawiera nazwę indeksu, jego typ, właściwość unikalności, a także kolumny klucza.
? Opcje. Kontroluje zachowanie indeksu. Tutaj każdy indeks można wyłączyć i ponownie włączyć.
? Dołączone kolumny. Zawiera kolumny bez klucza, które służą jako opakowanie indeksu.
? Przechowywanie. Pozwala umieścić indeks w wybranej grupie plików.
Rys 50.2. Parametry dowolnego indeksu można ustawić w oknie dialogowym Właściwości indeksu narzędzia Management Studio.
Po otwarciu istniejącego okna opcji indeksu zawiera również dwie dodatkowe karty.
? Fragmentacja. Wyświetla szczegółowe informacje o stanie indeksu.
? Rozszerzone właściwości. Zawiera zaawansowane opcje zdefiniowane przez użytkownika.
Zmiany wprowadzone w oknie opcji indeksu można zastosować natychmiast, klikając przycisk OK; ich aplikacja może zostać odłożona na określony czas, a także uosabiać wszystkie zmiany w skrypcie. Aby to zrobić, możesz użyć ikon u góry okna.
W kodzie programu indeksy są tworzone za pomocą instrukcji CREATE INDEX. Poniższy przykład tworzy indeks klastrowy IxOrderld, oparty na kluczu obcym OrderlD tabeli OrderDetail:
CREATE CLUSTERED INDEX IxOrderlD ON dbo.OrderDetail (OrderlD);
I Aby wyodrębnić wyczerpujące informacje o indeksie za pomocą pro-
Kod S VS gram wykorzystuje następujące funkcje i widoki katalogu:
I * sysindexes, sysindex_columns, sysstats, sysstats_columns, sysdm_db_
* index_physical_stats, sysdm_index_operational_stats, sysindexkey_
właściwość i sysindex_col.
Indeks klastrowany jest tworzony automatycznie podczas definiowania klucza podstawowego. Aby usunąć indeks, użyj instrukcji DROP INDEX, która określa nazwę tabeli i nazwę indeksu, na przykład:
DROP INDEX OrderDetail.IxOrderlD
Utworzone dodatkowe indeksy nie utrzymują stanu efektywnego automatycznie, operacje aktualizacji informacji mogą fragmentować indeksy i zmieniać współczynnik wypełnienia ich stron. W tym rozdziale wspominamy tylko o potrzebie utrzymania indeksów, a następnie, jak w rozdziale 37, mówiliśmy o wymogach niezbędnych do zapewnienia wydajności indeksów.
Indeksy kompozytowe
Indeks złożony lub nieklastrowany, który zawiera wiele kolumn, nazywany jest złożonym. W praktyce większość wskaźników jest złożona. Jeśli używasz okna opcji indeksowych Management Studio, indeksy złożone są tworzone przez dodanie wielu kolumn na karcie Ogólne. Aby utworzyć indeks złożony w kodzie programu, należy go zadeklarować za pomocą instrukcji DDL CREATE INDEX po utworzeniu tabeli. Poniższy przykład tworzy indeks klastrowany złożony dla tabeli GUIDE GUIDE2:
CREATE CLUSTERED INDEX IxGuideName ON dbo.Guide (LastName, FirstName);
Kolejność kolumn w indeksie złożonym jest bardzo ważna. W celu uzyskania wszystkich zalet indeksu złożonego podczas wyszukiwania, ten ostatni musi zawierać najczęściej używane kolumny od lewej do prawej. Jeśli indeks złożony wygląda jak nazwisko, imię, to wyszukiwanie tylko przez imię nie będzie używać indeksu, a wyszukiwanie według nazwiska lub razem według nazwiska i imienia będzie.
Dodatkowy SQL Server 2005 może indeksować słowa w kolumnach za pomocą pełnotekstowej funkcji wyszukiwania informacji, którą omówiliśmy w rozdziale 13.
Klucze podstawowe
Klucz podstawowy może być początkowo zdefiniowany jako indeks klastrowany lub nieklastrowany. Aby jednak zmienić typ indeksu, należy usunąć lub ponownie utworzyć ograniczenie klucza podstawowego. Jest to dość trudne zadanie, jeśli istnieje wiele kluczy obcych lub jeśli tabela jest replikowana.
Więcej informacji na temat tworzenia kluczy podstawowych znajduje się w rozdziale 17.
informacje
Pokrycie indeksów
Pokrycie to każdy indeks, który w pełni zaspokaja potrzeby konkretnego zapytania SELECT. Ponieważ sam DBMS programu SQL Server wybiera indeks, który ma być używany do pobierania danych, istnieje możliwość, że zapytanie będzie wymagało wszystkich kolumn w indeksie nieklastrowanym. W tym przypadku relacyjne jądro pobierze dane ze stron indeksu i nigdy nie będzie czytać ze stron danych. Zmniejsza to znacznie operacje we / wy, ponieważ odczyt odbywa się z węższych tabel zawierających więcej danych na jednej stronie, a dodatkowy odczyt ze stron danych nie jest w ogóle wykonywany.
Przy projektowaniu indeksu pokrycia bardzo ważne jest zrozumienie, w jaki sposób indeks klastrowany wpływa na indeks nieklastrowany. Ponieważ indeks nieklastrowany musi mieć możliwość dostępu do stron danych, w węzłach liści musi zawierać kolumnę kluczową indeksu klastrowego (jeśli istnieje w samej tabeli). W tym przypadku kolumny indeksu klastrowego są uwzględniane na końcu każdego indeksu nieklastrowanego (nawet jeśli nie są widoczne bezpośrednio w oknie dialogowym właściwości indeksu). Na przykład, jeśli tabela ma indeks klastrowany w kolumnie ContactID i nieklastrowany w kolumnach LastName i FirstName, indeks nieklastrowany zawiera dane z kolumn LastName, FirstName (sorted) i ContactID (unassorted). Znajomość tego faktu jest bardzo ważna przy projektowaniu indeksów okładek.
Indeks pokrycia może wymagać uwzględnienia niektórych kolumn, które nie wymagają rdzenia relacyjnego, aby zidentyfikować wiersze, które są usuwane przez klauzulę WHERE. Te dodatkowe kolumny, niebędące kluczami, nie muszą być sortowane w zrównoważonym drzewie indeksów - są one uwzględniane wyłącznie w celu zwracania kolumn z zapytaniem SELECT.
Wadą pokrycia indeksów w poprzednich wersjach programu SQL Server było Nowe 4, które przy sortowaniu wszystkich kolumn miały być sortowane - nawet te z 2005 r., Które nie były używane w wyborze wierszy, ale były uwzględniane wyłącznie
do zwracania danych. Zdolność programu SQL Server 2005 do oddzielania kolumn innych niż klucze poprawiła wydajność operacji aktualizacji przy użyciu indeksów obejmujących, w których kolumny niebędące kluczami nie są sortowane. Jednocześnie, zmniejszając rozmiar drzewa indeksów zrównoważonych, poprawiła się wydajność pobierania danych przez zapytanie.
Aby określić kolumny niebędące kluczami w indeksach nieklastrowanych, używany jest parametr INCLUDE. Te kolumny niebędące kluczami nie są sortowane jako część struktury drzewa indeksów zrównoważonych i są uwzględniane tylko w swoich węzłach liści. Poniższy przykład tworzy indeks, który sortuje dane według kolumny OrderNumber i zawiera dane z kolumny OrderDate:
UTWÓRZ INDEKS NIEKREŚLONY IxOrderNumber ON dbo. [Order] (OrderNumber)
INCLUDE (OrderDate);
Dzięki dołączonym kolumnom jako wąskiej kopii szerokiej tabeli, którą SQL Server stale synchronizuje z oryginalną tabelą, żadne dodatkowe strony nie są wymagane do wyodrębnienia danych.
Dołączone kolumny nie są uwzględniane w ograniczeniach indeksu nieklastrowanego - 16 kolumn klucza i 900 bajtów. W rzeczywistości do indeksu pokrycia można dołączyć do 1023 kolumn niebędących kluczami. Kolumny ze szczególnie dużymi typami danych - XML, varchar (max), nvarchar (max) i varbinary (max) - mogą być również włączone jako część danych, nawet jeśli nie mogą działać jako kolumny klucza.
Kolumny tabeli nie można podzielić, jeśli uczestniczy w roli kolumny włączającej w indeksie pokrycia. Przed podzieleniem takiej kolumny usuń indeks pokrywający.
Lokalizacja grupy plików
Jeśli baza danych używa wielu nazwanych grup plików, indeks można utworzyć w jednym z nich. Aby to zrobić, indeks jest używany w parametrze
ON Grupa nazw plików.
CREATE NONCLUSTERED INDEX nazwa_indeksu ON Tabela (kolumny)
ON Nazwa pliku Nazwa;
Ta opcja może być przydatna do dystrybucji strumieni we / wy do szczególnie obciążonych baz danych. Na przykład, jeśli witryna sieci Web jest żądana od milionów użytkowników na minutę, jej strona główna korzysta z zapytania zawierającego dwie tabele i trzy indeksy, a dostępnych jest kilka podsystemów dyskowych, a następnie umieszczenie każdej tabeli i jej indeksów we własnym podsystemie dysku znacznie poprawi wydajność. Należy zauważyć, że indeks klastrowany musi znajdować się w tym samym podsystemie dyskowym z powiązaną tabelą, ponieważ ich strony są połączone.
Dodatkowa Fizyczna alokacja tabel i indeksów może być konfigurowana i głębiej informowana przy użyciu grup plików i partycji. Omówimy te tematy bardziej szczegółowo w rozdziale 53.
Opcje indeksu
Indeksy SQL Server 2005 mają kilka parametrów, w tym unikalność, alokację przestrzeni i parametry wydajności.
Unikalne indeksy
UNIKALNY INDEKS to coś więcej niż indeks z ograniczeniem unikalności - optymalizacja jest dostępna dla unikalnych indeksów. Klucz podstawowy lub ograniczenie unikalne automatycznie tworzy unikalny indeks.
W Management Studio unikalny indeks jest tworzony przy użyciu pola wyboru Unikalne na karcie Ogólne w oknie dialogowym opcji indeksu.
W kodzie programu unikatowość indeksu jest wskazywana przez dodanie słowa kluczowego UNIQUE do definicji:
UTWÓRZ UNIKALNY INDEKS OrderNumber ON [Order] (OrderNumber);
Współczynnik wypełnienia indeksu
Każdy indeks wymaga trochę wolnego miejsca w drzewie, aby wstawienie nowych rekordów nie prowadziło do restrukturyzacji indeksu. Gdy serwer musi wstawić nowy wpis do ukończonej strony, dzieli tę stronę na dwie części, a następnie zapisuje dwie pełne strony na dysk. Taki bieg rzeczy powoduje trzy potencjalne problemy: sama strona jest zepsuta, nowe strony nie są już spójne, a każda strona zawiera mniej informacji. W rezultacie, aby odczytać tę samą ilość danych, musisz wyświetlić więcej stron.
Ponieważ indeks jest drzewem zrównoważonym, każda strona musi zawierać co najmniej dwie linie. Współczynnik wypełnienia i parametr wskaźnika padu wpływają zarówno na strony pośrednie, jak i węzły liści, jak pokazano w tabeli poniżej. 50.1.
Tabela 50.1. Współczynnik wypełnienia i parametr wskaźnika padu
Współczynnik
napełnianie
Strony pośrednie
Węzeł liścia
0
Jeden bezpłatny wstęp
100% wypełnienia
1-99
Jeden wolny wpis lub objętość mniejsza niż współczynnik wypełnienia, jeśli ustawiony jest indeks padu
Objętość mniejsza niż współczynnik wypełnienia
100
Jeden bezpłatny wstęp
100% wypełnienia
Współczynnik wypełnienia dotyczy tylko węzłów liści indeksu, jeśli indeks padu parametrów nie jest do niego zastosowany. Ten parametr nakazuje serwerowi zastosowanie słabości współczynnika wypełnienia również do pośrednich poziomów drzewa zbalansowanego.
Najlepszy współczynnik wypełnienia zależy od przeznaczenia bazy danych i typu indeksu klastrowego. Jeśli baza danych jest przeznaczona głównie do wyodrębniania danych lub klucz podstawowy jest spójny, wówczas wysoki współczynnik wypełnienia zmaksymalizuje strony indeksu pakietu. Jeśli indeks klastrowany nie jest sekwencyjny (na przykład naturalny klucz podstawowy), tabela jest potencjalnie podatna na podział strony - w tym przypadku użyj niskiego współczynnika wypełnienia i często defragmentuj strony.
Dodatkowy współczynnik wypełnienia indeksu, gdy strony są podzielone, informacje stopniowo rozwijają swoją rolę. Aby przywrócić współczynnik wypełnienia, plan konserwacji powinien obejmować okresowe ponowne indeksowanie. Informacje na temat utrzymywania indeksów znajdują się w rozdziale 37.
W Management Studio współczynnik wypełnienia jest ustawiany w zakładce Opcje okna dialogowego opcji indeksu. W kodzie T-SQL parametry wypełniacza i padu indeksu są określane po poleceniu CREATE INDEX. Poniższy przykład tworzy indeks OrderNumber z 15% wolnego miejsca zarówno na węzłach liści, jak i na stronach pośrednich:
UTWÓRZ INDEKS NIEKREŚLONY IxOrderNumber ON dbo. [Order] (OrderNumber)
WITH FILLFACTOR = 85, PAD_INDEX = ON;
Zarządzanie blokowaniem indeksu, tworzenie ich w czasie rzeczywistym i ograniczanie równoległości paralelizmu indeksu to nowe parametry, które się pojawiają
2005 w wersjach SQL Server 2005. Dla parametrów pad_index, fillfactor, sort_in_db,
Składnia ignore_dup_key, statistiсs_norecompute i drop_existing została zmieniona. Nowa składnia wymaga włączenia wyrażenia = op. Ze względu na kompatybilność wsteczną stara składnia jest nadal obsługiwana, ale nie będzie tak w przyszłych wersjach.
Ograniczanie blokad i współbieżności
Tryb działania blokad w zapytaniach przy użyciu indeksów można kontrolować za pomocą parametrów allow_row_locks i allow_page_locks. Zazwyczaj te blokady są dozwolone.
Kolejność sortowania indeksu
SQL Server DBMS jest w stanie tworzyć malejące indeksy. Każde zapytanie używające klauzuli ORDER BY spowoduje sortowanie rosnące, chyba że słowo kluczowe DESC jest wyraźnie określone w tej klauzuli.
W instrukcji DDL CREATE INDEX słowa kluczowe AS C i DESC natychmiast podążają za nazwą kolumny.
Parametr ignorujący duplikaty kluczy
Parametr I GNORE_DUP_KEY nie wpływa na sam indeks, ale tylko na sposób, w jaki indeks będzie wpływał na zmiany danych.
Zwykle transakcje są atomowe. Oznacza to, że transakcja jest wykonywana lub nie, jako jednostka logiczna. Jednocześnie parametr ignorujący duplikaty kluczy kieruje transakcje wstawiania, aby potwierdzić operację dla wszystkich wierszy, które spełniają warunek unikalności indeksu, i ignorując wszystkie wiersze, które naruszają wymóg unikalności indeksu.
Ten parametr nie narusza wyjątkowości indeksu. Zduplikowane rekordy pozostają w tabeli, dlatego integralność bazy danych pozostaje nienaruszona, a atomowość transakcji jest zerwana. Pomimo faktu, że parametr ten znacznie ułatwia import bilionów wątpliwych linii, nie podoba mi się żaden parametr osłabiający cechy ACL (tj. Atomowości, integralności, izolacji i przeżywalności) bazy danych.
Poniższy przykład powtarza poprzednią instrukcję tworzenia indeksu unikalnego, ale używa tego parametru do ignorowania duplikatów kluczy:
UTWÓRZ UNIKALNY INDEKS OrderNumber ON [Order] (OrderNumber)
Z IGNORE_DUP_KEY = ON
Opcja usunięcia istniejącego indeksu
Parametr DROP_EXISTING instruuje serwer, aby usunął bieżący indeks i ponownie go utworzył. Może to prowadzić do pewnych zysków w porównaniu z odbudowywaniem istniejącego indeksu, jeśli jest on klastrowany, a tabela zawiera również indeksy nieklastrowane. Wynika to z faktu, że restrukturyzacja indeksu klastrowego prowadzi do automatycznej restrukturyzacji wszystkich indeksów nieklastrowanych.
Parametr zabraniający konwersji statystyk indeksu
SQL Server Query Optimizer zależy od statystyk dystrybucji danych przy określaniu, który indeks jest bardziej znaczący w określonych kryteriach wyszukiwania tabeli. Zwykle SQL Server automatycznie aktualizuje te statystyki. Jednocześnie, tuż przed zapytaniem, niektóre tabele mogą otrzymać dużą ilość danych, w którym to przypadku statystyki mogą być nieaktualne. Szczególnie w sytuacjach, w których statystyki wymagają ręcznej aktualizacji, używany jest parametr statistics_norecompute, który anuluje automatyczną aktualizację statystyk. Praktycznie dla wszystkich indeksów ten tryb pracy nie jest zalecany.
Sortuj w bazie danych tempdb
Parametr sort_in_tempdb = on zmienia metodę tworzenia indeksu, wymuszając użycie tempdb, a nie pamięci. Jeśli indeks zostanie trwale usunięty i ponownie utworzony, ten parametr może skrócić czas tworzenia indeksu. W większości indeksów ten parametr nie ma większego znaczenia, a poza tym nie jest obowiązkowy.
Wyłącz indeks
Dowolny indeks może być tymczasowo wyłączony. Aby to zrobić, po prostu usuń zaznaczenie pola wyboru Użyj indeksu w zakładce Opcje okna dialogowego opcji indeksu. W kodzie programu T-SQL efekt ten osiąga się, dołączając parametr DISABLE w instrukcji DDL ALTER INDEX:
ALTER INDEKS [IxContact] ON [dbo]. [Kontakt] DISABLE
W niektórych szczególnie intensywnych operacjach importowania szybciej jest usunąć indeks i odtworzyć go niż zaktualizować podczas wstawiania każdego wiersza. Zaletą wyłączenia indeksu jest to, że metadane indeksu są przechowywane w samej bazie danych i nie zależą od kodu programu, który odtwarza prawidłowy indeks.
Wyłączenie indeksu klastrowego powoduje wyłączenie Uwaga! cały stół.
Aby ponownie włączyć indeks, użyj polecenia ALTER INDEX. . . ODBUDUJ Z:
ALTER INDEKS [PK____ Kontakt 0BC6C43E]
ON [dbo]. [Kontakt]
ODBUDUJ Z (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
POZWALAĆ ZAMKI WIERSZOWE = WŁĄCZONE,
ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = OFF,
ONLINE = OFF)
Tworzenie indeksów bazowych
Nawet przed ustawieniem lokalizacja kilku indeksów jest łatwa do ustalenia. Te podstawowe wskaźniki są pierwszym krokiem w tworzeniu solidnego zestawu wskaźników. Poniżej znajduje się kilka zaleceń, które należy zastosować przed utworzeniem tych podstawowych wskaźników.
1. Utwórz indeks klastrowy dla każdej z tabel. W tabelach podstawowych taki indeks jest najlepiej zorganizowany w kolumnach najczęściej używanych do wybierania wierszy - klucz podstawowy jest doskonałym kandydatem. W tabelach pomocniczych, gdzie najczęściej wyodrębnianych jest wiele powiązanych wierszy, utwórz klucz klastrowy dla najważniejszego klucza obcego, który grupuje te powiązane kolumny razem.
2. Utwórz indeksy nieklastrowane dla kolumn każdego klucza obcego, z wyjątkiem indeksowanych w kroku 1. Używaj tylko wartości kluczy obcych jako kluczy indeksu.
3. Utwórz indeks jednokolumnowy dla wszystkich kolumn, które najprawdopodobniej pojawią się w klauzulach WHERE, ORDER BY lub GROUP BY.
Chociaż proponowany plan indeksowania jest daleki od doskonałości, oferuje wstępny kompromis między brakiem indeksów a indeksami dostosowanymi. Zasadniczo można go uznać za podstawowy poziom wydajności w odniesieniu do późniejszego strojenia indeksu.
Źródło: Nielsen, Paul. Microsoft SQL Server 2005. Biblia użytkownika. : Trans. z angielskiego - M .: ID Williams ”, 2008. - 1232 s. : il - Paral. sikorka angielski
