Pobieranie rozmiarów tabel w SQL Server

Gdzie są moje dane

Każda baza danych ma tendencję do rozrastania się. W sumie po to one są - aby gromadzić dane. Administrator baz danych powinien mieć możliwość sprawdzenia, które z tabel rozrastają się ponad normę. Przecież właśnie tam, gdzie tabele są największe, zwykle pojawia się problem wydajności. Z drugiej strony, duże tabele to duże pole do popisu dla wszelkich metod optymalizacji. Pomijając potrzeby, przyjrzyjmy się metodom pobierania rozmiarów table w całej bazie.

Wbudowana procedura sp_spaceused

Najprostszą metodą sprawdzenia rozmiarów jednej tabeli jest użycie użytej w nagłówku procedury sp_spaceused. Bezparametrowe wywołanie tej procedury pobierze nam informacje o bazie danych. Jeżeli w pierwszym parametrze przekażemy nazwę tabeli, dostaniemy szczegółowe informacje o tej tabeli. Jeżeli potrzebne nam informacje o tylko jednej tabeli, można to zrobić tak:

sp_spaceused 'Logi'

Otrzymamy, oczywiście w zależności od tabeli, wynik podobny do poniższego:

Gdybyśmy potrzebowali informacji o wszystkich tabelach, należałoby te wszystkie dane zebrać:

CREATE TABLE #tableSizes (
    [name] varchar(128),
    [rows] char(11),
    reserved varchar(18),
    data varchar(18),
    index_size varchar(18),
    unused varchar(18)
)

INSERT INTO #tableSizes EXEC sp_MSforeachtable 'sp_spaceused [?]'
SELECT * FROM #tableSizes
DROP TABLE #tableSizes

Tym razem otrzymamy listę wszystkich tabel. Przykładowy wynik będzie wyglądał następująco:

Przedstawiony sposób wymaga tworzenia tabeli tymczasowej oraz użycia nieudokumentowanej procedury sp_MSforeachtableFunkcja nie ma oficjalnej dokumentacji, ale jest wśród doświadczonych administratorów, moim zdaniem, powszechnie znana. Jak na nieudokumentowaną procedurę pojawia się ona wyjątkowo często w różnych skryptach. Nie zmienia to faktu, że należy takich procedur unikać i, o ile istnieje jakaś alternatywa, korzystać z oficjalnych interfejsów.. Zbiór wynikowy jest też trudny w obróbce. Wartości liczbowe nie są przekazywane w postaci typów liczbowych - są to typy varchar i char. Sprawia to, że operacje sortowania będą wykonywane na tekście - wartość tekstowa 9 będzie zatem po wartości 10. O ile kolumnę rows można łatwo przekształcić, o tyle pozostałe kolumny zawierają przyrostek w postaci KB. Konwersja na typ int wymaga więc odcięcia wspomnianej końcówki:

CREATE TABLE #tableSizes (
    [name] varchar(128),
    [rows] char(11),
    reserved varchar(18),
    data varchar(18),
    index_size varchar(18),
    unused varchar(18)
)

INSERT INTO #tableSizes EXEC sp_MSforeachtable 'sp_spaceused [?]'
SELECT * FROM #tableSizes
ORDER BY CAST(SUBSTRING(reserved, 1, LEN(reserved)-3) AS int) DESC
DROP TABLE #tableSizes

W pokazanym powyżej przykładzie rekordy zostaną posortowane malejąco według kolumny reserved - na górze te rekordy, które zarezerwowały najwięcej miejsca. Nie jest to rozwiązanie eleganckie. Gdybyśmy przeanalizowali wszystkie akcje wykonywane przez serwer okazałoby się, że każda z tabel analizowana jest osobno w budowanym wewnętrznie kursorze (kursor tworzony jest przez procedurę sp_MSforeachtable). Na szczęście jest inne rozwiązanie.

Zwykłe i dynamiczne widoki systemowe

Od wersji SQL Server 2005 istnieje inny, zalecany przeze mnie sposób. W roku pisania tego artykułu, 2014, jest wielce prawdopodobne, że baza danych obsługuje wykorzystywane widoki. A są to, w porządku alfabetycznym:

• sys.dm_db_partition_stats - ten dynamiczny widok zawiera informacje o stronach we wszystkich partycjach, większość tabel w bazach mieści się na jednej partycji,
• sys.indexes - ten widok zawiera informacje o indeksach, szczegóły za chwilę,
• sys.tables - ten widok zawiera informacje o tabelach.

Nie wszystkie widoki są potrzebne, ale z pewnych względów postanowiłem włączyć je do zapytania. Zanim przejdę do wyjaśnień, popatrzmy na przykładowe zapytanie:

SELECT
    OBJECT_NAME(s.[object_id]) Name,
    MAX(row_count) 'Rows',
    SUM(reserved_page_count)*8 'Reserved [kB]',
    SUM(IIF(i.type=0 OR i.type=1,in_row_data_page_count, 0))*8 'Row data [kB]',
    SUM(used_page_count-IIF(i.type=0 OR i.type=1,in_row_data_page_count, 0))*8 'Index size [kB]',
    SUM(reserved_page_count-used_page_count)*8 'Unused [kB]'
FROM sys.dm_db_partition_stats s
JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.tables t ON s.[object_id]=t.[object_id]
--JOIN sys.objects o ON o.[object_id]=t.[object_id] WHERE t.is_ms_shipped=0
--WHERE s.[object_id] = object_id('dbo.TestNEWID')
GROUP BY s.[object_id]
ORDER BY MAX(row_count) DESC

