Zalecane konfiguracje bazy danych

  • 8 min

Zanim można dostroić zapytania lub poradzić sobie z problemami współbieżności, potrzebna jest odpowiednia infrastruktura pod spodem. Usługa Azure SQL Database udostępnia dwa modele zasobów i wiele warstw usług. Wybrana kombinacja ustawia limit zasobów obliczeniowych, pamięci, operacji wejścia/wyjścia i przechowywania dla obciążenia. Wybierz zbyt mało, a wydajność się pogarsza. Wybierz zbyt dużo i marnujesz budżet.

Porównanie modeli zasobów

Usługa Azure SQL Database obsługuje dwa modele zasobów: rdzenie wirtualne i jednostki DTU. Mierzą zasoby i rozliczają się inaczej, więc zrozumienie różnicy pomaga dokonać właściwego wyboru od samego początku.

Model rdzeni wirtualnych zapewnia bezpośrednią kontrolę nad rdzeniami wirtualnymi, pamięcią i magazynem. Niezależnie wybierasz generację sprzętu, warstwę usług i warstwę obliczeniową. Jeśli przeprowadzasz migrację z lokalnego SQL Server, ten model jest mapowany bezpośrednio do fizycznego CPU i pamięci, co sprawia, że planowanie pojemności jest bardziej przejrzyste. Obsługuje również ceny wystąpień zarezerwowanych i korzyść użycia hybrydowego platformy Azure w celu uzyskania oszczędności kosztów.

Model DTU łączy procesor CPU, pamięć i operacje we/wy w jedną jednostkę o nazwie Jednostka transakcji bazy danych (DTU). Warstwy oparte na jednostkach DTU (Podstawowa, Standardowa i Premium) oferują wstępnie skonfigurowane pakiety zasobów. Ten model działa, gdy nie potrzebujesz szczegółowej kontroli nad poszczególnymi wymiarami zasobów.

W przypadku większości nowych wdrożeń firma Microsoft zaleca model rdzeni wirtualnych. Zapewnia on wyższe limity zasobów, większe stopień szczegółowości skalowania i większą elastyczność cenową.

Zrozumienie warstw usług w modelu vCore

Model vCore ma trzy warstwy usług: Ogólnego Przeznaczenia, Biznes Krytyczny i Hiperskala. Każda warstwa używa innej architektury, co wpływa na typ pamięci, wydajność operacji I/O i dostępność.

Ogólne przeznaczenie oddziela zasoby obliczeniowe i magazynowe. Silnik bazy danych działa na węźle obliczeniowym, a pliki danych znajdują się w usłudze Azure Blob Storage. Opóźnienie pamięci masowej zwykle wynosi od 5 do 10 milisekund. Ta architektura zapewnia przyjazne dla budżetu ceny i sprawdza się dobrze w przypadku większości obciążeń biznesowych. Jeśli węzeł obliczeniowy ulegnie awarii, usługa Azure Service Fabric przeniesie proces do węzła zapasowego i ponownie dołączy pliki magazynu zdalnego.

Weź pod uwagę aplikację do handlu elektronicznego z wprowadzenia. W normalnych godzinach pracy General Purpose obsługuje zamówienia bez problemu. Ale podczas wakacyjnej sprzedaży flash opóźnienie operacji wejścia/wyjścia od 5 do 10 milisekund może nie być wystarczająco szybkie dla procesu realizacji transakcji.

Krytyczne dla działania firmy integruje zasoby obliczeniowe i pamięć masową na każdym węźle. Zarówno aparat bazy danych, jak i pliki danych używają lokalnie dołączonych dysków SSD w grupie dostępności Always On z trzema replikami wtórnymi. Ten projekt zapewnia najmniejsze opóźnienie we/wy (średnio od 1 milisekundy do 2 milisekund), najwyższą liczbę operacji we/wy na sekundę (IOPS) i bezpłatną replikę tylko do odczytu, którą można wykorzystać do zapytań raportowych. Cena jest kompromisem, wynoszącym około 2,7 razy więcej niż w przypadku General Purpose przy tej samej liczbie vKor. Dla zespołu ds. handlu elektronicznego Business Critical ma sens, jeśli ich transakcje przy realizacji zamówienia wymagają spójnego opóźnienia poniżej 2 milisekund.

Hyperscale korzysta z odseparowanej architektury pamięci masowej z niezależnymi serwerami stron i wielowarstwową pamięcią podręczną. Obsługuje ona bazy danych do 128 TB, umożliwia zero do czterech replik wysokiej dostępności i skaluje obliczenia w górę lub w dół bez kopiowania danych. Opłaty są naliczane tylko za przydzielony magazyn, a nie maksymalny magazyn. Hiperskala usuwa praktyczne limity magazynowania i skalowania innych warstw i nadaje się do obsługi najróżniejszych obciążeń.

