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
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !
a z innej strony - co gdybym ciąg znaków chciał mieć rozbity nie na wiersze a na kolumny? Czyli ciąg ABCD: 1. kolumna: A, 2. kolumna: B, 3. kolumna: C, 4 kolumna: D?
Ciekawy artykuł.
Czy można za pomocą EF wysłać swoje zapytanie?
Czy lepiej do tego użyć ADO.net i DataTable?