Kilka zdań wyjaśnienia. Przede wszystkim warto wiedzieć, że wartość każdego pola definiującego rozmiar jest podawana w stronach. Mnożenie tej wartości przez 8 pozwala otrzymać rozmiar w kilobajtach (strona - 8 kB). Zaskakujący może być sposób pobierania rozmiaru danych na podstawie typu indeksu. I tak, jeżeli tabela nie ma indeksu grupującego (CLUSTERED INDEX), wtedy dane leżą na tzw. stercie. To specjalny rodzaj indeksu składającego się z numeru pliku, numeru strony oraz numeru tego rekordu na stronie. Taki rodzaj indeksu może być tylko jeden i ma w tabeli sys.indexes numer 0. Gdy tabela posiada indeks grupujący, dane przechowywane są w tym właśnie indeksie w postaci drzewa. Taki indeks też może być tylko jeden i ma w tabeli sys.indexes numer 1. Pozostałe indeksy (niegrupujące, xml, przestrzenne, kolumnowe) mają typ większy od 1. Pokazana powyżej instrukcja, wzorowana na rozwiązaniu wykorzystującym procedurę sp_spaceused, pobiera tylko rozmiary tabel użytkownika. Informacja o pochodzeniu tabel znajduje się w widoku systemowym sys.objects w kolumnie is_ms_shipped lub, niejawnie, w widoku sys.tables (wszystkie tabele w tym widoku mają wartość 0, wystarczy filtr pochodzący ze złączenia). Jak wspomniałem, wyniki podawane są w takiej samej formie jak wyniki z procedury sp_spaceused. Tutaj, w przeciwieństwie do procedury, mamy jednak znacznie większą swobodę. Być może chcemy rozmiary wszystkich tabel, w tym także tych systemowych, być może chcemy rozmiar w bajtach. Znacznie łatwiejsze staje się także sortowanie. Gdybyśmy, korzystając z tego rozwiązania, zechcieli pobrać informacje o jednej tabeli, wystarczy nałożyć odpowiedni filtr usuwając komentarz w trzeciej linijce od końca (gdy warunków jest więcej trzeba zamienić WHERE na AND).

Napisałem, że widoki, a w zasadzie dynamiczny widok sys.dm_db_partition_stats, dostępny jest od wersji SQL Server 2005. Tymczasem powyższy skrypt nie wykona się na takiej wersji silnika bazy danych. Wszystko przez użytą funkcję IIF. W takim przypadku należy zamienić ją na odpowiednią instrukcję CASE WHEN THEN ELSE. Skrypt będzie wtedy wyglądał podobnie do tego:

SELECT
    OBJECT_NAME(s.[object_id]) [Name],
    MAX(row_count) 'Rows',
    SUM(reserved_page_count)*8 'Reserved [kB]',
    SUM(CASE WHEN i.type=0 OR i.type=1 THEN in_row_data_page_count ELSE 0 END)*8 'Row data [kB]',
    SUM(used_page_count-CASE WHEN i.type=0 OR i.type=1 THEN in_row_data_page_count ELSE 0 END)*8 'Index size [kB]',
    SUM(reserved_page_count-used_page_count)*8 'Unused [kB]'
FROM sys.dm_db_partition_stats s
JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.tables t ON s.[object_id]=t.[object_id]
GROUP BY s.[object_id]
ORDER BY MAX(row_count) DESC

Po uruchomieniu zapytania otrzymamy rezultat podobny do poniższego:

Podkreślę, że dane o rozmiarach podawane są w postaci liczbowej, a nie tak jak wcześniej, w postaci tekstowej.

Dynamiczny widok sys.dm_db_partition_stats zwraca znacznie więcej informacji niż procedura sp_spaceused. Oprócz rozdziału na partycje dostajemy informację o liczbie stron znajdujących się w obszarze dużych danych (LOB), o liczbie stron powstałych w wyniku przepełnienia rekordu w tabeli bez indeksu grupującego (ROW OVERFLOW). Dalsze, jeszcze bardziej szczegółowe informacje można uzyskać poprzez widok systemowy sys.allocation_units oraz inne, łącząc tabele w tradycyjny sposób, nakładając warunki i wybierając potrzebne kolumny. Znacznie trudniejsze jest wykonywanie tego typu instrukcji z procedur (oprócz zwracania wyników to tabeli tymczasowej można skorzystać z rozwiązania pokazanego tutaj: SELECT z instrukcji systemowych).

Rozmiar tabeli a FILESTREAM

Dane wykorzystywane przez bazę danych mogą być umieszczone w plikach bazy danych lub w systemie operacyjnym. Ta druga opcja to FILESTREAM, o którym można co nieco poczytać tutaj: FILESTREAM w Sql Server. Warto wiedzieć, że żadna z pokazanych metod nie pobiera informacji o rozmiarze plików umieszczonych w kolumnach typu FILESTREAM. W takim przypadku można wykorzystać następującą instrukcję:

SELECT SUM(DATALENGTH(Dane))
FROM Pliki

Aby pobrać kolumny, które zawierają takie dane można skorzystać z widoku sys.columns i kolumny is_filestream tego widoku:

SELECT OBJECT_NAME([object_id]) [Tabela], [name] [Kolumna]
FROM sys.columns
WHERE is_filestream=1

W przypadku FILESTREAM trzeba wiedzieć, że składowaniem danych zajmuje się system operacyjny. Czasami warto jednak wiedzieć co mamy i ile tego rzeczywiście, w różnych miejscach, jest.

Kategoria:SQL Server