Pliki i grupy plików w SQL Server

Baza danych stworzona w ramach SQL Server może składać się z następujących struktur plikowych:

• Plik główny (Primary data file) -plik główny bazy danych, który posiada każda baza danych. W ramach pojedynczej bazy danych może występować tylko jeden plik główny. Przechowuje on dane oraz pełni rolę kontrolną nad pozostałymi plikami. Pliki bazy danych w SQL Server mogą mieć dowolne rozszerzenie ale zwyczajowo plik główny ma rozszerzenie .mdf
• Plik pomocniczy (Secondary data file) – dodatkowe pliki danych. Każda baza może mieć wiele plików tego typu bądź nie mieć żadnego. Przyjmuje się, że pliki te mają rozszerzenie .ndf
• Plik dziennika transakcji (Log file) – plik zawierający dane istotne dla zachowania integralności transakcji bazy danych. Każda baza może mieć jeden lub wiele tego typu plików. Przyjmuje się, że mają one rozszerzenie .ldf
• Inne – pliki związane z technologiami Filestream czy Filetable.

Dziś zajmiemy się pierwszymi trzema rodzajami plików, technologiom Filestream i Filetable poświęcimy osobne artykuły.Zanim zaczniemy omawiać pliki przyjrzyjmy się najprostszej formie:

CREATE DATABASE testDB

Co się kryje pod tym prostym zapytaniem? Bardzo wiele czynności dzieje się “na zapleczu” tzn:

• tworzone są dwa pliki testDB.mdf oraz testDB_Log.ldf
• pliki są tworzone w w domyślnej lokalizacji tam gdzie zainstalowany jest SQL Server
• login, który tworzący bazę danych staje się właścicielem bazy, którą tworzy – -informacja ta jest przechowywana w systemowej bazie master
• w bazie master zapisane są również informacje pliku mdf na temat pozostałych plików bazy
• domyślny rozmiar bazy jest ustalany na taki jak plik główny systemowej bazy model
• maksymalny rozmiar bazy został ustalony na nieograniczony (-1) co można utożsamić z maksymalnym rozmiarem dysku na którym on się znajduje
• domyślnie plik danych będzie powiększany o 1MB, a plik dziennika transakcyjnego o 10%
• podczas tworzenia bazy na bazie model zakładana jest blokada (exclusive lock)

To tylko uproszczona lista czynności, która ma miejsce. Ale już w tym miejscu możemy sobie zdać sprawę jak wiele domyślnych wartości jest złych. Bardzo złym pomysłem jest umiejscowienie pliku bazy danych na dysku systemowym, szczególnie jeśli nie ma on górnego ograniczenia co do rozmiaru. Po pierwsze nie sprzyja to w żaden sposób ani standardom bezpieczeństwa ani wydajności –  dysk systemowy jest bardzo mocno wykorzystywany przez system operacyjny Windows, po drugie plik może urosnąć do tego rozmiaru, że zapcha cały wolumen uniemożliwiając pracę systemu Windows. To tylko wierzchołek góry lodowej, który aby zrozumieć musimy poznać krok po kroku. Na samym początku wprowadzimy pojęcie pliku.

Każdy plik posiada własne właściwości – aby się z nimi zapoznać możemy użyć widoku systemowego sys.database_files. Skorzystamy z niego aby zapoznać się z wybranymi właściwościami plików bazy Northwind. Tak więc wykonajmy poniższe zapytanie:

SELECT
	file_id ,
    file_guid ,
    type ,
    type_desc ,
    name ,
    physical_name ,
    state ,
    state_desc ,
    size,
    max_size ,
    growth ,
    is_percent_growth
FROM
	sys.database_files

W rezultacie otrzymamy następujące dane:

Każdą z powyższych właściwości pokrótce omówimy:

• file_id –identyfikator porządkowy plliku
• file_guid – globalnie unikalny identyfikator pliku, jeżeli w tym miejscu widzimy NULL oznacza to, że baza została zaktualizowana ze starszej wersji SQL Server
• Type – typ pliku. Wyróżnia się następujące typy plików:
  • 0 –  pliki danych
  • 1 – plik dziennika transakcyjnego
  • 2 – plik FILESTREAM
  • 3 – zarezerwowany
  • 4 – plik Fulltext stworzony w SQL Server wcześniejszym niż 2012 (w 2012 i wyżej dane te są oznaczane są jako 0)
