sys.dm_db_index_usage_stats pokazuje, jak często dany indeks jest wykorzystywany do odczytu i zapisu.
Co oznacza widok sys.dm_db_index_usage_stats w SQL Server
sys.dm_db_index_usage_stats jest dynamicznym widokiem zarządzania w Microsoft SQL Server. Zawiera statystyki użycia indeksów zarejestrowane przez uruchomiony silnik bazy danych. Dla każdego rozpoznanego indeksu można sprawdzić między innymi liczbę operacji wyszukiwania po strukturze indeksu, skanowania indeksu, wyszukiwania klucza w indeksie klastrowanym lub stosie oraz modyfikacji danych wymagających aktualizacji indeksu. Widok pozwala więc porównać korzyści odczytowe indeksu z kosztem, jaki jego utrzymanie powoduje podczas operacji zapisu.
Najczęściej analizowane kolumny to database_id, object_id, index_id, user_seeks, user_scans, user_lookups, user_updates oraz odpowiadające im kolumny dotyczące operacji systemowych. Dostępne są także znaczniki czasu, takie jak last_user_seek, last_user_scan, last_user_lookup i last_user_update. Wartość user_updates oznacza liczbę operacji aktualizacji indeksu, a nie liczbę zmodyfikowanych wierszy. Jedna instrukcja może zatem wpłynąć na wiele rekordów, ale statystyka nie jest prostym licznikiem zmienionych wierszy.
Jak SQL Server rejestruje operacje indeksu
Wartości w tym widoku są zwiększane w czasie pracy konkretnej instancji SQL Server. user_seeks obejmuje wyszukiwania wykonane przez zapytania użytkownika, user_scans pełne lub zakresowe skanowania indeksu, a user_lookups operacje pobierania dodatkowych danych z tabeli bazowej po znalezieniu wierszy przez indeks nieklastrowany. user_updates rośnie, gdy operacje INSERT, UPDATE albo DELETE wymagają modyfikacji danej struktury indeksowej. Osobno raportowane są operacje systemowe wykonywane przez mechanizmy silnika.
Statystyki nie są trwałym dziennikiem historycznym. Mogą zostać wyzerowane między innymi po ponownym uruchomieniu usługi SQL Server, a także w sytuacjach związanych z odłączeniem lub ponownym dołączeniem bazy danych. Z tego powodu interpretacja liczb wymaga znajomości czasu ostatniego restartu oraz czasu działania konkretnej bazy. Niska wartość user_seeks nie musi oznaczać, że indeks jest niepotrzebny, jeżeli pomiar trwał krótko albo obejmował okres bez wykonywania raportów, zadań okresowych i rzadkich operacji biznesowych.
Przykładowa analiza danych z DMV
Proste zestawienie indeksów można wykonać zapytaniem uruchomionym w bazie, której obiekty mają zostać przeanalizowane:
SELECT OBJECT_SCHEMA_NAME(s.object_id, s.database_id) AS schema_name, OBJECT_NAME(s.object_id, s.database_id) AS table_name, i.name AS index_name, s.index_id, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates, s.last_user_seek, s.last_user_scan, s.last_user_update FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_id WHERE s.database_id = DB_ID() ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC;
W praktyce warto analizować także indeksy, dla których nie wystąpiła jeszcze żadna operacja odczytu, lecz samo znalezienie takiego wiersza nie uzasadnia automatycznego usunięcia indeksu. Widok zawiera tylko statystyki zebrane dla indeksów, które zostały zaobserwowane przez silnik, dlatego brak wiersza nie zawsze oznacza brak indeksu. Przed zmianą schematu należy porównać wynik z definicją zapytań, planami wykonania, statystykami obciążenia oraz zależnościami wynikającymi z kluczy podstawowych i ograniczeń unikalności. Indeks może być używany rzadko, ale mieć znaczenie dla konkretnego raportu, procedury składowanej lub okresowego procesu.
Znaczenie DMV podczas diagnostyki wydajności
sys.dm_db_index_usage_stats jest narzędziem diagnostycznym, a nie samodzielnym mechanizmem podejmowania decyzji o przebudowie albo usunięciu indeksu. Duża liczba aktualizacji przy małej liczbie operacji odczytu może wskazywać na kosztowną strukturę, lecz trzeba uwzględnić charakter obciążenia i okres obserwacji. Z kolei wysoka liczba skanów może być prawidłowa dla zapytań zwracających znaczną część tabeli albo sygnalizować niedopasowanie indeksu do predykatów, kolejności kolumn i sortowania. Wysoka liczba wyszukiwań nie dowodzi natomiast, że indeks jest optymalny, ponieważ nie pokazuje czasu wykonania ani liczby odczytów logicznych.
Podczas diagnostyki serwerowej wynik tego widoku należy zestawić z planami wykonania zapytań, widokiem sys.dm_db_index_operational_stats, informacjami o fragmentacji oraz statystykami oczekiwań i użycia zasobów. Taka korelacja pomaga rozróżnić indeks faktycznie obciążający zapisy od indeksu, który jest potrzebny w rzadkich, ale krytycznych operacjach. Przed modyfikacją konfiguracji lub schematu należy wykonać pomiar bazowy, zapisać moment rozpoczęcia obserwacji i powtórzyć analizę po reprezentatywnym cyklu pracy. W serwisowej diagnostyce komputerów i środowisk SQL Server istotne jest również sprawdzenie, czy problem wynika z samego indeksu, czy z ograniczeń pamięci, nośnika danych, blokad albo nieaktualnych statystyk.
