sys.dm_db_index_usage_stats pokazuje, jak często dany indeks jest wykorzystywany do odczytu i zapisu.
Rola widoku sys.dm_db_index_usage_stats
sys.dm_db_index_usage_stats jest dynamicznym widokiem zarządzania w silniku Microsoft SQL Server. Udostępnia statystyki użycia indeksów przez obiekty znajdujące się w bazach danych dostępnych dla danej instancji. Wiersz widoku opisuje użycie konkretnego indeksu lub sterty w określonej bazie danych i tabeli. Dane pozwalają sprawdzić, czy indeks jest wykorzystywany podczas odczytu oraz jak często SQL Server modyfikuje go przy operacjach zapisu.
Widok rejestruje między innymi liczbę wyszukiwań klucza indeksu w kolumnie user_seeks, liczbę odczytów wykonywanych przez skanowanie indeksu w user_scans oraz liczbę odwołań pobierających pojedyncze wiersze lub zakresy przez user_lookups. Operacje modyfikujące dane są zliczane w user_updates. Dla diagnostyki systemowych operacji dostępne są analogiczne kolumny z prefiksem system_, a czas ostatniego wystąpienia poszczególnych typów operacji znajduje się w kolumnach takich jak last_user_seek, last_user_scan, last_user_lookup i last_user_update.
Jak interpretować statystyki odczytu i zapisu
Wartość user_seeks oznacza liczbę wyszukiwań wykonywanych przez zapytania użytkownika, lecz sama w sobie nie określa czasu trwania ani opłacalności tych operacji. Indeks może mieć wiele wyszukiwań, a mimo to nie być głównym źródłem poprawy wydajności, jeżeli zwracane zapytania są małe lub alternatywny plan wykonania byłby równie szybki. Z kolei user_scans może wskazywać na skanowanie całego indeksu albo jego większego zakresu, ale nie zawsze oznacza błąd. Skan jest prawidłowy między innymi wtedy, gdy zapytanie musi przeczytać znaczną część danych.
user_lookups jest często obserwowane przy indeksach nieklastrowanych, gdy plan wykonania po znalezieniu klucza musi pobrać dodatkowe kolumny z indeksu klastrowanego albo ze sterty. Duża liczba takich operacji może wskazywać na kosztowne wyszukiwania klucza, jednak jej ocena wymaga porównania z rzeczywistymi planami wykonania i liczbą zwracanych wierszy. user_updates rośnie podczas operacji INSERT, UPDATE i DELETE, ponieważ zmiana danych może wymagać aktualizacji struktur indeksowych. Wysokie aktualizacje przy zerowych lub bardzo małych odczytach są sygnałem, że indeks może generować koszt zapisu bez potwierdzonej korzyści dla zapytań.
Przykładowe zapytanie diagnostyczne
Do połączenia statystyk z nazwami baz, tabel i indeksów można użyć katalogowych widoków systemowych. Przykładowe zapytanie dla bieżącej bazy danych może wyglądać następująco: SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name, OBJECT_NAME(s.object_id) AS table_name, i.name AS index_name, i.type_desc, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates, s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id WHERE s.database_id = DB_ID() ORDER BY s.user_updates DESC;. Warunek z DB_ID() ogranicza wynik do aktualnie używanej bazy, a sortowanie według user_updates pokazuje indeksy często modyfikowane.
Przed usunięciem indeksu nie wolno opierać decyzji wyłącznie na jednym odczycie tego widoku. Statystyki nie zawierają pełnej historii i mogą nie obejmować okresu, w którym uruchamiane są rzadkie raporty, zadania kwartalne albo procedury konserwacyjne. Analizę należy prowadzić przez ustalony czas, zapisać kolejne próbki i zestawić je z definicjami indeksów w sys.indexes, kolumnami kluczowymi oraz dołączonymi w sys.index_columns. Przydatne jest także sprawdzenie planów wykonania i obciążenia zapytań, ponieważ niski licznik użycia nie dowodzi automatycznie, że indeks jest zbędny.
Ograniczenia danych i zastosowanie w diagnostyce
Statystyki sys.dm_db_index_usage_stats są gromadzone od momentu uruchomienia instancji SQL Server lub od chwili wystąpienia zdarzenia, które je resetuje. Wartości mogą zostać wyzerowane po ponownym uruchomieniu silnika, a także po odłączeniu lub zamknięciu bazy danych. Z tego powodu kolumna last_user_update musi być interpretowana razem z czasem startu instancji oraz okresem obserwacji. Brak wiersza dla indeksu oznacza, że w bieżącym okresie zbierania statystyk nie zarejestrowano operacji objętej tym widokiem; nie oznacza, że indeks nigdy nie był używany.
Dostęp do danych DMV podlega uprawnieniom wymaganym przez konkretną wersję i konfigurację SQL Server. Przy diagnostyce należy również rozróżnić indeksy użytkownika od indeksów wykorzystywanych przez system, korzystając z odpowiednich kolumn user_ i system_. Widok służy do obserwacji zachowania obciążenia, a nie do bezpośredniego pomiaru fragmentacji, rozmiaru indeksu ani jakości statystyk optymalizatora. Te zagadnienia wymagają odrębnych widoków i procedur, takich jak sys.dm_db_index_physical_stats dla kondycji fizycznej struktur. W praktyce DMV jest więc jednym z elementów diagnostyki: pomaga znaleźć indeksy intensywnie odczytywane, często aktualizowane lub potencjalnie pomijane, lecz każdą zmianę schematu należy potwierdzić analizą planów, obciążenia i wymagań aplikacji.