• name – nazwa pliku
• physical_name – nazwa pliku wraz z pełną ścieżką
• state – status pliku tj.:
  • 0 – ONLINE – plik w danym momencie sprawny i używany w standardowy sposób
  • 1 – RESTORING – plik jest odzyskiwany
  • 2 – RECOVERING – plik jest w trakcie procesu odzyskiwania
  • 3 – RECOVERY PENDING
  • 4 – SUSPECT – wystąpiły błędy związane z plikiem – dodatkowe czynności są potrzebne aby przywrócić bazę
  • 5 – zarezerwowany
  • 6  – OFFLINE – plik celowo został wyłączony
  • 7 – DEFUNCT – plik posiada ten stan w momencie gdy nadrzędna grupa plików została usunięta lub gdy nie została ona prawidłowo przywrócona
• size – bieżący rozmiar pliku
• maxsize – maksymalny rozmiar jaki może przyjąć plik (-1 oznacza, że nie ma górnego limitu)
• is_percent_growth – flaga oznaczająca czy plik jest rozszerzany o procent swojego rozmiaru=1 czy też o stałą wartość =0
• growth – jeżeli is_percent_growth =0 oznacza liczbę stron danych po 8KB, jeżeli is_percent_growth =1 oznacza procent o ile ma plik ma być zwiększany. Jeżeli właściwość ta jest równa 0 tzn. że plik jest stałego rozmiaru i nie będzie się zwiększał.

Już zapoznając się z tymi własnościami możemy sobie parę słów powiedzieć na temat dobrych praktyk z nimi związanych. Przede wszystkim tworząc nową bazę powinniśmy przemyśleć rozmiar początkowy pliku danych na podstawie dogłębnej analizy – nie ma tutaj prostej zasady ponieważ, każda implementacja ma swoje specyficzne wymogi. Ważną i uniwersalną wskazówką jest natomiast to aby ustawiać stały rozmiar powiększania pliku. Pomaga to rozmieszczać dane równomiernie w plikach zgodnie z zasadą proporcjonalnego wypełnienia.

Możemy również zadawać sobie pytanie po co nam kilka plików na dane w bazie danych? Przede wszystkim daje to większe możliwości zarządzania bazą danych. W przypadku odtwarzania bazy danych gdy mamy jeden plik np. o rozmiarze 500GB to musi być odtwarzany ten jeden plik i w tym czasie baza nie jest dostępna dla użytkowników. Gdy natomiast mamy 5 plików po 100GB każdy, to możemy je odtwarzać sekwencyjnie tj. gdy odtworzymy pierwszy plik 100GB po odtworzeniu dane w nim zawarte mogą być udostępnione dla użytkowników, a my w tym czasie możemy odtwarzać kolejne.

Pliki możemy umieścić z poziomu tzw. grup plików (ang. filegroup). Tworząc obiekty w ramach bazy danych SQL Server nie umieszczamy ich bezpośrednio w pliku, a właśnie w grupie plików. Każda z nich posiada od 1 do N plików – grupa zawierająca Główny plik danych (ang. Primary Data file) nazywana jest Primary Filegroup. W domyślnej konfiguracji  gdy jawnie nie wskażemy w zapytaniu tworzącym dany obiekt aby znajdował się on w konkretnej grupie to wtedy trafia on właśnie do Primary Filegroup.

Wyjątkiem od reguły jest plik dziennika transakcyjnego, który nie jest powiązany z żadną grupą plików co możemy udowodnić używając interfejsu graficznego Management Studio we właściwościach dowolnej bazy danych na zakładce Files:

Aby podejrzeć jakie grupy plików mamy stworzone w ramach konkretnej bazy danych, podobnie jak w przypadku plików mamy do dyspozycji widok systemowy sys.filegroups. Wykonajmy poniższe zapytanie na tym widoku aby omówić sobie wybrane własciwości:

USE Northwind
GO

SELECT
    name,
    type,
    is_default,
    is_system,
    is_read_only
FROM
    sys.filegroups

Jak widać w ramach bazy Northwind mamy do dyspozycji jedną grupę plików czyli domyślną grupę PRIMARY. Oprócz nazwy w ramach widoku mamy wiele właściwości do opisu wybraliśmy następujące:

