Sprawdź, co robią Twoje indeksy – sys.dm_db_index_usage_stats

Bieganie, sport, który stał się bardzo popularny, nie ma, co się dziwić. Dostarcza sporą dawkę endorfin. Dzięki temu paradoksalnie mimo zmęczenia czujemy się pełni energii. To uczucie wielu ludzi nakręcą, biegają i  częściej. Takie podejście może powodować pewne kłopoty bo więcej nie znaczy lepiej.

Pewnie czytasz i się zastanawiasz, co ma bieganie do baz danych? Bo raczej nie o bieganiu wpisu się spodziewasz. Odpowiadam, z bazami jest trochę jak z bieganiem. Przesadzając z ilością treningów możemy doprowadzić do gorszych rezultatów i samopoczucia, przesadzając z indeksami możemy doprowadzić do spadku wydajności. Żeby móc to jednak stwierdzić, niezbędne są dane, które trzeba zbierać i analizować.

Pomiary i ich gromadzenie

W sporcie kwestią pomiaru i ich zbierania musimy zająć się sami, z SQL Server jest prościej, bo pewne rzeczy robi za nas. Wszelkie statystki użycia dostarczane są przez dynamiczny  widok sys.dm_db_index_usage_stats , którym gromadzi na prawdę sporą ilość danych. Jednak w tym wpisie skupie się na metrykach związanych z operacjami:

  • seeks – przeszukiwanie indeksów w celu pobrania wierszy z klastrowych lub nieklastrowych indeksów. W bazach OLTP oczekujemy jak największej liczby wyszukań na indeksach z jednego prostego powodu, wyszukiwanie jest mniej kosztowne.
  • scans – skanowanie wykonuję pobranie wszystkich wierszy z indeksu w celu zwrócenie wyniku. Skanowanie jest efektywne głównie dla małych tabel.
  • lookups – występuję w sytuacji gdy zapytanie korzysta z indeksu nieklastrowego które nie zawiera wszystkich danych z klazuli SELECT. SQL Server zmuszony jest sięgnąć do indeksu klastrowego po tzw KeyID lub do sterty po tzw Row ID, w takiej sytuacji wykonywana jest  w operacja pętli Lookup. Tego typu operacje nie są porządane i jednym z sposobów jest tworzenie indeksu pokrywającego.
  • updates – wstawianie nowych danych, modyfikowanie istniejących

Te cztery metryki gromadzone są dla operacji systemowych (system) i użytkownika (user). Jak można domyślić się, wartości dla users związane są z operacjami na indeksach niezbędnych to wykonania poleceń użytkownika: pobranie, modyfikacje, wstawianie danych. Natomiast wartości dla system_seeks, system_scans i tak dalej, zliczane są przy operacjach na indeksach związanych z ich utrzymaniem, np. przebudowa indeksów oraz odświeżania statystyk.

Zobaczmy jak to wygląda w praktyce.  W celach demonstracji ograniczyłem się tylko do indeksów jednej tabeli z bazy AdventureWorks2012. Poniższe zapytanie pozwala wykonać migawkę widoku przed i po wykonanym zapytaniu. To po to, aby móc wyłapać zmiany liczników dla poszczególnych operacji.

W tym miejscu warto dodać, że restart instancji SQL Server czyści widok  sys.dm_db_index_usage_stats.

Operacje użytkownika na indeksach

Mało selektywne pobranie danych po kluczu z indeksu klastrowego kończy się skanowaniem indeksu co widac na poniżej.

Kolejny przykład, gdzie oczekujemy przeszukania indeksu nieklastrowego. Faktycznie ma to miejsce, jednak z również z mało pożądaną operacją Nested Loop. Jest to niezbędne w celu pobrania brakujących wartości z indeksu klastrowego które nie byłby dostępne w indeksie nieklastrowym.

Były polecenia pobrania danych, to kolej na INSERT. Dodanie jednego rekordu spowoduję operację user_updates na wszystkich indeksach w tej konkretnej tabeli.

Jeszcze ciekawiej wygląda to dla poleceń DELETE. Usunięcie danych powoduję jedną operację więcej. Mowa o opracji user_seeks na klastrowym indeksie niezbędna do znalezienia usuwanego rekordu oraz 3 operacje users_updates związane z naniesieniem zmian na indeksach tak jak przy dodawaniu danych.

Było wstawianie, usuwanie danych, zostało tylko modyfikowanie. Podobnie jak przy operacji usunięcia, w pierwszym kroku niezbędne jest pobierane rekordu stąd wyszukiwanie na indeksie klastrowym. Przykładowy UPDATE zmienia dwie wartości. Ta na indeksie klastrowym raczej nie wymaga tłumaczenia. Druga jednak wynika zmian na kolumnie ROWGUID, która jest kolumną indeksu nieklastrowego. W ten sposób SQL Server wykonał 3 operacje na indeksach.

Operacje systemowe na indeksach

Tak jak wspomniałem wyżej, operacje związane z utrzymaniem indeksów gromadzone są do kolumn z prefiksem system. Operacje przebudowy indeksu wiążą się z pełnym skanowaniem indeksów.

Update statystyk również wykonuję skanowania indeksów, co widać po zwiększonych licznikach w kolumnie system_scans.

Jako ciekawostkę dodam, że nie udało mi się zmusić SQL Server do zwiększenia wartości dla systemowych operacji w kolumnach innych niż wyżej wspomniany system_scans.

Podsumowanie

Wyżej pokazałem gdzie i kiedy SQL Server gromadzi dane na temat użycia indeksów i jak to wygląda dla różnych operacji. Mimo małej ilości indeksów w przykładowych tabelach, już można przypuszczać, że z wzrostem ilości indeksów nasze operacje DML staną się bardziej kosztowne. Jedna operacja wstawienia pociąga w tle operacje naniesienia zmian niekiedy na wszystkich indeksach dla tabeli.

Stąd tak ważne jest trzymanie w ryzach ilość indeksów w bazach danych nie tylko z względu na ilość zajmowanego miejsca, ale również z względu na wpływ na wydajność. Cykliczne przyglądanie widoku sys.dm_db_index_usage_stats jest jak najbardziej wskazane. Jeśli widzimy, indeksy, na których nie mamy żadnych operacji user_seeks, user_scans a dużą liczbę user_updates to jest to najprawdopodobniej idealny kandydat do usunięcia. Piszę najprawdopodobniej, ponieważ może się okazać, że jest to indeks używany rzadko a nasz serwer był restartowany.

Jak sobie z tym poradzić i jak usuwać indeksy, aby było po tym więcej korzyści niż problemów napiszę wkrótce. Jeśli  masz pytania, spostrzeżenia lub swoje doświadczenia w tym temacie, zapraszam do komentowania bądź pisania na maila. Tymczasem idę pobiegać 🙂

Z pasją poświęcam czas na zdobywanie wiedzy w zakresie szeroko rozumianej Data Platform. Zachwycony językiem skryptowym Windows PowerShell. Swoją wiedzę, doświadczenia i spostrzeżenia opisuję na blogu.

Leave a Reply

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *