sys.dm_db_file_space_usage pokazuje wykorzystanie przestrzeni w poszczególnych plikach bazy, w tym tempdb.
Rola sys.dm_db_file_space_usage w SQL Server
sys.dm_db_file_space_usage jest dynamicznym widokiem zarządzania silnika Microsoft SQL Server. Zwraca informacje o wykorzystaniu przestrzeni w plikach danych konkretnej bazy, czyli przede wszystkim w plikach typu .mdf i .ndf. Widok pokazuje przydział stron i extentów, a także podział zajętego miejsca według rodzaju obiektów. Nie opisuje zawartości pojedynczych tabel ani indeksów tak szczegółowo jak widoki katalogowe, lecz przedstawia stan przestrzeni na poziomie pliku danych.
Wynik zapytania zawiera między innymi identyfikator bazy, identyfikator pliku, liczbę nieprzydzielonych extentów oraz liczbę extentów przydzielonych. Dostępne są również wartości dotyczące stron zajętych przez obiekty użytkownika, obiekty wewnętrzne oraz magazyn wersji. Dzięki temu można rozróżnić sytuację, w której plik jest zapełniany przez zwykłe dane aplikacyjne, od sytuacji, w której znaczną część miejsca wykorzystują mechanizmy wewnętrzne SQL Server.
Kolumny opisujące przydział stron i extentów
SQL Server przechowuje dane na stronach o rozmiarze 8 KiB, a extenty są jednostkami obejmującymi osiem stron, czyli 64 KiB. Wartości zwracane przez sys.dm_db_file_space_usage są wyrażane głównie jako liczba stron. unallocated_extent_page_count wskazuje liczbę stron należących do wolnych extentów, natomiast allocated_extent_page_count przedstawia strony należące do extentów już przydzielonych. Do przeliczenia stron na megabajty można użyć wzoru: liczba stron pomnożona przez 8 i podzielona przez 1024.
Kolumny user_object_reserved_page_count i internal_object_reserved_page_count pozwalają określić, jaka część przestrzeni jest zarezerwowana odpowiednio dla obiektów użytkownika oraz struktur tworzonych wewnętrznie przez silnik. version_store_reserved_page_count dotyczy stron zajętych przez magazyn wersji w bazie tempdb. Występują także liczniki stron oczekujących na dealokację, między innymi user_object_pending_dealloc_page_count oraz internal_object_pending_dealloc_page_count. Ich obecność oznacza, że zwolnienie przestrzeni może jeszcze nie być zakończone, ponieważ SQL Server wykonuje operacje porządkowe.
Wynik obejmuje również informacje związane z extentami mieszanymi, a w odpowiednich wersjach silnika także wartości dotyczące zmodyfikowanych extentów. Nie należy interpretować pojedynczej kolumny jako całkowitego rozmiaru pliku. Rozmiar pliku i jego maksymalny limit są przechowywane w metadanych pliku, na przykład w sys.database_files, natomiast omawiany widok służy do analizy bieżącego rozdziału wykorzystanej przestrzeni.
Zapytania diagnostyczne dla tempdb i baz użytkownika
Widok jest używany w kontekście konkretnej bazy, dlatego typowe zapytanie uruchamia się po przełączeniu kontekstu na wybraną bazę:
USE tempdb;
SELECT file_id, unallocated_extent_page_count, allocated_extent_page_count, version_store_reserved_page_count, user_object_reserved_page_count, internal_object_reserved_page_count, user_object_pending_dealloc_page_count, internal_object_pending_dealloc_page_count
FROM sys.dm_db_file_space_usage;
W przypadku tempdb szczególnie istotne jest porównanie udziału magazynu wersji, obiektów użytkownika i obiektów wewnętrznych. Duża liczba stron w magazynie wersji może wynikać z aktywnych transakcji korzystających z izolacji opartej na wersjonowaniu wierszy. Zajętość przez obiekty użytkownika może wskazywać na tabele tymczasowe lub zmienne tabelaryczne, a obiekty wewnętrzne mogą być związane z sortowaniem, haszowaniem, tabelami roboczymi i innymi operacjami wykonywanymi przez optymalizator oraz aparat zapytań.
Przy analizie nie należy wyciągać wniosków wyłącznie z jednego odczytu. DMV pokazuje stan w chwili wykonania zapytania, a wartości mogą zmieniać się podczas działania transakcji i zadań w tle. Dobrym uzupełnieniem jest zestawienie wyników z sys.database_files, sprawdzenie autowzrostu plików oraz obserwacja czasu. Jeżeli plik powiększa się wielokrotnie o małe przyrosty, problem może dotyczyć konfiguracji wzrostu, a nie jedynie chwilowego zapotrzebowania na miejsce. Odczyt wymaga odpowiednich uprawnień diagnostycznych oraz dostępu do metadanych danej bazy.
Interpretacja wyników podczas awarii braku miejsca
Wysokie wykorzystanie pliku nie zawsze oznacza błąd. Jeżeli baza ma zaplanowany zapas wolnej przestrzeni, niski poziom unallocated_extent_page_count może być normalnym stanem eksploatacyjnym. Problem pojawia się wtedy, gdy plik nie może zwiększyć rozmiaru z powodu osiągnięcia wartości MAXSIZE, braku miejsca na woluminie, wyłączonego autowzrostu albo niewystarczających uprawnień procesu SQL Server do pliku. Sam widok sys.dm_db_file_space_usage nie rozstrzyga, która z tych przyczyn występuje, dlatego trzeba zestawić go z konfiguracją plików i komunikatami z dziennika.
W tempdb trwałe lub szybko narastające wykorzystanie przestrzeni może wymagać znalezienia zapytań tworzących duże struktury robocze, transakcji długo utrzymujących wersje wierszy albo operacji intensywnie korzystających z tabel tymczasowych. Zmniejszanie pliku bez usunięcia przyczyny zwykle nie rozwiązuje problemu i może powodować dodatkowe operacje wejścia-wyjścia oraz późniejszy ponowny autowzrost. Bezpieczna diagnostyka polega na identyfikacji kategorii zajętości, sprawdzeniu aktywnych sesji i transakcji oraz ocenie ustawień wszystkich plików danych.
Wyniki DMV należy odczytywać jako element diagnostyki SQL Server, a nie jako samodzielny raport o kondycji całego komputera. Nie pokazują one wolnego miejsca na dysku, wydajności kontrolera, opóźnień nośnika ani obciążenia procesora. Te informacje trzeba zebrać osobno, ponieważ baza może mieć wolną przestrzeń wewnątrz pliku, a jednocześnie działać na niemal pełnym woluminie, albo odwrotnie: mieć wolny dysk, lecz niewystarczający limit skonfigurowanego pliku.
