CREATE TABLE (MEMORY_OPTIMIZED = ON)

Polecenie CREATE TABLE z opcją MEMORY_OPTIMIZED = ON tworzy tabelę przechowywaną w pamięci operacyjnej zamiast na dysku.

Rola opcji MEMORY_OPTIMIZED w SQL Server

Opcja MEMORY_OPTIMIZED = ON w instrukcji CREATE TABLE oznacza utworzenie tabeli zoptymalizowanej pod kątem przechowywania i przetwarzania danych w pamięci operacyjnej. Mechanizm ten należy do technologii In-Memory OLTP dostępnej w Microsoft SQL Server. Taka tabela nie korzysta z klasycznego modelu blokad i stron danych w taki sam sposób jak tabela oparta na dysku. SQL Server używa wielowersyjnego mechanizmu kontroli współbieżności, dzięki czemu transakcje mogą ograniczyć oczekiwanie na blokady podczas pracy z intensywnie zmienianymi danymi.

„Przechowywanie w pamięci” nie oznacza, że dane zawsze znikają po ponownym uruchomieniu instancji. O zachowaniu tabeli decyduje między innymi opcja trwałości. Tabela utworzona z DURABILITY = SCHEMA_AND_DATA zapisuje dane w sposób umożliwiający ich odtworzenie po restarcie SQL Server. Przy DURABILITY = SCHEMA_ONLY zachowany pozostaje schemat tabeli, ale wiersze są usuwane po ponownym uruchomieniu bazy danych. Drugi wariant nadaje się do danych tymczasowych, które można ponownie wygenerować.

Warunki utworzenia tabeli zoptymalizowanej pamięciowo

Przed wykonaniem polecenia z MEMORY_OPTIMIZED = ON baza danych musi być przygotowana do obsługi In-Memory OLTP. Dla tabel trwałych wymagany jest plik lub grupa plików typu memory-optimized data filegroup, dodana do bazy danych za pomocą polecenia ALTER DATABASE. W tej grupie SQL Server przechowuje pliki kontrolne i strumienie danych potrzebne do odtworzenia zawartości tabel po ponownym uruchomieniu. Sama obecność odpowiedniej składni w CREATE TABLE nie wystarcza, jeżeli baza nie ma wymaganej konfiguracji.

Definicja tabeli musi również spełniać ograniczenia właściwe dla In-Memory OLTP. Tabela zoptymalizowana pamięciowo wymaga klucza indeksu, a indeksy są definiowane w ramach instrukcji tworzenia tabeli. Można użyć między innymi indeksu hashowego oraz indeksu nieklastrowanego zoptymalizowanego pamięciowo. Indeks hashowy jest przydatny przede wszystkim przy wyszukiwaniu po równości, natomiast indeks nieklastrowany lepiej odpowiada zapytaniom wykorzystującym zakresy, sortowanie lub częściowe dopasowanie wartości. Dla indeksu hashowego trzeba określić oczekiwaną liczbę kubełków parametrem BUCKET_COUNT; jego wartość powinna uwzględniać przewidywaną liczbę unikatowych kluczy.

Trwałość danych, indeksowanie i wykonanie zapytań

Tabela pamięciowa jest przechowywana w pamięci, dlatego jej rozmiar musi być uwzględniony w planowaniu dostępnej pamięci dla instancji SQL Server. W przypadku tabeli SCHEMA_AND_DATA SQL Server zapisuje zmiany w dzienniku transakcji oraz utrzymuje pliki danych potrzebne do późniejszego odtworzenia stanu. Operacje zapisu mogą więc nadal powodować obciążenie dziennika i podsystemu wejścia-wyjścia, mimo że bieżąca obsługa wierszy odbywa się w pamięci. Zastosowanie tej opcji nie jest zatem równoznaczne z całkowitym wyeliminowaniem dysku ani z automatycznym przyspieszeniem każdego zapytania.

Największe korzyści pojawiają się w scenariuszach z dużą liczbą krótkich transakcji, częstymi aktualizacjami oraz wysoką współbieżnością. Tabela może ograniczyć narzut klasycznych blokad i zmniejszyć koszty operacji na stronach danych, ale zapytania nadal muszą korzystać z właściwych indeksów. Przy nieodpowiednim indeksie wyszukiwanie może wymagać odczytu wielu wierszy, a koszt kopiowania danych, sortowania lub łączenia tabel może pozostać istotny. Przed migracją należy porównać plany wykonania, czasy transakcji, obciążenie pamięci oraz zachowanie aplikacji w warunkach współbieżnego dostępu.

Ważne jest także rozróżnienie tabel pamięciowych od natywnie kompilowanych procedur składowanych. MEMORY_OPTIMIZED = ON dotyczy tabeli, lecz nie powoduje automatycznej kompilacji wszystkich zapytań korzystających z tej tabeli do kodu natywnego. Aplikacja może używać zwykłych zapytań Transact-SQL, jednak ich zgodność zależy od konkretnej wersji SQL Server i obsługiwanych konstrukcji. Ograniczenia typów danych, indeksów, kluczy obcych, wyzwalaczy oraz innych elementów schematu trzeba sprawdzić przed zmianą istniejącej tabeli.

Diagnostyka problemów z tabelą MEMORY_OPTIMIZED = ON

Jeżeli polecenie CREATE TABLE kończy się błędem, pierwszym krokiem jest sprawdzenie, czy wskazana baza danych ma skonfigurowaną grupę plików dla danych zoptymalizowanych pamięciowo oraz czy używana edycja i wersja SQL Server obsługuje wymagany mechanizm. Następnie należy zweryfikować pełną definicję tabeli: obecność indeksu, typ klucza, liczbę kubełków indeksu hashowego, typy kolumn, ograniczenia oraz wybraną wartość DURABILITY. Błąd może wynikać z pojedynczego elementu schematu, nawet gdy sama opcja pamięciowa jest prawidłowa.

Po utworzeniu tabeli warto obserwować wykorzystanie pamięci przez obiekty In-Memory OLTP, rozmiar dziennika transakcji, czas oczekiwania transakcji oraz liczbę konfliktów współbieżności. Szczególnej uwagi wymagają operacje aktualizacji i usuwania, ponieważ wersjonowanie wierszy może zwiększać zużycie pamięci do czasu wykonania przez SQL Server odpowiedniego odzyskiwania. W środowisku serwisowym analiza takiej konfiguracji obejmuje sprawdzenie definicji bazy, komunikatów SQL Server, planów zapytań i objawów zgłaszanych przez aplikację. Diagnostyka powinna potwierdzić, czy problem dotyczy konfiguracji In-Memory OLTP, niedopasowanego indeksu, braku pamięci, czy niezgodności użytej funkcji z daną wersją silnika.

W przypadku komputera wykorzystywanego do lokalnego uruchamiania SQL Server istotne jest również sprawdzenie ilości dostępnej pamięci RAM, stanu dysku oraz poprawności działania samej instancji. Warszawskie Pogotowie Komputerowe wykonuje profesjonalną diagnostykę i naprawę laptopów, komputerów PC oraz ich podzespołów, gdy problemy z konfiguracją środowiska wpływają na działanie oprogramowania bazodanowego.