Spis treści:

Kategoria:IndeksySQL Server


Pobieranie informacji o rozmiarach indeksów

Po co nam takie informacje?

Skrypty pobierające rozmiary indeksów na danej tabeli nie są stworzone dla zasady. Powstały one w konkretnym celu. Krótki rzut oka na rozmiary indeksów, czyli algorytmicznie rzecz ujmując B-drzew, daje nam ogólny pogląd na zapotrzebowanie pamięciowe SQL Servera (przynajmniej z punktu widzenia indeksów). Nie trzeba chyba wyjaśniać, że mniejsze indeksy są szybsze, a więc lepsze. Mniejszy indeks to mniejsza ilość stron pamięci jakie muszą zostać pobrane/oczytane w celu wykorzystania tego indeksu. Co prawda rozmiar indeksu w porównaniu z rozmiarem samej tabeli nie jest zwykle duży, ale już kilka indeksów na tej samej tabeli złożonych z kilku kolumn mogą spowodować spadki wydajności. W przypadku dużych struktur warto pomyśleć, czy nie da się w odpowiedni sposób zastosować sprytnych indeksów częściowych. Przykładem z życia wziętym jest indeks na hipotetycznej kolumnie daty zwrotu, w której kilkadziesiąt procent wartości jest równa NULL. Stosując indeks częściowy (partial index) można wrzucić do niego tylko te klucze, które mają wartość różną od NULL. Szukając rekordów rozważamy tylko te, które mają tam jakąkolwiek datę, korzystając z tej daty jako filtra. Nie ma potrzeby, aby umieszczać w indeksie NULLe.

Trochę rozwlekły wstęp, bo myślę, że każdy, kto tutaj trafił ma nieco inną potrzebę. Przejdźmy zatem do skryptu.

Pobieranie rozmiaru indeksu w stronach pamięci

SELECT i.name AS [Index name],s.used_page_count [Index size in pages]
FROM sys.dm_db_partition_stats s
JOIN sys.indexes i ON s.[object_id] = i.[object_idAND s.index_id = i.index_id
WHERE s.[object_id] = object_id('dbo.Tabela')
ORDER BY i.name

Wynikiem tego skryptu są pary: nazwa indeksu - rozmiar indeksu. Jednostką rozmiaru są strony pamięci, a całość posortowana jest po nazwie indeksu. Parametrem jest nazwa tabeli, w tym przypadku dbo.Tabela.

Wykorzystanie statystyk indeksu

Druga metoda zwraca wyniki w takiej samej formie, ale korzysta z nieco innej techniki. Tym razem dane pobierane są z funkcji systemowej dm_db_index_physical_stats. Skrypt może wyglądać tak jak poniżej:

SELECT i.name [Index name], SUM(page_count) [Index size in pages]
FROM sys.dm_db_index_physical_stats(
    db_id(), object_id('dbo.Przynaleznosc'), NULLNULL'DETAILED') s
JOIN sys.indexes i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name

Metoda ta jest znacznie wolniejsza niż poprzednia, ale daje nam znacznie więcej informacji. Można sprawdzić między innymi to, jaka część danego indeksu znajduje się na konkretnym poziomie drzewa (B-drzewa), w jakim stopniu indeks jest wypełniony, poziom fragmentacji, ilość rekordów i wiele innych. Jeżeli ktoś będzie potrzebował szerszej wiedzy na temat indeksów i ich strukturze w pamięci, najprawdopodobniej tutaj należy skierować swoje poszukiwania.

Kategoria:IndeksySQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
To samo pytanie co wyżej. Mam za zadanie dodać kolumnę do istniejącej tabeli łącząc obie inne kolumny ze sobą, ale nie mam pojęcia jak za to się zabrać
działa :) tylko była literówka :)
Podziękował. Trochę późno, po 8 latach, ale dzięki za testy (rozumiem że dla SQL2012 robione). Tak się właśnie zastanawiałem ile złego czynię stosując czasem __(max).
Super robota, korzystając z innych internetowych kalkulatorow po prostu wątpiłem w ich prawdomówność, w końcu trafiłem tutaj i wynik w końcu jest wiarygodny. 40 km w 2 h 810 kcal, ciekawostka: na fitatu wyliczyło mi 5700 kcal 😊 najlepiej będzie chyba jak kupię zegarek sportowy.
Wielkie dzieki za solidne wyjasnienia tematu.