• type – tutaj będziemy mieli wartość FG czyli tradycyjną grupę plików lub FD czyli grupę plików związaną z technologią Filestream
• is_default – jeżeli dana grupa jest grupą domyślną w tym miejscu będzie 0, w przeciwnym wypadku 1
• is_system jeżeli dana grupa jest przez technologię fulltext to będziemy tu widzieć 1, w przeciwnym wypadku 0
• is_read_only jeżeli grupa jest przeznaczona tylko do odczytu to będziemy tu widzieć wartość 1, w przeciwnym wypadku 1

Możecie w tym miejscu zastanawiać się jak dodać grupę plików do konkretnej bazy – zróbmy to – dodajmy grupę o nazwie Secondary do bazy Northwind. Aby to zrobić użyjemy prostej składni TSQL:

ALTER DATABASE 
            [Northwind] 
ADD FILEGROUP 
            [SECONDARY]

po wykonaniu możemy ponownie odpytać przedstawiony wcześniej sys.filegroups aby zobaczyć czy grupa plików została dodana poprawnie. Po stworzeniu prostej grupy plików możemy chcieć aby stała się ona naszą grupą domyślną – aby to zrobić musimy znów uruchomić komendę TSQL typu DDL tak jak poniżej

USE Master
GO
ALTER DATABASE 
           [Northwind] 
MODIFY FILEGROUP 
          [SECONDARY] DEFAULT
GO

Aby oznaczyć grupę jako przeznaczoną tylko do odczytu należy wykonać następujące zapytanie:

USE Master
GO

ALTER DATABASE 
            [Northwind] 
MODIFY FILEGROUP 
         [SECONDARY] READONLY

w rezultacie powyższego zapytanie powinniśmy otrzymać błąd! Ponieważ nie da się oznaczyć grupy plików do odczytu jeżeli jest ona pusta tj. nie zawiera w sobie żadnego pliku! Dlatego też dodajmy do niej plik:

ALTER DATABASE 
           [Northwind] 
ADD FILE ( 
           NAME = N'Northwind_2', 
           FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATANorthwind_2.ndf' , 
           SIZE = 5120KB , 
           FILEGROWTH = 1024KB ) 
TO FILEGROUP [SECONDARY]
GO

Po dodaniu pliku musimy upewnić się, że nikt do naszej bazy nie jest podłączony (używając np. komendy sp_who) i jeżeli baza nie jest używana możemy wykonać polecenie przełączające grupę plików w tryb tylko do odczytu. Zmiana ta powinna być przemyślana ze względu na fakt, iż od momentu przełączenia obiekty przypisane do danej grupy nie będą mogły być już zmieniane (wyjątkiem od tej reguły jest grupa PRIMARY, która nie może być ustawiona w tryb READ-ONLY). W każdym momencie możemy przywrócić naszą grupę do trybu odczyt/zapis aby to zrobić należy podobnie jak powyżej upewnić się, że nikt nie jest podłączony do bazy i wykonać następujące polecenie:

USE Master
GO

ALTER DATABASE 
        [Northwind] 
MODIFY FILEGROUP 
        [SECONDARY] 
READ_WRITE
GO

W tym momencie możecie zadawać pytanie po co oznaczać grupę jako tylko do odczytu – odpowiedź jest bardzo prosta.  Niektóre systemy np. archiwizują dane i zabezpieczają przed zmianą, oznaczenie grupy plików jako tylko do odczytu jest jednym ze sposobów umożliwiających osiągnięcie zamierzonego celu.

Stwórzmy teraz przykładową tabelę w naszej bazie Northwind. Dodatkowo umieścimy ją w grupie Secondary:

USE Northwind
GO

CREATE TABLE dbo.Test
(
	id INT IDENTITY(1,1) NOT NULL,
	Test VARCHAR(20) NOT NULL
) ON SECONDARY

Dodatkowo stworzymy indeks niezgrupowany na kolumnie Test, który będzie znajdował się w grupie PRIMARY:

USE [Northwind]

GO

CREATE NONCLUSTERED INDEX [NCI_Test] ON [dbo].[Test]
(
	[Test] ASC
) ON [PRIMARY]

GO

Jak widać różne obiekty mogą znajdować się w oddzielnych grupach plików. Pliki wchodzące w skład każdej grupy mogą znajdować się na różnych fizycznych dyskach co pomoże uzyskać większą skalowalność i wydajność. Można sobie wyobrazić scenariusz gdy część naszych danych w ramach np. hurtowni danych ma być dostępna bardzo szybko – wtedy możemy daną grupę plików umieścić na szybkim dysku SSD, a dane które nie muszą być dostępne tak szybko na innych zapasowych dyskach.

Odstępstwem od powyższej reguły są pliki dziennika transakcyjnego – w 99% przypadków powinien istnieć tylko jeden plik dziennika. Oczywiście z technicznego punktu widzenia jesteśmy w stanie dodać wiele plików, nie daje to w rezultacie żadnych zysków ze względu na naturę samego dziennika, który nie działa równolegle na plikach tylko sekwencyjnie. Jedyne produkcyjne użycie wielu plików dziennika może pojawiać się wtedy gdy np. na dysku gdzie przechowujemy te pliki brakuje miejsca i musimy zapewnić miejsce dla loga – wtedy właśnie warto dodać nowy plik na dysku gdzie takowe miejsce jest.

Ogólnie rzecz biorąc rozmiar na dysku gdzie znajdują się pliki naszej bazy danych powinien być monitorowany. Może ku temu posłużyć bardzo wiele narzędzi od monitora wydajności (perfmon) i wskaźnika SQLServer:Database Object: Data File Size:

aż po nieudokumentowane procedury SQL Servera. Jedną z takich procedur jest sys.xp_fixeddrives która zwróci nam miejsce na dysku systemowym, wykonajmy ją:

USE master
GO

EXEC sys.xp_fixeddrives

Jako rezultat otrzymujemy wolne miejsce w MB wraz z literą dysku:

Oczywiście możemy użyć tej procedury do budowy mechanizmu powiadamiania w przypadku wyczerpywania się wolnego miejsca – jednak zdecydowanie lepszym pomysłem jest odizolowanie plików bazy danych od dysku systemowego. Dużym tematem jest również kwestia rozszerzania i zmniejszania plików baz danych oraz dziennik transakcyjny, którym poświęcę osobne artykuły.

Ostatnim tematem, który poruszymy w ramach niniejszego artykułu jest algorytm proporcjonalnego wypełniania plików. SQL Server w momencie gdy ma do dyspozycji kilka plików stara się aby były one w jednakowym stopniu obciążone pod kątem zapisu. Dlatego też zapisuje dane do pliku, który ma więcej wolnego miejsca. Z tego też zachowania wynika prosta zasada mówiąca o tym, iż pliki w ramach tej samej grupy plików powinny mieć zawsze ten sam rozmiar początkowy i powinny być rozszerzane o ten sam rozmiar zdefiniowany w MB. Szczególne znaczenie ma to dla dużych baz danych – jak widać na poniższym rysunku baza danych zawiera 4 pliki z czego 2 są w dużym stopniu zapełnione, a 2 kolejne zostały niedawno dodane:

Przy kolejnych zapisach do obiektów związanych z daną grupą plików gdzie będą trafiać nowe dane? Tak – zgodnie z zasadą proporcjonalnego wypełnienia do plików 3 oraz 4 przez co dyski na których się one znajdują będą bardzo mocno obciążone pod kątem zapisu w przeciwieństwie do dysków na których znajdują się pliki 1 oraz 2. Czy to jest dobre zbalansowanie obciążenia – oczywiście nie. Dlatego też niezmiernie ważne jest to aby odpowiednio wcześniej planować gdzie i jak nasze dane będą się znajdować, oraz jak dużych rozmiarów nasza baza urośnie. Oczywiście nie twórzmy nadmiernych plików w momencie tworzenia każdej bazy danych – twórzmy je dopiero wtedy gdy wiemy, że ilość danych w niej zawartych będzie bardzo duży i celowo chcemy rozłożyć obciążenie pomiędzy różne dyski.

Podsumowując warto pamiętać o właściwym zaplanowaniu plików bazy danych już w momencie jej tworzenia. Dzięki temu unikniemy późniejszych problemów w przypadku przywracania bazy i administracji zasobami. Zapraszam na kolejny artykuł mocno powiązany z plikami i grupami plików chodzi mianowicie o natychmiastowe inicjalizowanie plików (Instant file Initialization), który pojawi się na SeeQuaLity.net już niebawem. – Zapraszam!