Poniższa tabela zawiera podsumowanie kluczowych różnic:

Funkcja Ogólnego przeznaczenia Krytyczne dla działania firmy Hiperskala
Typ magazynu Remote (Azure Blob Storage) Lokalny dysk SSD Oddzielone od lokalnej pamięci podręcznej SSD
Maksymalny rozmiar magazynu 4 terabajty 4 terabajty 128 TB
Maks. IOPS na rdzeń wirtualny 320 4,000 5500 (lokalny dysk SSD)
Repliki dostępności 1 (brak replik do odczytu) 3 + 1 replika do odczytu Od 0 do 4 (można skonfigurować)
Optymalne zastosowanie Obciążenia zorientowane na budżet Małe opóźnienie, wysokie I/O Duże bazy danych, elastyczne skalowanie

Wybieranie warstwy obliczeniowej

W modelu rdzeni wirtualnych można również wybrać między dwiema warstwami obliczeniowymi: aprowizowaną i bezserwerową.

Aprowizowane zasoby obliczeniowe przydzielają stałą liczbę rdzeni wirtualnych, które są dostępne niezależnie od poziomu obciążenia. Płacisz stałą stawkę godzinową. Ta warstwa pasuje do obciążeń ze spójnym lub przewidywalnym zużyciem zasobów, takim jak aplikacja do handlu elektronicznego, która przetwarza zamówienia przez cały dzień.

Obliczenia bezserwerowe automatycznie skaluje vCores na podstawie zapotrzebowania i rozlicza opłaty w przeliczeniu na sekundy za używane zasoby obliczeniowe. Gdy baza danych jest w stanie bezczynności, może automatycznie wstrzymać działanie i całkowicie wyeliminować koszty obliczeniowe, chociaż obecnie autopauza jest obsługiwana tylko w wersji przeznaczonej do ogólnych zastosowań. Przetwarzanie bezserwerowe samo w sobie jest dostępne zarówno w warstwach Ogólnego przeznaczenia i Hiperskali. Działa dobrze w środowiskach deweloperskich, narzędziach wewnętrznych lub aplikacjach z sporadycznymi ruchem.

Dopasuj konfigurację do obciążenia

Jak więc, znając już opcje, podejmiesz decyzję? Oceń obciążenie pod kątem następujących czynników:

  • Wymagania dotyczące opóźnienia: jeśli aplikacja wymaga opóźnienia operacji we/wy poniżej 2 milisekund, wybierz pozycję Krytyczne dla działania firmy. W przypadku umiarkowanej tolerancji na opóźnienia, General Purpose jest wystarczający.
  • Rozmiar magazynu: jeśli baza danych przekroczy 4 TB lub spodziewasz się szybkiego wzrostu, hiperskala jest jedyną opcją, która pomieści do 128 TB.
  • Obciążenia z dużą ilością odczytów: Biznes krytyczny obejmuje bezpłatną replikę tylko do odczytu. Hiperskala obliczeniowa obsługuje nazwane repliki dla elastycznego wyskalowania odczytów w poziomie.
  • Czułość kosztów: Ogólnego zastosowania z dostępem do aprowizowanej mocy obliczeniowej oferuje przewidywalne ceny. Przetwarzanie bezserwerowe w środowiskach Ogólnego przeznaczenia lub Hiperskali zmniejsza koszty przy sporadycznych obciążeniach.
  • Wymagania dotyczące dostępności: Business Critical zapewnia najwyższą odporność systemu dzięki trzem synchronicznym replikom i najszybszemu przełączeniu awaryjnemu. Hiperskala umożliwia skonfigurowanie liczby replik w celu zrównoważenia odporności z kosztami.

Wskazówka

Podczas migracji z lokalnego programu SQL Server użyj modelu rdzeni wirtualnych, ponieważ mapuje bezpośrednio na fizyczne procesory CPU i pamięć. Model jednostek DTU nie uwidacznia pojedynczych wymiarów zasobów, co sprawia, że planowanie pojemności jest trudniejsze w przypadku migracji.

Konfigurowanie ustawień na poziomie bazy danych

Wybrano warstwę i model obliczeniowy. Teraz przyjrzyj się samej bazie danych. Kilka ustawień ma wpływ na sposób, w jaki usługa Azure SQL Database obsługuje równoległość, optymalizację zapytań i odzyskiwanie. Te ustawienia można dostosować bez zmiany warstwy usługi.

Steruj równoległością przy użyciu MAXDOP

