Wydajność BLOB w SQL Server

Wydajność BLOB w SQL Server zależy od tego, czy duże obiekty przechowuje się w tabeli, czy przez mechanizm FILESTREAM.

Jak SQL Server przechowuje dane typu BLOB

BLOB, czyli obiekt binarny o dużym rozmiarze, w SQL Server jest najczęściej przechowywany w kolumnie varbinary(max). Taka kolumna należy do tabeli, dlatego dane binarne są objęte tym samym modelem transakcyjnym, kontrolą dostępu, kopią zapasową i mechanizmami odzyskiwania co pozostałe dane wiersza. Rozwiązanie tabelaryczne upraszcza projekt aplikacji: rekord może zawierać identyfikator dokumentu, typ pliku, rozmiar, sumę kontrolną oraz samą zawartość binarną.

Przechowywanie BLOB-u w tabeli nie oznacza, że cały obiekt zawsze znajduje się fizycznie w podstawowej części wiersza. Dla wartości o większym rozmiarze SQL Server może używać stron danych przeznaczonych dla danych typu LOB, powiązanych z rekordem tabeli. Odczyt takiej wartości wymaga więc zarówno znalezienia wiersza, jak i dostępu do stron zawierających obiekt. Wydajność zależy między innymi od rozmiaru BLOB-ów, liczby odczytywanych rekordów, fragmentacji, układu indeksów, szybkości magazynu oraz tego, czy zapytanie pobiera całą zawartość, czy tylko metadane.

FILESTREAM a klasyczny zapis w kolumnie

FILESTREAM jest mechanizmem SQL Server przeznaczonym do przechowywania danych varbinary(max) w systemie plików, przy zachowaniu zarządzania nimi przez bazę danych. Aby go użyć, baza musi mieć skonfigurowaną obsługę FILESTREAM oraz specjalną grupę plików FILESTREAM. Kolumna przeznaczona do takiego zapisu jest deklarowana z atrybutem FILESTREAM, na przykład jako varbinary(max) FILESTREAM. Tabela musi również posiadać kolumnę typu ROWGUIDCOL z unikatowymi wartościami, ponieważ SQL Server wykorzystuje identyfikator wiersza do powiązania rekordu z plikiem w magazynie FILESTREAM.

Klasyczny dostęp przez T-SQL pozwala traktować zawartość FILESTREAM podobnie jak zwykłą wartość binarną. Przy dużych obiektach aplikacja może jednak użyć funkcji PathName() oraz systemowej funkcji GET_FILESTREAM_TRANSACTION_CONTEXT(), a następnie otworzyć strumień przez interfejs OpenSqlFilestream. Taki dostęp odbywa się w ramach transakcji i korzysta z uchwytu udostępnionego przez SQL Server. Nie jest to zwykłe, niezależne otwarcie pliku z katalogu systemowego; bez prawidłowego kontekstu transakcyjnego aplikacja nie powinna próbować omijać mechanizmów bazy.

Co decyduje o czasie odczytu i zapisu

Dla małych i średnich obiektów zapis w tabeli może być prostszy i wystarczająco szybki, zwłaszcza gdy aplikacja i tak wykonuje operacje transakcyjne przez T-SQL. W przypadku dużych plików znaczenie ma koszt przesyłania danych przez warstwę zapytań, ilość generowanego dziennika transakcyjnego oraz liczba jednocześnie wykonywanych operacji. Odczytywanie BLOB-u razem z listą dokumentów może powodować duży transfer i zwiększać zużycie pamięci po stronie serwera oraz klienta. Zapytania pokazujące listę plików powinny zwykle pobierać identyfikator, nazwę, rozmiar i inne metadane, a zawartość odczytywać dopiero przy żądaniu konkretnego obiektu.

FILESTREAM może ograniczyć narzut związany z obsługą bardzo dużych wartości binarnych, gdy aplikacja korzysta ze strumieniowego interfejsu dostępu. Nie oznacza to automatycznej poprawy dla każdego zapytania. Odczyt przez T-SQL nadal może mieć inny charakter niż dostęp przez OpenSqlFilestream, a wynik zależy od konfiguracji dysków, obciążenia instancji SQL Server, rozmiaru obiektów i wzorca dostępu. Trzeba również uwzględnić blokowanie i czas trwania transakcji. Długa transakcja utrzymywana podczas przesyłania dużego pliku może opóźniać zwalnianie zasobów i zwiększać konkurencję między operacjami.

Przy częściowych aktualizacjach danych binarnych można rozważyć metodę .WRITE() dla kolumny varbinary(max), zamiast przesyłania całej wartości od początku. W praktyce wymaga to poprawnego określenia fragmentu, pozycji i długości danych oraz sprawdzenia, jak operacja wpływa na log transakcyjny. Pomiar powinien obejmować pełny scenariusz: zapis, zatwierdzenie transakcji, odczyt, transfer do aplikacji i ewentualną kopię zapasową. Sam czas wykonania pojedynczego polecenia nie wystarcza do oceny rozwiązania.

Diagnostyka problemów z wydajnością BLOB

Analizę należy rozpocząć od określenia, gdzie powstaje opóźnienie. Inny problem występuje, gdy SQL Server długo wyszukuje rekord, inny gdy wolno odczytuje strony LOB, a jeszcze inny gdy serwer szybko zwraca dane, lecz klient długo je przetwarza. Pomocne są rzeczywiste czasy zapytań, plany wykonania, statystyki wejścia-wyjścia oraz obserwacja obciążenia procesora, pamięci i podsystemu dyskowego. Warto sprawdzić, czy kolumna BLOB nie jest pobierana w zapytaniach, które powinny zwracać wyłącznie metadane, oraz czy filtr korzysta z odpowiednio dobranego indeksu.

W przypadku FILESTREAM trzeba dodatkowo zweryfikować konfigurację grupy plików, dostępność woluminu, przepustowość i opóźnienia magazynu oraz sposób użycia transakcyjnego interfejsu strumieniowego. Należy kontrolować, czy aplikacja prawidłowo zamyka uchwyty, nie utrzymuje niepotrzebnie otwartych transakcji i nie pobiera jednocześnie wielu dużych obiektów bez ograniczenia. Kopie zapasowe oraz odtwarzanie również powinny być testowane z uwzględnieniem danych FILESTREAM, ponieważ wydajność operacji produkcyjnych zależy nie tylko od zapytań, lecz także od całej ścieżki przechowywania i odzyskiwania danych.

Przy diagnozowaniu komputera używanego do pracy z SQL Server należy rozdzielić problem silnika bazy od problemu stacji roboczej. Warto sprawdzić stabilność połączenia sieciowego, sterownik klienta, dostępne zasoby pamięci, obciążenie procesora, stan dysku oraz błędy systemowe. Dopiero porównanie tych danych z pomiarem po stronie SQL Server pozwala ocenić, czy przyczyną jest sposób przechowywania BLOB-ów w tabeli, konfiguracja FILESTREAM, zapytanie, transfer danych czy lokalne środowisko uruchomieniowe.