Wskazówka dnia: Jak sprawdzić zajmowane miejsce przy użyciu sp_spaceused i sys.dm_os_performance_counters

Dziś zajmiemy się dosyć powszechną kwestią, a mianowicie jak sprawdzić rozmiar określonego obiektu w bazie danych – zaczynajmy!

Najprostszym sposobem na osiągnięcie pożądanego rezultatu jest użycie wbudowanej w SQL Server funkcji sp_spaceused pełna jej składnia została przedstawiona poniżej:

sp_spaceused [[ @objname = ] 'objname' ]   
[, [ @updateusage = ] 'updateusage' ]  
[, [ @mode = ] 'mode' ]  
[, [ @oneresultset = ] oneresultset ]

Gdzie:

• objname to nazwa obiektu, którego wielkość chcemy sprawdzić. Jeśli nie podamy tego parametru to sprawdzony zostanie rozmiar całej bazy danych
• updateusage – może być ustawiona na TRUE lub FALSE i w zależności od przekazanej wartości uruchamia komendę DBCC UPDATEUSAGE, która aktualizuje informacje zużycia miejsca przez obiekty/bazę.
• mode – przyjmuje wartości ALL, LOCAL_ONLY, REMOTE_ONLY i oznacza czy komenda będzie sprawdzała jedynie lokalne miejsce zajmowane przez obiekt czy też to miejsce, które jest zajmowane w chmurze w ramach mechanizmu Stretch database (o którym będę mówił już wkrótce). Opcja ALL jak sama nazwa wskazuje sprawdzi zajmowane miejsce w każdej z lokalizacji.
• oneresultset – nowa opcja w SQL Server 2016 mówiąca o tym czy zwracany procedura ma zwrócić dane w jednym czy też dwóch rezultatach.

Tak wiec wykonajmy tą procedurę na bazie WorldWideImporters:

USE WideWorldImportersDW
GO

sp_spaceused @updateusage=TRUE, @mode='ALL'

Jak widać na powyższym zrzucie baza WideWorldImportersDW zajmuje 6052MB z czego ponad 3248MB nie zostało jeszcze zaalokowanych tzn. jest to wolne miejsce w ramach bazy danych.

W drugim rezultacie możemy odczytać następujące informacje:

• reserved – miejsce zaalokowane przez obiekty w ramach bazy danych
• data – miejsce używane przez dane
• index_size – miejsce używane przez indeksy
• unused – miejsce zarezerwowane przez obiekty ale jeszcze nieużywane

Po szybkim obliczeniu możecie dojść do wniosku, że database_size jest większe niż to pokazywane w polu reserved. Dzieje się tak dlatego, że database_size to rozmiar zarówno pliku bazy danych jak i dziennika transakcyjnego.

W przypadku gdy chcemy otrzymać bardziej kompaktowy rezultat wystarczy użyć wspomnianego wyżej parametru oneresultset i ustawić go na TRUE

sp_spaceused @updateusage=TRUE, @mode='ALL',@oneresultset=TRUE

Użycie parametru umożliwia zapis do tabeli składnią INSERT INTO… EXEC co nie było możliwe w poprzednich wersjach SQL Server. W tym momencie możliwe jest zaplanowanie joba który cyklicznie będzie zapisywał informacje o rozmiarze bazy danych do tabeli.

Możemy również sprawdzić jak te wszystkie informacje są ze sobą skorelowane na podstawie poniższych wzorów:

used = data + index_size
reserved = used + unused
log_space=database_size-unallocated_space-reserved
database_size = reserved + unallocated space + log space

sprawdźmy to na naszym przykładzie:

used = 724784+124416=849200
reserved=849200+18448=867648
database_size=867648+3326658.56 + log_space=6197248
log_space=6197248-3326658.56-867648=2002941.44

Sprawdźmy czy wszystko się zgadza sprawdzając rozmiar pliku loga – służy do tego poniższe polecenie:

DBCC SQLPERF ( LOGSPACE )

Po przeliczeniu na KB rozmiar loga wynosi 2002935.808 co mniej więcej zgadza się z naszymi obliczeniami. Dlaczego tylko mniej więcej? Ponieważ rozmiar bazy i niezalokowanego miejsca nie jest dokładny i podany w zaokrągleniu w megabajtach.  Dlatego też nasze obliczenia są poprawne. Dla tych, którzy chcą wyświetlić dokładne rozmiary bazy danych możemy użyć widoku z licznikami sys.dm_os_performance_counters

   SELECT 
	instance_name,
	counter_name,
	cntr_value
   FROM sys.dm_os_performance_counters
   WHERE counter_name IN
   (
       'Data File(s) Size (KB)',
       'Log File(s) Size (KB)',
       'Log File(s) Used Size (KB)',
       'Percent Log Used'
   ) AND 
   instance_name != '_Total' AND
   instance_name='WideWorldImportersDW'

Po powyższym zrzucie widać różnice w wielkościach – dlatego też użycie widoku sys.dm_os_performance_counters jest dużo bardziej precyzyjne.

Pamiętajmy o tym, że możliwe jest również sprawdzenie miejsca zajmowanego przez konkretny obiekt – wystarczy podać parametr @objname. Sprawdźmy rozmiar danych na konkretnym obiekcie jakim jest tabela Dimension.Customer

sp_spaceused 
   @objname='Dimension.Customer', 
   @updateusage=TRUE, 
   @mode='ALL',
   @oneresultset=TRUE

Jak widać oprócz znanych już nam informacji pojawiła się również informacja o ilości wierszy – pochodzi ona wprost ze statystyk i nie koniecznie jest dokładna, ale daje pewien pogląd.

Oczywiście istnieje jeszcze kilka innych metod sprawdzenia ile miejsca zajmuje baza danych jednakże wybrałem te, których sam używam. Mam nadzieję, że podane tutaj informacje będą dla was użyteczne.