sys.dm_db_index_usage_stats – czy nasz indeks jest używany?

Indeksy w środowisku bazodanowym są powszechnym sposobem na przyspieszenie działania naszych zapytań. Tak jak we wszystkim również i w tym wypadku ważne jest aby nie przesadzić ponieważ nadmiar indeksów może być nawet bardziej szkodliwy niż ich brak. Tutaj pojawia się pytanie skąd wiadomo, które indeksy są używane przez zapytania, a co za tym idzie, które z nich są użyteczne? Używając widoku zarządczego (ang. Data Management View – DMV) sys.dm_db_index_usage_stats o którym powiemy w dalszej części niniejszego artykułu.

Indeksy mogą być użyteczne w pobieraniu danych natomiast mogą spowalniać operacje m.in wstawienia danych. Warto więc aby nasza baza miała tylko te indeksy, które są w jakikolwiek sposób używane. Posłuży nam do tego wspomniany widok sys.dm_db_index_usage_stats. Aby go przeanalizować użyjemy bazy Adventure Works i tabeli Production.Product. Aby nasza analiza miała sens musimy zdawać sobie sprawę z dwóch istotnych faktów, przede wszystkim musimy wiedzieć, że dane w widokach są czyszczone po restarcie systemu lub gdy baza zostanie odłączona i podłączona na nowa (detach – attach), a po drugie w widoku sys.dm_db_index_usage_stats zawarte są tylko dane na temat używanych indeksów, nieużywane indeksy nie są wymienione. Dlatego też poniższe zapytanie zwraca puste kolumny z charakterystykami użycia i dlatego też omawiany widok został zewnętrznie połączony z  sys.indexes aby pokazać wszystkie indeksy a nie tylko te używane:

Result

Omówmy sobie jakie kolumny otrzymaliśmy w rezultacie:

  • name – nazwa indeksu
  • type_desc – typ indeksu
  • is_unique – czy jest unikalny
  • user_seeks – ilość operacji seek wykonanych na indeksie przez zapytania użytkowników od czasu uruchomienia serwera
  • users_scans – ilość operacji scan wykonanych na indeksie przez zapytania użytkowników od czasu uruchomienia serwera
  • user_lookups – ilość operacji lookup wykonanych na indeksie przez zapytania użytkowników od czasu uruchomienia serwera
  • last_user_seek – data ostatniego wystąpienia operacji seek
  • last_users_scan – data ostatniego wystąpienia operacji scan
  • last_user_lookup – data ostatniego wystąpienia operacji lookup

Te informacje powinny nam wystarczyć aby poprawnie zdiagnozować to czy nasze indeksy są używane czy też nie. Oczywiście to nie wszystkie możliwe kolumny – istnieją również system_seek wskazujący operacje seek wykonywane wewnętrznie np. przy budowaniu statystyk, jednak nie jest to istotne z punktu użyteczności indeksów dla zapytań użytkowników. Aby przetestować działanie wykonajmy kilka  zapytań aby sprawdzić poprawność zwracanych przez widok danych.

Pierwsze zapytanie pobiera wszystkie kolumny tabeli bez żadnej filtracji. Ze względu na fakt, iż zapytanie nie jest selektywne i pobiera wszystkie wiersze optymalizator powinien zdecydować się na wykonanie operacji Scan na całej klastrowanej tabeli czyli w planie zapytania powinniśmy zobaczyć operację Clustered Index Scan co potwierdza poniższy zrzut ekranowy.

Clustered index Scan

Następnie musimy uruchomić zapytanie na widoku aby sprawdzić czy fakt wykonania operacji Scan został odnotowany – jak widać poniżej tak właśnie się stało.

Index stats

Podobnie możemy wywołać seek oraz lookup

Index Seek

Index Key lookup

Jak można zauważyć wszystkie te fakty zostały zwrócone przez nasze zapytanie.

Result

Dzięki temu bardzo prostemu widokowi na metadane SQL Server możemy na bieżąco sprawdzać użyteczność naszych indeksów i usuwać te, które nie są używane. Jeżeli chcemy składować tego typu dane możemy stworzyć harmonogram i zadanie SQL Server Agent, które będzie zrzucało te dane cyklicznie do wskazanego przez nas miejsca.

W ten sposób można w bardzo prosty sposób monitorować indeksy – ale czy to wszystko? Oczywiście, że nie! Istnieje jeszcze jeden niezwykle istotny fakt, którego omawiany DMV nie odnotowuje. Dla jasności obrazu warto zrestartować usługę SQL Server aby nasze liczniki się wyzerowały.

Jak widać na poniższym planie zapytania, optymalizator musiał przeskanować całą tabelę (indeks klastrowany) aby zwrócić dane.

Index Seek

Natomiast teraz usuńmy indeks unikalny, który istnieje na kolumnie ProductNumber…

… a następnie wykonajmy powyższe zapytanie z DISTINCT raz jeszcze. Plan wykonania został przedstawiony poniżej.

Result

Zauważyliście różnicę? Wcześniej nie występował operator Sort (Distinct Sort), poza tym teraz zajmuje on, aż 59% całego zapytania! Zachowanie to jest warte zapamiętania ponieważ indeks, który usunęliśmy zapewniał optymalizator, że wartości w ramach pola ProductNumber są unikalne – a co za tym idzie kombinacja pól ProductNumber oraz Style również jest unikalna. SQL Server wie o tym fakcie właśnie dzięki indeksom unikalnym,
a ten fakt nie jest odnotowany w widoku sys.dm_db_index_usage_stats – naprawdę warto o tym pamiętać. Mam nadzieję, że niniejszy artykuł okazał się dla was przydatny
i interesujący. Pozdrawiam!

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Latest posts by Adrian Chodkowski (see all)

1 Comment

  1. Gość

    Cóż mogę powiedzieć? Bardzo interesujący i bardzo przydatny wpis. Wyjaśnił mi kilka kluczowych spraw, za co szczerze dziękuję.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *