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:

SELECT 
	i.name,
	i.type_desc,
	i.is_unique,
	us.user_seeks,
	us.user_scans,
	us.user_lookups,
	us.user_updates,
	us.last_user_seek,
	us.last_user_scan,
	us.last_user_lookup,
	us.last_user_update
FROM 
	sys.indexes i
LEFT JOIN 
	sys.dm_db_index_usage_stats us
ON
	i.object_id=us.object_id AND i.index_id=us.index_id
WHERE
	i.object_id=OBJECT_ID('[Production].[Product]')

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.

SELECT ProductID ,
       Name ,
       ProductNumber ,
       MakeFlag ,
       FinishedGoodsFlag ,
       Color ,
       SafetyStockLevel ,
       ReorderPoint ,
       StandardCost ,
       ListPrice ,
       Size ,
       SizeUnitMeasureCode ,
       WeightUnitMeasureCode ,
       Weight ,
       DaysToManufacture ,
       ProductLine ,
       Class ,
       Style ,
       ProductSubcategoryID ,
       ProductModelID ,
       SellStartDate ,
       SellEndDate ,
       DiscontinuedDate ,
       rowguid ,
       ModifiedDate 
FROM Production.Product

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.

 

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.

Podobnie możemy wywołać seek oraz lookup

--index seek
SELECT ProductID,
	   ProductNumber
FROM 
	Production.Product	
WHERE
	ProductID=1
	
--index key lookup
SELECT ProductID,
	   ProductNumber,
	   Style
FROM 
	Production.Product	
WHERE
	ProductNumber='BC-R205'

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

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.

SELECT DISTINCT 
		ProductNumber,
		Style
FROM
	Production.Product

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

 

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

DROP INDEX [AK_Product_ProductNumber] 
ON [Production].[Product]

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

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!