Maksymalny stopień równoległości (MAXDOP) określa, ile wątków procesora aparat przypisuje do pojedynczego zapytania. Usługa Azure SQL Database domyślnie ma wartość 8, która działa dla najszerszej gamy obciążeń. Przed wrześniem 2020 r. nowe bazy danych domyślnie miały wartość 0, nieograniczoną równoległość i spowodowały problemy. Pojedyncze zapytanie analityczne może zużywać każdy dostępny wątek, utrudniając proces realizacji płatności na CPU.

Możesz ustawić MAXDOP na poziomie bazy danych za pomocą ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP. Można również ustawić inną wartość dla replik pomocniczych, gdy obciążenia odczytu/zapisu i tylko do odczytu mają różne wymagania dotyczące współbieżności. W przypadku określonego zapytania użyj OPTION (MAXDOP) wskazówki. Jedna reguła: unikaj opcji MAXDOP 0 w środowisku produkcyjnym. Nieograniczona równoległość prowadzi do wyczerpania zasobów, przekroczenia limitu czasu zapytań i awarii aplikacji.

Pozwól automatycznemu dostrajaniu wykrywać regresje

Optymalizator zapytań nie zawsze wybiera najlepszy plan. Statystyki tracą na aktualności, rozkłady danych zmieniają się, a strategia, która była szybka wczoraj, staje się powolna dzisiaj. Automatyczne dostrajanie monitoruje wydajność zapytań i stosuje poprawki bez oczekiwania na powiadomienie.

Usługa Azure SQL Database obsługuje trzy opcje:

  • FORCE_LAST_GOOD_PLAN wykrywa regresje planu i wymusza poprzedni szybki plan. Włączone domyślnie.
  • CREATE_INDEX identyfikuje brakujące indeksy, tworzy je i weryfikuje poprawę. Wyłączone domyślnie.
  • DROP_INDEX usuwa nieużywane i zduplikowane indeksy. Wyłączone domyślnie. Unikatowe indeksy, w tym indeksy obsługujące klucz podstawowy i unikatowe ograniczenia, nigdy nie są usuwane.

Każda zmiana przechodzi przez okno weryfikacji, od 30 minut do 72 godzin w zależności od częstotliwości zapytań. Jeśli wydajność się pogorszy, zmiana zostanie automatycznie przywrócona.

ALTER DATABASE CURRENT
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON,
                      CREATE_INDEX = ON,
                      DROP_INDEX = OFF);

Pomyśl o aplikacji do handlu elektronicznego podczas sprzedaży wakacyjnej. Wzorce zapytań zmieniają się w miarę wzrostu popularności różnych stron produktów. FORCE_LAST_GOOD_PLAN przechwytuje te regresje automatycznie, więc zła zmiana planu o 2 rano nie spowalnia procesu realizacji zamówień, dopóki ktoś tego nie zauważy w poniedziałek rano. Prawdopodobnie chcesz pozostawić CREATE_INDEX i DROP_INDEX off, dopóki nie zostaną poddane przeglądowi.

Odblokowywanie funkcji optymalizatora przy użyciu poziomu zgodności

Każda baza danych ma poziom zgodności , który określa, które zachowania optymalizatora zapytań są dostępne. Nowe bazy danych w usłudze Azure SQL Database domyślnie mają poziom 170 lub najwyższy dostępny poziom. Każdy poziom odblokowuje zestaw funkcji inteligentnego przetwarzania zapytań (IQP):

  • Poziom 150: tryb wsadowy na magazynie wierszy, odroczona kompilacja zmiennych tabelarycznych, wbudowywanie skalarnej funkcji zdefiniowanej przez użytkownika (UDF).
  • Poziom 160: optymalizacja planu uzależnionego od parametrów (PSP), informacje zwrotne dotyczące szacowania kardynalności.
  • Poziom 170: opcjonalna optymalizacja planu parametrów.

Istniejące bazy danych mogą działać na niższym poziomie zgodności, ponieważ firma Microsoft nigdy nie uaktualnia tego ustawienia. Baza danych utworzona, gdy obowiązuje niższa wartość domyślna, utrzymuje oryginalny poziom. Jeśli na przykład utworzono usługę Azure SQL Database w 2024 r., baza danych będzie nadal na poziomie 160, jeśli poziom nie zostanie ręcznie zaktualizowany. Podobnie, jeśli zaimportowano bazę danych za pośrednictwem pliku BACPAC, poziom zgodności zaimportowanej bazy danych jest oparty na poziomie zgodności źródłowej bazy danych. Aby przejść w górę:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 170;

