Spis treści:

Kategoria:SQL Server


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:

namerowsreserveddataindex_sizeunused
Logi1265874259208 KB258112 KB968 KB128 KB

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 [?]'
SELECTFROM #tableSizes
DROP TABLE #tableSizes

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

namerowsreserveddataindex_sizeunused
tabela 152441664 KB1104 KB296 KB264 KB
tabela 2811488 KB11432 KB16 KB40 KB
tabela 31767136 KB72 KB16 KB48 KB
tabela 4266336 KB280 KB16 KB40 KB

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 [?]'
SELECTFROM #tableSizes
ORDER BY CAST(SUBSTRING(reserved, 1, LEN(reserved)-3) AS intDESC
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.indexesON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.tablesON 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 ELSEEND)*8 'Row data [kB]',
SUM(used_page_count-CASE WHEN i.type=0 OR i.type=1 THEN in_row_data_page_count ELSEEND)*8 'Index size [kB]',
SUM(reserved_page_count-used_page_count)*8 'Unused [kB]'
FROM sys.dm_db_partition_stats s
JOIN sys.indexesON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.tablesON 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:

NameRowsReserved [kB]Row data [kB]Index size [kB]Unused [kB]
tabela 1524416641104296264
tabela 2811488114321640
tabela 31767136721648
tabela 42663362801640

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

, 2014-06-17

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?