Nie zmieniaj tego ustawienia w sposób ślepy w środowisku produkcyjnym. Użyj Query Store, aby przechwycić punkt odniesienia wydajności na obecnym poziomie, zaktualizować w środowisku testowym i porównać wyniki. W przypadku regresji zapytania możesz wymusić użycie starego planu na czas dochodzenia.

Zmniejszenie nadmiernego rozrostu pamięci podręcznej planu za pomocą OPTIMIZE_FOR_AD_HOC_WORKLOADS

Aplikacja do handlu elektronicznego generuje zapytania wyszukiwania produktów z dziesiątkami kombinacji filtrów. Każdy unikatowy tekst zapytania pobiera własny skompilowany plan w pamięci podręcznej, nawet jeśli to zapytanie nigdy nie zostanie uruchomione ponownie. Wraz z upływem czasu pamięć podręczna planu wypełnia tysiące planów pojedynczego użycia, wypychając często wykonywane plany, które rzeczywiście mają znaczenie.

OPTIMIZE_FOR_AD_HOC_WORKLOADS rozwiązuje ten problem. Po włączeniu aparat przechowuje niewielki skompilowany wycink planu podczas pierwszego wykonania zamiast pełnego planu. Tylko wtedy, gdy to samo zapytanie jest uruchamiane po raz drugi, aparat kompiluje i buforuje pełny plan.

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

To ustawienie utrzymuje wydajną pamięć podręczną i gwarantuje, że plany dla najważniejszych zapytań pozostają w pamięci.

Omówienie przyspieszonego odzyskiwania bazy danych

Przyspieszone odzyskiwanie bazy danych jest zawsze włączone w usłudze Azure SQL Database. Nie można go wyłączyć i nie musisz tego robić. Przyspieszone odzyskiwanie bazy danych (ADR) przeprojektuje proces odzyskiwania, tak aby czas odzyskiwania pozostał stały niezależnie od liczby aktywnych transakcji uruchomionych w przypadku wystąpienia błędu. Zapewnia również natychmiastowe wycofywanie transakcji i agresywne obcinanie dziennika.

ADR przechowuje wersje wierszy w trwałym magazynie wersji (PVS) wewnątrz bazy danych, a nie w tempdb. W zależności od rozmiaru modyfikowanego wiersza wersje są przechowywane w wierszu na stronach danych lub poza wierszem w oddzielnej tabeli wewnętrznej. Obciążenia intensywnie korzystające z zapisu mogą prowadzić do większej liczby podziałów stron oraz wzrostu generacji dzienników, ponieważ rejestrowana jest każda wersja wiersza. Aby zminimalizować to obciążenie, zachowaj krótkie transakcje i zmniejsz niepotrzebne przerwane transakcje.

PVS dzieli przydzielone miejsce w bazie danych, więc rosnący PVS zmniejsza dostępną przestrzeń na Twoje dane. Aby monitorować obciążenie poza wierszem PVS, wykonaj zapytanie sys.dm_tran_persistent_version_store_stats i sprawdź kolumnę persistent_version_store_size_kb , która zgłasza tylko rozmiar wersji poza wierszami i nie zawiera wersji wierszy przechowywanych na stronach danych. Aby ustanowić punkt odniesienia podczas typowych obciążeń, porównaj te wartości z całkowitym rozmiarem bazy danych. Jeśli PVS znacznie wykracza poza ten plan bazowy, poszukaj długotrwałych transakcji lub wysokich współczynników przerwania, które opóźniają czyszczenie wersji.

Kluczowe wnioski

Usługa Azure SQL Database oferuje dwa modele zasobów, trzy warstwy usług i dwie warstwy obliczeniowe. Model vCore o ogólnym przeznaczeniu obejmuje większość obciążeń. Krytyczne dla działania firmy dodaje opóźnienie podrzędne 2 milisekund. Hiperskala usuwa limity magazynu i skalowania. Wewnątrz bazy danych, ustawienie MAXDOP 8 jest bezpiecznym domyślnym wyborem, automatyczne dostrajanie wychwytuje regresje planów, a zwiększenie poziomu zgodności odblokowuje najnowsze funkcje inteligentnego przetwarzania zapytań (IQP). Włącz OPTIMIZE_FOR_AD_HOC_WORKLOADS, aby utrzymać czystość pamięci podręcznej planu i monitorować użycie magazynu PVS z wykorzystaniem ADR sys.dm_tran_persistent_version_store_stats, szczególnie w scenariuszach z dużym obciążeniem zapisem. Następnie dowiesz się, jak poziomy izolacji i kontrola współbieżności wpływają na zapytania uruchomione wewnątrz tej infrastruktury.